SQL Syntax

Lexical Structure

식별자와 키워드
1. select, from : 키워드
2. "select" : ""로 감싼 식별자. U&"d\0061t\+000061"처럼 유니코드 코드 포인트를 이용할 수 있다
3. table_name : Unquoted 식별자. 대소문자를 구별하지 않는다
  select라는 이름의 컬럼을 가져오려면 select "select" from table_name;처럼 이용한다. 그로지 말자...
상수
1. 문자열 상수 : '문자열'
2. 달러 인용 문자열 상수 : 달러 인용 문자열은 이스케이프되지 않는다
  ↓ sql
  $$Dianne's horse$$, $SomeTag$Dianne's horse$SomeTag$ CREATE OR REPLACE FUNCTION change_inaba_name() RETURNS TRIGGER AS $$ BEGIN NEW.status_text = REPLACE(NEW.status_text, 'イナバ', '이나바'); RETURN NEW; END; $$ LANGUAGE plpgsql;

Value Expressions

열 참조

↓ sql

-- correlation은 테이블 이름이나 alias correlation.columnname

위치 매개변수 : 함수 정의의 매개변수 위치를 이용해 변수 참조

↓ sql

-- 문자열을 2번 반복해서 반환하는 함수 CREATE OR REPLACE FUNCTION test(text) RETURNS text AS $body$ SELECT $1 || $1 $body$ LANGUAGE sql;

배열 요소 참조

↓ sql

-- subscript는 integer 값을 내는 표현식 expression[subscript] expression[lower_subscript:upper_subscript]

필드 선택

↓ sql

-- 특정 행의 열 참조. expression은 row type 또는 열집합 expression.fieldname -- 일반적으로 expression은 괄호로 감싸져야 하지만, 테이블이나 위치 매개변수의 경우 생략할 수 있다 -- .*로 모든 필드를 얻을 수 있다

집계 함수 호출

집계 함수(aggregate function)란 여러 입력 행들을 하나의 결과행으로 줄이는 함수
집계 함수 호출 형태
↓ sql
aggregate_name (expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ] -- default로 ALL이므로 아래와 동일 aggregate_name (ALL expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ] aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ] -- ↓ 보통 count에서만 사용되는 형태 aggregate_name ( * ) [ FILTER ( WHERE filter_clause ) ] -- expression은 집계 함수나 윈도우 함수 호출을 포함하지 않아야 한다 aggregate_name ( [ expression [ , ... ] ] ) WITHIN GROUP ( order_by_clause ) [ FILTER ( WHERE filter_clause ) ] -- 복수의 인자를 받는 집계 함수에서는 ORDER BY 절이 전체 인자가 끝나고 등장해야 한다 SELECT string_agg(str, ',' ORDER BY str) FROM table; -- 실제 호출 예 SELECT count(*) AS unfiltered, count(*) FILTER (WHERE num < 5) AS filtered FROM generate_series(1,10) AS num;
내장 집계 함수 목록

윈도우 함수 호출

윈도우 함수는 집계 함수와 비슷하지만, 입력 행들이 결과에 그대로 나타난다
윈도우 함수 호출 형태
↓ sql
function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER window_name function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition ) function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER window_name function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )
window_name ::= WINDOW 절로 정의된 윈도우 이름
OVER window_name과 OVER (window_name)은 다르다. 후자의 경우 윈도우 정의를 복사하여 적용하며, frame 절을 포함하는 경우 수행되지 않는다
window_definition ::=
↓ sql
[ existing_window_name ] -- PARTITION BY 옵션은 행 그룹을 만든다. GROUP BY와 달리 출력-열이나 숫자를 지정할 수 없다 [ PARTITION BY expression [, ...] ] -- ORDER BY 절도 마찬가지로 출력-열이나 숫자를 지정할 수 없다 [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] [ frame_clause ]
frame_clause

frame_clause는 윈도우 함수가 전체 파티션이 아닌, 현재까지의 파티션을 대상으로 계산을 수행하게 한다

{ RANGE | ROWS } frame_start -- default로 frame_end는 CURRENT ROW
{ RANGE | ROWS } BETWEEN frame_start AND frame_end
↑ UNBOUNDED PRECEDING으로 지정된 frame_start는 파티션의 첫 행, UNBOUNDED FOLLOWING으로 지정된 frame_end는 파티션 마지막 행을 의미한다

frame_start, frame_end
1. UNBOUNDED PRECEDING
2. CURRENT ROW
3. UNBOUNDED FOLLOWING
4. value PRECEDING
5. value FOLLOWING
  ↑ value PRECEDING, FOLLOWING은 ROWS 모드에서만 사용 가능하고, 현재 행을 전후로 하여 프레임 크기를 설정한다
frame_exclusion
1. EXCLUDE CURRENT ROW
2. EXCLUDE GROUP
3. EXCLUDE TIES
4. EXCLUDE NO OTHERS
expression은 윈도우 함수 호출을 포함하지 않아야 한다
내장 윈도우 함수 목록

Collation Expressions

↓ sql

-- 로캐일에 따른 정렬 SELECT * FROM tbl WHERE ... ORDER BY a COLLATE "C"; -- 결과 정렬 SELECT * FROM tbl WHERE a > 'foo' COLLATE "C"; -- 입력 정렬 SELECT * FROM tbl WHERE a COLLATE "C" > 'foo'; -- 입력 정렬 SELECT * FROM tbl WHERE (a > 'foo') COLLATE "C"; -- 에러. 연산 결과인 boolean은 non-collatable

LC_COLLATE를 설정하여 기본 로캐일에 따른 정렬을 수행하게 할 수 있다

데이터베이스 최초 생성 시 LC_COLLATE 변수 값을 'C'로 설정
기존 데이터베이스를 dump하고, LC_COLLATE를 'C'로 설정한 데이터베이스를 만들어 복구
↓ sql
db_name=# DROP DATABASE [db_name]; db_name=# CREATE DATABASE [db_name] LC_COLLATE 'C'; -- 데이터베이스는 생성될 때 기본적으로 template1 데이터베이스를 복제하여 생성된다. 하지만 이는 로캐일이 이미 설정되어 충돌하여 에러가 발생할 수 있다 db_name=# CREATE DATABASE [db_name] TEMPLATE template0 LC_COLLATE 'C';

Scalar Subqueries : 한 행 또는 한 컬럼을 반환하는, 괄호에 감싸인 SELECT 쿼리

↓ sql

SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name) FROM states;

배열 생성자

↓ sql

SELECT ARRAY[1,2,3+4]; SELECT ARRAY[1,2,22.7]::integer[]; -- 빈 배열도 형식은 필요 SELECT ARRAY[]::integer[]; SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]]; SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%'); CREATE TABLE arr(f1 int[], f2 int[]); INSERT INTO arr VALUES (ARRAY[[1,2],[3,4]], ARRAY[[5,6],[7,8]]);

행 생성자

↓ sql

SELECT ROW(1,2.5,'this is a test'); SELECT ROW(t.*, 42) FROM t; -- t는 행 값 SELECT ROW(t.*, 42) FROM (SELECT ROW(1,2.5,'this is a test')) as t; CREATE TABLE mytable(f1 int, f2 float, f3 text); CREATE OR REPLACE FUNCTION getf1(mytable) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL; -- No cast needed since only one getf1() exists SELECT getf1(ROW(1,2.5,'this is a test')); CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric); CREATE OR REPLACE FUNCTION getf1(myrowtype) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL; -- Now we need a cast to indicate which function to call: SELECT getf1(ROW(1,2.5,'this is a test')::mytable); SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS myrowtype));

Expression Evaluation Rules

SELECT true OR somefunc();와 SELECT somefunc() OR ture; 모두 somefunc()가 호출되지 않는다. short-circuit 연산과는 다르다
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
SELECT CASE WHEN x > 0 THEN x ELSE 1/0 END FROM tab;
주의. IMMUTABLE 함수는 값이 먼저 평가되기 때문에, 실제로 x가 항상 0보다 크더라도 1/0이 미리 평가되어 에러가 발생한다xfunc-volatility

함수 호출

↓ sql

-- PostgreSQL의 함수의 인자는 이름과 위치 둘 다로 참조할 수 있다 CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false) RETURNS text AS $$ SELECT CASE WHEN $3 THEN UPPER($1 || ' ' || $2) ELSE LOWER($1 || ' ' || $2) END; $$ LANGUAGE SQL IMMUTABLE STRICT;

Using Positional Notation
↓ sql
SELECT concat_lower_or_upper('Hello', 'World');
Using Named Notation
↓ sql
SELECT concat_lower_or_upper(a => 'Hello', b => 'World');
Using Mixed Notation
키워드 인자는 나머지보다 뒤에 위치해야 한다
↓ sql
SELECT concat_lower_or_upper('Hello', 'World', uppercase => true);

Data Definition

Default Values

↓ sql

-- 기본값에 표현식을 사용할 수 있다. 가장 흔히 쓰이는 것은 timestamp 열이 CURRENT_TIMESTAMP 값을 갖게 하는 것 CREATE TABLE products ( product_no integer DEFAULT nextval('products_product_no_seq'), ... );

Constraints

Check Constraints
↓ sql
CREATE TABLE products ( product_no integer, price numeric CHECK (price > 0) ); -- 식별자를 줄 수 있다 CREATE TABLE products ( product_no integer, price numeric CONSTRAINT positive_price CHECK (price > 0) ); -- 여러 열을 체크할 수 있다 CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0), discounted_price numeric CHECK (discounted_price > 0), CHECK (price > discounted_price) ); -- 열 삽입 제약 조건을 테이블 제약 조건으로 사용할 수 있다 CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0), discounted_price numeric, CHECK (discounted_price > 0 AND price > discounted_price) );
Not-Null Constraints
↓ sql
CREATE TABLE products ( product_no integer NOT NULL, price numeric );
Unique Constraints
↓ sql
CREATE TABLE products ( product_no integer UNIQUE, price numeric ); CREATE TABLE products ( product_no integer, price numeric, UNIQUE (product_no) ); CREATE TABLE example ( a integer, b integer, c integer, UNIQUE (a, c) ); -- 식별자를 부여할 수 있다 CREATE TABLE products ( product_no integer CONSTRAINT must_be_different UNIQUE, price numeric );
Primary Keys
↓ sql
CREATE TABLE example ( a integer, b integer, c integer, PRIMARY KEY (a, c) );
Foreign Keys
↓ sql
CREATE TABLE orders ( order_id integer PRIMARY KEY, product_no integer REFERENCES products (product_no), quantity integer ); CREATE TABLE t1 ( a integer PRIMARY KEY, b integer, c integer, FOREIGN KEY (b, c) REFERENCES other_table (c1, c2) ); -- 외래키와 기본키 혼용 CREATE TABLE order_items ( product_no integer REFERENCES products, order_id integer REFERENCES orders, quantity integer, PRIMARY KEY (product_no, order_id) );
무결성 제약 : ON DELETE, ON UPDATE에 사용
↓ sql
CREATE TABLE order_items ( product_no integer REFERENCES products ON DELETE RESTRICT, -- 참조하는 행이 삭제되지 않음 order_id integer REFERENCES orders ON DELETE CASCADE, -- 참조하는 행이 삭제되면 같이 삭제됨 quantity integer, PRIMARY KEY (product_no, order_id) );

System Columns

oid : object_id. 테이블을 WITH OIDS로 생성하거나, default with oids이 설정된 경우 생성되는 열. 타입 이름도 oidddl-system-columns

Modifying Tables

Adding a Column
↓ sql
ALTER TABLE products ADD COLUMN description text; ALTER TABLE products ADD COLUMN description text CHECK (description != '');
Removing a Column
↓ sql
ALTER TABLE products DROP COLUMN description; -- 외래키로 참조중인 경우, 제약을 명시해줘야 반영된다 ALTER TABLE products DROP COLUMN description CASCADE;
Adding a Constraint
↓ sql
ALTER TABLE products ADD CHECK (name != ''); ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no); ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups; ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;
Removing a Constraint
↓ sql
ALTER TABLE products DROP CONSTRAINT some_name; ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
Changing a Column's Default Value
↓ sql
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77; ALTER TABLE products ALTER COLUMN price DROP DEFAULT;
Changing a Column's Data Type
↓ sql
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2); -- 기존 값이 새로운 타입으로 암묵적으로 변환된다면 위의 것으로 충분. 복잡한 변환을 명시하려면 USING 절을 추가 사용
Renaming a Column
↓ sql
ALTER TABLE products RENAME COLUMN product_no TO product_number;
Renaming a Table
↓ sql
ALTER TABLE products RENAME TO items;

Privileges

권한 : SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, USAGE << ALL

↓ sql

GRANT UPDATE ON table_name TO username; REVOKE ALL ON table_name FROM username; GRANT SELECT ON ALL TABLES IN SCHEMA public TO user; GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO user;

Row Security Policiesddl-rowsecurity

↓ sql

-- 관리자 그룹에 속한 사용자가, 자신과 관련한 행만 접근 CREATE TABLE accounts (manager text, company text, contact_email text); ALTER TABLE accounts ENABLE ROW LEVEL SECURITY; CREATE POLICY account_managers ON accounts TO managers USING (manager = current_user);

Schemas

↓ sql

CREATE SCHEMA myschema; -- Schema의 테이블은 schema.table 또는 database.schema.table로 접근 CREATE TABLE myschema.mytable ( ... ); DROP SCHEMA myschema; DROP SCHEMA myschema CASCADE; -- 포함된 내용도 삭제 -- 모든 새 데이터베이스는 public이라는 스키마를 갖고 있다. 따라서 아래 두 문장은 서로 같다 CREATE TABLE products ( ... ); CREATE TABLE public.products ( ... );

Table Partitioning

개요
1. Table partitioning : 논리적으로 하나인 거대한 테이블을 물리적으로 여러 개로 쪼개 저장하는 것
2. Range partitioning : 중복되지 않는 열 집합의 구간들로 분할
3. List partitioning : 명시적인 키값 리스트로 분할
4. Hash partitioning : 모듈로 n 공간에서 키값의 나머지가 같은 것들로 분할
5. 상속을 이용한 파티셔닝 : 자식 테이블은 추가적인 컬럼을 가질 수 있으며, 다중 상속도 가능하다
파티셔닝
CHECK, NOT NULL 제약 조건은 파티션 테이블에 상속된다. NO INHERIT 마킹된 CHECK 제약조건은 제외
↓ sql
create table api_access_log ( account_id int, access_time datetime, request_uri text ) partition by range(access_time); create table api_access_log_201901 partition of api_access_log for values from ('2019-01-01') to ('2019-02-01'); -- 파티션 테이블을 파티셔닝할 수 있다 create table api_access_log_201901 partition of api_access_log for values from ('2019-01-01') to ('2019-02-01') partition by range(account_id % 100);
주의
1. 값 변경으로 인해 행이 한 파티션에서 다른 파티션으로 이동되는 경우, 예약된 UPDATE, DELETE 작업이 실패할 수 있다
2. BEFORE ROW 트리거는 각 파티션 테이블에 따로 정의해야 한다
Partition Pruning
1. SET enable_partition_pruning = on;로 옵션을 켤 수 있다. 켜 있는 게 디폴트
2. 가지치기 옵션이 켜있는 경우, 파티셔닝된 테이블의 정의(인덱스의 정의가 아니라)를 살펴, 스캔하지 않아도 되는 테이블들은 제외한다
3. 가지치기 시점 : 쿼리 플랜 초기화 시, 쿼리 플랜 실행 중(Append 노드 타입에 한하여)

Data Manipulation

Inserting Data, Updating Data, Deleting Data
DB - PostgreSQL 시작하기 - SQL 언어 참고
Returning Data From Modified Rows
↓ sql
INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id; UPDATE products SET price = price * 1.10 WHERE price <= 99.99 RETURNING name, price AS new_price; DELETE FROM products WHERE obsoletion_date = 'today' RETURNING *;

Queries

Table Expressions

FROM 절

일반
↓ sql
FROM table_reference [, table_reference [, ...]]
Cartesian product(Cross join)
↓ sql
FROM T1 CROSS JOIN T2
또는 콤마로 구분
↓ sql
FROM T1, T2
판별식을 이용한 조인
↓ sql
FROM T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
두 테이블에 대해 판별식이 참일 경우에만 결과 행으로 반환된다
컬럼 셋을 이용한 조인
↓ sql
FROM T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )
두 테이블의 컬럼 셋이 일치하는 경우에만 결과 행으로 반환된다
자연 조인
↓ sql
FROM T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2
두 테이블의 공통된 컬럼 리스트를 USING한 것과 동일한 결과를 산출한다
테이블 별명
↓ sql
FROM table_reference [AS] alias
셀프 조인을 위해선 필수적으로 사용된다. 모호함을 피하기 위해 괄호를 이용할 수도 있다
↓ sql
SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...
컬럼에도 별명을 붙일 수 있다
↓ sql
FROM table_reference [AS] alias ( column1 [, column2 [, ...]] )
서브쿼리 사용을 위한 별명
↓ sql
FROM (SELECT * FROM table1) AS alias
Table function에 별명 사용
↓ sql
-- Table function : 행 집합을 반환하는 함수 CREATE TABLE foo (fooid int, foosubid int, fooname text); CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$ SELECT * FROM foo WHERE fooid = $1; $$ LANGUAGE SQL; SELECT * FROM getfoo(1) AS t1;
LATERAL 서브쿼리
키워드 좌측의 테이블들을 참조할 수 있다
↓ sql
-- unknown_log 테이블로부터 각 로그의 첫 30글자, 삽입 시각, 로그 길이를 가져오기 SELECT substring(log.log, 1, 30), log.insert_datetime, len FROM unknown_log log LEFT JOIN LATERAL LENGTH(log.log) len ON true; -- LEFT JOIN ON true == CROSS JOIN. 사실 LATERAL 빼도 잘 동작하며, 실제로 사용할 일이 있을진 모르겠음...

WHERE 절

GROUPING SETS
한 테이블에 대해 같은 조건으로 여러 그룹에 대해 SELECT해야하는 경우, 이를 한 번에 질의할 수 있다
↓ sql
-- 브랜드별 판매량, 사이즈별 판매량, 전체 판매량 각각이 한 테이블에 모두 표시된다 SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());
GROUP BY CUBE ( a, b )
a, b의 파워 셋에 대해 질의한 것과 같다. ((), (a), (b), (a, b))
GROUP BY ROLLUP ( a, b )
공집합부터 a, b를 순서대로 추가한 집합들에 대해 질의한 것과 같다. ((), (a), (a, b))
CUBE, ROLLUP의 요소로 리스트가 올 수 있다
ROLLUP(a, (b, c)) == ((), (a), (a, b, c))
여러 그루핑 조건을 같이 쓰는 경우, 마지막 조건에 대해 cross product가 이루어진다
↓ sql
GROUP BY a, CUBE (b, c), GROUPING SETS (d, e) -- ((a, d), (a, e), (a, b, d), (a, b, e), ..., (a, b, c, e))
GROUP BY 절에서 행 생성자의 이용은 ROW(a, b)를 통해 할 수 있다

Select Lists

↓ sql

SELECT DISTINCT select_list ...

각 열집합에 대해 고유한 행들만 선택한다

↓ sql

SELECT DISTINCT ON (expression [, expression ...]) select_list ...

모든 행에 대해 expression이 평가된다. 두 행의 모든 평가값이 같은 경우 두 행은 같다고 간주한다

Combining Queries

↓ sql

query1 UNION [ALL] query2 query1 INTERSECT [ALL] query2 query1 EXCEPT [ALL] query2

Sorting Rows

↓ sql

ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }] [, sort_expression2 ...]

ASC, DESC : 기본값은 ASC
NULLS FIRST, NULLS LAST
null값에 대한 정렬 순서를 의미하며, 기본값은 ASC의 경우 NULLS LAST, DESC의 경우 NULLS FIRST

LIMIT and OFFSET

↓ sql

SELECT select_list FROM table_expression [ ORDER BY ... ] [ LIMIT { number | ALL } ] [ OFFSET number ]

LIMIT : 출력 행의 개수 제한
OFFSET : 산출된 순서대로 n개 행을 출력하지 않고 무시

VALUES Lists

VALUES ( expression [, ...] ) [, ...]
↓ sql
SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);
실제 디스크 상에 테이블을 만들지 않으면서 상수 테이블을 만드는 방법
문법적으로 "SELECT select_list FROM table_expression"과 동일하게 취급된다

WITH Queries

SELECT in WITH

여러 개의 SELECT 서브 쿼리를 날리는 대신 WITH을 이용하여 분리할 수 있다
↓ sql
with tmp1 AS (SELECT ...), tmp2 AS (SELECT ...) SELECT ... WHERE tmp1...
RECURSIVE를 이용하여 iteration 연산이 가능하다
연산 순서
1. non-recursive 부분을 계산한다. UNION을 위해 (not UNION ALL) 중복되는 행을 제거한다. 남은 행들을 결과에 포함시키고, 임시 작업 테이블로 설정한다
2. 작업 테이블이 빌 때까지 아래를 반복한다
  1. recursive 부분을 계산하고, 작업 테이블 내용을 덮어쓴다. UNION을 위해 (not UNION ALL) 중복된 행(이전 결과와도 비교)들을 제거한다. 남은 행들을 결과에 포함시키고, 임시 중간 테이블로 설정한다
  2. 작업 테이블의 내용을 중간 테이블 내용으로 대체한다. 중간 테이블은 비운다
↓ sql
-- 10~100의 합계 WITH RECURSIVE t(n) AS ( VALUES (10) UNION SELECT n+1 FROM t WHERE n < 100 ) SELECT sum(n) FROM t;
t working_table
empty empty
10 10
10, 11 11
10, 11, 12 12
... ...
10, 11, 12, 13, ..., 100 100
10, 11, 12, 13, ..., 100 empty
RECURSIVE 연산은 계층 구조나 트리 형태의 데이터에 유용하다
↓ sql
WITH RECURSIVE search_graph(id, link, data, depth) AS ( SELECT g.id, g.link, g.data, 1 FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1 FROM graph g, search_graph sg WHERE g.id = sg.link ) SELECT * FROM search_graph;

t	working_table
empty	empty
10	10
10, 11	11
10, 11, 12	12
...	...
10, 11, 12, 13, ..., 100	100
10, 11, 12, 13, ..., 100	empty

Data-Modifying Statements in WITH

INSERT, UPDATE, DELETE를 WITH과 함께 사용할 수 있다

↓ sql

WITH moved_rows AS ( DELETE FROM products WHERE isnert_datetime < '2010-11-01' RETURNING * ) INSERT INTO products_log SELECT * FROM moved_rows;

Data Types

Numeric Types

Name	Size	Description
smallint, int2	2 bytes	signed integer
integer, int, int4	4 bytes	signed integer
bigint, int8	8 bytes	signed integer
decimal(p, s), numeric(p, s)	variable	임의 개수의 유효숫자와 소수 정밀도를 가진 10진수
real, float4	4 bytes	IEEE 754 단일 정밀도 부동소수(최소 유효숫자 6자리)
double precision, float8	8 bytes	IEEE 754 배 정밀도 부동소수(최소 유효숫자 15자리)
smallserial, serial2	2 bytes	자동 증가 정수
serial, serial4	4 bytes	자동 증가 정수
bigserial, serial8	8 bytes	자동 증가 정수

decimal, numeric
1. p(precision) : 유효숫자 수
2. s(scale) : 소수 정밀도
  예. 3.141592의 precision = 7, scale = 6
  정수는 scale = 0인 numeric으로 간주해도 무방
3. NUMERIC(precision) : scale = 0
4. NUMERIC : 임의 유효숫자, 소수 정밀도의 10진수 모두 저장 가능
  무한히 큰 수를 지원하진 않는다. 소수점 전 131072 자리, 소수점 후 16383 자리까지 지원
5. 'NaN' 상수 지원
  'NaN'에 대한 연산 결과는 'NaN'
6. Rounding mode
  numeric 타입의 반올림은 0에서 멀어지는 방향으로 이루어진다
real, double precision
'NaN', 'Infinity', '-Infinity' 상수 지원
serial
serial 타입은 1부터 시작하여 자동으로 1씩 증가하는 정수에 대한 표현 편의를 제공한다. 9.6 버전에서는 sequence를 이용함으로써 구현한다

Character Types

Name	Description
character [ (n) ], char [ (n) ]	고정 길이(n) 문자열. 스페이스가 덧붙여진다
character varying [ (n) ], varchar [ (n) ]	가변 길이(최대 n) 문자열
text	가변 길이 문자열

Binary Data Type : bytea

로캐일에 의존하지 않는 이진 문자열을 표현한다
bytea의 출력에는 'hex', 'escape' 두 가지 방식(bytea_output)이 있으며, 기본값은 'hex'로 설정되어 있다
- hex : 각 바이트를 16진수 2자리로 표현. 예. '\xDEADBEEF'
- escape : 출력 가능 아스키 문자(32 ~ 126)는 있는 그대로, 백슬래시는 \\, 그 외는 \xxx로 표현한다. 예. 'abc klm *\251T'

Date/Time Types

Name	Size	Description	Range	Resolution
timestamp [ (p) ] [ without time zone ]	8 bytes	date and time (no time zone)	4713 BC ~ 294276 AD	1μs
timestamp [ (p) ] with time zone, timestamptz	8 bytes	date and time, including time zone	4713 BC ~ 294276 AD	1μs
date	4 bytes	date	4713 BC ~ 5874897 AD	1 day
interval [ fields ] [ (p) ]	16 bytes	time span	-178000000 years ~ 178000000 years	1μs
time [ (p) ] [ without time zone ]	8 bytes	time of day (no time zone)	00:00:00 ~ 24:00:00	1μs
time [ (p) ] with time zone, timetz	12 bytes	time of day, with time zone	00:00:00+1459 ~ 24:00:00-1459	1μs

p
초 단위 소수 precision. 0 ~ 6까지 가능하며, 기본값은 없다(설정되지 않은 상태).
interval field
YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, YEAR TO MONTH, DAY TO HOUR, DAY TO MINUTE, DAY TO SECOND, HOUR TO MINUTE, HOUR TO SECOND, MINUTE TO SECOND

Special Date/Time Input String

String	Valid Types	Description
epoch	date, timestamp	1970-01-01 00:00:00+00 (Unix system time zero)
infinity	date, timestamp	later than all other time stamps
-infinity	date, timestamp	earlier than all other time stamps
now	date, time, timestamp	current transaction's start time
today	date, timestamp	midnight today
tomorrow	date, timestamp	midnight tomorrow
yesterday	date, timestamp	midnight yesterday
allballs	time	00:00:00.00 UTC

boolean, bool

TRUE, 't', 'true', 'y', 'yes', 'on', '1'
FALSE, 'f', 'false', 'n', 'no', 'off', '0'

Enumerated Types

↓ sql

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'); -- 대소문자 구분한다

Geometric Types

Name	Size	Description	Representation
point	16 bytes	xy 좌표계 상의 점	(x, y)
line	32 bytes	직선	{A, B, C} // Ax + By + C = 0또는 직선 상의 두 점을 콤마로 구분하여 제공
lseg	32 bytes	선분	((x1, y1), (x2, y2))
box	32 bytes	모든 변이 축과 평행한 직사각형	((x1, y1), (x2, y2))
path	16n bytes	Closed path	((x1, y1), ...)
path	16n bytes	Open path	[(x1, y1), ...]
polygon	40+16n bytes	Polygon	((x1, y1), ...)
circle	24 bytes	Circle	<(x, y), r>

JSON Types : json, jsonb

jsonb : 이진 포맷으로 저장하여 입력 시 조금 느리지만, 처리는 json보다 빠르다
배열 포함 여부 확인 예. SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;
배열 원소 존재 유무 확인 예. SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';
key의 존재 유무 확인 예. SELECT '{"foo": "bar"}'::jsonb ? 'foo';
key로 접근 예. select '{"key":"value"}'::jsonb -> 'key';

Array

배열 타입 정의
PostgreSQL은 배열 크기를 제한하지 않는다
↓ sql
create table test( nums integer[] ); create table test( nums integer ARRAY ); create table test( matrix integer[][] );
배열 표현
↓ sql
insert into test values( '{1, 2, 3, 4}'::int[] ); -- insert시엔 ::int[]같은 캐스팅 생략 가능 insert into test values( ARRAY[[1, 2], [3, 4]] ); -- 배열 생성자는 애초에 배열을 반환하므로 캐스팅 불필요
배열 참조
1. 원소 참조 arr[idx]
2. 슬라이스 arr[start : end]
  모든 차원을 슬라이스한다고 간주된다. arr[1:2][2]는 arr[1:2][1:2]와 같다
3. 배열 차원 반환 array_dims()
4. 지정된 차원에 대해 배열 크기 반환 array_length()
5. 전체 원소 수 반환 cardinality()
6. 현재 배열 크기보다 큰 인덱스에 값을 할당하는 경우, 중간은 null로 채워진다
7. 배열 연결
  - 연산자 || : n차원 배열과 n차원 배열의 연결 또는 n차원 배열에 n-1차원 배열(값)을 추가해준다
  - array_prepend() : 1차원 배열 앞에 원소를 추가한다
  - array_append() : 1차원 배열 뒤에 원소를 추가한다
  - array_cat() : 다차원 배열들에 대해 || 연산과 같은 작업을 한다. 단, 배열만 인자로 사용 가능하다. 0차원 값은 안 된다
배열 검색

배열의 원소 중 하나라도 100
↓ sql
SELECT ... WHERE 100 = ANY(arr);
배열의 원소 모두가 100
↓ sql
SELECT ... WHERE 100 = ALL(arr);
generate_subscripts() : 주어진 배열과 차원에 대해 subscript의 집합을 반환한다
↓ sql
SELECT generate_subscripts('{{"1,1","1,2"},{"2,1","2,2"},{"3,1","3,2"}}'::text[][], 1) AS s; -- 1차원 subscripts → 1, 2, 3 SELECT generate_subscripts('{{"1,1","1,2"},{"2,1","2,2"},{"3,1","3,2"}}'::text[][], 2) AS s; -- 2차원 subscripts → 1, 2 -- 배열 펼치기 WITH arr AS (SELECT '{{"1,1","1,2"},{"2,1","2,2"},{"3,1","3,2"}}'::text[][] AS arr), dims AS (SELECT arr, s1, s2, arr[s1][s2] FROM arr, generate_subscripts(arr, 1) AS s1, generate_subscripts(arr, 2) AS s2) SELECT * FROM dims;

functions-array.html

Composite Types

↓ sql

CREATE TYPE complex AS ( r float4, i float4 );

테이블을 만들면 그와 동일한 이름의 타입이 생성된다
↓ sql
CREATE TABLE m_item ( item_id int, name text ); CREATE TABLE t_user_item ( user_id bigint, item m_item, count int );
Literal composite value
포맷 : '( val1 , val2 , ... )'
↓ sql
INSERT INTO table_name (complex_column_name) VALUES((1.1,2.2));
SET 바로 다음엔 괄호가 없어도 되지만, 등호 다음엔 필요하다
↓ sql
UPDATE table_name SET complex_column.r = (complex_column).r + 1 WHERE ...;

Range Types

int4range — Range of integer
int8range — Range of bigint
numrange — Range of numeric
tsrange — Range of timestamp without time zone
tstzrange — Range of timestamp with time zone
daterange — Range of dateRANGE-OPERATORS-TABLE RANGE-FUNCTIONS-TABLE

Domain Types

기저 타입을 토대로 제약을 추가하여 새로 정의하는 타입

↓ sql

CREATE DOMAIN non_negative_int AS integer CHECK (VALUE >= 0);

Functions and Operations

Logical Operators

AND, OR, NOT
NULL도 논리 상태로 이용한다
- TRUE AND NULL == NULL
- FALSE AND NULL == FALSE
- TRUE OR NULL == TRUE
- FALSE OR NULL == NULL
- NULL AND NULL == NULL OR NULL == NOT NULL == NULL

Comparison Functions and Operators

<, >, <=, >=, =, <> or !=
a [NOT] BETWEEN x AND y
a [NOT] BETWEEN SYMMETRIC x AND y : x, y 대소관계를 판별하여 적절히 비교한다
a IS DISTINCT FROM b
a != b과 같지만 null을 일반적인 값으로 취급한다는 차이가 있다
expression IS [NOT] NULL
boolean_expression IS [NOT] TRUE // FALSE, UNKNOWN

Mathematical Functions and Operators

+, -, *, /, %, @(절댓값)
^(거듭제곱), |/(제곱근), ||/(삼중근), !(팩토리얼, 후위), !!(팩토리얼, 전위)
비트 연산자 &, |, #(XOR), ~, <<, >>
abs(x), sqrt(x), cbrt(x), ceil(x), floor(x), exp(x), div(x, y), round(x, y), ...
random() : x ∈ [0.0, 1.0), setseed(dp) : dp ∈ [-1.0, 1.0]

String Functions and Operators

문자열 변형

문자열 연결 연산자 ||
비문자열도 피연산자 중 한쪽이 될 수 있다
lower(string) : 소문자 문자열로 변환
upper(string) : 대문자 문자열로 변환
initcap(string) : 각 단어의 첫 글자만 대문자가 되도록 변환
split_part(string, delimiter, n) : 구분자로 문자열을 분할하여 n번째 문자열 반환
concat(param1 [, param2 [,...]]) : 각 인자들을 한 문자열로 합쳐 반환한다. null은 무시한다
concat_ws(sep, param1 [, param2 [,...]]) : sep를 구분자로 하여 접합한다
format(formatstr [, formatarg [,...]]) : C의 printf와 유사
left(string, n), right(string, n)
lpad(string, length [, fill_string]), rpad(string, length [, fill_string])
fill_string 기본값은 스페이스
quote_ident(string) : ""로 감싼 문자열 표현 반환
quote_literal(param), quote_nullable(param) : ''로 감싼 문자열 표현 반환
repeat(string, number)
reverse(string)

문자열 길이 관련

bit_length(string) : 문자열 비트 수
octet_length(string) : 문자열 바이트 수
char_length(string) : 문자열 문자 수
character_length(string) : 문자열 문자 수
length(string) : 문자열 문자 수
length(string | bytea, encoding) : 문자열 문자 수

검색

replace(src_string, target_string, dest_string)
position(substring in string) : substring의 위치 반환
substring(string [from int] [for int])
substring(string from pattern) : POSIX 정규표현식
substring(string from pattern for escape) : SQL 정규표현식
trim([leading | trailing | both] [characters] from string)
regexp_match(string, pattern[, flags]) Since PostgreSQL 10
POSIX 정규표현식. 첫 번째 매칭 문자열 배열 반환. 패턴에 그룹이 없으면 전체 매칭 문자열이, 그룹이 있으면 각 그룹에 매칭되는 부분 문자열들이 배열의 원소가 된다
regexp_matches(string, pattern[, flags])
POSIX 정규표현식. 매칭 문자열 배열로 이루어진 행집합을 반환한다. 전체 문자열 매칭을 위해 'g' 플래그를 이용해야 한다
PostgreSQL 10 이전 버전에서 regexp_match와 같은 결과를 얻기 위한 서브쿼리 트릭
↓ sql
SELECT ..., (SELECT regexp_matches(str, pattern)) FROM table...
regexp_split_to_array(string, pattern[, flags])
POSIX 정규표현식. 매칭 문자열을 구분자로 하여 분리한 부분 문자열들로 구성된 배열 반환. 문자열 시작, 끝의 길이 0 매칭 문자열은 무시된다
regexp_split_to_table(string, pattern[, flags])
POSIX 정규표현식. 매칭 문자열을 구분자로 하여 분리한 부분 문자열들로 구성된 테이블 반환. 문자열 시작, 끝의 길이 0 매칭 문자열은 무시된다
regexp_replace(string, pattern, replacement[, flags]) : POSIX 정규표현식
starts_with(string, prefix)
translate(string, from, to)
문자열 각 문자에 대하여, from[i]에 일치하는 문자를 to[i]로 교체한다

인코딩 관련

pg_client_encoding() : 현재 인코딩 이름 반환
ascii(string)
첫 글자의 아스키 코드값을 반환한다. UTF8 문자열의 경우 유니코드 코드 포인트를 반환한다
chr(int)
주어진 코드값에 대한 문자를 반환한다. UTF8 문자에 대해서는 유니코드 코드 포인트를 인자로 전달하고, 그 외의 경우엔 아스키 코드를 인자로 전달해야 한다. 0은 입력으로 허용되지 않는다
convert(string | bytea, src_encoding, dest_encoding)
가능한 빌트인 변환 목록 :#CONVERSION-NAMES
convert_from(string | bytea, src_encoding) : dest_encoding = DB 인코딩
convert_to(string | bytea, dest_encoding) : src_encoding = DB 인코딩
to_ascii(string [, encoding]
LATIN1, LATIN2, LATIN9, WIN1250문자열을 아스키 문자열로 변환
to_hex(number) : 16진수 표현 문자열 반환
decode(string, format), encode(string, format)
지원 format : base64, hex, escape
Data Types - Binary Data Types - escape 참고
set_bit(string, offset, newvalue), set_byte(string, offset, newvalue)
md5(string), sha256(bytea), sha512(bytea)

Bit String Operators

Operator	Description	Example	Result
\|\|	concatenation	B'10001' \|\| B'011'	10001011
&	bitwise AND	B'10001' & B'01101'	00001
\|	bitwise OR	B'10001' \| B'01101'	11101
#	bitwise XOR	B'10001' # B'01101'	11100
~	bitwise NOT	~ B'10001'	01110
<<	bitwise shift left	B'10001' << 3	01000
>>	bitwise shift right	B'10001' >> 2	00100

Pattern matching

LIKE

전체 문자열이 패턴과 일치하는지 여부를 판별한다
Syntax
↓ sql
string [NOT] LIKE pattern [ESCAPE escape-character]
pattern
- '_' : 임의의 1글자
- '%' : 연속적인 임의의 0개 이상 글자
- 'string' : 'string' 자체
ESCAPE
'%', '_'의 이스케이핑을 끄고 싶을 때 : ESCAPE ''
기타
PostgreSQL 고유의 ILIKE : 대소문자 구별없는 LIKE
LIKE = ~~, ILIKE = ~~*, NOT LIKE = !~~, NOT ILIKE = !~~*

SIMILAR TO 정규표현식

LIKE와 비슷하나, 패턴으로 SQL 표준 정규표현식을 이용한다
LIKE와 마찬가지로 전체 문자열이 패턴과 일치해야 한다
Syntax
↓ sql
string [NOT] SIMILAR TO pattern [ESCAPE escape-character]
pattern
1. '_'와 '%'는 LIKE와 같은 의미를 지닌다
2. sub1 | sub2 : sub1 또는 sub2
3. * : 0 or more
4. + : 1 or more
5. ? : 0 or 1
6. {m} : exactly m times
7. {m, } : m or more
8. {m, n} : m~n times
9. () : 그루핑
10. [] : 글자 집합

POSIX 정규표현식

용어

RE; 정규표현식 : 정규표현식은 |로 구분되는 브랜치들로 구성된다. 임의의 브랜치와 일치하는 부분 문자열은 매칭 결과가 된다
Branch : 브랜치는 quantified atom 또는 constraint들로 구성된다. 구성요소 순서대로 매칭을 시도하며, 빈 브랜치는 빈 문자열과 매칭된다
Quantified atom : quantifier가 붙은 atom
Constraint : 길이 0인 문자열이 매칭되지만, 특별한 위치와 의미를 지닌다

연산자

~ : case sensitive match
~* : case insensitive match
!~ : case sensitive 'not' match
!~* : case insensitive 'not' match

Atom

(re) : 정규표현식 re를 매칭하며, 그루핑 번호가 붙는다
(?:re) : re에 대해 매칭은 하지만, 그루핑 번호는 붙지 않는다(non-capturing set)
. : 임의의 문자에 매칭
[chars] : 문자 집합 임의의 문자에 매칭
\k : k는 non-alphanumeric 문자. 예. \\는 백슬래시 문자 자체에 매칭된다
\c : c는 alphanumeric 문자. 이스케이핑된다
{ : 뒤에 숫자가 오는 경우 정규표현식 quantifier가 된다
x : 문자 x 자체에 매칭된다

Quantifier

* : 0 or more
+ : 1 or more
? : 0 or 1
{m} : exactly m times
{m, } : m or more
{m, n} : m~n times
*? : non-greedy version of *
+? : non-greedy version of +
?? : non-greedy version of ?
{m}? : non-greedy version of {m}
{m,}? : non-greedy version of {m,}
{m,n}? : non-greedy version of {m,n}

Constraint

^ : 문자열의 시작에 매칭된다
$ : 문자열의 끝에 매칭된다
(?=re) : positive lookahead. 매칭된 부분문자열의 시작부터 재탐색한다
↓ sql
-- 예. 아이디는 영문 대소문자와 숫자로만 구성할 수 있으며, 첫 글자는 숫자가 될 수 없다 SELECT 'awef79' ~ '(?=^[a-zA-Z])(?=^[a-zA-Z0-9]+$)'; -- t SELECT '79awef' ~ '(?=^[a-zA-Z])(?=^[a-zA-Z0-9]+$)'; -- f
(?!re) : negative lookahead. 매칭된 부분문자열의 시작이 아닌 위치부터 재탐색한다
(?<=re) : positive lookahead. 매칭된 부분문자열의 끝부터 재탐색한다
(?<!re) : negative lookahead. 매칭된 부분문자열의 끝이 아닌 위치부터 재탐색한다
Lookahead와 lookbehind는 back reference를 포함할 수 없으며, 모든 괄호는 non-capturing으로 간주된다

문자 이스케이프

\a(alert character), \b(backspace), \f, \n, \r, \t, \v(수직 탭), \0
\B : \\
\cX : X는 임의 문자. 하위 5개 비트가 같고 나머지 비트가 모두 0
\uFFFF, \UFFFFFFFF : 유니코드 코드포인트. DB 인코딩에 따라 달라진다...
\xFFF, \xy(8진수 2개), \xyz(8진수 3개)

클래스 약어 이스케이프

alnum, alpha, blank, cntrl, digit, graph, lower, print, punct, space, upper, xdigit

\d : [[:digit:]]
\s : [[:space:]]
\w : [[:alnum:]_] // underscore included
\D : [^[:digit:]]
\S : [^[:space:]]
\W : [^[alnum:]_] // underscore included

Constraint 이스케이프

\A : 문자열 시작, \Z : 문자열 끝
^, $와 다르게 멀티 라인 처리 지원 x
\m : 단어 시작
\M : 단어 끝
\y : 단어 시작 또는 끝
\Y : not \y

Back reference

\m[nn] : m은 nonzero digit. n은 추가적인 자릿수

Option letter

정규표현식 플래그에 사용되는 문자들은 아래와 같다

c : 대소문자 구분 (overrides operator type)
i : 대소문자 구분 x (overrides operator type)
s : 멀티 라인 처리 x. (default)
n, m : 멀티 라인 처리
p : 부분적 멀티 라인 처리. ^와 $는 멀티 라인 처리 x
w : 부분적 멀티 라인 처리. ^와 $는 멀티 라인 처리 o

Formatting Functions

functions-formatting.html

to_char()
to_date()
to_number()
to_timestamp()

Date/Time Functions and Operators

Date/Time Operators

Operator	Example	Result
+	date '2001-09-28' + integer '7'	date '2001-10-05'
+	date '2001-09-28' + interval '1 hour'	timestamp '2001-09-28 01:00:00'
+	date '2001-09-28' + time '03:00'	timestamp '2001-09-28 03:00:00'
+	interval '1 day' + interval '1 hour'	interval '1 day 01:00:00'
+	timestamp '2001-09-28 01:00' + interval '23 hours'	timestamp '2001-09-29 00:00:00'
+	time '01:00' + interval '3 hours'	time '04:00:00'
-	- interval '23 hours'	interval '-23:00:00'
-	date '2001-10-01' - date '2001-09-28'	integer 3
-	date '2001-10-01' - integer '7'	date '2001-09-24'
-	date '2001-09-28' - interval '1 hour'	timestamp '2001-09-27 23:00:00'
-	time '05:00' - time '03:00'	interval '02:00:00'
-	time '05:00' - interval '2 hours'	time '03:00:00'
-	timestamp '2001-09-28 23:00' - interval '23 hours'	timestamp '2001-09-28 00:00:00'
-	interval '1 day' - interval '1 hour'	interval '1 day -01:00:00'
-	timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'	interval '1 day 15:00:00'
*	900 * interval '1 second'	interval '00:15:00'
*	21 * interval '1 day'	interval '21 days'
*	double precision '3.5' * interval '1 hour'	interval '03:30:00'
/	interval '1 hour' / double precision '1.5'	interval '00:40:00'
overlaps	(date '2000-01-01', date '2000-12-31') overlaps (date '2000-07-07', date '2000-08-08')	t
overlaps	(date '2000-01-01', make_interval(years => 1)) overlaps (date '2000-07-07', date '2000-08-08')	t

Date/Time Functions

Function	Return Type	Description	Example	Result
clock_timestamp()	timestamptz	현재 timestamp. 클록 기준
timeofday()	text	현재 시각(clock_timestamp)을 문자열로 반환
current_date	date	현재 date. 트랜잭션 시작 기준
current_time	timetz	현재 time. 트랜잭션 시작 기준. CURRENT_TIME(precision) 가능
current_timestamp	timestamptz	현재 timestamp. 트랜잭션 시작 기준. CURRENT_TIMESTAMP(precision) 가능
localtime	time	현재 time. 트랜잭션 시작 기준. LOCALTIME(precision) 가능
localtimestamp	timestamp	현재 timestamp. 트랜잭션 시작 기준. LOCALTIMESTAMP(precision) 가능
now()	timestamptz	현재 timestamp. 트랜잭션 시작 기준
statement_timestamp()	timestamptz	현재 timestamp. 현재 문장 실행 기준
transaction_timestamp()	timestamptz	현재 timestamp. 트랜잭션 시작 기준
make_date(year int, month int, day int)	date	date 생성	make_date(2013, 7, 15)	2013-07-15
make_interval(years int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0)	interval	interval 생성	make_interval(days => 10)	10 days
make_time(hour int, min int, sec double precision)	time	time 생성	make_time(8, 15, 23.5)	08:15:23.5
make_timestamp(year int, month int, day int, hour int, min int, sec double precision)	timestamp	timestamp 생성	make_timestamp(2013, 7, 15, 8, 15, 23.5)	2013-07-15 08:15:23.5
make_timestamptz(year int, month int, day int, hour int, min int, sec double precision, [timezone text])	timestamptz	timestamp 생성. timezone 생략시 현재 설정 사용	make_timestamptz(2013, 7, 15, 8, 15, 23.5)	2013-07-15 08:15:23.5+01
to_timestamp(double precision)	timestamptz	Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp	to_timestamp(1284352323)	2010-09-13 04:32:03+00
at time zone	timestamptz	Convert timestamp to timestamptzwiki/List_of_tz	timestamp '2000-01-01 00:00:00' at time zone 'Asia/Seoul'	1999-12-31 07:00:00-08
age(timestamp,timestamp)	interval	연월일로 표기되는 두 시각 사이의 차이. 부정확	age(timestamp '2001-04-10', timestamp '1957-06-13')	43 years 9 mons 27 days
age(timestamp)	interval	현재 date로부터 뺀다. = age(current_date::timestamp, from)	age(timestamp '1957-06-13')	43 years 8 mons 3 days
date_part(text,timestamp)	float8	= extract	date_part('hour', timestamp '2001-02-16 20:38:40')	20
date_part(text,interval)	float8	= extract	date_part('month', interval '2 years 3 months')	3
extract(field from timestamp)	float8	Get subfield	extract(hour from timestamp '2001-02-16 20:38:40')	20
extract(field from interval)	float8	Get subfield	extract(month from interval '2 years 3 months')	3
extract, date_part field	century, day, decade, dow(day of the week), doy(day of the year), epoch(number of seconds sinse 1970-01-01 00:00:00 UTC), hour, isodow(day of the week), isoyear(ISO 8601 week-numbering), microseconds, millenium, milliseconds, minute, month, quarter, second, timezone, timezone_hour, timezone_minute, week(ISO 8601 week-numbering), year
isfinite(date)	boolean	Test for finite date (not +/-infinity)	isfinite(date '2001-02-16')	true
isfinite(timestamp)	boolean	Test for finite time stamp (not +/-infinity)	isfinite(timestamp '2001-02-16 21:28:30')	true
isfinite(interval)	boolean	Test for finite interval	isfinite(interval '4 hours')	true
date_trunc(text,timestamp)	timestamp	Truncate to specified precision	date_trunc('hour', timestamp '2001-02-16 20:38:40')	2001-02-16 20:00:00
date_trunc(text,interval)	interval	Truncate to specified precision	date_trunc('hour', interval '2 days 3 hours 40 minutes')	2 days 03:00:00
date_trunc field	microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, millenium
justify_days(interval)	interval	Adjust interval so 30-day time periods are represented as months	justify_days(interval '35 days')	1 mon 5 days
justify_hours(interval)	interval	Adjust interval so 24-hour time periods are represented as days	justify_hours(interval '27 hours')	1 day 03:00:00
justify_interval(interval)	interval	Adjust interval using justify_days and justify_hours, with additional sign adjustments	justify_interval(interval '1 mon -1 hour')	29 days 23:00:00

JSON Functions and Operators

functions-json.html

json and jsonb Operators

Operator	Right Operand Type	Description	Example	Example Result
->	int	Get JSON array element(인덱스는 0부터 시작. 음수면 뒤에서부터)	'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2	{"c":"baz"}
->	text	Get JSON object field by key	'{"a": {"b":"foo"}}'::json->'a'	{"b":"foo"}
->>	int	Get JSON array element as text	'[1,2,3]'::json->>2	3
->>	text	Get JSON object field as text	'{"a":1,"b":2}'::json->>'b'	2
#>	text[]	Get JSON object at specified path	'{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'	{"c": "foo"}
#>>	text[]	Get JSON object at specified path as text	'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'	3

Sequence Manipulation Functions

currval(regclass) : 지정된 sequence의 가장 최근 반환된 nextval
lastval() : 가장 최근 반환된 임의 sequence의 nextval
nextval(regclass) : sequence를 증가시키고 반환
setval(regclass, bigint) : sequence의 현재값 설정
regclass는 pg_class 시스템 카탈로그에 저장된 sequence의 OID며, 이를 직접 찾을 필요는 없다. '' 사이에 식별자를 넣어주면 알아서 변환해준다

Conditional Expressions

case
↓ sql
case when condition then result [when ...] [else result] end
coalesce
↓ sql
coalesce(value [, ...])
처음으로 등장하는 non-null값을 반환한다. 모두 null이면 최종적으로 null이 반환된다
nullif
↓ sql
nullif(value1, value2)
value1과 value2가 같으면 null 반환. 다르면 value1 반환
greatest
↓ sql
greatest(value [, ...])
least
↓ sql
least(value [, ...])

Subquery Expressions

exists
exists(subquery) : 서브쿼리가 한 행 이상 반환하면 true, 아니면 false
in
expression in (subquery) : expression 판별값이 subquery 결과 중에 있으면 true, 아니면 false
not in
any, some
↓ sql
expression operator any (subquery) expression operator some (subquery)
subquery는 하나의 컬럼만 반환해야 한다. expression 판별값이 subquery 결과 중에 하나라도 operator를 만족하면 true
all
↓ sql
expression operator all (subquery)
Single-row Comparison
row_constructor operator (subquery) : subquery는 row_constructor와 같은 개수의 컬럼을 반환해야 한다

Series Generating Functions

generate_series(start, stop)
generate_series(start, stop, step)
generate_series(start, stop interval)

System Information Functions

functions-info.html

동시성 제어

소개

PostgreSQL은 다중버전 모델 MVCC(Multiversion Concurrency Control)을 이용해서 동시성 처리를 관리한다
각 트랜잭션의 시작을 기점으로 자료를 각각의 스냅샷(데이터베이스 버전)으로 처리해서 세션이 사용한다. 이렇게 하면 최소한의 잠금을 사용하여 트랜잭션 격리가 이루어진다
MVCC와 별개로 개발자가 이용할 수 있는 테이블, 행 단위 잠금 기능을 제공한다

트랜잭션 격리

SQL 표준 트랜잭션 격리 수준; Transaction Isolation Level
- dirty read : 다른 트랜잭션이 아직 커밋하지 않은 자료도 읽을 수 있다
- nonrepeatable read : 다른 트랜잭션이 커밋한 자료를 읽을 수 있다. 다음 읽기 시도시 해당 트랜잭션이 값을 변경했다면 변경된 값을 읽는다
- phantom read : 다른 트랜잭션이 커밋한 자료를 읽을 수 있다. 다음 읽기 시도 시, 최초 읽은 값 그대로 보여준다
- serialization anomaly
  1. let result ::= 트랜잭션 그룹에 대한 최종 실행 성공 상태
  2. let possibleResults ::= 각 트랜잭션을 하나씩 순서대로 실행하는 모든 경우에 대해, 각 실행 성공 상태 리스트
  3. 직렬화 이상 == result not in possibleResults

PostgreSQL 트랜잭션 격리 수준

트랜잭션 격리 수준을 설정하려면 SET TRANSACTION 명령을 이용한다. PostgreSQL은 MVCC 모델을 이용하기 때문에, Read uncommitted는 Read committed처럼 작동한다

격리 수준	Dirty Read	Nonrepeatable Read	Phantom Read	Serialization Anomaly
Read uncommitted	허용, PG에서는 없음	가능	가능	가능
Read committed	불가능	가능	가능	가능
Repeatable read	불가능	불가능	허용, PG에서는 없음	가능
Serializable	불가능	불가능	불가능	불가능

Read Committed 격리 수준

PostgreSQL 기본 격리 수준
for update/share 절 없는 select 쿼리는 쿼리 실행 전 마지막으로 커밋된 자료를 읽는다
다른 트랜잭션과 분리된 스냅샷을 이용하므로, select 쿼리 실행 중 다른 트랜잭션이 변경하여 커밋한 자료는 읽을 수 없다. 이때, 같은 select 쿼리가 다시 호출되는 경우 변경 커밋된 자료를 가져온다
현재 트랜잭션에서 업데이트한 자료는 커밋되지 않았더라도 다시 읽을 때 갱신된 값으로 가져온다
update, delete, select for update/share 쿼리는 타깃 행을 찾는 것까지는 select 쿼리와 동일하다
실행 시각을 기준으로 발견한 타깃 행이 변경/삭제/잠금 중인 경우 커밋이나 롤백되기를 기다린다. 행이 변경된 경우 where 절이 재평가되고 갱신된 행을 대상으로 쿼리 실행, 삭제된 경우 무시, 롤백된 경우 기존 행을 대상으로 쿼리가 실행된다

Repeatable Read 격리 수준

트랜잭션 실행 전 마지막으로 커밋된 자료만을 본다
물론 현재 트랜잭션에서 업데이트한 자료는 커밋되지 않았더라도 다시 읽을 때 갱신된 값으로 가져온다
update, delete, select for update/share 쿼리는 타깃 행을 찾는 것까지는 select 쿼리와 동일하다
실행 시각을 기준으로 발견한 타깃 행이 변경/삭제/잠금 중인 경우 커밋이나 롤백되기를 기다린다. 롤백된 경우 기존 행에 대해 쿼리가 실행되고, 변동이 있다면 에러를 발생하고 롤백한다. ← Repeatable Read 트랜잭션은 트랜잭션 시작 이후 다른 트랜잭션에 의해 변경된 행을 갱신하거나 잠글 수 없다
따라서 update 트랜잭션은 응용 수준에서 트랜잭션 직렬화 실패에 대한 대응이 필요하다
읽기만 하는 트랜잭션은 직렬화 실패가 일어날 수 없다
트랜잭션 시작 시각을 기준으로 DB에 대한 엄격한 정적 뷰를 보장하지만, 동시 실행되는 트랜잭션에 의해 일부 불일치가 발생할 수 있다
예를 들어 다른 트랜잭션에서 여러 연관 테이블의 행들을 순차적으로 업데이트하는 와중에 읽기를 수행하는 경우, 시간적으로는 일관적이지만, 논리적으로는 일관적이지 않은 데이터를 읽게될 수 있다

Serializable 격리 수준

커밋된 모든 트랜잭션들을 하나씩 순차적으로 실행한다
이걸로 모든 이상 발생 가능성이 차단되는 것이 아니기 떄문에, 여전히 트랜잭션 직렬화 실패에 대한 대응이 필요하다
성능을 위한 유의사항
- 가능한 경우 READ ONLY 명시
- 기본으로 잠금을 제공하므로 select for update/share 구문은 필요없다
- 페이지 수준 predicate 잠금이 여러 개 필요하지만 메모리 부족으로 릴레이션 수준의 잠금이 걸리는 경우, 최대 잠금 수 설정을 조정한다GUC-MAX-PRED-LOCKS-PER-TRANSACTION
- 릴레이션 수준 잠금이 설정되는 것을 방지하기 위해 인덱스 스캔을 이용한다

Explicit Locking

Table-level Locks

아래 표는 테이블 수준 잠금의 종류와, 각 요청된 잠금 수준에 대해 이미 설정된 잠금으로 인해 요청이 처리되지 않는 경우를 보여준다. 테이블 잠금은 LOCK 명령어로 획득할 수 있다

Requested Lock Mode	Current Lock Mode
Requested Lock Mode	ACCESS SHARE	ROW SHARE	ROW EXCLUSIVE	SHARE UPDATE EXCLUSIVE	SHARE	SHARE ROW EXCLUSIVE	EXCLUSIVE	ACCESS EXCLUSIVE
ACCESS SHARE								X
ROW SHARE							X	X
ROW EXCLUSIVE					X	X	X	X
SHARE UPDATE EXCLUSIVE				X	X	X	X	X
SHARE			X	X		X	X	X
SHARE ROW EXCLUSIVE			X	X	X	X	X	X
EXCLUSIVE		X	X	X	X	X	X	X
ACCESS EXCLUSIVE	X	X	X	X	X	X	X	X

ACCESS SHARE
select 명령이 이 잠금을 필요로 한다. 테이블을 읽기만 하는 쿼리들은 보통 이 잠금 수준에서 동작한다
ROW SHARE
select for update, select for share 명령이 이 잠금을 필요로 한다
ROW EXCLUSIVE
update, delete, insert 명령이 이 잠금을 필요로 한다. 테이블 데이터를 수정하는 쿼리들은 보통 이 잠금 수준에서 동작한다
SHARE UPDATE EXCLUSIVE
Acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, CREATE STATISTICS and ALTER TABLE VALIDATE and other ALTER TABLE variants
SHARE
Acquired by CREATE INDEX (without CONCURRENTLY)
SHARE ROW EXCLUSIVE
Acquired by CREATE COLLATION, CREATE TRIGGER, and many forms of ALTER TABLE
EXCLUSIVE
Acquired by REFRESH MATERIALIZED VIEW CONCURRENTLY
ACCESS EXCLUSIVE
Acquired by the DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, and REFRESH MATERIALIZED VIEW (without CONCURRENTLY) commands

Row-level Lock Modes
행 수준 잠금은 자동적으로 설정되고 해제된다
1. FOR UPDATE
  select한 행들을 변경을 위해 잠근다. 다른 트랜잭션의 잠금, 변경, 삭제를 막는다
2. FOR NO KEY UPDATE
  FOR UPDATE와 비슷. select for key share 명령은 막지 않는다
3. FOR SHARE
  FOR NO KEY UPDATE와 비슷. 공유된 잠금을 설정하며, select for share, select for key share 명령은 막지 않는다
4. FOR KEY SHARE
  FOR SHARE와 비슷. select for no key update 명령은 막지 않는다

Performance Tips

Populating a Database
1. 여러 insert 명령이 순차적으로 실행되는 경우, autocommit 기능을 끄고 마지막에 한꺼번에 커밋하는 것이 좋다
  SQL 명령문에서 BEGIN, COMMIT 명령으로 트랜잭션을 수행하는 것과 동일하다
2. 여러 행 데이터를 입력하는 경우, COPY 명령을 이용하는 것이 좋다
3. 테이블을 최초로 생성하면서 데이터를 입력하는 경우, 인덱스를 제거하고 나중에 다시 만드는 것이 좋다
4. 일시적으로 maintenance_work_mem, max_wal_size 설정을 높인다
5. pg_dump는 위에 적힌 것을 비롯한 여러 성능 향상 기법들을 이용한다
Non-Durable Settings
데이터베이스의 안정성이 필요없는 경우 아래와 같이 성능 위주의 설정이 가능하다
1. DB 클러스터를 RAM 디스크에 둔다
2. fsync, synchronous commit, full page writes 설정을 끈다
3. max_wal_size, checkpoint_timeout 설정을 높인다
4. 테이블 생성 시 UNLOGGED를 설정한다

Author	License
© Donggi Kim	MIT
w3css	No license
highlight.js	BSD-3-Clause
MathJax	Apache-2.0
QRCode.js	MIT
D2	MPL-2.0