Switch from mysql to postgres

1 files changed, 51 insertions, 0 deletions

diff --git a/sql/ddl/pgsql.sql b/sql/ddl/pgsql.sql
new file mode 100644
index 0000000..18c0661
--- /dev/null
+++ b/sql/ddl/pgsql.sql
@@ -0,0 +1,51 @@
+create table users
+(
+    id          serial primary key,
+    name        text not null,
+    pwd         text not null,
+    description text
+);
+
+create unique index users_name ON users(name);
+
+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,
+    months      smallint 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,
+    year        smallint not null,
+    month       smallint not null,
+    day         smallint not null,
+    category_id integer not null references categories,
+    user_id     integer not null references users
+);
+
+CREATE INDEX idx_single_date ON single_expenses(user_id, year, month);
\ No newline at end of file