Say I have a table table1, I can get a list of all columns of, say, the double precision type with:
SELECT column_name
FROM information_schema.columns
WHERE data_type = 'double precision'
AND table_name = 'table1';
How can I select just these columns?
This question is similiar to SELECT * but * should also recognize type.
CREATE TABLE table1(ID SERIAL,col1 INT, col2 double precision,
col3 VARCHAR(100), col4 double precision);
INSERT INTO table1(col1,col2,col3,col4)
VALUES (1,2.0,'a',4.0), (10, 3.0, 'b', NULL);
In my opinion the right way is just to specify column names in SELECT:
SELECT col2, col4
FROM table1;
To make this easier you could write simple query that will generate column list for you:
;WITH cte(data_type, tab_name) AS
(
SELECT 'double precision'::text, 'table1'::text
)
SELECT *
,FORMAT('SELECT %s FROM %s',
COALESCE((SELECT string_agg(column_name, ',')
FROM information_schema.columns
WHERE data_type = c.data_type
AND table_name = c.tab_name), '*')
,c.tab_name) AS result_query
FROM cte c;
Output:
╔═══════════════════╦═══════════╦══════════════════════════════╗
║ data_type ║ tab_name ║ result_query ║
╠═══════════════════╬═══════════╬══════════════════════════════╣
║ double precision ║ table1 ║ SELECT col2,col4 FROM table1 ║
╚═══════════════════╩═══════════╩══════════════════════════════╝
Of course you can play with dynamic sql or even wrap it with function, but I would not go this path.
