How to view or modify the tables of a database in POSTGRESQL

This forum is for eXpress++ general support.
Message
Author
Diego Euri Almanzar
Posts: 155
Joined: Thu Nov 05, 2020 10:51 am
Location: DOMINICAN REPUBLIC

How to view or modify the tables of a database in POSTGRESQL

#1 Post by Diego Euri Almanzar »

I had problems on one of my two computers installing POSTGRESQL. Finally, I was able to get the installation on one of them.

Then, to get started on POSTGRESQL, run the MDIDEMO.EXE program. Obviously I uploaded the tables with DBFUPSIZE.EXE

The MDIDEMO.EXE program runs fine. My only doubt is how to modify the structure of a table, when it is already included in POSTGRESQL. Sometimes one adds more fields to the tables.

Search the Web for topics related to viewing or modifying tables in POSTGRESQL, and the information is almost non-existent, despite the fact that it is an everyday topic, since it is common that you do not want to modify the tables.

Can someone help me with the topic, how to view or modify the tables of a database, in POSTGRESQL?

Best regards

User avatar
Tom
Posts: 1165
Joined: Thu Jan 28, 2010 12:59 am
Location: Berlin, Germany

Re: How to view or modify the tables of a database in POSTGRESQL

#2 Post by Tom »

If you use the PGDBE with upsized ISAM tables, you can use all mechanisms known from other DBEs, like DbCreate(), OrdCreate(), DbExport() and others. This works similar to file based DBEs. It's the idea of PGDBE to use the same code, functions, methods and procedures. To be honest, DELETE FILE or ERASE don't work if you want to delete a table there. You must use DROP TABLE to erase a table which you want to create again with a different structure, if you want to use DbCreate().
Did you move the MDI-sample to PGDBE?

You may also use SQL, for instance ALTER TABLE, which is the command to change a table structure. This should work as long as indexes are not effected, from inside your app or from pgAdmin.
Best regards,
Tom

"Did I offend you?"
"No."
"Okay, give me a second chance."

User avatar
Tom
Posts: 1165
Joined: Thu Jan 28, 2010 12:59 am
Location: Berlin, Germany

Re: How to view or modify the tables of a database in POSTGRESQL

#3 Post by Tom »

Addition:

If you want to look at the upsized tables, install and open pgAdmin. Connect to the server, look for your database (it should be "MDIDEMO"). Find "Schemas", click on this, you should see "Public". Schemas are kind of folders in a PostGres database. By default, DbfUpsize creates all tables in "Public". If you click this, there will be a position "Tables" - here you find all the tables. If you right-click on a table name, you can view or edit data. If you double-click, you can see the indexes and other stuff. You may also change or add columns from here, but be careful if indexes are effected. This won't work with ISAM emulation.

pgAdmin is found here: https://www.pgadmin.org/download/
Best regards,
Tom

"Did I offend you?"
"No."
"Okay, give me a second chance."

Diego Euri Almanzar
Posts: 155
Joined: Thu Nov 05, 2020 10:51 am
Location: DOMINICAN REPUBLIC

Re: How to view or modify the tables of a database in POSTGRESQL

#4 Post by Diego Euri Almanzar »

Excellent, TOM,

Thanks a lot.

I have not done the tests of your recommendations, but I would like to continue taking advantage of your knowledge, before doing the tests.

I don't know anything about database. It is the first time that I enter that world, I have always used DBF. Therefore I will continue to use the ISAM model through PGDBE.
In that order of ideas, do you know if the index files uploaded to POSTGRESQL with DBUPSIZE can be updated through DBCREATEINDEX?

I mean:

USE Customer ALIAS Cust NEW EXCLUSIVE
DbCreateIndex("CUSTA", "CUSTNO", {|| CUSTNO }, .T. )

This updates the CUSTA index within POSTGRESQL?

Best regards

User avatar
Tom
Posts: 1165
Joined: Thu Jan 28, 2010 12:59 am
Location: Berlin, Germany

Re: How to view or modify the tables of a database in POSTGRESQL

#5 Post by Tom »

Hi, Diego.
This updates the CUSTA index within POSTGRESQL?
Yes, it does. The PGDBE allows to use tables like with DBF-based DBEs.

Take care about:

- Pathes in filenames (don't use them with the PGDBE)
- FErase/DELETE FILE/ERASE FILE - they don't work (use DROP TABLE instead), remove "Ferase(<cIndexName>)" before index creation
- OrdKeyNo() only works if there is no index on the file (which doesn't make any sense)
- FILTERS don't work in most of the cases, but very complex evaluations on workareas do. If you are in need of a complex filter, try to move it inside a DO WHILE !EoF()-loop and evaluate the expression there. Alaska is working on local filters for the PGDBE
- There are still some PDRs open for the PGDBE, but nothing that really hurts. The model works excellent in > 95 percent of the cases
- If you migrate from DBFCDX, read a little about OrdListAdd() and other functions for TAGless indexes
- The PGDBE is not able to auto-open indexes with a table. That comes later
- If you look for if a table exists or not, use the function "Table()" instead of "File()"

That's it.
Last edited by Tom on Tue Apr 05, 2022 1:32 am, edited 1 time in total.
Best regards,
Tom

"Did I offend you?"
"No."
"Okay, give me a second chance."

Diego Euri Almanzar
Posts: 155
Joined: Thu Nov 05, 2020 10:51 am
Location: DOMINICAN REPUBLIC

Re: How to view or modify the tables of a database in POSTGRESQL

#6 Post by Diego Euri Almanzar »

Hello Tom

With all this information you have given me, I have become an expert in Postgresql driver. Even, I already know how to use PGADMIN, and I have found the MDIDEMO demo tables, I have added new fields from PGADMIN, etc., thanks to your recommendations.

I only have 2 questions:

1 Any stranger, or anyone who is not an administrator, can enter the server and use PGADMIN.
How do I prevent strangers from using PGADMIN, can PGADMIN be configured to ask for a password?

2 I have not found examples of uploading indexes that have For statements, through DBFUPSIZE. And, in order to finish my work, I must upload 4 tables that have indexes that use For, example:

INDEX ON DtoS(InvDate)+PartNo TO InvB FOR InvDate >= CtoD("01/01/93")

How do I upload an index including the For statement, is there a way to upload the For statement?

Best regards.

User avatar
Tom
Posts: 1165
Joined: Thu Jan 28, 2010 12:59 am
Location: Berlin, Germany

Re: How to view or modify the tables of a database in POSTGRESQL

#7 Post by Tom »

Hi, Diego.

You can add a password for pgAdmin and a password for the server and one for the databases. You can create roles and manage access rights. Look at the PostGreSQL docs for this: https://www.postgresql.org/docs/14/index.html

The FOR statement is not supported by DbfUpsize, but by INDEX ON and OrdCreate.

I recommend to use DbfUpsize without indexes and create them later using INDEX ON/OrdCreate. DbfUpsize tends get slower the more indexes are to be created.
Best regards,
Tom

"Did I offend you?"
"No."
"Okay, give me a second chance."

Diego Euri Almanzar
Posts: 155
Joined: Thu Nov 05, 2020 10:51 am
Location: DOMINICAN REPUBLIC

Re: How to view or modify the tables of a database in POSTGRESQL

#8 Post by Diego Euri Almanzar »

Hello, Tom

Excellent, wonderful, thank you very much.

So, when the POSTGRESQL driver is used, are indexes created with INDEX ON, or ordcreate(), automatically added to the active table?

Diego Euri Almanzar
Posts: 155
Joined: Thu Nov 05, 2020 10:51 am
Location: DOMINICAN REPUBLIC

Re: How to view or modify the tables of a database in POSTGRESQL

#9 Post by Diego Euri Almanzar »

Hello, Tom

On the other hand, Alaska apparently fixed the filter issue in version 2.1559

The bears report the following:

Known Issues
Documentation:

The German product edition of Xbase++ 2.0 is shipped with English documentation.

PostgreSQL ISAM:

There are issues with complex filter expressions when using the PostgreSQL ISAM support. For example, the usage of a user-defined function in a filter expression may lead to an invalid or incorrect set of data.

Diego Euri Almanzar
Posts: 155
Joined: Thu Nov 05, 2020 10:51 am
Location: DOMINICAN REPUBLIC

Re: How to view or modify the tables of a database in POSTGRESQL

#10 Post by Diego Euri Almanzar »

Hello,Tom

I'm already working on the suggestions you gave me regarding the POSTGRESQL driver. First, I uploaded the DBFs alone, without the indexes, with DBFUPSIZE, then I reorganized the tables with INDEX ON. The result was not satisfactory, because the indexes were not created in POSTGRESQL.

The second, or the next, thing I did was upload the DBFs again with their respective CDX or orders, with DBFUPSIZE. The result was satisfactory, everything went well. In short, it is decisive for the PGDBE driver that tables and their indexes are uploaded with DBFUPSIZE.

So far everything has gone well for me. I notice that my software runs much faster with POSTGRESQL than with ADS, the table server.

Until now, I only have one problem, and it is the following: If I exit or cancel the program abruptly, the record that was locked at that time, is forever locked, until I turn off the computer.

I want it to unlock within a minute, in case of an abnormal system exit.

Is there any solution for that?

Post Reply