How to check if a column exists in a table using SQL statement

Question

Is there a simple alternative in PostgreSQL for this statement created in Oracle?

select table_name from user_tab_columns where table_name = myTable and column_name = myColumn; 

Then I check if the query returns anything to prove that the column exists.

I know that with psql I can find them separately, but it is necessary to get the result in the program I am writing to verify that the requested attribute field exists in my database table.

Answer 1

Try the following:

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

Answer 2

The accepted answer is correct, but there is no scheme and a more pleasant conclusion (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

This is simpler (and SQLi-safe) with PostgreSQL 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) 

Learn about the meaning of the columns in the manual .

If you are creating dynamic SQL and the column name is specified as a parameter, you can use quote_ident() to avoid SQL injection:

 ... AND attname = quote_ident('myColumn'); 

Works for tables outside search_path too:

 ... WHERE attrelid = 'mySchema.myTable'::regclass ... 

Answer 4

 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 correct values. If a row is returned, a column with that name exists, otherwise it is not.

Answer 5

Unlike Oracle, PostgreSQL supports the standard INFORMATION_SCHEMA ANSI views.

The corresponding standard Oracle view user_tab_columns is equal to information_schema.columns

http://www.postgresql.org/docs/current/static/infoschema-columns.html

Answer 6

Here's a similar answer from Erwin Brandstreter. Here we also check the schema if we have similar tables in different schemas.

 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