In Postgres 12, how to change PK columns into IDENTITY using dynamic SQL?

Question

So I migrated my database from SQLite to Postgres 12 using pgloader 3.4.1, and for some reason the PK columns across all tables aren't sequential/auto-increment. They're indexed as NOT NULL (correct) and bigint or int (correct), but they don't contain a default value, so I need to manually change them to IDENTITY type.

However, I need to leave alone some varchar PK columns.

So far, I've tried this in psql:

do
$$
declare
  l_rec record;
  l_sql text;
  l_table text;
begin
  for l_rec in select table_schema, table_name, column_name, data_type, is_nullable
               from information_schema.columns
               where data_type in ('bigint', 'integer')
                 and is_nullable = 'NO' 
                 and is_generated = 'NO'
                 and is_identity = 'NO'
  loop
    l_sql := format('alter table %I.%I alter %I add generated always as identity', 
                     l_rec.table_schema, 
                     l_rec.table_name, 
                     l_rec.column_name);                 
    execute l_sql;
    l_table := concat(quote_ident(l_rec.table_schema), '.', quote_ident(l_rec.table_name));
    l_sql := format('select setval(pg_get_serial_sequence(%L, %L), max(%I)) from %I.%I', 
                    l_table, 
                    quote_ident(l_rec.column_name), 
                    l_rec.column_name, 
                    l_rec.table_schema, 
                    l_rec.table_name);
    execute l_sql;
  end loop;
end;  
$$
;

It spit out "DO," so I assume it must have worked, but when I use \d table_name to view the schema, it still doesn't have a default value.

Help please?

Was the table you are looking at part of the initial SELECT? Maybe the columns aren't defined as NOT NULL? — , Jul 11 '20 at 06:12
Hey @a_horse_with_no_name, the columns are definitely defined as NOT NULL, even before I run my dynamic SQL. Not sure what else I can do? — Hmong Bong, Jul 13 '20 at 20:29
well, you need to debug the code. Check if the `select` returns the columns you expect it to. Print out the generated SQL (using `raise`) instead of running it and run it manually to see if any errors happen, that you don't see. Are you properly committing everything? — , Jul 13 '20 at 20:30
@a_horse_with_no_name How do I use raise? Apologies, I'm very new to dynamic SQL. — Hmong Bong, Jul 13 '20 at 21:48
Put a `raise notice '%', l_sql;` immediately before the `execute` lines (and run `set client_min_messages=notice;` before you run the whole block). For more details please refer to the [PL/pgSQL language reference](https://www.postgresql.org/docs/current/plpgsql.html) Did you run the SELECT statement on its own and see what it returns? — , Jul 14 '20 at 08:47

score 0 · Accepted Answer · answered Jul 18 '20 at 19:06

0

The error lies in this line:

is_generated = 'NO'

is_generated only takes "ALWAYS" or "NEVER" as values.

I was lucky enough to catch that in the Postgres documentation.

Hope this helps someone else!

answered Jul 18 '20 at 19:06

Hmong Bong

33
6

In Postgres 12, how to change PK columns into IDENTITY using dynamic SQL?

1 Answers1