0

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user2966197
  • 2,793
  • 10
  • 45
  • 77
  • 1
    Are you talking about column *names* or *values*. You write about column names like those were values. Provide example code (even if it isn't working) and, as *always*, your version of Postgres. Also, define "standard function of SQL". – Erwin Brandstetter Jul 31 '14 at 03:15
  • I am talking about column names and not values. By standard function I meant that I do not want to use any function like `RIGHT()` or `LFET()` to do the job. – user2966197 Jul 31 '14 at 15:38
  • So, `NOT LIKE` qualifies as not one of those functions? – Erwin Brandstetter Jul 31 '14 at 15:51
  • No NOT LIKE does not qualifies. I am trying NOT LIKE to see if everything runs correct – user2966197 Jul 31 '14 at 16:11

2 Answers2

2

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:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

You should find information_schema

SELECT table_name, 
       column_name, 
       REPLACE(column_name, '_1', '') 
FROM   information_schema.columns 
WHERE  column_name LIKE '%_col' 
       AND table_name = 'matches' 
       AND table_schema = 'public';
David Buck
  • 3,752
  • 35
  • 31
  • 35
Manish Samariya
  • 1
  • 1