Update date range in Postgres table

Question

I have table with dates:

select id,date date_ranges where range_id = 1;
1 2016-04-12
2 2016-04-13
3 2016-04-14

also i have an array:

example:

array('2016-04-11','2016-04-12','2016-04-13','2016-04-14','2016-04-15')

or

array('2016-04-13','2016-04-14','2016-04-15')

How can i insert new values from array to my table without changing existing table values? And if i have second array, how can i delete value 2016-04-12 from table?

Help plz, I need one query)

Start by providing proper information: Postgres version, table definition. — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 15, 2016 at 1:38

apolev · Accepted Answer · 2016-04-15 07:26:23Z

1

WITH current_values AS (
    SELECT generate_series('2016-04-13'::DATE, '2016-04-17'::DATE, '1 day')::DATE AS date
), 
deleted_values AS (
    DELETE FROM date_ranges WHERE date NOT IN (SELECT * FROM current_values) RETURNING id
)
INSERT INTO date_ranges ("date", range_id) 
    WITH new_values AS (
        SELECT new."date" 
        FROM current_values AS new 
        LEFT JOIN date_ranges AS old 
            ON old."date" = new."date" 
        WHERE old.id IS NULL
    ) 
    SELECT date, 1 FROM new_values;

edited Apr 15, 2016 at 7:26

user330315

answered Apr 15, 2016 at 7:11

apolev

862 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update date range in Postgres table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related