I have a table x(x_id, ts), where ts is a timestamp. And I have a second table y(y_id, day, month, year), which is supposed to have its values from x(ts). (Both x_id and y_id are serial) For example:
x y
_x_id_|__________ts__________ _y_id_|_day_|_month_|__year__
1 | '2019-10-17 09:10:08' 1 17 10 2019
2 | '2019-01-26 11:12:02' 2 26 1 2019
However, if on x I have 2 timestamps on the same day but different hour, this how both tables should look like:
x y
_x_id_|__________ts__________ _y_id_|_day_|_month_|__year__
1 | '2019-10-17 09:10:08' 1 17 10 2019
2 | '2019-10-17 11:12:02'
Meaning y can't have 2 rows with the same day, month and year. Currently, the way I'm doing this is:
INSERT INTO y(day, month, year)
SELECT
EXTRACT(day FROM ts) AS day,
EXTRACT(month FROM ts) AS month,
EXTRACT(year FROM ts) AS year
FROM x
ORDER BY year, month, day;
However, as you probably know, this doesn't check if the timestamps share the same date, so how can I do that? Thank you for your time!
Add a UNIQUE constraint on table y to prevent adding the same date twice.
CREATE UNIQUE INDEX CONCURRENTLY y_date
ON y (year,month,day)
Then add it to y:
ALTER TABLE y
ADD CONSTRAINT y_unique_date
UNIQUE USING INDEX y_date
Note that you'll get an SQL error when the constraint is violated. If you don't want that and just ignore the INSERT, use a BEFORE INSERT trigger, returning NULL when you detect the "date" already exists, or just use ON CONFLICT DO NOTHING in your INSERT statement, as hinted by @Belayer.