Skip to content
Search

PostgreSQL:Select

lastmod 2026-02-26created 2021-03-0213 edits

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

See SQL:Join for details.

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;

For reference, the CASE statement above that checks for NULL can be replaced with the COALESCE function.

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;

If you don't want to remove duplicates, use 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.

:Template:WARNING 

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

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

Syntax for using conditional expressions 

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

Temporary table 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

Adding all columns of a specific table to the Select result

Use it like table.*: 

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

FOR UPDATE

SELECT ... FOR UPDATE acquires a row-level lock on the selected rows. Other transactions trying to lock the same rows wait until the first transaction commits and then re-read the latest data.

Under PostgreSQL's default isolation level, READ COMMITTED, each transaction can see only data committed by other transactions. As a result, a TOCTOU (Time-of-Check to Time-of-Use) problem can occur when concurrent transactions check the same condition, and FOR UPDATE solves it.

Problem: Race Condition

When a constraint such as "the last one cannot be deleted" is implemented with a trigger, concurrent transactions cannot see each other's uncommitted changes, so the constraint can be bypassed.

Reproduction scenario

When owner A and owner B are the only 2 owners of the same organization and both try to leave at the same time:

Time

Tx A (owner A leaves)

Tx B (owner B leaves)

t1

DELETE FROM org_members WHERE user_id = A
trigger: COUNT owners excl. A → B exists → count=1 → passes

t2

DELETE FROM org_members WHERE user_id = B
trigger: COUNT owners excl. B → A exists (uncommitted) → count=1 → passes

t3

COMMIT

COMMIT

Result

A deleted + B deleted → 0 owners → organization orphaned

At t2, Tx A's DELETE has not been committed yet, so Tx B concludes that A still exists.

Solution: apply FOR UPDATE

Time

Tx A

Tx B

t1

DELETE → trigger runs
SELECT ... FOR UPDATE
→ acquires lock on B row
→ count=1 → passes

t2

DELETE → trigger runs
SELECT ... FOR UPDATE
→ waits for A row lock

t3

COMMIT (A deleted, lock released)

acquires lock, re-reads latest data
→ A deleted → count=0
→ RAISE EXCEPTION

Result

A deleted + B deletion blocked → 1 owner kept

Syntax 

-- 기본: 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

author Zer0 sitemap