Question 247050 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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 $$;
Tags:

3

Answers


  1. 0

    Qualify the ambiguous name

    ...  SELECT columns.table_name FROM information_schema.columns ...
    Login or Signup to reply.
  2. 0

    All object should be named unique

    in your case the name table_name is part of the information schema as name of the variaböe

    so rewrite it like this

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

    As already stated, you had an identifier conflict between the variable table_name and a column table_name in information_schema.columns.

    Apart from JGH’s suggestion to qualify it and nbk’s to rename it, it’s good to know that there’s also a special command in plpgsql that can change the behaviour: demo

    DO $anonymous_routine$
#variable_conflict error        --that's the default
#variable_conflict use_variable
#variable_conflict use_column   --if you enter all three, the last one will be used
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 $anonymous_routine$;

    Another way to qualify things in PL/pgSQL is through the use of labels useful when you do actually mean the variable: demo

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

    Note that FOR table_name isn’t considered ambiguous because it’s outside the context of that SELECT statement, where there’s only your variable available under that name.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search