Likes table for a post - how to identify if the post was already liked by that user? [duplicate]

Question

I would like to set up a table in PostgreSQL such that two columns together must be unique. There can be multiple values of either value, so long as there are not two that share both.

For instance:

CREATE TABLE someTable (
    id int PRIMARY KEY AUTOINCREMENT,
    col1 int NOT NULL,
    col2 int NOT NULL
)

So, col1 and col2 can repeat, but not at the same time. So, this would be allowed (Not including the id)

1 1
1 2
2 1
2 2

but not this:

1 1
1 2
1 1 -- would reject this insert for violating constraints

Answer 1

CREATE TABLE someTable (
    id serial PRIMARY KEY,
    col1 int NOT NULL,
    col2 int NOT NULL,
    UNIQUE (col1, col2)
)

autoincrement is not postgresql. You want a integer primary key generated always as identity (or serial if you use PG 9 or lower. serial was soft-deprecated in PG 10).

If col1 and col2 make a unique and can't be null then they make a good primary key:

CREATE TABLE someTable (
    col1 int NOT NULL,
    col2 int NOT NULL,
    PRIMARY KEY (col1, col2)
)

Answer 2

Create unique constraint that two numbers together CANNOT together be repeated:

ALTER TABLE someTable
ADD UNIQUE (col1, col2)