PostgreSQL duplicating rows with two column duplicates

Question

In PostgreSQL I am looking for an answer to the following problem. There are two columns providing data about 'start' and 'end', together with a 'date' column. Currently the date column only exists once with 'start' and 'end' being filled with possibilities.

I am looking for the possibility to create a 'start' and 'end' column with unique values, but with duplicating dates.

current:

id  date          start    end
1   2017-03-13    a        [null]
2   2017-03-14    [null]   a
3   2017-03-14    b        [null]
4   2017-03-16    [null]   b
5   2017-03-16    c        c

wish:

id  date          start    end
1   2017-03-13    a        [null]
2   2017-03-14    [null]   a
3   2017-03-14    b        [null]
4   2017-03-16    [null]   b
5   2017-03-16    c        [null]
6   2017-03-16    [null]   c

Anyone an idea?

Thanks for your reactie Mokadillion. When I do that I get the error `ERROR: could not create unique index "test_idx" DETAIL: Key (start, end)=(c, c) is duplicated.' This is what I do expect but do not see how it could resolve my issue. — aptroost
– aptroost, Commented Mar 15, 2017 at 11:00
as below, end is a keyword and will need to be double quoted, best use a different name. — Gurmokh
– Gurmokh, Commented Mar 15, 2017 at 13:00

Laurenz Albe · Accepted Answer · 2017-03-15 12:49:45Z

1

If I understood your problem correctly, and you want exactly one of start and "end" to be set, and the combination with date unique, you can do this:

ALTER TABLE tab
   ADD CHECK(start IS NULL AND "end" IS NOT NULL
          OR start IS NOT NULL AND "end" IS NULL);

CREATE UNIQUE INDEX ON tab (date, COALESCE(start, "end"));

answered Mar 15, 2017 at 12:49

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL duplicating rows with two column duplicates

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