How do I list all columns for a specified table that begin with a specific string

Question

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

Answer 1

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