How can I test if a column exists in a table using an SQL statement
PostgresqlInformation SchemaPostgresql Problem Overview
Is there a simple alternative in PostgreSQL to this statement produced in Oracle?
select table_name from user_tab_columns
where table_name = myTable and column_name = myColumn;
I am then testing whether the query returns anything so as to prove the column exists.
I am aware that using psql I can find these out individually but this is required to produce a result in a program I am writing to validate that a requested attribute field exists in my database table.
Postgresql Solutions
Solution 1 - Postgresql
Try this :
SELECT column_name
FROM information_schema.columns
WHERE table_name='your_table' and column_name='your_column';
Solution 2 - Postgresql
Accepted answer is correct, but is missing the schema and nicer output (True/False):
SELECT EXISTS (SELECT 1
FROM information_schema.columns
WHERE table_schema='my_schema' AND table_name='my_table' AND column_name='my_column');
Solution 3 - Postgresql
Simpler and SQLi-safe using PostgreSQL's object identifier types:
SELECT true
FROM pg_attribute
WHERE attrelid = 'myTable'::regclass -- cast to a registered class (table)
AND attname = 'myColumn'
AND NOT attisdropped -- exclude dropped (dead) columns
-- AND attnum > 0 -- exclude system columns (you may or may not want this)
System catalogs are many times faster than querying the notoriously convoluted information_schema
(but still just milliseconds for a single query). See:
Read about the significance of the columns in the manual.
While building dynamic SQL with the column name supplied as parameter, use quote_ident()
to defend against SQL injection:
...
AND attname = quote_ident('myColumn');
Works for tables outside the search_path
, too:
...
WHERE attrelid = 'mySchema.myTable'::regclass
...
Solution 4 - Postgresql
Unlike Oracle, PostgreSQL supports the ANSI standard INFORMATION_SCHEMA
views.
The corresponding standard view to Oracle's user_tab_columns is information_schema.columns
http://www.postgresql.org/docs/current/static/infoschema-columns.html
Solution 5 - Postgresql
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.
Solution 6 - Postgresql
Here is a similar variant of Erwin Brandstetter answer. Here we check schema too in case we have similar tables in different schema.
SELECT TRUE FROM pg_attribute
WHERE attrelid = (
SELECT c.oid
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE
n.nspname = CURRENT_SCHEMA()
AND c.relname = 'YOURTABLENAME'
)
AND attname = 'YOURCOLUMNNAME'
AND NOT attisdropped
AND attnum > 0