Alignment

align lines up sibling rows of a construct so their markers share a column. It names the CONSTRUCTS that line up, each by a name of its own:

value	what lines up	what the column is keyed on
aliases	a select list's expr as name	the as — and an alias written without one still joins the name column
case_arms	a case's when … then …, and its else	the then, with the else's value under the arms' values
assignments	an update … set's target = value	the =
comparisons	a where's conditions — =, <>, <, >, <=, >= and the rest alike	the OPERATOR, with each value following its own
named_arguments	a call's name => value	the =>, or the := of the same construct
column_definitions	a table's columns	nothing — the name, the type and the rest are three cells

The value is a comma-separated set and align: all is every construct there is, now and as more are added. It is empty by default, so nothing is padded until you name something. A name nobody claims is refused, with the list above, rather than quietly aligning nothing.

It names constructs, not tokens

A token is not the thing being lined up, and three facts make that more than a wording preference.

One token is written by two constructs. = is an assignment's and a comparison's, and a where's conditions have never shared a column with a set's assignments — so there is a real question ("which of them did you mean?") that naming the token cannot answer. align: assignments and align: comparisons answer it separately, and align: assignments,comparisons asks for both.

One construct is written with two tokens. make_interval(years => 1) and make_interval(years := 1) are the same named argument spelled two ways, and both line up under named_arguments.

One construct has no marker token at all. A column definition is a name, a type and whatever follows; there is no symbol in it to name. column_definitions could not have been spelled as a token even in principle.

How the padding is made

The padding is part of the layout rather than a pass over the finished text. It is written as a wider gap in front of the marker, and that gap is the same number the solver prices when it decides where lines break, so alignment and line breaking are settled together instead of overruling each other.

Three things have to be true before anything moves. Between them they account for nearly every time the option looks as though it did nothing.

Two rows have to carry the construct. A group of one aligns with nothing, so select count(*) as cnt from t, select case when x then 'A' end from t and update t set a = 1 where id = 1 are the same text whatever align says.

Each row has to start a line of its own. Rows that share a line share no column, so alignment only appears once maxLineWidth has broken the list apart. This is the common reason a short statement looks unaffected.

The column has to be reachable inside the margin. Every column a group could use is weighed against the others, and only columns its rows can actually reach are considered at all. A row reaches a column in one of two ways: the marker sits at the column on the row's own line, with the gap in front of it widened to get there; or the marker takes the line below and begins that line at the column, under the markers of the rows above it. A row that can reach a column does, so the only rows left where they were are the ones that cannot reach it on either line without running past the margin — and a row standing out of the column does not take the group with it. Padding is never bought by taking an expression apart: a row that fits its line keeps the shape it has.

There is one other way a construct comes into force, and it is not this option: preserveBreaks breaks_and_alignment reads the padding you already wrote and re-emits it, for any construct, whatever align names.

Aligning a select list's aliases

The same list, first with align at its default and then with aliases named.

select customer_id as cust, count(order_id) as order_count, sum(total) as total_spent from orders

select
    customer_id as cust,
    count(order_id) as order_count,
    sum(total) as total_spent
from orders

select customer_id as cust, count(order_id) as order_count, sum(total) as total_spent from orders

select
    customer_id     as cust,
    count(order_id) as order_count,
    sum(total)      as total_spent
from orders

An alias written without AS joins the same column

aliases lines up the NAMES, not the as keywords — so an item that leaves as out is a row of the same table with its marker column empty, and its name still lands under the others'. order_count below sits exactly where cust and total_spent do.

select customer_id as cust, count(order_id) order_count, sum(total) as total_spent from orders

select
    customer_id as cust,
    count(order_id) order_count,
    sum(total) as total_spent
from orders

select customer_id as cust, count(order_id) order_count, sum(total) as total_spent from orders

select
    customer_id     as cust,
    count(order_id)    order_count,
    sum(total)      as total_spent
from orders

The widest expression sets the column

very_long_column_name keeps a single space before its as, and the shorter expressions pad out to meet it.

select customer_id as cust, total_amount as total, very_long_column_name as short from t

select
    customer_id           as cust,
    total_amount          as total,
    very_long_column_name as short
from t

At a narrower margin the widest row cannot hold its alias on its own line at all, so the alias takes the line below — and begins that line at the column, where the two shorter rows' aliases are. One column, three as.

select
    customer_id  as cust,
    total_amount as total,
    very_long_column_name
                 as short
from t

Two aliases are enough

select customer_id as cust, total_amount as total from t

select
    customer_id as cust,
    total_amount as total
from t

select customer_id as cust, total_amount as total from t

select
    customer_id  as cust,
    total_amount as total
from t

Items without an alias do not take part

An item with no AS is not in the group, so it is neither padded nor measured.

select customer_id as cust, total_amount, name as n, another_very_long_column as col from t

select
    customer_id              as cust,
    total_amount,
    name                     as n,
    another_very_long_column as col
from t

Aliased and unaliased items interleaved

select a, count(b) as cnt, c, sum(d) as total from t

select
    a,
    count(b) as cnt,
    c,
    sum(d)   as total
from t

An unaliased item between two aliased ones

select customer_id as cust, total_amount, name as n from t

select
    customer_id as cust,
    total_amount,
    name        as n
from t

A list of aggregates

select customer_id, count(order_id) as order_count, sum(total) as total_spent, avg(total) as avg_order from orders

select
    customer_id,
    count(order_id) as order_count,
    sum(total)      as total_spent,
    avg(total)      as avg_order
from orders

Alignment waits for the list to break

At 80 characters this list stays on one line, and rows sharing a line have no column to share. Naming AS makes no difference.

select a as x, bbb as y, cc as z from t

select a as x, bbb as y, cc as z from t

Narrow the margin until each item takes a line and the same setting pads.

select
    a   as x,
    bbb as y,
    cc  as z
from t

Leading commas move the comma, not the column.

select
      a   as x
    , bbb as y
    , cc  as z
from t

A select list that is already aligned

Formatting a file whose columns are already padded reproduces them.

SELECT CUSTOMER_ID,
  COUNT(ORDER_ID) AS ORDER_COUNT,
  SUM(TOTAL)      AS TOTAL_SPENT,
  AVG(TOTAL)      AS AVG_ORDER
FROM ORDERS

SELECT
    CUSTOMER_ID,
    COUNT(ORDER_ID) AS ORDER_COUNT,
    SUM(TOTAL)      AS TOTAL_SPENT,
    AVG(TOTAL)      AS AVG_ORDER
FROM ORDERS

Aligning a case's arms

case_arms lines the arms of a case up by padding after the condition.

select case when status = 'active' then 'A' when x then 'X' end from t

select
    case
        when status = 'active' then 'A'
        when x then 'X'
    end
from t

select case when status = 'active' then 'A' when x then 'X' end from t

select
    case
        when status = 'active' then 'A'
        when x                 then 'X'
    end
from t

Varying condition widths

select case when a then 1 when abc then 2 when abcdef then 3 end from t

select
    case
        when a      then 1
        when abc    then 2
        when abcdef then 3
    end
from t

ELSE lines up with the arms' values

else has no condition, so it pads its value out to the column the then values start at.

select case when status = 'active' then 'A' when x then 'X' else 'Z' end from t

select
    case
        when status = 'active' then 'A'
        when x                 then 'X'
        else                        'Z'
    end
from t

Equal conditions need no padding

When every condition is the same width the then keywords already line up and nothing is added to the arms. The else value still moves out to their column.

select case when a = 1 then 'x' when b = 2 then 'y' else 'z' end from t

select
    case
        when a = 1 then 'x'
        when b = 2 then 'y'
        else            'z'
    end
from t

A longer case

select case when status = 'A' then 'Active' when status = 'I' then 'Inactive' when status = 'P' then 'Pending' when status = 'R' then 'Rejected' when status = 'D' then 'Deleted' when status = 'S' then 'Suspended' when status = 'X' then 'Expired' when status = 'C' then 'Completed' else 'Unknown' end from t

select
    case
        when status = 'A' then 'Active'
        when status = 'I' then 'Inactive'
        when status = 'P' then 'Pending'
        when status = 'R' then 'Rejected'
        when status = 'D' then 'Deleted'
        when status = 'S' then 'Suspended'
        when status = 'X' then 'Expired'
        when status = 'C' then 'Completed'
        else                   'Unknown'
    end
from t

Three conditions, no ELSE

select case when status = 'active' then 'A' when status = 'pending_review' then 'P' when s = 1 then 'X' end from t

select
    case
        when status = 'active'         then 'A'
        when status = 'pending_review' then 'P'
        when s = 1                     then 'X'
    end
from t

Three arms and an ELSE

select case when status = 'active' then 'A' when status = 'pending_review' then 'P' when s = 1 then 'X' else 'Unknown' end from t

select
    case
        when status = 'active'         then 'A'
        when status = 'pending_review' then 'P'
        when s = 1                     then 'X'
        else                                'Unknown'
    end
from t

An ELSE at 50 characters

select case when x > 1 then 'a' when very_long_condition > 2 then 'b' else 'c' end from t

select
    case
        when x > 1                   then 'a'
        when very_long_condition > 2 then 'b'
        else                              'c'
    end
from t

When the column would not fit

At 60 characters, padding when x = 1 out to the longer condition's column still fits, so it pads.

select case when medium_condition_name = 1 then 'a' when x = 1 then 'result_bbb' end from t

select
    case
        when medium_condition_name = 1 then 'a'
        when x = 1                     then 'result_bbb'
    end
from t

At 50 neither arm can reach the other's column — padding the short arm runs past the margin, and folding the wide arm's then under it costs a line the group does not buy — so both arms keep their own then and both still fit on their own lines.

select
    case
        when medium_condition_name = 1 then 'a'
        when x = 1 then 'result_bbb'
    end
from t

The same rule in a smaller case

select case when x > 1 then 'aaaa' when lng > 2 then 'bb' end from t

select
    case
        when x > 1   then 'aaaa'
        when lng > 2 then 'bb'
    end
from t

select case when x > 1 then 'aaaa' when lng > 2 then 'bb' end from t

select
    case
        when x > 1 then 'aaaa'
        when lng > 2 then 'bb'
    end
from t

Aligning an UPDATE's assignments

update t set first_name = 'John', last_name = 'Doe', email_address = 'john@example.com' where id = 1

update t
set
    first_name = 'John',
    last_name = 'Doe',
    email_address = 'john@example.com'
where id = 1

update t set first_name = 'John', last_name = 'Doe', email_address = 'john@example.com' where id = 1

update t
set
    first_name    = 'John',
    last_name     = 'Doe',
    email_address = 'john@example.com'
where id = 1

A short SET, once it breaks

update t set a = 1, bb = 2, ccc = 3 where id = 1

update t
set
    a   = 1,
    bb  = 2,
    ccc = 3
where id = 1

With a WHERE after the SET

The where is a clause of its own and takes no part in the SET's group.

update employees set salary = 50000, department = 'Engineering', title = 'Senior' where id = 42

update employees
set
    salary     = 50000,
    department = 'Engineering',
    title      = 'Senior'
where id = 42

An UPDATE that is already aligned

update employees
set
    salary     = 50000,
    department = 'Engineering',
    title      = 'Senior'
where id = 42

update employees
set
    salary     = 50000,
    department = 'Engineering',
    title      = 'Senior'
where id = 42

Comparisons across WHERE conditions

A where that fits its line keeps its conditions on it, and nothing aligns.

select * from t where status = 'active' and priority = 5 and region = 'US'

select *
from t
where status = 'active' and priority = 5 and region = 'US'

Narrow enough and the conditions take a line each, and the = signs share one column. The column belongs to the lines, not to the conditions: a condition that follows an and starts four columns further right, so it pads four columns less to reach it, and the first condition pads out to meet the and rows.

select *
from t
where
    status       = 'active'
    and priority = 5
    and region   = 'US'

With andOrPosition set to after, the and ends the line instead of starting the next one, every condition starts in the same column, and the = signs line up after the widest condition.

select *
from t
where
    status   = 'active' and
    priority = 5 and
    region   = 'US'

Every comparison lines up, on the operator

comparisons covers every comparison the dialect has — =, <>, !=, <, >, <=, >= and the rest — so a where that mixes them lines all of them up.

The column is the operator's, not the value's. Each value simply follows its own operator, so a two-character <> puts its value one column further right than a = does. That is deliberate: a shared value column could only sit where the widest operator wanted it, and every condition with a narrower operator would then be unable to reach it and would drop out of the group entirely.

select * from t where status = 'active' and priority > 5 and region <> 'US'

select *
from t
where
    status       = 'active'
    and priority > 5
    and region   <> 'US'

One token, two constructs

The same = in both halves of one statement, and the two halves answer separately. This is the distinction a token could not express: = named both at once or neither.

update t set first_name = 'John', email_address = 'a@b.c' where status = 'active' and priority = 5

Neither named — both ragged:

update t
set
    first_name = 'John',
    email_address = 'a@b.c'
where
    status = 'active'
    and priority = 5

assignments — the SET lines up, the WHERE is left alone:

update t
set
    first_name    = 'John',
    email_address = 'a@b.c'
where
    status = 'active'
    and priority = 5

comparisons — the WHERE lines up, the SET is left alone:

update t
set
    first_name = 'John',
    email_address = 'a@b.c'
where
    status       = 'active'
    and priority = 5

Both, which is what naming the token used to do:

update t
set
    first_name    = 'John',
    email_address = 'a@b.c'
where
    status       = 'active'
    and priority = 5

Aligning a call's named arguments

named_arguments pads after the parameter name, so the arrows share a column.

select make_interval(years => 1, hours => 22, secs => 333) from t

select make_interval(
    years => 1,
    hours => 22,
    secs => 333
)
from t

select make_interval(years => 1, hours => 22, secs => 333) from t

select make_interval(
    years => 1,
    hours => 22,
    secs  => 333
)
from t

The := spelling is the same construct

PostgreSQL, DuckDB, Snowflake and CockroachDB accept name := value for the same named argument, and named_arguments covers it. Nothing has to name the second spelling, because the spelling was never what the option named.

select make_interval(years := 1, hours := 22, secs := 333) from t

select make_interval(
    years := 1,
    hours := 22,
    secs  := 333
)
from t

Aligning a table's column definitions

column_definitions is the one construct with no marker: a column definition is a name, a type and whatever follows, which are three cells rather than content either side of a symbol. The names pad to the widest name, and the types of the columns that go on to something pad to the widest such type — name text has nothing after its type, so nothing pads after it.

create table t (id bigint not null, name text, amount numeric(10, 2) not null)

create table t(
    id bigint not null,
    name text,
    amount numeric(10, 2) not null
)

create table t (id bigint not null, name text, amount numeric(10, 2) not null)

create table t(
    id     bigint         not null,
    name   text,
    amount numeric(10, 2) not null
)

A blank line starts a new group of columns

A group is a run of rows with nothing between them, so a blank line inside the column list ends one group and begins another, and each settles on its own width. Here id/name pad to four and created_at/modified to ten; neither group can see the other. The same is true of a comment between two columns, and of every other construct on this page — grouping is what an empty line means, so it is believed rather than reflowed.

create table t (
    id bigint not null,
    name text,

    created_at timestamp not null,
    modified timestamp
)

create table t(
    id   bigint not null,
    name text,

    created_at timestamp not null,
    modified   timestamp
)

all names every construct

align: all is every construct in the table above, and it keeps meaning that as constructs are added. Each construct still gets a column of its own: the arms of the case line up on their then, and the select list lines up on its as, with end padding out to join some_long_name because a multi-line item is one row of the list like any other.

select case when a = 1 then 'x' when bbbb = 2 then 'y' end as flag, some_long_name as n from t

select
    case
        when a = 1 then 'x'
        when bbbb = 2 then 'y'
    end as flag,
    some_long_name as n
from t

select case when a = 1 then 'x' when bbbb = 2 then 'y' end as flag, some_long_name as n from t

select
    case
        when a = 1    then 'x'
        when bbbb = 2 then 'y'
    end            as flag,
    some_long_name as n
from t

Naming a construct the statement does not use

aliases has nothing to match in an update, and naming it alongside assignments costs nothing.

update t set short = 1, very_long_name = 2 where id = 1

update t
set
    short          = 1,
    very_long_name = 2
where id = 1

Two constructs that both have work to do

case_arms aligns inside the case and aliases inside the select list, and the two groups do not see each other. Each has a column of its own: one for the then of the arms, another for the as of the select items.

The select list here is two items, case … end as status_code and name as n, so they are a group like any other pair of aliased items and their as share a column. An item spread over several lines is still one item of the group — its as is placed from where its expression ends, which is the line end is on — so the two are measured against each other: end is three characters and name is four, the column falls one space past the longer of them, and end takes one space of padding on top of its ordinary one to reach it. Inside the case, the arms are a separate group and settle their then column among themselves, nowhere near the aliases'. A group is the rows of one list, not every appearance of a token in the statement.

select case when status = 'active' then 'A' when status = 'pending_review' then 'P' end as status_code, name as n from t

select
    case
        when status = 'active'         then 'A'
        when status = 'pending_review' then 'P'
    end  as status_code,
    name as n
from t