Wrong CREAT TABLE syntax for Postgresql

Hi

I am using HeidiSQL as a GUI front end for Postgresql and it has been fantastic! Much easier to use that phpPgAdmin or other tools.

Rcently, had to move databases between servers and found some issues when using the Export database as SQL option. The problem is with CREATE TABLE code generated by HeidiSQL. The environment is Postgresql 12 on CentOS7.9 / Postgresql 14 on Ubuntu 22.04 for the servers and latest Heidisql 12.4 64bit on Windows 10

• A column with the SERIAL datatype cannot be migrated. In the CREATE TABLE code, The datatype is changed to BIGINT NOT NULL DEFAULT 'nextval(''"seq"''::regclass)' and since seq is not yet created, it throws an error.

• Any table with a user created index cannot be migrated. In the CREATE TABLE code, the indexes are created using inline index syntax rather than separate index creation statements. This doesn't seem to be supported by Postgresql according to stackoverflow.com /questions/6239657/can-you-create-an-index-in-the-create-table-definition

• A rather insidious problem is that any column with an expression as DEFAULT value is changed to the value of that expression as default. e.g. all my TIMESTAMPTZ columns with now() as default were changed to DEFAULT '2023-04-03 11:18:22.824666+00' :-(

I just wanted to know if these and other issues of this kind (DB specific CREATE codes) would be targeted in near future?

I have since fallen back to pg_dump / pg_restore but there is something I miss about the running progress bars :-)

Thanks in advance

Yours sincerely

Ashish Varman

I wrote the SQL export mainly for MySQL and MariaDB only, as this was the only supported server at that time. There are plenty of other DDL issues with MS SQL, SQLite and Interbase as well, and I'm not able to keep track for all of these. So, these incompatibilities you describe won't disappear in the near future. If at all I would recommend to use the export for data-only tasks, but not for exporting structure.