I have a schema in which all tables have the same basic set of columns. Now I want to query a specific column in all existing tables dynamically to get the distinct list of values of that column. The result should be a list consisting of all "table_name" and "unique_value" combinations (of that queried column in that table).
The result should be this (with the given test set below):
table_name | query_me
-------------------------
sql_test_1 | a
sql_test_1 | b
sql_test_1 | c
sql_test_2 | a
sql_test_2 | f
sql_test_2 | g
Important: I do not want to hard-code the table names I will query, I really want to query "all existing tables (with a given name scheme)".
I unfortunately have absolutely no clue how to do this in (Postgre)SQL. Can somebody help?
test case
CREATE TABLE IF NOT EXISTS public.sql_test_1 (
id serial PRIMARY KEY,
query_me varchar(10) NOT NULL,
val TEXT
);
CREATE TABLE IF NOT EXISTS public.sql_test_2 (
id serial PRIMARY KEY,
query_me varchar(10) NOT NULL,
val TEXT
);
INSERT
INTO
public.sql_test_1 (query_me, val)
VALUES
('a', 'hey'),
('b', 'ho'),
('c', 'silver'),
('a', 'let''s'),
('b', 'ride');
INSERT
INTO
public.sql_test_2 (query_me, val)
VALUES
('a', 'hey'),
('f', 'ho'),
('g', 'silver'),
('a', 'let''s'),
('f', 'ride');
expected result see above.