Will a column be dropped if I deleted it from information_schema.columns?

Question

I use Postgres 14. I know about ALTER TABLE DROP COLUMN. But this option doesn't really work in my case.

Are these two queries equal:

ALTER TABLE <some_table_1>
    DROP COLUMN IF EXISTS <column_1>,
    DROP COLUMN IF EXISTS <column_2>;
ALTER TABLE <some_table_2>
    DROP COLUMN IF EXISTS <column_1>,
    DROP COLUMN IF EXISTS <column_2>;

And

DELETE FROM information_schema.columns
WHERE table_name IN (<some_table_1>, <some_table_2>)
AND column_name IN (<column_1>, <column_2>)

Or ALTER TABLE does some extra work? I want to use DELETE FROM because I really need filters like WHERE in columns deletion.

Answer 1

You cannot DELETE rows from any view in the information schema. That would be nonsense for multiple reasons. For the record, views in the information schema are not part of the system catalogs. But you don't mess with system catalogs directly, either - even if that might be possible. One false move and you can break your database (cluster). Use dedicated DDL commands.

You are looking for dynamic SQL - which can be based on either: information schema or system catalogs. Each has pros and cons. See:

• Get column names and data types of a query, table or view

My implementation uses system catalogs:

CREATE OR REPLACE PROCEDURE public.my_column_drop(_tbls text[]
                                                , _cols text[]
                                                , _schema text = 'public')
  LANGUAGE plpgsql AS
$proc$
DECLARE
   _tbl regclass;
   _drops text;
BEGIN
   FOR _tbl IN
      SELECT c.oid
      FROM   pg_catalog.pg_class c
      WHERE  c.relkind = 'r'  -- only plain tables (?)
      AND    c.relnamespace = _schema::regnamespace
      AND    c.relname = ANY (_tbls)
   -- more filters HERE
   LOOP
   -- RAISE NOTICE '%', _tbl;
      SELECT INTO _drops
             string_agg(format('DROP COLUMN IF EXISTS %I', a.attname), ', ')
      FROM   pg_catalog.pg_attribute a
      WHERE  a.attrelid = _tbl
      AND    a.attname = ANY (_cols)
      AND    NOT a.attisdropped
      AND    a.attnum > 0
   -- more filters HERE
      ;

      IF _drops IS NOT NULL THEN
      -- RAISE NOTICE '%',  concat_ws(' ', 'ALTER TABLE', _tbl, _drops);
         EXECUTE concat_ws(' ', 'ALTER TABLE', _tbl, _drops);
      ELSE
         RAISE NOTICE 'Table % has no candidate columns', _tbl;
      END IF;
   END LOOP;
END
$proc$;

Call:

CALL public.my_column_drop ('{some_table_1,some_table_2}', '{column_1, column_2}');

The attempt to drop columns fails if there are any dependencies. My simple function does not check for those. You would have to define what to check for, and what to do in case of dependencies ...

Procedures were added with Postgres 11. You can do the same with a function in older versions. Or with a DO command for one-off use in any version. See:

• Do stored procedures run in database transaction in Postgres?

Basics for this kind of dynamic SQL:

• Table name as a PostgreSQL function parameter
• Define table and column names as arguments in a plpgsql function?
• How to check if a table exists in a given schema

Adding IF EXISTS seems like overkill after we checked that the column exists. Only makes sense if multiple transactions might manipulate columns concurrently, which seems like an extremely odd case.