CODEWITHSUNDEEP
sqlpostgresqldate
C0deGen
C0deGen

Reputation: 85

Update date range in Postgres table

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)

Upvotes: 1

Views: 330

Answers (1)

apolev
apolev

Reputation: 86

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;

Upvotes: 1

Related Questions