Examples

Before/after formatting examples for common PostgreSQL patterns. All examples use default options (lowercase keywords, standard density, trailing commas).

DML

Basic SELECT

- select id,title,price from books where in_stock=true order by price asc;
+ select
+   id,
+   title,
+   price
+ from books
+ where in_stock = true
+ order by price asc;

JOIN

- select books.id,books.title,authors.first_name,authors.last_name from books inner join authors on books.author_id=authors.id where books.price<50;
+ select
+   books.id,
+   books.title,
+   authors.first_name,
+   authors.last_name
+ from
+   books
+   join authors on books.author_id = authors.id
+ where books.price < 50;

JOIN types

- select * from books left join authors on authors.id=books.author_id right join publishers on publishers.id=books.publisher_id cross join tags;
+ select *
+ from
+   books
+   left join authors on authors.id = books.author_id
+   right join publishers on publishers.id = books.publisher_id
+   cross join tags;

CASE expression

- select id, case when price < 10 then 'cheap' when price < 50 then 'mid' else 'expensive' end as tier from books;
+ select
+   id,
+   case
+     when price < 10 then 'cheap'
+     when price < 50 then 'mid'
+   else 'expensive'
+   end as tier
+ from books;

IN / NOT IN

- select id, title from books where author_id in (1, 2, 3) and status not in ('draft', 'archived');
+ select
+   id,
+   title
+ from books
+ where
+   author_id in (1, 2, 3)
+   and status not in ('draft', 'archived');

BETWEEN

- select id, title, price from books where price between 10.00 and 50.00;
+ select
+   id,
+   title,
+   price
+ from books
+ where price between 10.00 and 50.00;

LIKE / ILIKE

- select id, title from books where title ilike '%postgres%' and isbn not like '978-0%';
+ select
+   id,
+   title
+ from books
+ where
+   title ilike '%postgres%'
+   and isbn not like '978-0%';

EXISTS subquery

- select id, name from customers where exists (select 1 from orders where orders.customer_id = customers.id);
+ select
+   id,
+   name
+ from customers
+ where exists (
+   select 1
+   from orders
+   where orders.customer_id = customers.id
+ );

CTE

- with recent_orders as (select customer_id, sum(amount) as total from orders where created_at > now() - interval '30 days' group by customer_id) select customers.name, recent_orders.total from customers join recent_orders on customers.id = recent_orders.customer_id order by recent_orders.total desc;
+ with
+   recent_orders as (
+     select
+       customer_id,
+       sum(amount) as total
+     from orders
+     where created_at > now() - interval '30 days'
+     group by customer_id
+   )
+ select
+   customers.name,
+   recent_orders.total
+ from
+   customers
+   join recent_orders on customers.id = recent_orders.customer_id
+ order by recent_orders.total desc;

Window functions

- select id, author_id, price, row_number() over (partition by author_id order by price desc) as rank, sum(price) over (partition by author_id rows between unbounded preceding and current row) as running_total from books;
+ select
+   id,
+   author_id,
+   price,
+   row_number() over (partition by author_id order by price desc) as rank,
+   sum(price) over (partition by author_id rows between unbounded preceding and current row) as running_total
+ from books;

DISTINCT ON

- select distinct on (author_id) id, author_id, title from books order by author_id, price asc;
+ select distinct on (author_id)
+   id,
+   author_id,
+   title
+ from books
+ order by
+   author_id,
+   price asc;

Set operations (UNION / INTERSECT / EXCEPT)

- select id, name from customers union select id, title as name from archived_books;
+ select
+   id,
+   name
+ from customers
+ union
+ select
+   id,
+   title as name
+ from archived_books;

GROUP BY / HAVING

- select genre_id, author_id, sum(price) from books group by genre_id, author_id having sum(price) > 5000 order by sum(price) desc;
+ select
+   genre_id,
+   author_id,
+   sum(price)
+ from books
+ group by
+   genre_id,
+   author_id
+ having sum(price) > 5000
+ order by sum(price) desc;

ROLLUP / CUBE / GROUPING SETS

- select genre_id, author_id, sum(price) from books group by rollup(genre_id, author_id);
+ select
+   genre_id,
+   author_id,
+   sum(price)
+ from books
+ group by rollup(genre_id, author_id);

- select genre_id, author_id, sum(price) from books group by grouping sets ((genre_id, author_id), (genre_id), ());
+ select
+   genre_id,
+   author_id,
+   sum(price)
+ from books
+ group by grouping sets((genre_id, author_id), (genre_id), ());

LATERAL subquery

- select b.id, r.avg_price from books b, lateral (select avg(price) as avg_price from books where author_id = b.author_id) r;
+ select
+   b.id,
+   r.avg_price
+ from
+   books as b,
+   lateral (
+     select avg(price) as avg_price
+     from books
+     where author_id = b.author_id
+   ) as r;

Aggregate FILTER and ORDER BY

- select author_id, count(*) filter (where in_stock = true) as in_stock_count, string_agg(title, ', ' order by title) as titles from books group by author_id;
+ select
+   author_id,
+   count(*) filter (where in_stock = true) as in_stock_count,
+   string_agg(title, ', ' order by title) as titles
+ from books
+ group by author_id;

FOR UPDATE / FOR SHARE

- select id, title from books where id = 1 for update;
+ select
+   id,
+   title
+ from books
+ where id = 1
+ for update;

- select id from orders for share skip locked;
+ select id
+ from orders
+ for share skip locked;

INSERT — single and multi-row VALUES

- insert into books (title, price, genre_id) values ('The Pragmatic Programmer', 49.99, 1), ('Clean Code', 39.99, 1);
+ insert into books (title, price, genre_id)
+ values
+   ('The Pragmatic Programmer', 49.99, 1),
+   ('Clean Code', 39.99, 1);

INSERT — ON CONFLICT DO UPDATE

- insert into customers (id, email) values (1, 'alice@example.com') on conflict (id) do update set email = excluded.email;
+ insert into customers (id, email)
+ values (1, 'alice@example.com')
+ on conflict (id) do update
+ set email = excluded.email;

INSERT — RETURNING

- insert into orders (customer_id, amount) values (42, 150.00) returning id, created_at;
+ insert into orders (customer_id, amount)
+ values (42, 150.00)
+ returning
+   id,
+   created_at;

INSERT — DEFAULT VALUES

- insert into reviews default values returning id, created_at;
+ insert into reviews
+ default values
+ returning
+   id,
+   created_at;

UPDATE with RETURNING

- update customers set active = false where last_order_at < now() - interval '1 year' returning id, email;
+ update customers
+ set active = false
+ where last_order_at < now() - interval '1 year'
+ returning
+   id,
+   email;

DELETE with RETURNING

- delete from orders where placed_at < now() - interval '1 year' returning id, customer_id;
+ delete from orders
+ where placed_at < now() - interval '1 year'
+ returning
+   id,
+   customer_id;

Data-modifying CTE

- with moved as (delete from orders where status = 'cancelled' returning id, customer_id, amount) insert into archived_orders (id, customer_id, amount) select id, customer_id, amount from moved;
+ with
+   moved as (
+     delete from orders
+     where status = 'cancelled'
+     returning
+       id,
+       customer_id,
+       amount
+   )
+ insert into archived_orders (id, customer_id, amount)
+ select
+   id,
+   customer_id,
+   amount
+ from moved;

TRUNCATE

- truncate table orders, archived_books restart identity cascade;
+ truncate table orders, archived_books restart identity cascade;

DDL

CREATE TABLE with type modifiers

- create table books (id integer, title varchar(100), price numeric(10,2), tags text[], published_date timestamptz);
+ create table books (
+   id integer,
+   title varchar(100),
+   price numeric(10, 2),
+   tags text[],
+   published_date timestamptz
+ );

CREATE TABLE with PARTITION BY

- create table orders (id integer not null, region text not null) partition by range (region);
+ create table orders (
+   id integer not null,
+   region text not null
+ )
+ partition by range (region);

CREATE TABLE PARTITION OF

- create table orders_us partition of orders for values from ('US') to ('ZZ');
+ create table orders_us
+ partition of orders
+ for values from ('US') to ('ZZ');

CREATE TABLE LIKE

- create table orders_copy (like orders including all);
+ create table orders_copy (
+   like orders including all
+ );

VACUUM / REINDEX

- vacuum (full, analyze) orders;
+ vacuum (full, analyze) orders;

- reindex (verbose) table orders;
+ reindex (verbose) table orders;

Recursive CTE with SEARCH / CYCLE

- with recursive t as (select id, parent_id from tree union all select tree.id, tree.parent_id from tree join t on t.id = tree.parent_id) search breadth first by id set ordercol cycle id set is_cycle using path select * from t;
+ with recursive
+   t as (
+     select
+       id,
+       parent_id
+     from tree
+     union all
+     select
+       tree.id,
+       tree.parent_id
+     from
+       tree
+       join t on t.id = tree.parent_id
+   )
+   search breadth first by id set ordercol
+   cycle id set is_cycle using path
+ select *
+ from t;

TABLESAMPLE

- select id, title from books tablesample bernoulli(10);
+ select
+   id,
+   title
+ from books tablesample bernoulli(10);

Expressions

SUBSTRING

- select substring(title from 'pg.*'), substring(title from 1 for 5) from books;
+ select
+   substring(title from 'pg.*'),
+   substring(title from 1 for 5)
+ from books;

EXTRACT

- select extract(year from created_at), extract(month from created_at) from orders;
+ select
+   extract(year from created_at),
+   extract(month from created_at)
+ from orders;

TRIM

- select trim(leading ' ' from name), trim(trailing ' ' from name), trim(both ' ' from name) from customers;
+ select
+   trim(leading ' ' from name),
+   trim(trailing ' ' from name),
+   trim(both ' ' from name)
+ from customers;

AT TIME ZONE

- select created_at at time zone 'UTC', placed_at at time zone 'America/New_York' from orders;
+ select
+   created_at at time zone 'UTC',
+   placed_at at time zone 'America/New_York'
+ from orders;

INTERVAL literals and :: casts

- select price::numeric, title::text, interval '30 days', '2024-01-01'::date from books;
+ select
+   price::numeric,
+   title::text,
+   interval '30 days',
+   '2024-01-01'::date
+ from books;

INTERVAL with field modifiers

- select interval '1' year, interval '1:30' hour to minute from books;
+ select
+   interval '1' year,
+   interval '1:30' hour to minute
+ from books;

Array subscripts

- select arr[1], arr[2:4], arr[:3] from books;
+ select
+   arr[1],
+   arr[2:4],
+   arr[:3]
+ from books;

Named function arguments

- select make_date(year => 2024, month => 1, day => 15);
+ select make_date(year => 2024, month => 1, day => 15);

SQL value functions

- select current_date, current_timestamp, current_user, session_user from books;
+ select
+   current_date,
+   current_timestamp,
+   current_user,
+   session_user
+ from books;

FilesExpand file tree

examples.md

Latest commit

History

examples.md

File metadata and controls

Examples

DML

Basic SELECT

JOIN

JOIN types

CASE expression

IN / NOT IN

BETWEEN

LIKE / ILIKE

EXISTS subquery

CTE

Window functions

DISTINCT ON

Set operations (UNION / INTERSECT / EXCEPT)

GROUP BY / HAVING

ROLLUP / CUBE / GROUPING SETS

LATERAL subquery

Aggregate FILTER and ORDER BY

FOR UPDATE / FOR SHARE

INSERT — single and multi-row VALUES

INSERT — ON CONFLICT DO UPDATE

INSERT — RETURNING

INSERT — DEFAULT VALUES

UPDATE with RETURNING

DELETE with RETURNING

Data-modifying CTE

TRUNCATE

DDL

CREATE TABLE with type modifiers

CREATE TABLE with PARTITION BY

CREATE TABLE PARTITION OF

CREATE TABLE LIKE

VACUUM / REINDEX

Recursive CTE with SEARCH / CYCLE

TABLESAMPLE

Expressions

SUBSTRING

EXTRACT

TRIM

AT TIME ZONE

INTERVAL literals and :: casts

INTERVAL with field modifiers

Array subscripts

Named function arguments

SQL value functions