23Feb/074
PostgreSQL: Determine if a column exists or not.
Here's a quick query you can run to determine whether or not a particular column in a table exists or not:
SELECT attname FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'YOURTABLENAME') AND attname = 'YOURCOLUMNNAME';
Of course, replace YOURTABLENAME and YOURCOLUMNNAME with the proper values. If a row is returned, a column with that name exists, otherwise it does not.
July 31st, 2009 - 00:44
Thanks for this
November 4th, 2009 - 20:26
Another thank you for taking the time to post this.
May 6th, 2010 - 11:34
Useful, Thank you.
May 10th, 2010 - 07:00
This creates a function to check if a column exists
– check if a column exists
CREATE OR REPLACE FUNCTION column_exists(colname text, tablename text)
RETURNS boolean AS
$BODY$
DECLARE
q text;
onerow record;
BEGIN
q = ‘SELECT attname FROM pg_attribute WHERE attrelid = ( SELECT oid FROM pg_class WHERE relname = ”’||tablename||”’) AND attname = ”’||colname||”’ ‘;
FOR onerow IN EXECUTE q
LOOP
RETURN true;
END LOOP;
RETURN false;
END;
$BODY$
LANGUAGE ‘plpgsql’ VOLATILE