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.
Thanks for this 🙂
Another thank you for taking the time to post this.
Useful, Thank you.
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
Thanks for this
Wicked! Cheers