[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
[ * | expression [ [ AS ] output_name ] [, ...] ]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition [, ...] ]
[ WINDOW window_name AS ( window_definition ) [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]WITH목록에 정의된 요소들이 계산됩니다. 여기서 계산된 결과는FROM절에서 사용할 수 있으며,NOT MATERIALIZED를 사용하지 않는 이상FROM에서 2번 이상 사용되더라도 계산은 한번만 이루어집니다.
FROM목록에 정의된 요소들이 계산됩니다. 두 개 이상의 요소가 주어진 경우corss-join됩니다.
- 만약
WHERE절이 사용되었다면, 주어진 조건을 만족하지 않는 행은 결과집합에서 제외됩니다.
- 만약
GROUP BY절 또는집계함수가 사용되었다면, 결과집합을 그룹화한 뒤에 집계함수의 결과를 계산합니다. 만약HAVING절이 사용되었다면, 주어진 조건을 만족하지 않는 그룹은 결과집합에서 제외됩니다.
SELECT절에 주어진행또는그룹의 목록을 사용하여 실제 결과집합을 계산합니다.
SELECT DISTINCT는 결과집합에서 중복된 행을 제거합니다.SELECT ALL(default)는 중복된 행도 포함하여 반환합니다.
- 집합 연산자 (
UNION,INTERSECT,EXCEPT)를 계산합니다.ALL를 사용하지 않는 이상 결과집합에서 중복된 행은 제거됩니다.
ORDER BY가 사용되었다면 해당 순서로 결과집합을 정렬합니다. 사용되지 않았다면 빨리 찾아낸 순서대로 반환됩니다.
LIMIT(=FETCH FIRST) 또는OFFSET이 사용되었다면, 결과집합에서 해당 부분집합만 반환합니다.
FOR UPDATE,FOR NO KEY UPDATE또는FOR KEY SHARE이 사용되었다면 결과집합에 포함된 행에 대해LOCK을 겁니다.
WITH절은 서브쿼리를 가상의 뷰로 만들어 FROM절에 제공할 수 있도록 도와줍니다. 각 서브쿼리는 SELECT, TABLE, VALUES, INSERT, UPDATE, DELETE 중 하나가 올 수 있으며, 만약 부작용을 동반하는 문장(INSERT, UPDATE, DELETE)이라면 RETURNING절의 결과를 사용합니다. RETURNING절을 사용되지 않아도 에러를 발생시키지는 않지만, FROM절에서 해당 뷰를 참조할 수 없게 됩니다.
WITH RECURSIVE 형태로 사용된다면 뷰가 자기 자신을 참조하는 것을 허용합니다. 이 경우 재귀참조는 아래처럼 UNION [ALL | DISTINCT]의 우측에 존재해야 합니다.
WITH RECURSIVE
test AS (
NON-RECURSIVE-SELECT
UNION [ALL | DISTINCT]
RECURSIVE-SELECT
)
...WITH RECURSIVE
test AS (
SELECT 1 AS num
UNION ALL
SELECT num+1 FROM test WHERE num < 10
)
SELECT * FROM test;
CREATE TABLE test (
child int,
parent int NULL
);
INSERT INTO test VALUES (1, 5), (2, 5), (3, 5), (5, 10), (10, 15), (15, null);
WITH RECURSIVE
temp AS (
-- first node.
SELECT
test.child,
test.parent,
1 AS depth,
test.child::text AS path
FROM test
WHERE test.child = 1
UNION ALL
-- next node.
SELECT
test.child,
test.parent,
depth + 1,
path || ' → ' || test.child
FROM test, temp
WHERE temp.parent = test.child
)
SELECT * FROM temp;
RECURSIVE는 단 한번만 사용되어야 하며, 재귀를 사용하는 모든 뷰에 적용됩니다. 단, 재귀를 사용하지 않는 뷰에 대해서는 적용되지 않습니다.
WITH RECURSIVE
test1 AS (
SELECT 1 AS num1
UNION ALL
SELECT num1+1 FROM test1 WHERE num1 < 1000
),
test2 AS (
SELECT 1 AS num2
UNION ALL
SELECT num2+1 FROM test2 WHERE num2 < 1000
)
SELECT * FROM test1, test2 WHERE num1 = num2;자신보다 이전에 선언된 형제만 참조할 수 있습니다.
WITH
test1 AS (
SELECT * FROM test2 -- ERROR
),
test2 AS (
SELECT 1 AS x
)
SELECT * FROM test1;PRIMARY QUERY와 WITH QUERY는 동일한 시간값을 가지고 실행되므로 WITH에서 데이터를 수정해도 RETUNING을 제외한 다른 방법으로 변경된 데이터를 감지하는 것은 불가능합니다. 따라서 아래처럼 두 서브쿼리가 동일한 행을 수정하려고 하면 예측할 수 없는 결과가 만들어집니다.
CREATE TABLE test (
val int
);
INSERT INTO test VALUES (0);
WITH
m1 AS (
UPDATE test SET val = 1 WHERE val = 0
),
m2 AS (
UPDATE test SET val = 2 WHERE val = 0
)
SELECT * FROM test;WITH에 사용된 서브쿼리는 여러번 참조되어도 한 번만 계산되도록 제한되어 있기 때문에 별도의 실행계획을 사용하지만, NOT MATERIALIZED가 함께 사용하면 이러한 제한을 제거할 수 있으며, 주 쿼리와 서브 쿼리를 풀어내어 계산하므로 더 좋은 실행계획을 찾을 가능성이 높아집니다. 그러나 이것은 휘발성이 존재하는 쿼리인 경우에만 작동하고, 휘발성이 아닌 쿼리(재귀적이거나 부작용이 없는 쿼리)라고 판단되면 NOT MATERIALIZED는 무시됩니다.
where from_item can be one of:
[ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
[ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]
[ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
[ LATERAL ] function_name ( [ argument [, ...] ] )
[ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
[ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias ( column_definition [, ...] )
[ LATERAL ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
[ LATERAL ] ROWS FROM( function_name ( [ argument [, ...] ] ) [ AS ( column_definition [, ...] ) ] [, ...] )
[ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]FROM절은 해당 쿼리에서 사용할 데이터 소스의 목록을 지정하는 역할을 합니다. 만약 여러개의 소스가 지정되었다면, 모든 소스를 카테시안 곱셈(CROSS-JOIN)한 결과를 반환합니다. 이 때, WHERE절을 명시적으로 추가하여 카테시안 곱셈의 작은 부분집합을 가져올 수 있습니다.
데이터 소스는 다음 중 하나를 허용합니다.
- 테이블
- 서브쿼리
WITH절에 선언된 쿼리- 함수호출
데이터 소스로 테이블을 사용할 수 있습니다. 만약 ONLY를 테이블 이름의 앞에 사용했다면, 해당 테이블만 스캔 되도독(=자식 테이블이 스캔되지 않도록) 지정할 수 있습니다. 만약 *를 테이블 이름의 뒤에 사용했다면, 명시적으로 모든 자식 테이블을 데이터 소스로 지정할 수 있습니다.
-- X의 자식 테이블은 제외.
FROM ONLY X;
-- X의 모든 자식 테이블도 포함.
FROM X;
FROM X*;데이터 소스로 서브쿼리를 사용할 수 있습니다. 이 경우, 해당 서브쿼리는 Temporary Table로 구체화되어 쿼리에서 사용됩니다. 각각의 서브쿼리는 반드시 괄호로 감싸야하며 alias도 함께 주어져야 합니다. VALUES ... 또한 서브쿼리로 사용할 수 있습니다.
SELECT *
FROM ( SELECT 1 AS x, 2 AS y ) AS TEST;
-- 또는
SELECT *
FROM ( SELECT 1, 2 ) AS TEST(x, y);
-- 또는
SELECT *
FROM ( VALUES (1, 2) ) AS TEST(x, y);
-- Multiple Values
SELECT *
FROM ( VALUES (1, 2), (3, 4), ... ) AS TEST(x, y);데이터 소스로 WITH절에 선언된 쿼리를 사용할 수 있습니다. 만약 WITH-QUERY의 이름과 중복되는 테이블이 있다면 WITH-QUERY가 우선적으로 사용됩니다. 즉, 기존 테이블의 이름은 가려집니다.
-- 테이블 생성
CREATE TABLE TEST (
x int
);
INSERT INTO TEST VALUES (1), (2), (3);
-- 기존 테이블의 이름과 겹치도록 WITH 쿼리를 생성
WITH TEST AS (
SELECT * FROM ( VALUES (4), (5), (6) ) AS TEST(y)
)
SELECT * FROM TEST;
데이터 소스로 함수호출을 사용할 수 있습니다. 일반적으로는 모든 함수를 사용할 수 있지만, 보편적으로는 집합을 반환하는 함수가 사용됩니다. WITH ORDINALITY가 함께 사용되면, 마지막 컬럼에 bigint형식의 데이터 순번이 부여됩니다.
SELECT *
FROM unnest( array['x', 'y', 'z'] ) AS test(token);
SELECT *
FROM unnest(array['x', 'y', 'z']) WITH ORDINALITY AS test(token, seq);
기본적으로 FROM에서 Right-Hand Element는 Left-Hand Element의 데이터를 읽을 수 없습니다. 그러나 SUB-SELECT 또는 FUNCTION-CALL의 앞에 LATERAL 키워드를 함께 사용하면, 좌측 데이터를 읽을 수 있습니다.
-- 샘플 데이터 테이블
CREATE TABLE test (
x int,
y int
);
INSERT INTO test VALUES (1, 1), (1, 2), (1, 3), (2, 1), (2, 5), (2, 6);먼저 잘못된 쿼리부터 살펴보겠습니다. 우측 인라인뷰에서 (test.)x와 (test.)y를 읽으려고 했지만, 기본적으로 좌측의 테이블의 데이터를 읽을 수 없으므로 에러가 발생합니다.
SELECT *
FROM test, ( SELECT x + y ) as sub("x+y");
[output]
ERROR: column "x" does not exist
There is a column named "x" in table "test", but it cannot be referenced from this part of the query.하지만 LATERAL 키워드를 함께 사용하면 좌측의 test의 컬럼을 읽을 수 있습니다.
SELECT *
FROM test, LATERAL ( SELECT x + y ) as sub("x+y");
이것을 JOIN과 함께 사용하면 상호 연관 서브쿼리와 결과가 같아집니다. 예를 들기위해, 먼저 각 x마다 y가 평균보다 큰 행만 출력하는 작업을 상호 연관 서브쿼리로 작성하면 다음과 같습니다.
SELECT *
FROM test t1
WHERE y > (
SELECT avg(t2.y)
FROM test t2
WHERE t2.x = t1.x
);
위의 쿼리가 동작하는 방식은 다음과 같습니다.
메인쿼리에서t1.x를 읽을 때 마다서브쿼리에 전달합니다.서브쿼리에서t1.x에 해당하는t2.y를 읽어 평균을 구하고 반환합니다.WHERE절에서 평균보다 큰 행만 가져옵니다.
메인쿼리에서 참조되는 데이터가 읽힐 때 마다 서브쿼리에 삽입되어 호출됩니다. 호출된 횟수는 실행계획의 LOOP 항목을 통해 확인할 수 있습니다.
이것과 동일한 결과를 반환하는 쿼리를 LATERAL JOIN으로도 작성할 수 있습니다.
SELECT t1.*
FROM test as t1 JOIN LATERAL (
SELECT avg(t2.y)
FROM test as t2
WHERE t2.x = t1.x
) as sub
ON t1.y > avg;
-- OR
WHERE t1.y > avg;위의 쿼리가 동작하는 방식은 다음과 같습니다.
좌측에서t1.x를 읽을 때 마다우측에 전달합니다.우측에서t1.x에 해당하는t2.y를 읽어 평균을 구합니다.ON절 또는WHERE절에서 평균보다 큰 행만 가져옵니다.
좌측쿼리에서 참조되는 데이터가 읽힐 때 마다 우측쿼리(=LATERAL Query)에 삽입되어 호출됩니다. 호출된 횟수는 실행계획의 LOOP 항목을 통해 확인할 수 있습니다.
LATERAL은 함수호출의 앞에 사용할 수 있지만, 이 경우에는 Noise Word입니다. 있으나 없으나 LATERAL로 간주되기 때문입니다.
SELECT temp2.*
FROM (VALUES (array[1, 2, 3], array[4, 5, 6])) as temp1(x, y),
unnest(x, y) as temp2(a, b); -- Implicit LATERAL
WHERE boolean_expression주어진 표현식이 true인 행만 결과집합에 포함시킵니다.
boolean을 반환하는 연산자는 다음이 있습니다.
동등 및 대소비교:
=><>=<=<>or!=BETWEEN a AND b: 주어진 숫자가a이상b이하라면true.
SELECT 1 = 1; -- true
SELECT 2 != 2 -- false
SELECT 1 BETWEEN 1 AND 3; -- true
SELECT 3 BETWEEN 1 AND 3; -- true논리 연산자 :
NOTANDOR
SELECT true AND true; -- true
SELECT true OR false; -- true
SELECT NOT false; -- trueLIKE 표현식 :
str LIKE exp:str이exp에 일치하면true를 반환합니다.
LIKE에 사용할 수 있는 특수기호는 다음과 같습니다.
| 기호 | 의미 |
|---|---|
| _ | 임의의 한 글자에 매칭 |
| % | 임의의 여러 글자에 매칭 |
SELECT 'Hello, World!' LIKE 'H_'; -- false
SELECT 'Hello, World!' LIKE 'H%'; -- true
SELECT 'Hello, World!' LIKE '%W%'; -- trueSMILAR TO 정규 표현식 :
str SIMILAR TO expstr NOT SIMILAR TO exp
SIMILAR TO에 사용할 수 있는 특수기호는 다음과 같습니다.
| 기호 | 의미 |
|---|---|
| | | 대체자. a 또는 b |
| * | 앞의 아이템이 0개 이상인 경우에 매칭 |
| + | 앞의 아이템이 1개 이상인 경우에 매칭 |
| {m} | 앞의 아이템이 m개인 경우에 매칭 |
| {m,} | 앞의 아이템이 m개 이상인 경우에 매칭 |
| {m, n} | 앞의 아이템이 m개 이상 n개 이하인 경우에 매칭 |
| () | 여러 아이템을 하나의 아이템으로 묶음 |
| [...] | 문자열을 각 문자로 쪼갬 |
SELECT 'Hello, World!' SIMILAR TO '[a-zA-Z]*'; -- false
SELECT 'Hello, Hello!' SIMILAR TO '(Hello(,|!) ){2}'; -- truePOSIX 정규 표현식 :
str ~ pattern: 대소문자를구분하고비교하여str이pattern에 일치하면true.str ~* pattern: 대소문자를구분하지 않고비교하여str이pattern에 일치하면true.str !~ pattern: 대소문자를구분하고비교하여str이pattern에 일치하지 않으면true.str !~* pattern: 대소문자를구분하지 않고비교하여str이pattern에 일치하지 않으면true.
SELECT 'Hello, World!' ~ '^H.*!$'; -- true;자세한 사항은 공식문서의 POSIX-REGEXP를 참조해주세요.
포함 및 미포함 :
IN: 주어진 항목이 리스트에 있다면true.NOT IN: 주어진 항목에 리스트에 없다면true.EXISTS: 주어진 서브쿼리가 공집합이 아니라면true.NOT EXISTS: 주어진 서브쿼리가 공집합이 이라면true.
-- Single Column.
SELECT 1 IN (1, 2, 3); -- true
-- Multi Column.
SELECT (1, 2) NOT IN ((1, 2), (3, 4), (5, 6)); -- falseSELECT EXISTS ( SELECT 1 ); -- true;
SELECT NOT EXISTS ( SELECT 1 WHERE false ); -- true;GROUP BY grouping_element [, ...]GROUP BY절은 하나 이상의 컬럼을 그룹으로 묶어 동일한 그룹 값을 갖는 여러 데이터들을 집계 함수를 통해 하나의 행으로 압축하는 역할을 수행합니다. 아래는 각 (학년, 반)의 평균 키를 구하는 예제입니다.
** 결과행은 개수는 그룹의 개수보다 커질 수 없습니다.
테스트 데이터 :
CREATE TABLE students (
grade int,
class int,
name text,
sex text,
height int
);
INSERT INTO students VALUES
(1, 1, 'grace', 'f', 164),
(1, 1, 'erica', 'f', 174),
(1, 2, 'caden', 'm', 182),
(2, 1, 'lucas', 'm', 177),
(2, 1, 'irene', 'f', 167);각 (학년, 반) 마다 평균키 계산 :
SELECT grade, class, avg(height) as "avg_height"
FROM students
GROUP BY (grade, class);GROUP BY는 SELECT에서 사용된 순서대로 1부터 번호를 매기고 있기 때문에, 위의 쿼리는 다음처럼 바꿔 사용할 수 있습니다.
SELECT grade, class, avg(height) as "avg_height"
FROM students
GROUP BY (1, 2);컬럼의 이름에 입력 컬럼이름과 결과 컬럼이름을 둘 다 사용할 수 있지만, 둘의 이름이 겹친경우 입력 컬럼이름을 우선적으로 사용합니다. 먼저 결과 컬럼이름을 사용할 수 있다는 것 부터 확인하겠습니다. 다음 쿼리는 정상적으로 실행됩니다.
SELECT grade as "g", class as "c", avg(height) as "avg_height"
FROM students
GROUP BY (g, c);이번에는 일부러 class의 이름만 grade로 바꿔보았습니다. grade라는 이름이 겹치기 때문에, 아래의 쿼리는 에러가 발생할 것 같지만 입력 컬럼이름을 우선적으로 사용한다는 규칙에 의해 정상적으로 실행됩니다.
SELECT grade, class as "grade", avg(height) as "avg_height"
FROM students
GROUP BY (grade, class);그룹화에 사용되지 않은 데이터는 집계함수 없이 사용할 수 없습니다. 즉, 다음 쿼리는 실행되지 않습니다.
SELECT grade, class, height
FROM students
GROUP BY (grade, class);GROUPING SETS, CUBE 또는 ROLL UP을 사용하지 않은 다중 그룹은, 단일 그룹으로 결합됩니다. 즉, 아래의 쿼리는 모두 동일한 동작을 수행합니다.
GROUP BY (grade, class, sex);
-- OR
GROUP BY (grade), (class), (sex);
-- OR
GROUP BY (grade, class), (sex);
-- OR
GROUP BY (grade), (class, (sex);
-- OR
GROUP BY grade, class, sex;GROUPING SETS, CUBE 또는 ROLL UP을 사용하면 여러 각도에서 좀 더 복잡한 그룹화를 수행할 수 있습니다. 이것을 다차원 분석이라고 합니다.
GROUPING SETS는 각각의 그룹화 표현식을 사용하여 쿼리를 실행하고 각각의 결과를 결합합니다. 예를 들어, 다음 쿼리는 (grade), (class), (sex)에 대해 쿼리를 수행한 뒤 결과를 결합합니다. 이 때, 그룹화에 사용되지 않은 컬럼은 null로 출력됩니다.
SELECT grade, class, sex, avg(height) as "avg_height"
FROM students
GROUP BY GROUPING SETS ((grade), (class), (sex));
만약 비어있는 컬럼집합을 사용한다면, 모든 데이터가 집계 함수로 넘어갑니다. 아무 컬럼도 사용되지 않아 (null, null)로 출력되었을 겁니다. 즉, 아래 사진에서 172.8이 전체 학생의 평균키입니다.
SELECT grade, class, avg(height) as "avg_height"
FROM students
GROUP BY GROUPING SETS ((grade), (class), ());
비어있는 컬럼집합을 통해 알 수 있듯이, 다차원 분석에 넘겨진 그룹들의 컬럼개수가 서로 동일하지 않아도 괜찮습니다.
SELECT grade, class, sex, avg(height) as "avg_height"
FROM students
GROUP BY GROUPING SETS ((grade, sex), (class), ());
ROLLUP은 각각의 그룹화 표현식 리스트를 뒤에서부터 하나씩 제거하면서(말아 올라가면서), 쿼리를 수행하고 각각의 결과를 결합합니다. ROLLUP을 GROUPING SETS로 표현하면 다음과 같습니다.
ROLLUP(a, b, c, ...)
GROUPING SETS (
(a, b, c, ...),
...,
(a, b, c),
(a, b),
(a),
()
)예를 들어, 다음 쿼리는 (grade, class, sex)부터 시작하여 (grade, class), (grade), ()까지 집계함수를 실행합니다.
SELECT grade, class, sex, avg(height) as "avg_height"
FROM students
GROUP BY ROLLUP(grade, class, sex);
-- OR
GROUP BY ROLLUP((grade), (class), (sex));
당연하지만, 이번에도 각각의 그룹의 컬럼개수는 일치하지 않아도 괜찮습니다. 이번에는 ((grade, class), (sex))부터 시작하여 ((grade, class)), ()까지 집계함수를 실행합니다.
SELECT grade, class, sex, avg(height) as "avg_height"
FROM students
GROUP BY ROLLUP((grade, class), (sex));
CUBE는 각각의 그룹화 표현식 리스트에서 가능한 모든 조합을 생성해낸 뒤, 집계함수를 실행하고 각각의 결과를 결합합니다. CUBE을 GROUPING SETS로 표현하면 다음과 같습니다.
CUBE (a, b, c);
-- OR
CUBE ((a), (b), (c));
GROUPING SETS (
( a, b, c ),
( a, b ), -- (a, b)
( a, c ), -- (a, c)
( a ), -- (a)
( b, c ), -- (b, c)
( b ), -- (b)
( c ), -- (c)
( ) -- ( )
)마찬가지로, 각각의 그룹의 컬럼개수가 동일하지 않아도 괜찮습니다. 아래의 쿼리는 다음 조합을 생성하여 집계함수에 넘깁니다.
((grade, class), (sex))((grade, class))((sex))()
SELECT grade, class, sex, avg(height) as "avg_height"
FROM students
GROUP BY CUBE((grade, class), (sex));
GROUP BY를 사용하지 않고 집계 함수를 사용할 수 있습니다. 이 경우 비어있는 그룹화 표현식을 사용한 것으로 간주됩니다.
SELECT avg(height) as "avg_height"
FROM students
-- GROUP BY ()이 때, 다른 컬럼을 이용하려고 하면 데이터 손실 규칙를 위반하므로 에러가 발생합니다.
SELECT grade, class, avg(height) as "avg_height"
FROM students
-- GROUP BY ()avg(exp):null이 아닌 값을 평균냄sum(exp):null이 아닌 값을 합계냄min(exp):null이 아닌 최소값을 찾아냄max(exp):null이 아닌 최대값을 찾아냄count(exp):null이 아닌 값의 개수를 반환함count(*): 모든 행의 개수를 반환함
더 많은 집계함수는 여기에서 확인할 수 있습니다.
위에서 보듯이 null이 아닌 값을 알아서 거르기 때문에, 각 컬럼마다 null이면 0으로 치환할 필요가 전혀 없으며, 불필요한 계산으로 인해 성능을 감소시키는 좋지 않은 패턴입니다. 단, 대상이 아무것도 없는 경우에는 집계함수 자체가 null을 반환하기 때문에, 겉에서는 치환해야 합니다.
avg(COALESCE(height, 0)); -- SO BAD.
COALESCE(avg(height), 0); -- GOOD!HAVING conditionGROUP BY을 수행하고 조건을 만족하지 않는 그룹을 결과에서 제거합니다. 기본적은 사항은 WHERE과 같으나 집계함수를 사용할 수 있다는 점이 다릅니다. 먼저 필터링하기 전의 쿼리와 그 결과부터 보고 시작하겠습니다.
SELECT grade, class, avg(height) as "avg_height"
FROM students
GROUP BY (grade, class);
클래스가 1반이고, 평균키가 170이상인 그룹만 남도록 필터링하면...
SELECT grade, class, avg(height) as "avg_height"
FROM students
GROUP BY (grade, class)
HAVING class = 1 AND 170 <= avg(height);
1반이 아닌 반은 어차피 거를 것 임에도 불구하고, 집계함수의 대상으로 포함되었기 때문에 불필요한 계산이 발생했다고 볼 수 있습니다. WHERE절이 먼저 실행되므로 다음 쿼리가 더 좋은 판단입니다.
SELECT grade, class, avg(height) as "avg_height"
FROM students
WHERE class = 1
GROUP BY (grade, class)
HAVING 170 <= avg(height);하지만 최신의 데이터베이스는 집계함수가 사용되지 않은 조건절을 WHERE로 옮기도록 최적화하기 때문에 어떤 쿼리를 사용해도 괜찮긴 하지만, 왜 성능이 향상되는지는 알아두는 편이 좋습니다.
[ WINDOW window_name AS ( window_definition ) [, ...] ]여러번 재사용되는 윈도우 구간을 정의합니다. 이 절을 이해하기 위해서는 먼저 윈도우 함수부터 알아야 합니다.
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_definition :
[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]frame_clause is one of :
{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]frame_start, frame_end is one of :
UNBOUNDED PRECEDING
offset PRECEDING
CURRENT ROW
offset FOLLOWING
UNBOUNDED FOLLOWINGframe_exclusion is one of :
EXCLUDE CURRENT ROW
EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS윈도우 함수는 집계 함수와 유사하지만 다중 행을 단일 행으로 압축하지는 않습니다.
하지만 윈도우 함수와 집계 함수는 거의 동일하기 때문에, OVER구문을 사용해야 윈도우 함수로 동작합니다. OVER구문이 사용되지 않은 경우에는 집계 함수로 동작합니다.
-- sum은 집계 함수
SELECT sum(salary) FROM empsalary;
-- sum은 윈도우 함수
SELECT sum(salary) OVER () FROM empsalary;윈도우 함수는 파티션과 프레임이라는 특별한 개념을 사용하는데, 간단하게 설명하면 다음과 같습니다.
- 프레임 :
행의 집합 - 파티션 :
프레임의 집합 - 테이블 :
파티션의 집합
각 행은 자신이 속한 프레임의 식별자를 가지고 있고, 윈도우 함수는 최상위 프레임에서 시작해서 각 프레임 구간에 윈도우 함수를 적용합니다.
프레임 구간은 꽤 중요한 용어입니다. 연속된 1개 이상의 프레임이 동시에 윈도우 함수에 전달될 수 있다는 것을 의미하기 때문입니다. PostgreSQL이 기본적으로 사용하는 파티션 구간은 다음과 같습니다.
프레임 시작점: 각 파티션의 첫 번째 프레임프레임 종료점: 현재 가르키고 있는 프레임
위의 기본값이 의미하는 것은 같은 파티션에서는 프레임이 누적된다는 것이며, 어떤 파티션이 [x, y, z]라는 프레임으로 나눠져 있다면, 각각 [x], [x, y], [x, y, z]를 타겟으로 하는 윈도우 함수가 차례대로 호출된다는 것입니다.
OVER (PARTITION BY ...) 구문을 사용하여 테이블을 파티션으로 나눌 수 있습니다. 먼저 예제 데이터를 생성합니다.
CREATE TABLE empsalary (
depname text,
empno int,
salary int
);
INSERT INTO empsalary VALUES
('develop', 11, 5200),
('develop', 7, 4200),
('develop', 9, 4500),
('develop', 8, 6000),
('develop', 10, 5200),
('personnel', 5, 3500),
('personnel', 2, 3900),
('sales', 3, 4800),
('sales', 1, 5000),
('sales', 4, 4800);depname을 기준으로 파티션을 나누고, 각 파티션의 평균 임금을 구해보겠습니다. 윈도우 함수는 프레임에만 적용된다는 것을 생각하면 의아할 수 있으나, ORDER BY가 생략되면 각 파티션이 하나의 프레임으로 간주되기 때문에 괜찮습니다.
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;| depname | empno | salary | avg |
|---|---|---|---|
| develop | 11 | 5200 | 5020.0000000000000000 |
| develop | 7 | 4200 | 5020.0000000000000000 |
| develop | 9 | 4500 | 5020.0000000000000000 |
| develop | 8 | 6000 | 5020.0000000000000000 |
| develop | 10 | 5200 | 5020.0000000000000000 |
| personnel | 5 | 3500 | 3700.0000000000000000 |
| personnel | 2 | 3900 | 3700.0000000000000000 |
| sales | 3 | 4800 | 4866.6666666666666667 |
| sales | 1 | 5000 | 4866.6666666666666667 |
| sales | 4 | 4800 | 4866.6666666666666667 |
PARTITION BY를 지정하지 않았다면 테이블이 하나의 파티션이 됩니다.
SELECT depname, empno, salary, avg(salary) OVER () FROM empsalary;| depname | empno | salary | avg |
|---|---|---|---|
| develop | 11 | 5200 | 4710.0000000000000000 |
| develop | 7 | 4200 | 4710.0000000000000000 |
| develop | 9 | 4500 | 4710.0000000000000000 |
| develop | 8 | 6000 | 4710.0000000000000000 |
| develop | 10 | 5200 | 4710.0000000000000000 |
| personnel | 5 | 3500 | 4710.0000000000000000 |
| personnel | 2 | 3900 | 4710.0000000000000000 |
| sales | 3 | 4800 | 4710.0000000000000000 |
| sales | 1 | 5000 | 4710.0000000000000000 |
| sales | 4 | 4800 | 4710.0000000000000000 |
PARTITION BY는 다중 컬럼도 허용합니다.
SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY (depname, empno)) FROM empsalary;| depname | empno | salary | sum |
|---|---|---|---|
| develop | 7 | 4200 | 4200 |
| develop | 8 | 6000 | 6000 |
| develop | 9 | 4500 | 4500 |
| develop | 10 | 5200 | 5200 |
| develop | 11 | 5200 | 5200 |
| personnel | 2 | 3900 | 3900 |
| personnel | 5 | 3500 | 3500 |
| sales | 1 | 5000 | 5000 |
| sales | 3 | 4800 | 4800 |
| sales | 4 | 4800 | 4800 |
ORDER BY를 사용하여 현재 파티션을 프레임으로 나누어 정렬할 수 있습니다. 함께 기술된 정렬 조건에서 최상위에 위치한 프레임부터 윈도우 함수가 적용됩니다.
SELECT depname, empno, salary, sum(salary) OVER (ORDER BY depname) FROM empsalary;| depname | empno | salary | sum |
|---|---|---|---|
| develop | 11 | 5200 | 25100 |
| develop | 7 | 4200 | 25100 |
| develop | 9 | 4500 | 25100 |
| develop | 8 | 6000 | 25100 |
| develop | 10 | 5200 | 25100 |
| personnel | 5 | 3500 | 32500 |
| personnel | 2 | 3900 | 32500 |
| sales | 3 | 4800 | 47100 |
| sales | 1 | 5000 | 47100 |
| sales | 4 | 4800 | 47100 |
위의 쿼리는 다음 순서대로 동작합니다.
PARTITION BY가 선언되지 않았으므로 테이블이 하나의 파티션이 됩니다.ORDER BY가 선언되었으므로 파티션이depname으로 3개의 프레임으로 나뉩니다.- 정렬 요구조건에 따라
develop,personnel,sales에sum()을 적용합니다. 프레임 구간 옵션은 기본값(동일 파티션 내, 프레임 누적)이 사용됩니다.
결과를 의사코드로 재구성해보면 다음과 같습니다.
25100: sum(develop)32500: sum(develop,personnel)47100: sum(develop,personnel,sales)
윈도우 함수에는 연속적인 1개 이상의 프레임을 동시에 넘길 수 있습니다. 이 때, 프레임 구간을 설정하려면 시작점과 종료점이 주어져야 하죠. 이 지점을 넘기는 방식은 BETWEEN의 사용여부에 따라 2가지로 나뉩니다.
BETWEEN을 함께 사용하려면, 아래의 쿼리처럼 srt와 end가 함께 주어져야 합니다. 이 경우에는 [srt, end]가 프레임 구간이 됩니다.
OVER ( ... BETWEEN srt AND end)BETWEEN을 사용하지 않으려면 point를 하나만 주어도 괜찮습니다. 이 경우에는 point가 CURRENT ROW보다 작다면 srt로 간주되고, 그렇지 않다면 end로 간주됩니다. 물론 반대편은 CURRENT ROW가 됩니다.
OVER ( ... p)포인트는 다음과 같이 주어질 수 있습니다.
UNBOUNDED PRECEDING: 해당 파티션의 첫 행.UNBOUNDED FOLLOWING: 해당 파티션의 마지막 행.n PRECEDING: 현재 행에서n칸 전의 행 또는 프레임.n FOLLOWING: 현재 행에서n칸 후의 행 또는 프레임.CURRENT ROW: 현재 프레임의 마지막 행 또는 프레임.
윈도우 함수는 프레임을 기준으로 삼는다고 했는데, 뜬금없이 행에 관련된 얘기가 튀어나왔습니다. 이 의문에 대한 대답은 다음 절인 프레임 구간 모드에서 자세히 다룹니다.
사실 프레임 구간의 각 지점은 프레임 단위 뿐만이 아니라 행 단위로도 지정할 수 있습니다. 어떤 단위로 시작점을 정의할 것 인지 프레임 구간 모드를 명시적으로 적어서 설정할 수 있습니다.
현재 사용할 수 있는 프레임 구간 모드(mode)는 다음 3가지가 있습니다.
ROWS: 행 단위 이동. 프레임 보정 없음.RANGE: 행 단위 이동. 단, 가장 가까운 프레임으로 이동.GROUPS: 프레임 단위 이동.
먼저 예제를 생성해보겠습니다.
CREATE TABLE numbers (
n int
);
INSERT INTO numbers VALUES
(0),
(0),
(1),
(5),
(8),
(8),
(14),
(14),
(14),
(22);그리고 동일한 숫자끼리 같은 프레임으로 묶은 뒤, 프레임 구간을 조절해가면서 합계를 구해보겠습니다.
SELECT n, sum(n) OVER (ORDER BY n ...) FROM numbers;먼저 각 모드들은 CURRENT ROW에 대한 개념부터 다릅니다. ROWS는 현재 행만 가르키지만, RANGE는 현재 프레임의 마지막 행, GROUPS는 현재 프레임을 가르킵니다.
다음으로 n PRECEDING/FOLLOWING의 이동방식이 다릅니다. ROWS는 카운트 행 단위, GROUPS는 카운트 프레임 단위로 이동하지만 RANGE는 오프셋 값 단위로 이동합니다.
RANGE에 대해 자세히 설명하면 3 PRECEDING은 현재 프레임의 값인 8에서 3이전의 값인 5를 찾는다는 뜻입니다. 이 때, 값이 완벽하게 일치하는 것이 없다면, 그것보다 큰 값을 갖는 프레임을 선택합니다.
이러한 이유로 RANGE 모드에서 1 PRECEDING과 2 PRECEDING은 현재 프레임을 벗어나지 못합니다.
앞서 정의된 프레임 구간에서, 특정 행이나 프레임을 제외시킬 수 있습니다. 가능한 옵션은 다음과 같습니다.
EXCLUDE CURRENT ROW
현재 행만 제외합니다. GROUPS 또는 RANGE 모드로 작동하더라도, 그 프레임의 1개 행만 제외합니다. 같은 프레임의 다른 데이터는 살아남습니다.
EXCLUDE GROUP
현재 프레임의 데이터 전체를 제외합니다. ROWS 모드로 작동하더라도 다중 행이 제외될 수 있습니다.
EXCLUDE TIES
프레임 데이터를 묶어서 1개로만 취급합니다. 프레임에 여러 행이 있더라도 1개 행만 전달됩니다.
EXCLUDE NO OTHERS (default)
아무것도 제외하지 않습니다.
같은 윈도우가 여러번 사용되는 경우, SELECT의 부속절인 WINDOW절에서 미리 선언할 수 있습니다.
SELECT salary, sum(salary) over (my_window)
FROM empsalary
WINDOW my_window AS (ORDER BY salary);ROW_NUMBER()
각 파티션의 최상위 행부터 출발하여 1부터 번호를 매깁니다. 등수를 매기는데에도 사용될 수 있습니다.
RANK()
각 파티션의 최상위 행부터 출발하여 1부터 번호를 매깁니다. 중복된 값에는 같은 번호를 매기지만, 각 번호가 연속적이지 않을 수 있습니다.
DENSE_RANK()
각 파티션의 최상위 행부터 출발하여 1부터 번호를 매깁니다. 중복된 값에는 같은 번호를 매기며, RANK()와 다르게 연속적으로 번호를 부여합니다.
NTILE(n)
각 파티션에 데이터를 n개의 버킷에 최대한 공평하게 분배하고, 버킷의 번호를 반환합니다. 분배 규칙은 다음과 같습니다.
x개를n으로 나눈 몫과 나머지를 구한다. (11 / 4 = Q(2) + R(3))- 각 버킷에 몫을 더한다. bucket_size = [
2,2,2,2] - 나머지를 앞선 버킷부터 더한다. bucket_size = [
2+1,2+1,2+1,2]
select ...
{UNION | INTERSECT | EXCEPT} [ALL | EXCEPT]
select ...집합 연산자는 호환 가능한 두 SELECT의 합집합, 교집합, 차집합을 계산합니다. 여기서 호환 가능하다라는 의미는 다음 조건을 의미합니다.
- 컬럼의 개수가 같다.
- 각 컬럼의 타입이 같다.
컬럼의 이름은 일치하지 않아도 괜찮습니만, 좌측에서 사용된 컬럼의 이름을 우선적으로 사용합니다. 현재는 ORDER BY와 WITH LOCKING구문이 없어야만 사용할 수 있습니다.
UNION은 두 집합의 합집합을 계산합니다. EXCEPT(default)가 사용되면 중복된 행을 제거합니다. ALL이 사용되면 중복된 행을 제거하지 않습니다.
INTERSECT는 두 집합의 교집합을 계산합니다. EXCEPT(default)가 사용되면 중복된 행을 제거합니다. ALL이 사용되면 중복된 행이 min(좌측중복개수, 우측중복개수) 만큼 출력됩니다.
EXCEPT는 왼쪽 집합에서 오른쪽 집합을 뺀 차집합을 계산합니다. EXCEPT(default)가 사용되면 중복된 행을 제거합니다. ALL이 사용되면 중복된 행이 min(좌측중복개수 - 우측중복개수, 0) 만큼 출력됩니다.
ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...]결과 집합을 주어진 정렬조건을 사용하여 정렬하며, 표현식에 컬럼 또는 계산식을 사용할 수 있습니다. 컬럼에는 컬럼이름 또는 좌측에서부터 부여된 서수번호를 사용하여 지정할 수 있습니다.
SELECT val FROM data ORDER BY val;
SELECT val FROM data ORDER BY 1;
SELECT val FROM data ORDER BY val * val;컬럼의 이름을 사용하는 경우 입력 컬럼이름과 결과 컬럼이름을 둘 다 사용할 수 있지만, 둘의 이름이 겹친경우 출력 컬럼이름을 우선적으로 사용합니다.
CREATE TABLE data (
k int,
v int
);
INSERT INTO data VALUES
(2, 1),
(1, 3),
(3, 2);
SELECT
k as g,
v as k
FROM data ORDER BY k;| g | k |
|---|---|
| 2 | 1 |
| 3 | 2 |
| 1 | 3 |
정렬에 사용된 컬럼이 SELECT-List에 포함되지 않아도 됩니다.
SELECT k FROM data ORDER BY v;[OFFSET srt]
[LIMIT {cnt | ALL}]결과 집합에서 첫 srt개를 무시하고, 다음에 만나는 cnt개를 반환합니다. ALL(default)인 경우 행의 끝까지 반환합니다.
FOR lock_strength [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ]lock_strength is one of :
UPDATE
NO KEY UPDATE
SHARE
KEY SHAREOF절에 명시된 테이블에 ROW-LEVEL LOCK을 걸 수 있습니다. 만약 OF절이 생략되어 있다면 같은 레벨의 FROM절에서 사용된 모든 테이블에 적용됩니다. 이것은 서브쿼리에서 사용된 잠금이 상위쿼리에 영향을 끼치지 않으며, WITH 쿼리와 메인 쿼리의 잠금절은 서로 별개로 동작한다는 것을 의미합니다.
아래처럼 하나의 테이블에 대해 여러개의 잠금이 정의될 수 있습니다. 하지만 이러한 경우에는 가장 제약이 강한 것 하나만 적용됩니다. 아래의 쿼리는 SHARE와 UPDATE잠금이 동시에 적용되었으므로, 가장 제약이 강한 UPDATE만 적용됩니다.
SELECT * FROM t
FOR SHARE OF t
FOR UPDATE OF t어떤 행은 다른 트랜잭션에서 이미 잠금이 걸려있을 수 있습니다. 보통은 해당 행의 잠금이 풀릴때까지 대기하지만, 이것을 원치 않는 경우 NOWAIT또는 SKIP LOCKED를 사용하여 방지할 수 있습니다.
NOWAIT: 잠금해제를 기다리지 않고, 즉시 에러를 발생시킵니다.SKIP LOCKED: 잠금이 걸린 행만 무시하고 진행합니다.
LIMIT과 OFFSET이 같이 사용된 경우, LIMIT 이후의 행에는 잠금이 걸리지 않습니다. 하지만 OFFSET 이전의 행에는 잠금이 걸림에 주의해야 합니다. 마찬가지로 CURSOR-QUERY에 사용된 경우, 아직 가져오지 않은 행에만 락이 적용되지 않습니다.
내부 쿼리에서 광범위하게 락을 적용했더라도, 외부 쿼리에서 WHERE절로 행을 제한하면, 필터링된 행에는 락이 적용되지 않습니다. 즉, 내부 쿼리에 조건이 명시적으로 사용되지 않았더라도, 외부 쿼리의 조건에 의해 잠금범위가 축소될 수 있습니다. 예를 들어, 아래의 쿼리에서 n=5를 만족하는 행에만 잠금이 적용되며, 나머지 행에는 락이 적용되지 않습니다.
SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss WHERE n = 5;9.3 이상의 버전은 ROLLBACK TO를 사용하면 잠금을 유지할 수 없습니다. 예를 들어, 아래의 쿼리는 mytable에 걸린 FOR UPDATE 잠금이 해제됩니다.
BEGIN;
SELECT * FROM mytable WHERE key = 1 FOR UPDATE;
SAVEPOINT s;
UPDATE mytable SET ... WHERE key = 1;
ROLLBACK TO s;각 행은 갱신, 삭제, 조회를 방지하기 위한 락이 설정될 수 있습니다. 여러개의 잠금이 동시에 설정될 수 있지만 충돌하지 않는 락으로만 구성되어야 합니다. 행 잠금의 종류는 다음과 같으며, 위에 있을수록 더 강력한 잠금입니다.
UPDATENO KEY UPDATESHAREKEY SHARE
UPDATE
갱신 또는 삭제를 위한 배타적 잠금입니다. 배타적 잠금은 타 트랜잭션의 아래의 구문을 블럭합니다.
UPDATEDELETESELECT FOR UPDATESELECT FOR NO KEY UPDATESELECT FOR SHARESELECT FOR KEY SHARE
NO KEY UPDATE
기본적으로는 FOR UPDATE와 동일하지만 조금 더 약합니다. SELECT FOR KEY SHARE를 블럭하지 않습니다. 이 잠금은 FOR UPDATE를 사용하지 않은 UPDATE구문에 의해서도 획득됩니다. 즉, 타 트랜잭션의 아래의 구문을 블럭합니다.
UPDATEDELETESELECT FOR UPDATESELECT FOR NO KEY UPDATESELECT FOR SHARE
SHARE
기본적으로는 NO KEY UPDATE와 동일하지만 조금 더 약합니다. 다만, 위에서 소개된 잠금이 배타적 잠금을 걸었다면 이하의 잠금은 공유적 잠금을 겁니다. 공유적 잠금은 타 트랜잭션의 아래의 구문을 블럭합니다.
UPDATEDELETESELECT FOR UPDATESELECT FOR NO KEY UPDATE
SELECT FOR KEY SHARE
기본적으로는 FOR SHARE와 동일하지만 조금 더 약합니다. SELECT FOR SHARE를 블럭하지 않습니다. 즉, 타 트랜잭션의 아래의 구문을 블럭합니다.
UPDATEDELETESELECT FOR UPDATE
위의 내용을 표로 정리하면 다음과 같습니다.
※ 모든 락은 UPDATE와 DELETE를 블럭합니다.
PostgreSQL에서 행이 잠김다는 것은 행에 잠금 비트가 쓰여진다는 것을 의미합니다. 즉, 행을 잠그면 디스크 쓰기가 발생합니다.
다음 두 개의 쿼리는 서로 같습니다.
TABLE table_name;
SELECT * FROM table_name;하지만 다음 하위절만 지원합니다.
WITHUNION/INTERSECT/EXCEPTORDER BYLIMIT/OFFSETFOR LOCKING
따라서 아래의 하위절은 지원하지 않습니다.
WHEREGROUP BY/HAVING(AGGREGATION)
일부 데이터베이스는 더미 테이블을 사용해야 하지만, PostgreSQL은 아예 FROM절을 사용하지 않습니다.
SELECT 2+2;
?column?
----------
4그러나 FROM절 없이 테이블 컬럼에 접근할 수 없습니다.
SELECT distributors.* WHERE distributors.name = 'Westward';비어있는 결과집합을 표현하기 위해 SELECT-LIST를 비워둘 수 있습니다. 즉, 아래의 쿼리는 공집합을 표현합니다.
SELECT FROM t;여기서도 FROM절은 생략할 수 있습니다.
SELECT별칭을 표현하는 AS는 생략될 수 있습니다.
SELECT t.col1 x
FROM mytable tSQL Standard는 ONLY를 사용할 경우 소괄호를 함께 사용해야 합니다.
SELECT * FROM ONLY (tab1), ONLY (tab2) WHERE ...PostgreSQL은 소괄호 생략 스타일도 지원합니다.
SELECT * FROM ONLY tab1, ONLY tab2 WHERE ...표준에 의하면 모든 FROM절에서 이 기능을 지원해야 하지만, PostgreSQL은 regular tables 또는 materialized view에만 적용할 수 있습니다.
표준에 의하면 FROM절에서 함수호출을 하기 위해서는 서브쿼리로 감쌀 필요가 있습니다. 즉, PostgreSQL에서 아래의 쿼리는 서로 같습니다.
FROM func(...) alias
FROM LATERAL (SELECT func(...)) alias이전에 설명했듯이 FROM 절에서 함수호출이 이루어지면, LATERAL 키워드는 생략하든, 생략하지 않든 강제적으로 적용됩니다.
LATERAL은 함수호출의 앞에 사용할 수 있지만, 이 경우에는Noise Word입니다. 있으나 없으나LATERAL로 간주되기 때문입니다.
표준에 의하면 GROUP BY와 ORDER BY에서 사용될 수 있는 것은 컬럼과 서수뿐입니다. 하지만 PostgreSQL은 이것을 더욱 확장하여 표현식도 사용할 수 있습니다. 단, 표현식에서 사용되는 컬럼은 입력컬럼이름을 우선적으로 사용한다는 것을 기억해야 합니다.
표준은 OFFSET / FETCH 이지만, PostgreSQL은 LIMIT / OFFSET구문도 지원합니다. 이러한 스타일은 IBM DB2에서도 사용되고 있습니다.
표준에서 FOR ... 구문은 CURSOR-QUERY에서만 사용할 수 있지만, PostgreSQL은 SUB-QUERY와 WITH-QUERY를 비롯한 모든 쿼리에서 사용할 수 있습니다. 또한 NOWAIT와 SKIP LOCKED옵션은 표준이 아닙니다.
PostgreSQL은 WITH-QUERY에서 INSERT, UPDATE, DELETE를 사용할 수 있습니다. 그러나 표준에서는 이러한 조항이 명시되어 있지 않습니다.
다음 구문은 표준을 확장한 것 입니다.
DISTINCT ON ( ... )ROWS FROM( ... )WITH에서MATERIALIZED,NOT MATERIALIZED옵션