1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
pwd TEXT NOT NULL,
description TEXT
);
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
parent_id INTEGER REFERENCES categories,
user_id INTEGER NOT NULL REFERENCES users
);
CREATE INDEX parent_id
ON categories (parent_id);
CREATE INDEX user_id
ON categories (user_id);
CREATE TABLE const_expenses (
id SERIAL PRIMARY KEY,
description TEXT,
expense NUMERIC(10, 2) NOT NULL,
duration INTERVAL NOT NULL,
start DATE NOT NULL,
"end" DATE NOT NULL,
prev_id INTEGER REFERENCES const_expenses,
category_id INTEGER NOT NULL REFERENCES categories,
user_id INTEGER NOT NULL REFERENCES users
);
CREATE TABLE single_expenses (
id BIGSERIAL PRIMARY KEY,
description TEXT,
expense NUMERIC(10, 2) NOT NULL,
date DATE NOT NULL,
-- we need the cast to timestamp, because it is only considered immutable then
corr_month DATE GENERATED ALWAYS AS (DATE_TRUNC('month', date::timestamp)) STORED,
category_id INTEGER NOT NULL REFERENCES categories,
user_id INTEGER NOT NULL REFERENCES users
);
CREATE INDEX idx_single_month ON single_expenses (user_id, corr_month);
|
