I have a table with a structure like this:
abc_col | abcd | ab_col | | | |
Some column names end in _col
and some do not. abc
or ab
or abcd
are random column name beginnings. I want to select the names of columns which do not end in _col
.
The function RIGHT()
can do this but due to some extended constraints I cannot use RIGHT()
. I want to form an SQL statement which does not involve any standard function of SQL for this problem.
By “standard functions” I mean functions like RIGHT()
, LEFT()
and so on. LIKE
or NOT LIKE
are fine.
Advertisement
Answer
You have to retrieve column names from the system catalogs or the information schema. Using the system catalog table pg_attribute
:
SELECT attname FROM pg_attribute WHERE attrelid = 'mytbl'::regclass AND attname NOT LIKE '%_col' AND NOT attisdropped -- no dropped (dead) columns AND attnum > 0; -- no system columns
More information in this related answer: