Repository navigation
Huge list of problems with PostgreSQL dumps #1100
Description
I think it needed to united all problems in one issue to not to create others because there's so many.
-
SHOW CREATE DATABASEerror showing for every (!) table in Postgres dump when Create Database option selected in SQL Export Table tools dialog, so it's unable to create dumps with this option. -
Getting an
"Invalid input syntax for integer nextval('albums_id_seq'::regclass)"error while import created dumps, so it's unable to import created dumps. The solution is to replace
"id" INTEGER NOT NULL DEFAULT 'nextval(''albums_id_seq''::regclass)'
with
"id" SERIAL
Postgres already supports it almost about 10 years.
- Postgres don't increase
AUTO_INCREMENTvalue when you insert data in new table, so you need to make this query after every table which have data to insert in dump file:
SELECT SETVAL('<table>_id_seq', (SELECT MAX(id) FROM "<table>"))
where "id" is table auto increment (serial) column.
Otherwise you can't insert new values in this tables. Yep, it's Postgres(
- Unable to create indexes in Postgres, because it creates as in MySQL now, not Postgres. Here're the queries to create differ type of indexes on existing tables:
ALTER TABLE "table" ADD UNIQUE ("column1", "column2"); -- UNIQUE KEY in MySQL
CREATE INDEX CONCURRENTLY ON "table" ("column1", "column2"); -- KEY in MySQL. You can't create it with ALTER on Postgres.
ALTER TABLE "table" ADD PRIMARY KEY ("column1", "column2"); -- The same in MySQL
Seems you need to add this queries to allow indexes creation in HeidiSQL and makes correct dumps with it (and in table indexes tab too).
CHECKconditions don't added in Postgres dumps, for example I need CHECK ("approve" >= 0 AND "approve" <= 1) condition, so it must be
"approve" smallint CHECK ("approve" >= 0 AND "approve" <= 1) NOT NULL DEFAULT 1,
in dump, but now it's ommited.
-
Can't open more than two instances of application even "Allow multiple application instances" option selected. Because sometimes I need even more than two instances opened.
-
It'll be great to add an option to truncate several table in tables list in SQL Export dialog. It can be useful when needed to dump some tables, but without unneeded data which was forgotted to truncate earlier. Today I need to close this dialog and go to truncate this tables and open this dialog again after.
And thanks a lot for the great application, indeed it have no alternatives even with working with Postgres and of course with MySQL!
Environment
- 11.0.0.6044
Activity
ansgarbecker commentedon Jul 23, 2020
Sames as in #118 - I should probably disable the structure export for PG at first, until I fixed most of these SQL differences.
pashagolub commentedon Sep 1, 2020
How about support for
pg_dump(https://www.postgresql.org/docs/12/app-pgdump.html), the native PostgreSQL utility? Most of administration tools (if not every) are using it internally, e.g. dbeaver, pgadmin etc.acirulis commentedon Mar 10, 2021
While not relaying on external tools (eg pg_dump) is exactly why HeidiSQL is so great, I agree with @pashagolub that in this case it might solve bunch of problems and still allow to use Heidi for PostgreSQL databases.
pashagolub commentedon Mar 10, 2021
Well, in fact, we are already using
libpqlibrary. I do not see any harm in adding one morepg_dumpthat serves the same purpose. If you do not like the concept of.exewe can use it as a library. I did it for https://github.com/pashagolub/PaGoDump-PaGoRestorearturm11 commentedon Mar 22, 2023
Note that SERIAL is not real type. Heidi tries to detect "autoincrement" column in MySQL flavor, PG is not very well supported. This is the reason.
arturm11 commentedon Mar 22, 2023
Updating sequence using MAX() probably will work but this is not right solution. Real sequence values should be dumped and restored separately as pg_dump does.
acuna-public commentedon Apr 7, 2023
arturm11 thanks, I understand it, but furtunately there's few in my project what I need to edit database structure, so I using Heidi only as nice browser, sometimes doing raw queries, so it's easier for me to make a query manually than to fight with unmaitained project. As for dumps - I'm simply leave only one "Create" tick in "Table" item in "Export database as SQL" dialog, so it allows me to create and deploy dumps well.