Selecting columns whose name matches a regular expression in PostgreSQL

Selecting columns whose name matches a regular expression in PostgreSQL

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

Solutions/Answers:

Solution 1:

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 ‘a%’;

References

Related:  VIM: insert or delete data based on position