Skip to content

PostgreSQL:Select

Operator

Operator

Description

=

Equal

>

Greater than

<

Less than

>=

Greater than or equal

<=

Less than or equal

<> or !=

Not equal

AND

Logical operator AND

OR

Logical operator OR

IN

Return true if a value matches any value in a list

BETWEEN

Return true if a value is between a range of values

LIKE

Return true if a value matches a pattern

IS NULL

Return true if a value is NULL

NOT

Negate the result of other operators

Timestamp

Join

SQL:Join 항목 참조.

LIMIT 

-- 처음 10개의 Row를 반환
SELECT * FROM test LIMIT 10;

OFFSET 

-- 위 SQL과 아래의 SQL은 같은 결과
SELECT * FROM test LIMIT 10 OFFSET 0;

LIMIT and OFFSET 

-- 11번째 부터 10개의 Row를 반환.
SELECT * FROM test LIMIT 10 OFFSET 10;

Left join

Left join example: 

SELECT
    u.uid AS uid,
    pm.project_uid AS project_uid,
    gm.group_uid AS group_uid,
    CASE
        WHEN pm.permission_uid IS NOT NULL THEN pm.permission_uid
        ELSE gm.permission_uid
    END AS permission_uid
FROM
    recc_user u
    LEFT JOIN recc_project_member pm ON pm.user_uid=u.uid
    LEFT JOIN recc_project p ON p.uid=pm.project_uid 
    LEFT JOIN recc_group_member gm ON gm.user_uid=u.uid
ORDER BY u.uid;

참고로, 위에서 NULL 체크하는 CASE 문은 COALESCE함수로 대체할 수 있다.

NOT EXISTS

Often fastest in Postgres. 

SELECT ip 
FROM   login_log l 
WHERE  NOT EXISTS (
   SELECT  -- SELECT list mostly irrelevant; can just be empty in Postgres
   FROM   ip_location
   WHERE  ip = l.ip
   );

LEFT JOIN / IS NULL

Sometimes this is fastest. Often shortest. Often results in the same query plan as NOT EXISTS. 

SELECT l.ip 
FROM   login_log l 
LEFT   JOIN ip_location i USING (ip)  -- short for: ON i.ip = l.ip
WHERE  i.ip IS NULL;

EXCEPT / EXCEPT ALL

Short. Not as easily integrated in more complex queries. 

SELECT ip 
FROM   login_log

EXCEPT ALL  -- "ALL" keeps duplicates and makes it faster
SELECT ip
FROM   ip_location;

중복을 제거하고 싶지 않다면 EXCEPT ALL를 사용하면 된다.

NOT IN

Only good without NULL values or if you know to handle NULL properly. I would not use it for this purpose.

:틀:WARNING 

SELECT ip 
FROM   login_log
WHERE  ip NOT IN (
   SELECT DISTINCT ip  -- DISTINCT is optional
   FROM   ip_location
   );

CASE ~~ WHEN ~~ THEN ~~ ELSE ~~ END

조건식을 사용할 때 문법 

SELECT
    CASE
        WHEN
            user_name IS NOT NULL THEN user_name
        ELSE
            NULL
        END AS worker_name;

임시 테이블 JOIN 

select * from
  (
   <your first query here>
  ) tbl1
  join (
    <your second query here>
  ) tbl2
  on tbl1.c_project_Id = tbl2.c_project_Id
 and tbl1.c_projectphase_Id = tbl2.c_projectphase_Id -- you might add or
 and tbl1.c_projecttask_Id  = tbl2.c_projecttask_Id  -- remove join criteria 
 and tbl1.m_product_Id = tbl2.m_product_Id           -- here

특정 테이블의 전체 컬럼을 Select 결과에 추가

table.*과 같이 사용하면 된다: 

SELECT myTable.*, otherTable.foo, otherTable.bar...

FOR UPDATE

SELECT ... FOR UPDATE는 SELECT한 row에 row-level lock을 획득한다. 같은 row를 잠그려는 다른 트랜잭션은 첫 번째 트랜잭션이 커밋될 때까지 대기한 후 최신 데이터로 재조회한다.

PostgreSQL 기본 격리 수준 READ COMMITTED에서, 각 트랜잭션은 다른 트랜잭션이 커밋한 데이터만 볼 수 있다. 이로 인해 동시 트랜잭션이 동일한 조건을 검사할 때 TOCTOU(Time-of-Check to Time-of-Use) 문제가 발생할 수 있으며, FOR UPDATE로 이를 해결한다.

문제: Race Condition

"마지막 1개는 삭제할 수 없다"와 같은 제약을 trigger로 구현할 때, 동시 트랜잭션이 서로의 미커밋 변경을 볼 수 없어 제약이 우회될 수 있다.

재현 시나리오

owner A와 owner B가 동일 조직의 유일한 owner 2명일 때, 동시에 탈퇴를 시도하는 경우:

시간

Tx A (owner A 탈퇴)

Tx B (owner B 탈퇴)

t1

DELETE FROM org_members WHERE user_id = A
trigger: COUNT owners excl. A → B 존재 → count=1 → 통과

t2

DELETE FROM org_members WHERE user_id = B
trigger: COUNT owners excl. B → A 존재 (미커밋) → count=1 → 통과

t3

COMMIT

COMMIT

결과

A 삭제 + B 삭제 → owner 0명 → 조직 고아화

t2에서 Tx A의 DELETE가 아직 커밋되지 않았으므로, Tx B는 A가 여전히 존재한다고 판단한다.

해결: FOR UPDATE 적용

시간

Tx A

Tx B

t1

DELETE → trigger 실행
SELECT ... FOR UPDATE
→ B row에 lock 획득
→ count=1 → 통과

t2

DELETE → trigger 실행
SELECT ... FOR UPDATE
→ A row lock 대기

t3

COMMIT (A 삭제, lock 해제)

lock 획득, 최신 데이터로 재조회
→ A 삭제됨 → count=0
→ RAISE EXCEPTION

결과

A 삭제 + B 삭제 차단 → owner 1명 유지

구문 

-- 기본: SELECT한 모든 row에 lock
SELECT * FROM table_name
WHERE condition
FOR UPDATE;

-- OF: JOIN 시 특정 테이블의 row만 lock
SELECT * FROM table_a a
JOIN table_b b ON a.id = b.a_id
WHERE condition
FOR UPDATE OF a;

-- NOWAIT: lock 대기 없이 즉시 실패
SELECT * FROM table_name
WHERE condition
FOR UPDATE NOWAIT;

-- SKIP LOCKED: lock된 row를 건너뛰고 나머지만 반환 (큐 패턴)
SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

See also

Favorite site