How can I test if a column exists in a table using an SQL statement

Question

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.

Answer 1

Try this :

SELECT column_name 
FROM information_schema.columns 
WHERE table_name='your_table' and column_name='your_column';

Answer 2

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');

Answer 3

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:

• Get column names and data types of a query, table or view

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
...

Answer 4

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

Answer 5

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.

Answer 6

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