Adam Johnson has an excellent post which suggests a management command to migrate legacy PostgreSQL serial columns to identity columns.
Unfortunately, when I tried to use it on my first database, I got a permission error when running the cursor.execute(find_serial_columns, (like,)) line:
django.db.utils.ProgrammingError: permission denied for schema pg_toast
I guess I could try to change the database user to temporarily use a PostgreSQL admin account just to run the script. However, I wonder if someone has an idea about writing a find_serial_columns query which allows less-privileged accounts to collect serial columns of a database.
I couldn’t find much information around about pg_toast, it isn’t exactly covered well in the PostgreSQL docs: PostgreSQL: Search results . But I guess it’s a schema implicitly created by PostgreSQL for large value storage.
I did find this SO answer:
Using the pg_class table to limit the search to ordinary and partitioned tables sounds legitimate to me.
Here’s an updated version of the query that does so:
# Adapted from: https://dba.stackexchange.com/a/90567
find_serial_columns = """\
SELECT
a.attrelid::regclass::text AS table_name,
a.attname AS column_name
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
WHERE
a.attrelid::regclass::text LIKE %s
AND c.relkind IN ('r', 'p') /* regular and partitioned tables */
AND a.attnum > 0
AND NOT a.attisdropped
AND a.atttypid = ANY ('{int,int8,int2}'::regtype[])
AND EXISTS (
SELECT FROM pg_attrdef ad
WHERE
ad.adrelid = a.attrelid
AND ad.adnum = a.attnum
AND (
pg_get_expr(ad.adbin, ad.adrelid)
=
'nextval('''
|| (
pg_get_serial_sequence(a.attrelid::regclass::text, a.attname)
)::regclass
|| '''::regclass)'
)
)
ORDER BY a.attnum
"""
``
Can you try using that? I haven’t tried to reproduce your issue locally, since I'm not quite sure how to do so. But if this works for you then I can update the post.