How to set NOT NULL to a column present in all the tables of my database at once?

Question

I'm working with PostgreSQL. I have to set the NOT NULL property on a column that exists in all the tables of my database. I know how to set in one table, using:

ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;

But I have about 400 tables in my database, so I need something to do it at once, does anybody the way?

Thank you in advance!

I'm not familiar with Postgres but if you can run a command to get the table names (like this; https://stackoverflow.com/questions/14730228/postgresql-query-to-list-all-table-names ) then you can simple paste them into Excel and use a formula there to have a row per table which includes each table name to make a SQL statement. — d219, Jun 30 '20 at 11:21

score 0 · Answer 1 · answered Jun 30 '20 at 10:53

Use psql's \gexec:

SELECT format(
          'ALTER TABLE %I.%I ALTER column_name SET NOT NULL;',
          table_schema,
          table_name
       )
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema') \gexec

Alternatively, you can run the statement, save the output to a script and execute that.

You can also write a DO statement with PL/pgSQL code that loops through the results of the query and uses EXECUTE to execute them as dynamic SQL.

score 0 · Accepted Answer · answered Jun 30 '20 at 11:26

As laurenz stated, you can achieve it by executing the ALTER commands in the DO statement like below,

DO $$
DECLARE
    selectrow record;
BEGIN
    FOR selectrow IN
        SELECT format(
                  'ALTER TABLE %I.%I ALTER %s SET NOT NULL;',
                  table_schema,
                  table_name,
                  'my_column'
               ) AS script
        FROM information_schema.tables
        WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
    LOOP
        EXECUTE selectrow.script
    END LOOP;
END;
$$;

How to set NOT NULL to a column present in all the tables of my database at once?

2 Answers2