Skip to main content

DML Formatting

INSERT, UPDATE, DELETE and MERGE follow the rule every statement follows: a statement that fits stays on one line, and one that does not breaks one clause per line, with VALUES, SET and FROM each taking their own line and their bodies indented beneath them.

INSERT VALUES​

insert into t (a, b) values (1, 2)
insert into t(a, b) values (1, 2)

INSERT SELECT​

insert into t select a from t2
insert into t select a from t2

UPDATE SET​

update t set a = 1 where b = 2
update t set a = 1 where b = 2

UPDATE SET multiple columns​

update t set a = 1, b = 2, c = 3 where id = 1
update t set a = 1, b = 2, c = 3 where id = 1

DELETE FROM​

delete from t where a = 1
delete from t where a = 1

INSERT VALUES multi-row​

insert into t (a, b) values (1, 2), (3, 4)
insert into t(a, b) values (1, 2), (3, 4)

INSERT VALUES single row unchanged​

Single row should look the same as multi-row format.

insert into t (a, b) values (1, 2)
insert into t(a, b) values (1, 2)

CREATE TABLE single column​

create table t (id int)
create table t(id int)

Nested subqueries (double nesting)​

select a from t where a in (select b from t2 where b in (select c from t3))
select a from t where a in (select b from t2 where b in (select c from t3))
select a from t where a in (select b from t2 where b in (select c from t3))
select a
from t
where a in (
    select b
    from t2
    where b in (
        select c
        from t3
    )
)

Subquery in WHERE​

select a from t where a in (select b from t2)
select a from t where a in (select b from t2)
select a from t where a in (select b from t2)
select a
from t
where a in (
    select b
    from t2
)

Subquery in FROM​

select a from (select b from t2) sub
select a from (select b from t2) sub
select a from (select b from t2) sub
select a
from (
    select b
    from t2
) sub

WITH CTE​

with cte as (select 1 as a) select a from cte
with cte as (select 1 as a) select a from cte
with cte as (select 1 as a) select a from cte
with
    cte as (
        select 1 as a
    )
select a
from cte

UNION ALL​

select a from t1 union all select b from t2
select a from t1 union all select b from t2

MERGE basic​

Each WHEN arm is a clause of the statement and starts its own line, at the statement's column like every other clause. USING and ON fit on the MERGE line, so they stay there.

merge into t1 using t2 on t1.id = t2.id when matched then update set t1.a = t2.a when not matched then insert (a) values (t2.a)
merge into t1 using t2 on t1.id = t2.id
when matched then update set t1.a = t2.a
when not matched then insert (a) values (t2.a)

INSERT SELECT with WHERE​

INSERT...SELECT with a WHERE clause.

insert into t select a, b from t2 where x > 1
insert into t select a, b from t2 where x > 1

CREATE TABLE with NOT NULL and DEFAULT​

CREATE TABLE with column constraints.

create table t (id int not null, name varchar(100) default 'unknown', active boolean not null default true)
create table t(
    id int not null,
    name varchar(100) default 'unknown',
    active boolean not null default true
)

Multiple CTEs​

Multiple CTEs with commas.

with cte1 as (select 1 as a), cte2 as (select 2 as b) select a, b from cte1, cte2
with cte1 as (select 1 as a), cte2 as (select 2 as b)
select a, b
from cte1, cte2
with cte1 as (select 1 as a), cte2 as (select 2 as b) select a, b from cte1, cte2
with
    cte1 as (
        select 1 as a
    ),

    cte2 as (
        select 2 as b
    )
select a, b
from cte1, cte2

Messy INSERT with extra whitespace​

insert   into   t  ( a ,  b ,  c )  values  ( 1 ,  2 ,  3 ) , ( 4 ,  5 ,  6 )
insert into t(a, b, c) values (1, 2, 3), (4, 5, 6)

UPDATE SET with subquery​

Operator followed by opening paren should have a space: = ( not =(.

update t set a = (select max(b) from t2) where id = 1
update t set a = (select max(b) from t2) where id = 1
update t set a = (select max(b) from t2) where id = 1
update t
set
    a = (
        select max(b)
        from t2
    )
where id = 1

INSERT VALUES multi-row adaptive: preserves 3-per-line grouping​

insert into t (a) values (1), (2), (3),
(4), (5), (6),
(7)
insert into t(a) values (1), (2), (3), (4), (5), (6), (7)

INSERT VALUES multi-row adaptive: preserves 2-per-line grouping​

insert into t (a) values (1), (2),
(3), (4),
(5)
insert into t(a) values (1), (2), (3), (4), (5)

CREATE TABLE​

create table t (id int, name varchar(100) not null)
create table t(id int, name varchar(100) not null)