6

How can I select only those columns whose name matches a regular expression in PostgreSQL?

For example, how do I select only the columns whose name begins with 'A' in the following table, without explicitly enumerating them in the select list?

id  A1 A2 A3 A4 A5 B
1   a  b  c  d  e  f
2   g  h  i  j  k  l
DKS
  • 63
  • 1
  • 4
  • 2
    Short answer: you can't. You could do this with dynamic SQL and a function if you really need to do this. But these kind of "requirements" often hide a different problem, such as a wrong data model. Numbered columns where the name means something do raise a red flag. –  Feb 20 '15 at 22:39
  • 1
    @a_horse_with_no_name: You're right: I would have designed the schema differently. But given the schema this would be very useful. – DKS Feb 20 '15 at 23:14

1 Answers1

5

You will need to write a dynamic sql('select '||colname||' from (yourtable)') to accomplish this and dynamic sql should have supplied column names from the following sql:

SELECT column_name 
    FROM information_schema.columns 
    WHERE table_name = '<your table>' 
    AND column_name LIKE '<beginning of column name>%';
brunerm99
  • 35
  • 6
Somnath Bandopadhyay
  • 66
  • 4