Get column names which do not match a particular ending string

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.

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:

• Determining the OID of a table in Postgres 9.1?