Postgresql – Removing all columns with given name

dynamic-sqlplpgsqlpostgresqlpostgresql-9.3

I am converting a MSSQL schema to PostgreSQL and in that schema most tables have a column called Timestamp that are of MSSQL timestamp datatype which is effectively rowversion.

When inserting records into those tables you do not need to specify values for timestamp columns as MSSQL auto updates that column. I believe this is the same as xmin in PG.

So now when my app tries to insert into the table PG blows up saying it needs a value for the Timestamp column.

I'm therefore thinking after importing the schema writing something that drops all columns from my tables that are called Timestamp and have timestamp datatype.

I have found I can get column and table info from pgclass and pgattribute however I"m now stuck how to loop and do checks and then drop columns. Could someone point me in the right direction please?

select
    t.relname,
    a.attname,
    d.typname
from
  pg_class t
  INNER JOIN pg_attribute a 
  on a.attrelid = t.oid
  INNER JOIN pg_type d
  on d.oid = a.atttypid
  where relkind='r' and attname = 'timestamp' and d.typname = 'timestamp'
ORDER BY t.relname

Best Answer

For a one-time use, you don't need to persist a function. Use a DO statement:

DO
$do$
DECLARE
   rec record;
BEGIN
FOR rec IN
   SELECT a.attrelid::regclass::text AS tbl, a.attname
   FROM   pg_class c
   JOIN   pg_attribute a ON a.attrelid = c.oid
   WHERE  c.relkind = 'r'
   AND    a.attname = 'timestamp'
   AND    a.atttypid = 'timestamp'::regtype
   AND    a.attnum > 0
   AND    NOT a.attisdropped
LOOP
   RAISE NOTICE '%', format('ALTER TABLE %s DROP COLUMN %I', rec.tbl, rec.attname);
   -- Check test output before uncommenting EXECUTE!
   -- EXECUTE format('ALTER TABLE %s DROP COLUMN %I', rec.tbl, rec.attname);
END LOOP;
END
$do$;

Major points

  • As mentioned at the top, probably no need for a function. The body of a DO statement is identical, default language is plpgsql.

  • Use the implicit cursor of a FOR loop. Simpler and faster. Explicit cursors are rarely necessary in plpgsql.

  • Table names are not unique in a Postgres database. There can be any number of tables with the same name in multiple schemas.

  • Avoid SQL injection and basic exceptions with non-standard (double quoted) identifiers.

    • A cast to regclass takes care of the table name. At the same time, table names are schema-qualified automatically where necessary (taking the current search_path into account).
    • format() with %I sanitizes the column names.

    Details:

  • I assume you are aware of timestamp and timstamptz data types? This only removes columns with data type timestamp [without time zone].

  • Identify the data type with a.atttypid = 'timestamp'::regtype, so you don't have to join to pg_type. Simpler.

  • Exclude system columns and dead columns:
    AND a.attnum > 0 AND NOT a.attisdroppedand

Related Question