migrations/006_create_votes_table.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

CREATE TABLE votes (
    id SERIAL PRIMARY KEY,
    post_id INT REFERENCES posts(id) ON DELETE CASCADE,
    comment_id INT REFERENCES comments(id) ON DELETE CASCADE,
    user_id INT NOT NULL,
    inc INT NOT NULL CHECK (inc in (-1, 1)),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT vote_pkey UNIQUE NULLS NOT DISTINCT (post_id, comment_id, user_id),
    CHECK ((post_id IS NOT NULL AND comment_id IS NULL) OR (comment_id IS NOT NULL AND post_id IS NULL))
);

CREATE TRIGGER set_timestamp
BEFORE UPDATE ON votes
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();

---- create above / drop below ----

DROP TABLE votes;