Remarks
본 글은 Learn PostgreSQL Tutorial - Full Course for Beginners (freeCodeCamp.org)을 참고하여 작성되었습니다.
1. Install
1.1 Docker run
#!/bin/bash
docker pull postgres:latest
docker run -d -p 5432:5432 --name postgres \
-e POSTGRES_PASSWORD="postgres" \
-e TZ="Asia/Seoul" \
-v /workspace/db/postgres:/var/lib/poostgresql/data \
postgres:latest
2. Run
$ docker exec --user='root' -it postgres bash
3. Connect to database
$ su - postgres
$ psql
$ psql test # connect to 'test' database
Otherwise,
$ docker exec -it postgres psql -U postgres
4. DDL
Notations
$: command in bash shell
#: command in postgres shell
CAPITAL: reserved words
Create database
CREATE DATABASE test;
Remove database
DROP DATABASE test;
List databases
$ psql -l
# \l
Connect to another database
# \c test
Create table
CREATE TABLE table_name (
column name + data type + constraints if any
)
CREATE TABLE person (
id int,
first_name VARCHAR(50),
last_name VARCHAR(50),
gender VARCHAR(7),
date_of_birth DATE
);
CREATE TABLE person (
id BIGSERIAL NOT NULL PRIMARY KEY, /* BIGSERIAL: autoincrementing 8 bytes integer */
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
gender VARCHAR(7) NOT NULL,
date_of_birth DATE NOT NULL,
email VARCHAR(150)
);
Delete table
# DROP TABLE test;
List tables
# \d /* with sequence */
# \dt /* without sequence */
Describe table
# \d person
Foreign keys
CREATE TABLE car(
id BIGSERIAL NOT NULL PRIMARY KEY,
make VARCHAR(100) NOT NULL,
model VARCHAR(100) NOT NULL,
price NUMERIC(19, 2) NOT NULL
);
CREATE TABLE person (
id BIGSERIAL NOT NULL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
gender VARCHAR(7) NOT NULL,
email VARCHAR(100),
date_of_birth DATE NOT NULL,
country_of_birth VARCHAR(50) NOT NULL,
car_id BIGINT REFERENCES car (id) UNIQUE
);
insert into car (id, make, model, price) values (1, 'Ford', 'Econoline E150', '11479.96');
insert into car (id, make, model, price) values (2, 'Mazda', 'MX-6', '58238.86');
insert into person (first_name, last_name, email, gender, date_of_birth, country_of_birth) values ('Marina', 'D''Ugo', 'mdugo0@github.com', 'Female', '9/3/2023', 'Burundi');
insert into person (first_name, last_name, email, gender, date_of_birth, country_of_birth) values ('Flori', 'Delaney', 'fdelaney1@themeforest.net', 'Female', '3/2/2023', 'Poland');
insert into person (first_name, last_name, email, gender, date_of_birth, country_of_birth) values ('Hildy', 'Heister', 'hheister2@shareasale.com', 'Female', '7/27/2023', 'Brazil');
test=# UPDATE person SET car_id = 3 WHERE id = 2;
ERROR: insert or update on table "person" violates foreign key constraint "person_car_id_fkey"
DETAIL: Key (car_id)=(3) is not present in table "car".
test=# DELETE FROM car WHERE id = 1;
ERROR: update or delete on table "car" violates foreign key constraint "person_car_id_fkey" on table "person"
DETAIL: Key (id)=(1) is still referenced from table "person".
On delete cascade
CREATE TABLE car(
car_uid UUID NOT NULL PRIMARY KEY,
make VARCHAR(100) NOT NULL,
model VARCHAR(100) NOT NULL,
price NUMERIC(19, 2) NOT NULL CHECK (price > 0)
);
CREATE TABLE person (
person_uid UUID NOT NULL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
gender VARCHAR(7) NOT NULL,
email VARCHAR(100) UNIQUE,
date_of_birth DATE NOT NULL,
country_of_birth VARCHAR(50) NOT NULL,
car_uid UUID REFERENCES car (car_uid) ON DELETE CASCADE
);
Constraints
Setting primary key when creating table
CREATE TABLE person (
id BIGSERIAL NOT NULL PRIMARY KEY
)
Add primary key to existing table
ALTER TABLE person ADD PRIMARY KEY (id);
Alter primary key constraints
test=# \d person
Table "public.person"
Column | Type | Collation | Nullable | Default
------------------+-----------------------+-----------+----------+------------------------------------
id | bigint | | not null | nextval('person_id_seq'::regclass)
Indexes:
"person_pkey" PRIMARY KEY, btree (id)
test=# ALTER TABLE person DROP CONSTRAINT person_pkey;
ALTER TABLE
test=# \d person
Table "public.person"
Column | Type | Collation | Nullable | Default
------------------+-----------------------+-----------+----------+------------------------------------
id | bigint | | not null | nextval('person_id_seq'::regclass)
Alter unique constraint
test=# ALTER TABLE person ADD CONSTRAINT person_email_key
UNIQUE (email);
ALTER TABLE
test=# ALTER TABLE person ADD UNIQUE (email);
ALTER TABLE
test=# \d person
Table "public.person"
Column | Type | Collation | Nullable | Default
------------------+-----------------------+-----------+----------+------------------------------------
id | bigint | | not null | nextval('person_id_seq'::regclass)
email | character varying(50) | | |
Indexes:
"person_pkey" PRIMARY KEY, btree (id)
"unique_email_address" UNIQUE CONSTRAINT, btree (email)
test=# ALTER TABLE person DROP CONSTRAINT unique_email_address;
ALTER TABLE
test=# \d person
id | bigint | | not null | nextval('person_id_seq'::regclass)
email | character varying(50) | | |
Indexes:
"person_pkey" PRIMARY KEY, btree (id)
Check constraints
test=# CREATE TABLE car(
id BIGSERIAL NOT NULL PRIMARY KEY,
price NUMERIC(19, 2) NOT NULL CHECK (price > 0)
);
test=# ALTER TABLE person ADD CONSTRAINT gender_constraint CHECK (gender = 'Female' OR gender = 'Male');
ALTER TABLE
test=# \d person
Table "public.person"
Column | Type | Collation | Nullable | Default
------------------+-----------------------+-----------+----------+------------------------------------
id | bigint | | not null | nextval('person_id_seq'::regclass)
email | character varying(50) | | |
gender | character varying(50) | | |
Indexes:
"person_pkey" PRIMARY KEY, btree (id)
"person_email_key" UNIQUE CONSTRAINT, btree (email)
Check constraints:
"gender_constraint" CHECK (gender::text = 'Female'::text OR gender::text = 'Male'::text)
Insert
Insert into table
INSERT INTO person (
first_name,
last_name,
gender,
date_of_birth)
VALUES ('Anne', 'Smith', 'FEMALE', DATE '1988-01-09');
INSERT INTO person (
first_name,
last_name,
gender,
date_of_birth,
email)
VALUES ('Jake', 'Jones', 'MALE', DATE '1990-01-10', 'jake@gmail.com');
On conflict
Do nothing
INSERT INTO person
VALUES (1, 'a', 'b', 'd', 'Male', DATE '1952-09-25', 'e')
ON CONFLICT (id) DO NOTHING;
Do update set
INSERT INTO person
VALUES (1, 'a', 'b', 'd', 'Male', DATE '1952-09-25', 'e')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;
Delete records
DELETE FROM person WHERE id = 1;
Update records
UPDATE person SET first_name = 'Omar', last_name = 'Montana', email = 'omar.montana@hotmail.com' WHERE id = 1;
BIGSERIAL
test=# SELECT * FROM person_id_seq;
last_value | log_cnt | is_called
------------+---------+-----------
3 | 30 | t
(1 row)
Export to CSV
test=# \copy (SELECT * FROM person LEFT JOIN car ON car.id = person.car_id) TO '/workspace/sql/results.csv' DELIMITER ',' CSV HEADER;
COPY 3
Data generator (mockaroo)
Execute SQL from file
# \i /workspace/sql/person.sql
5. DML
기본적으로는 MySQL query와 유사하므로, MySQL과 비교하여 주요한 특이점을 기술한다.
LIMIT, OFFSET, FETCH
SELECT * FROM table LIMIT 5; /* ID: [1, 5] */
SELECT * FROM table FETCH FIRST 5 ROWS ONLY; /* ID: [1, 5] */
SELECT * FROM table OFFSET 5 LIMIT 5; /* ID: [6, 10] */
LIKE
동일하게 wildcard (%, _) 사용가능
COUNT
COUNT(*) : number of input rows
COUNT(exp): number of input rows which the value of exp IS NOT NULL
COALESCE
Arguments를 받아 not null 값으로 채움
- MySQL의 IFNULL과 같이 사용할 수 있음 (Oracle: NVL)
SELECT COALESCE(1) as number; -- 1 SELECT COALESCE(1, 2) as number; -- 1 SELECT COALESCE(NULL, 2, 1) as number; -- 2 SELECT COALESCE(email, 'Email not provided');
NULLIF
SELECT NULLIF(a, b); -- NULL if a == b else a
SELECT 10 / 0; -- ERROR: division by zero
SELECT 10 / NULL; -- SUCCESS: NULL
SELECT 10 / NULLIF(2, 5); -- SUCCESS: 10/2 -> 5
SELECT COALESCE(10 / NULLIF(num, 0), 0); -- how to handle division by zero
Join
test=# SELECT * FROM person JOIN car ON person.car_id = car.id;
id | first_name | last_name | gender | email | date_of_birth | country_of_birth | car_id | id | make | model | price
----+------------+-----------+--------+---------------------------+---------------+------------------+--------+----+-------+----------------+----------
2 | Flori | Delaney | Female | fdelaney1@themeforest.net | 2023-03-02 | Poland | 1 | 1 | Ford | Econoline E150 | 11479.96
1 | Marina | D'Ugo | Female | mdugo0@github.com | 2023-09-03 | Burundi | 2 | 2 | Mazda | MX-6 | 58238.86
(2 rows)
test=# SELECT * FROM person LEFT JOIN car ON person.car_id = car.id;
id | first_name | last_name | gender | email | date_of_birth | country_of_birth | car_id | id | make | model | price
----+------------+-----------+--------+---------------------------+---------------+------------------+--------+----+-------+----------------+----------
2 | Flori | Delaney | Female | fdelaney1@themeforest.net | 2023-03-02 | Poland | 1 | 1 | Ford | Econoline E150 | 11479.96
1 | Marina | D'Ugo | Female | mdugo0@github.com | 2023-09-03 | Burundi | 2 | 2 | Mazda | MX-6 | 58238.86
3 | Hildy | Heister | Female | hheister2@shareasale.com | 2023-07-27 | Brazil | | | | |
(3 rows)
test=# SELECT * FROM person JOIN car ON person.car_id = car.id WHERE car.* IS NOT NULL;
test=# SELECT * FROM person FULL JOIN car ON person.car_id = car.id;
id | first_name | last_name | gender | email | date_of_birth | country_of_birth | car_id | id | make | model | price
----+------------+-----------+--------+---------------------------+---------------+------------------+--------+----+-------+----------------+----------
2 | Flori | Delaney | Female | fdelaney1@themeforest.net | 2023-03-02 | Poland | 1 | 1 | Ford | Econoline E150 | 11479.96
1 | Marina | D'Ugo | Female | mdugo0@github.com | 2023-09-03 | Burundi | 2 | 2 | Mazda | MX-6 | 58238.86
3 | Hildy | Heister | Female | hheister2@shareasale.com | 2023-07-27 | Brazil | | | | |
(3 rows)
Using
Key의 이름이 동일한 경우 사용가능.
test=# SELECT * FROM person;
person_uid | first_name | last_name | gender | email | date_of_birth | country_of_birth | car_uid
--------------------------------------+------------+-----------+--------+---------------------------+---------------+------------------+--------------------------------------
f3445c81-cf5f-456d-9f86-585ce34e618b | Hildy | Heister | Female | hheister2@shareasale.com | 2023-07-27 | Brazil |
ca77b3e2-8395-40df-9cdb-a937c876a06b | Marina | D'Ugo | Female | mdugo0@github.com | 2023-09-03 | Burundi | 7065ad29-0b1e-480b-b1bc-bd0793f6566a
460ac8f6-a8e2-4de9-bac7-a5e5d975170e | Flori | Delaney | Female | fdelaney1@themeforest.net | 2023-03-02 | Poland | 4f27958e-b3cb-4eae-a305-2c61791e63e8
(3 rows)
test=# SELECT * FROM car;
car_uid | make | model | price
--------------------------------------+-------+----------------+----------
7065ad29-0b1e-480b-b1bc-bd0793f6566a | Ford | Econoline E150 | 11479.96
4f27958e-b3cb-4eae-a305-2c61791e63e8 | Mazda | MX-6 | 58238.86
(2 rows)
test=# SELECT * FROM person JOIN car USING (car_uid);
car_uid | person_uid | first_name | last_name | gender | email | date_of_birth | country_of_birth | make | model | price
--------------------------------------+--------------------------------------+------------+-----------+--------+---------------------------+---------------+------------------+-------+----------------+----------
7065ad29-0b1e-480b-b1bc-bd0793f6566a | ca77b3e2-8395-40df-9cdb-a937c876a06b | Marina | D'Ugo | Female | mdugo0@github.com | 2023-09-03 | Burundi | Ford | Econoline E150 | 11479.96
4f27958e-b3cb-4eae-a305-2c61791e63e8 | 460ac8f6-a8e2-4de9-bac7-a5e5d975170e | Flori | Delaney | Female | fdelaney1@themeforest.net | 2023-03-02 | Poland | Mazda | MX-6 | 58238.86
(2 rows)
DATE
NOW()
test=# SELECT NOW();
now
-------------------------------
2023-10-11 09:40:23.326007+00
(1 row)
test=# SELECT NOW()::DATE;
now
------------
2023-10-11
(1 row)
test=# SELECT NOW()::TIME;
now
----------------
09:42:04.97737
(1 row)
INTERVAL
test=# SELECT NOW() - INTERVAL '10 YEARS';
?column?
-------------------------------
2013-10-11 09:45:41.177401+00
(1 row)
test=# SELECT NOW() - INTERVAL '10 MONTHS';
?column?
-------------------------------
2022-12-11 09:46:03.224482+00
(1 row)
test=# SELECT NOW() - INTERVAL '10 DAYS';
?column?
-------------------------------
2023-10-01 09:46:09.939125+00
(1 row)
test=# SELECT (NOW()::DATE + INTERVAL '10 MONTHS')::DATE;
date
------------
2024-08-11
(1 row)
Extract fields
- https://www.postgresql.org/docs/16/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT ``` test=# SELECT EXTRACT(YEAR FROM NOW()); extract ——— 2023 (1 row)
test=# SELECT EXTRACT(MONTH FROM NOW()); extract ——— 10 (1 row)
test=# SELECT EXTRACT(DAY FROM NOW()); extract ——— 11 (1 row)
test=# SELECT EXTRACT(HOUR FROM NOW()); extract ——— 9 (1 row)
test=# SELECT EXTRACT(MINUTE FROM NOW()); extract ——— 49 (1 row)
test=# SELECT EXTRACT(SECOND FROM NOW()); extract ———- 9.058259 (1 row)
test=# SELECT EXTRACT(DOW FROM NOW()); – 0(Sunday) to 6(Saturday) extract ——— 3 (1 row)
test=# SELECT EXTRACT(CENTURY FROM NOW()); extract ——— 21 (1 row)
## Age
test=# SELECT first_name, date_of_birth, AGE(NOW(), date_of_birth) age FROM person; first_name | date_of_birth | age —————-+—————+——————————– Marina | 2023-09-03 | 1 mon 8 days 09:59:09.89202 Flori | 2023-03-02 | 7 mons 9 days 09:59:09.89202 Hildy | 2023-07-27 | 2 mons 15 days 09:59:09.89202
# 6. Extensions
test=# SELECT * FROM pg_available_extensions; name | default_version | installed_version | comment ——————–+—————–+——————-+———————————————————————— pg_freespacemap | 1.2 | | examine the free space map (FSM) pg_walinspect | 1.1 | | functions to inspect contents of PostgreSQL Write-Ahead Log moddatetime | 1.0 | | functions for tracking last modification time adminpack | 2.1 | | administrative functions for PostgreSQL isn | 1.2 | | data types for international product numbering standards refint | 1.0 | | functions for implementing referential integrity (obsolete)
## UUID(Universally Unique IDentifier) data type
### UUID function
[https://www.postgresql.org/docs/current/uuid-ossp.html#UUID-OSSP-FUNCTIONS-SECT](https://www.postgresql.org/docs/current/uuid-ossp.html#UUID-OSSP-FUNCTIONS-SECT)
test=# CREATE EXTENSION IF NOT EXISTS “uuid-ossp”; CREATE EXTENSION
test=# \df List of functions Schema | Name | Result data type | Argument data types | Type ——–+——————–+——————+—————————+—— public | uuid_generate_v1 | uuid | | func public | uuid_generate_v1mc | uuid | | func public | uuid_generate_v3 | uuid | namespace uuid, name text | func public | uuid_generate_v4 | uuid | | func public | uuid_generate_v5 | uuid | namespace uuid, name text | func public | uuid_nil | uuid | | func public | uuid_ns_dns | uuid | | func public | uuid_ns_oid | uuid | | func public | uuid_ns_url | uuid | | func public | uuid_ns_x500 | uuid | | func (10 rows)
test=# SELECT uuid_generate_v4(); uuid_generate_v4 ————————————– d2acadb9-f68e-415f-9276-361cea7d9cb5 (1 row)
### UUID as primary keys
CREATE TABLE car( car_uid UUID NOT NULL PRIMARY KEY, make VARCHAR(100) NOT NULL, model VARCHAR(100) NOT NULL, price NUMERIC(19, 2) NOT NULL CHECK (price > 0) );
CREATE TABLE person ( person_uid UUID NOT NULL PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, gender VARCHAR(7) NOT NULL, email VARCHAR(100) UNIQUE, date_of_birth DATE NOT NULL, country_of_birth VARCHAR(50) NOT NULL, car_uid UUID REFERENCES car (car_uid) UNIQUE );
insert into car (car_uid, make, model, price) values (uuid_generate_v4(), ‘Ford’, ‘Econoline E150’, ‘11479.96’); insert into car (car_uid, make, model, price) values (uuid_generate_v4(), ‘Mazda’, ‘MX-6’, ‘58238.86’);
insert into person (person_uid, first_name, last_name, email, gender, date_of_birth, country_of_birth) values (uuid_generate_v4(), ‘Marina’, ‘D’‘Ugo’, ‘mdugo0@github.com’, ‘Female’, ‘9/3/2023’, ‘Burundi’); insert into person (person_uid, first_name, last_name, email, gender, date_of_birth, country_of_birth) values (uuid_generate_v4(), ‘Flori’, ‘Delaney’, ‘fdelaney1@themeforest.net’, ‘Female’, ‘3/2/2023’, ‘Poland’); insert into person (person_uid, first_name, last_name, email, gender, date_of_birth, country_of_birth) values (uuid_generate_v4(), ‘Hildy’, ‘Heister’, ‘hheister2@shareasale.com’, ‘Female’, ‘7/27/2023’, ‘Brazil’);
test=# UPDATE person SET car_uid = ‘7065ad29-0b1e-480b-b1bc-bd0793f6566a’ WHERE person_uid = ‘ca77b3e2-8395-40df-9cdb-a937c876a06b’; UPDATE 1
test=# UPDATE person SET car_uid = ‘4f27958e-b3cb-4eae-a305-2c61791e63e8’ WHERE person_uid = ‘460ac8f6-a8e2-4de9-bac7-a5e5d975170e’; UPDATE 1
test=# SELECT * FROM person; person_uid | first_name | last_name | gender | email | date_of_birth | country_of_birth | car_uid ————————————–+————+———–+——–+—————————+—————+——————+————————————– f3445c81-cf5f-456d-9f86-585ce34e618b | Hildy | Heister | Female | hheister2@shareasale.com | 2023-07-27 | Brazil | ca77b3e2-8395-40df-9cdb-a937c876a06b | Marina | D’Ugo | Female | mdugo0@github.com | 2023-09-03 | Burundi | 7065ad29-0b1e-480b-b1bc-bd0793f6566a 460ac8f6-a8e2-4de9-bac7-a5e5d975170e | Flori | Delaney | Female | fdelaney1@themeforest.net | 2023-03-02 | Poland | 4f27958e-b3cb-4eae-a305-2c61791e63e8 (3 rows)
test=# SELECT * FROM car; car_uid | make | model | price ————————————–+——-+—————-+———- 7065ad29-0b1e-480b-b1bc-bd0793f6566a | Ford | Econoline E150 | 11479.96 4f27958e-b3cb-4eae-a305-2c61791e63e8 | Mazda | MX-6 | 58238.86 (2 rows) ```