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.
Try this :
SELECT column_name
FROM information_schema.columns
WHERE table_name='your_table' and column_name='your_column';
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]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');
SELECT true as exists FROM information_schema.columns WHERE ...;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
...
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
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.
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
