Removing all columns with given name

Question

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

Answer 1

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:

  • SQL injection in Postgres functions vs prepared queries

• 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

• Jon: I'm completely new to PostgreSQL, 2 days in so far and learning as I go. Thanks for the help

Answer 2

Well I seem to have got it working like this:

DROP FUNCTION somefuncname();
CREATE OR REPLACE FUNCTION somefuncname() RETURNS void LANGUAGE plpgsql AS $$
DECLARE
 tablename VARCHAR(500);
 columnname varchar(500);
 curs1 CURSOR FOR 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;
BEGIN
 FOR myrecordvar IN curs1 LOOP 
 RAISE NOTICE E'Table Name:%',myrecordvar.relname;
 EXECUTE 'ALTER TABLE "' || myrecordvar.relname || '" DROP COLUMN ' || myrecordvar.attname;
 END LOOP;
END
$$;
SELECT somefuncname();