How to select all the columns whose names begins with "img" from a PostgreSQL table?

I wrote that, but doesn't work

select 'img%' from mytable
  • 1
    Column's name begins with img or the content of this columns begins with img? – McNets May 17 '18 at 15:10
  • Hi, the name of the columns needs to begin with img (I don't mind the content). I have many columns named img1, img2, img3... Thank you – ilFonta May 17 '18 at 15:17

You can use INFORMATION_SCHEMA views for this purpose:

create table tbl 
    id serial,
    img1 text,
    img2 text,
    foo int,
    bar int,
    img14 text
select table_catalog, table_name, column_name
from   information_schema.columns
where  table_catalog = 'postgres'
and    table_name = 'tbl'
and    column_name like 'img%';
table_catalog | table_name | column_name
:------------ | :--------- | :----------
postgres      | tbl        | img1       
postgres      | tbl        | img2       
postgres      | tbl        | img14      

db<>fiddle here

| improve this answer | |
  • I supposed it was easier. The result I expected was keeping only the columns named img1, img2, img3 (until img227) from my table, dropping all the others. Something like select img1, img2, img3, ..., img227 from mytable. Thank you very much anyway. – ilFonta May 17 '18 at 15:53
  • You could build a dynamic query using the returned column's name. – McNets May 17 '18 at 15:55
  • It's the third day I'm into PostgreSQL. It's too much for me right now. Thank you very much – ilFonta May 17 '18 at 16:04

Not the answer you're looking for? Browse other questions tagged or ask your own question.