+2

Primary Keys not detected from Postgres dumps

stephen 3 years ago 0

I recently did a postgres schema dump (pg_dump and pgAdmin 4) from my existing database (postgres 12). For primary keys the dump adds ALTER TABLE statements [1] and when processed for import I get an error about Unknown SQL statement [2]. As a result not only don't I get the primary keys but all the foreign keys are skipped as well since there are no primary keys. If I manually modify the SQL DDL output to specify PRIMARY KEY on the columns in create table statement then it works fine.

I would expect SqlDBM to automatically handle the default format of postgres schema dumps for something as essential as primary keys. If this isn't possible, is there a way to request a supported format from pg_dump/pgAdmin?

[1] ALTER TABLE ONLY public.users ADD CONSTRAINT users_pkey PRIMARY KEY (user_id);

[2] Warning: Unknown SQL statement: 'ALTER TABLE ONLY public.users': line 506. The statement will be skipped.

I did try removing the ONLY on the ALTER TABLE to no effect.