78

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.

GEOCHET
  • 21,119
  • 15
  • 74
  • 98
CSharpened
  • 11,674
  • 14
  • 52
  • 86

6 Answers6

145

Try this : 

SELECT column_name 
FROM information_schema.columns 
WHERE table_name='your_table' and column_name='your_column';
Ramandeep Singh
  • 5,063
  • 3
  • 28
  • 34
  • Thanks. Accepted as answer as it is a direct alternative to what I was doing in Oracle. – CSharpened Apr 03 '12 at 10:13
  • @CSharpened what is the query for Oracle? – Evgeny Apr 18 '13 at 19:41
  • 1
    This is an *extremely* fragile answer - e.g,. if a table called `your_table` appears in a `schema` that is higher up in `search_path`. My PostGIS database has monthly schema, each with identical table names; using this answer, `vicmap201208.address` would be found before `vicmap201910.address` because `vicmap201208` appears before `vicmap201910` on `search_path` (for good reasons that I wouldn't want to change just to make a bad answer work). [As it happens, the column names are constant across schemas: that's not the point] – GT. Dec 03 '19 at 05:27
56

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');
juan Isaza
  • 3,646
  • 3
  • 31
  • 37
25

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
...
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
5

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

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

aleroot
  • 71,077
  • 30
  • 176
  • 213
  • Thanks for the response. Although your example works fine I have opted to accept Ramandeeps answer due to its simplicity and the fact that it seems a more direct alternative to my issue. – CSharpened Apr 03 '12 at 10:14
2

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
user2434435
  • 127
  • 5