4nrknsZK

1--CREATE DATABASE Sem_1 WITH ENCODING 'UTF-8';
2/*
3CREATE TYPE c_country AS ENUM ('Россия', 'Украина');
4
5CREATE TABLE city
6(
7	city_id SERIAL NOT NULL PRIMARY KEY,
8	name VARCHAR(50) NOT NULL,
9	country c_country
10);
11
12CREATE TABLE users (
13	user_id SERIAL NOT NULL PRIMARY KEY,
14	firstname VARCHAR(50) NOT NULL,
15	surname VARCHAR(50) NOT NULL,
16	city INT,
17	CONSTRAINT users_city_id_fk FOREIGN KEY (city)
18	REFERENCES city (city_id)
19);
20
21DROP TABLE category;
22
23CREATE TABLE IF NOT EXISTS category (
24	category_id SERIAL,
25	name VARCHAR(50) NOT NULL
26);
27
28ALTER TABLE category 
29	ADD PRIMARY KEY (category_id);
30
31CREATE TABLE IF NOT EXISTS post (
32	post_id SERIAL NOT NULL PRIMARY KEY,
33	title VARCHAR(255) NOT NULL,
34	category_id INT,
35	"text" TEXT,
36	CONSTRAINT post_category_id_fk FOREIGN KEY (category_id)
37	REFERENCES category (category_id)
38		ON DELETE CASCADE
39		ON UPDATE NO ACTION
40);
41*/
42
43
44--COPY city("name", country) FROM '/home/thefacetakt/Documents/projects/technotrack/db/data' 
45--	WITH (FORMAT CSV, DELIMITER ';')