Let's say I have these tables

CREATE TABLE primaryTable (
  primary_table_id bigserial primary key
);

CREATE TABLE tableOne (
  table_a_id bigserial primary key,
  primary_table_id bigint,
  date_added timestamp
);

CREATE TABLE tableTwo (
  table_b_id bigserial primary key,
  primary_table_id bigint,
  date_added timestamp
);

CREATE TABLE tablelThree (
  table_three_id bigserial primary key,
  primary_table_id bigint,
  date_added timestamp
);

This is just a gross oversimplication of the tables I have, but as you can see, the last 3 tables have the same columns date_added (other than the foreign key). The only thing that connects them all is the foreign key to primaryTable. I wanna get all of date_added values that exist in any of those tables, how should I do it?

The only solution I have right now is to do it on the backend-side. I create 3 different SQL statements that select the column from each of those tables and then filter the results by either storing them on a hash map or doing another for loop on an array to filter out the same timestamp. But I don't feel it'd be a best solution & I'm afraid it'd be annoying if I have another table with the date_added column that I wanna get in the future. Is there any way I can do it on the database-side?

Another thing to consider is I only wanna get the month & year part like using this query select to_char(date_added, 'MM-YYYY') as month_added. I don't need to get the whole timestamps.

I'm doing this on postgresql.

CodePudding user response：

I wanna get all of date_added values that exist in any of those tables

Wouldn’t a simple UNION meet your requirements?

SELECT to_char(date_added, 'MM-YYYY') as month_added
FROM tableOne
UNION
SELECT to_char(date_added, 'MM-YYYY') as month_added
FROM tableTwo
UNION
SELECT to_char(date_added, 'MM-YYYY') as month_added
FROM tableThree