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

postgresql information-schema
CSharpened picture CSharpened · Apr 3, 2012 · Viewed 84k times · Source

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

Ramandeep Singh picture Ramandeep Singh · Apr 3, 2012

Try this : 

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

Related questions

List all tables in postgresql information_schema

What is the best way to list all of the tables within PostgreSQL's information_schema? To clarify: I am working with an empty DB (I have not added any of my own tables), but I want to see every table …

Read More
How to check if a table exists in a given schema

Postgres 8.4 and greater databases contain common tables in public schema and company specific tables in company schema. company schema names always start with 'company' and end with the company number. So there may be schemas like: public company1 company2 company3 ... …

Read More
Finding columns that are NOT NULL in PostgreSQL

I had an assignment for each table to count nullable columns. Easy: SELECT table_name, count(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE is_nullable='NO' GROUP BY table_name; Now I have to modify this to count "columns that have property "…

Read More