How to add PK on every id column in Postgres

Question

I have an issue. I have database backup for existing DB let's call it dev_db. I recovered it but under a different name like master_db and backup how I understood has only tables, columns types and etc but it hasn't system information and tables like pg_constrains table and etc that's why I lost all my PK.

So, my first question can I update all tables in my database with PK for "id" column, is it enough? (if yes how to perform that) And my second question is how can I create a full backup of the necessary database with all system information?

Answer 1

You can generate the SQL to add the primary key with:

select concat('ALTER TABLE ',table_schema,'.',table_name,' ADD CONSTRAINT ',
 table_schema,'.pk_',table_name,' PRIMARY KEY (id)')
from information_schema.columns
where column_name ='id'
and table_schema='xxx';

Replace the xxx with the schema for which you want to create the primary keys. You only need to write the output to a file and check and execute it.