printing multiple combined tables if date is equal

linux_beginner 0
Viewed 10

Using postgres 14.2
I have identical table for each currency, below example for eur and usd. 
CREATE TABLE IF NOT EXISTS DOLLAR
(
    id                      serial PRIMARY KEY,
    updateTime              timestamp,
    //some more columns
);  

CREATE TABLE IF NOT EXISTS EURO
(
    id                      serial PRIMARY KEY,
    updateTime              timestamp,
    //some more columns
);

Now I need 2 things:

  1. select all records that have same value under updateTime and then print them both (paired).

In other words (pseudo code):

foreach e in EURO
  if (exist d in DOLLAR) and (d.updateTime == e.updateTime)
    print (e,d)
  1. Print all rows from EURO that are not printed above

To make things easier updateTime is unique in particular table

sql postgresql
1 Answers

I have identical table for each currency

This should probably be single table with a currency column. If so, you'd do the same thing but as a self-join.

select all records that have same value under updateTime and then print them both (paired).

Inner join the tables on updateTime.

select updateTime, dollar.id, euro.id
from dollar
join euro on dollar.updateTime = euro.updateTime

Note that timestamp is accurate to microseconds, so you might want to truncate it. If you do this, you'll need to change your unique index to include the truncation.

CREATE UNIQUE INDEX dollar_updateTime_seconds_idx ON dollar(date_trunc('second', updateTime);
CREATE UNIQUE INDEX euro_updateTime_seconds_idx ON euro(date_trunc('second', updateTime);

Print all rows from EURO that are not printed above

Do a left excluding join. This is a left join on euro, so all euro columns are included, but then exclude those which have no matching dollar row.

select euro.*
from euro
left join euro on dollar.updateTime = euro.updateTime
where dollar.id is null
Schwern0
Related