sqlpostgresqlplpgsqlinformation-schema

How to update type of all columns with the same name in all tables in one query in Postgres?

I'd like to assign type timestamp without timezone to all columns name created_at at my database scheme. I'm trying to run this query:

DO $$
DECLARE
  table_name text;
BEGIN
  FOR table_name IN SELECT table_name FROM information_schema.columns WHERE column_name = 'created_at' AND table_schema = 'public' LOOP
    EXECUTE 'ALTER TABLE ' || table_name || ' ALTER COLUMN created_at TYPE timestamp without time zone;';
  END LOOP;
END $$;

but I'm getting this error:

ERROR:  column reference "table_name" is ambiguous
LINE 1: SELECT table_name FROM information_schema.columns WHERE colu...
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  SELECT table_name FROM information_schema.columns WHERE column_name = 'created_at' AND table_schema = 'public'
CONTEXT:  PL/pgSQL function inline_code_block line 5 at FOR over SELECT rows
SQL state: 42702

Guys, also please note, that if you want to edit it not in the public, but, say, audit, schema, change it accordingly:

DO $$
DECLARE
  table_name text;
BEGIN
  FOR table_name IN SELECT columns.table_name FROM information_schema.columns WHERE column_name = 'created_at' AND table_schema = 'audit' LOOP
    EXECUTE 'ALTER TABLE audit' || table_name || ' ALTER COLUMN created_at TYPE timestamp without time zone;';
  END LOOP;
END $$;
Solution

  • Qualify the ambiguous name

    ...  SELECT columns.table_name FROM information_schema.columns ...