This question already has an answer here:

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

marked as duplicate by Evan Carroll postgresql May 17 '18 at 17:36

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 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

  • 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.