Add primary key to all tables in database that don't have one yet

user_709339 0
Viewed 413

Original question

In my PostgreSQL database, I have about 400 tables that currently lack a PRIMARY KEY (which leads to numerous problems in different applications).

I want to fix that now. I don't want to repeat the following 400 times:

ALTER TABLE table_schema.table_name ADD COLUMN ID SERIAL PRIMARY KEY;

Instead I found a way to find all tables that currently don't have any PRIMARY KEY:

select tab.table_schema, tab.table_name
from information_schema.tables tab
left join information_schema.table_constraints tco 
          on tab.table_schema = tco.table_schema
          and tab.table_name = tco.table_name 
          and tco.constraint_type = 'PRIMARY KEY'
where tab.table_type = 'BASE TABLE'
      and tab.table_schema not in ('pg_catalog', 'information_schema')
      and tco.constraint_name is null
order by table_schema,
         table_name

This results in something like this:

[table_schema]  [table_name]
"abc"           "grid_100m_20151127"
"abc"           "grid_100m_20190220"

... plus 400 more rows

Question: How can I combine the two queries so that the results of the SELECT query are used in the ALTER query?

Complete solution

@JimJones answer below resulted in the following solution:

DO $$
DECLARE row RECORD;
BEGIN
  FOR row IN 
    SELECT tab.table_schema, tab.table_name
    FROM information_schema.tables tab
    LEFT JOIN information_schema.table_constraints tco 
          ON tab.table_schema = tco.table_schema
          AND tab.table_name = tco.table_name 
          AND tco.constraint_type = 'PRIMARY KEY'
    WHERE tab.table_type = 'BASE TABLE'
          AND tab.table_schema not in ('pg_catalog', 'information_schema')
          AND tco.constraint_name is null
    ORDER BY table_schema, table_name 
    LOOP 
        EXECUTE 'ALTER TABLE "' || row.table_schema || '"."' || row.table_name  || '" ADD COLUMN PRIMARY_KEY SERIAL PRIMARY KEY';
    END LOOP;  
END;
$$;

Thank you so much for helping me out with this one!

postgis plpgsql postgresql
1 Answers

Have you tried using a LOOP inside an anonymous code block?

DO $$
DECLARE row RECORD;
BEGIN
  FOR row IN SELECT tab.table_schema, tab.table_name ... LOOP 
    EXECUTE 'ALTER TABLE "' || row.table_schema || '"."' || row.table_name  || '" ADD COLUMN PRIMARY_KEY SERIAL PRIMARY KEY';
  END LOOP;
END;
$$;

Place your query between FOR row IN and LOOP.

I've dealt with a similar issue in another answer.

EDIT: The following syntax suggested by @filiprem looks also very neat (seem comments) :)

EXECUTE format('ALTER TABLE %I.%I ADD id serial PRIMARY KEY', table_schema, table_name);
user_22753880
Related