36. PostgreSQL: SQL functions

CREATE SCHEMA ventes;
SET search_path TO ventes;

CREATE TABLE client (
  id serial PRIMARY KEY, nom varchar(80) NOT NULL, ville varchar(60),
  email varchar(120), cree_le timestamp NOT NULL);
CREATE TABLE article (
  id serial PRIMARY KEY, reference varchar(20) NOT NULL UNIQUE, designation varchar(120) NOT NULL,
  prix numeric(10,2) NOT NULL, stock integer NOT NULL DEFAULT 0, attributs jsonb);
CREATE TABLE commande (
  id serial PRIMARY KEY, client_id integer NOT NULL REFERENCES client (id),
  date_commande date NOT NULL, statut varchar(20) NOT NULL DEFAULT 'en cours', livree_le timestamp);
CREATE TABLE ligne_commande (
  commande_id integer NOT NULL REFERENCES commande (id), article_id integer NOT NULL REFERENCES article (id),
  quantite integer NOT NULL, prix_unitaire numeric(10,2) NOT NULL, PRIMARY KEY (commande_id, article_id));

INSERT INTO client (nom, ville, email, cree_le) VALUES
  ('Dupont', 'Lyon', '[email protected]', '2026-01-15 09:30:00'),
  ('Martin', 'Paris', '[email protected]', '2026-02-03 14:05:00'),
  ('Bernard', 'Lyon', NULL, '2026-03-21 08:00:00'),
  ('Élodie Petit', NULL, '[email protected]', '2026-04-02 17:45:30');
INSERT INTO article (reference, designation, prix, stock, attributs) VALUES
  ('STY-001', 'Stylo bille bleu', 1.20, 500, '{"couleur": "bleu", "tags": ["bureau", "ecriture"], "dim": {"l": 14, "d": 0.8}}'),
  ('CAH-096', 'Cahier 96 pages', 2.50, 120, '{"couleur": "rouge", "tags": ["papier"], "pages": 96}'),
  ('CLA-A4', 'Classeur A4', 4.90, 35, '{"couleur": "noir", "tags": [], "recycle": true}'),
  ('AGR-024', 'Agrafeuse', 12.00, 0, NULL);
INSERT INTO commande (client_id, date_commande, statut, livree_le) VALUES
  (1, '2026-09-28', 'livrée', '2026-10-01 10:15:00'), (1, '2026-10-05', 'en cours', NULL),
  (2, '2026-10-06', 'livrée', '2026-10-08 16:40:00'), (3, '2026-10-09', 'annulée', NULL);
INSERT INTO ligne_commande VALUES
  (1, 1, 100, 1.20), (1, 2, 10, 2.50), (2, 3, 4, 4.90), (3, 1, 50, 1.10), (3, 4, 2, 12.00), (4, 2, 5, 2.50);

SELECT 7 / 2, -7 / 2, 7 / 2.0, 1.0 / 3, 7::float8 / 2, 7 % 2, 2 ^ 3;

3 | -3 | 3.5000000000000000 | 0.33333333333333333333 | 3.5 | 1 | 8

SELECT 5 BETWEEN 1 AND 10, 5 IN (1, 5), 1 IS DISTINCT FROM NULL, NULL IS NOT DISTINCT FROM NULL,
       1 = ANY (ARRAY[1, 2]), 3 <> ALL (ARRAY[1, 2]), (1, 2) < (1, 3);

t | t | t | t | t | t | t

SELECT x ISNULL, x NOTNULL, 'abc' ~~ 'a%', 'abc' ~~* 'A%', 'abc' !~~ 'b%' FROM (SELECT NULL::int AS x) t;

t | f | t | t | t

SELECT true AND NULL, false AND NULL, true OR NULL, NOT true;

NULL | f | t | f

SELECT 'Stylo' || ' ' || 'bille', 'Total : ' || 12.5 || ' €', 'n=' || NULL, coalesce('n=' || NULL, 'sans');

Stylo bille | Total : 12.5 € | NULL | sans

concat(value1, value2, ...)
concat_ws(separator, value1, value2, ...)

SELECT concat(nom, ' (', ville, ')') AS a,
       nom || ' (' || coalesce(ville, '?') || ')' AS b,
       concat_ws(' - ', nom, ville, email) AS c
FROM client ORDER BY id;

 a               | b                | c
-----------------+------------------+------------------------------------
 Dupont (Lyon)   | Dupont (Lyon)    | Dupont - Lyon - [email protected]
 Martin (Paris)  | Martin (Paris)   | Martin - Paris - [email protected]
 Bernard (Lyon)  | Bernard (Lyon)   | Bernard - Lyon
 Élodie Petit () | Élodie Petit (?) | Élodie Petit - [email protected]

upper(string)    lower(string)    initcap(string)

SELECT upper('café'), lower('CAFÉ'), initcap('jean-pierre de la fontaine');

CAFÉ | café | Jean-Pierre De La Fontaine

length(string)    char_length(string)    character_length(string)
octet_length(string)    bit_length(string)

SELECT length('café'), char_length('café'), octet_length('café'), bit_length('café'), length('abc'::bytea);

4 | 4 | 5 | 40 | 3

trim([BOTH | LEADING | TRAILING] [characters] FROM string)
trim(string)
btrim(string [, characters])    ltrim(string [, characters])    rtrim(string [, characters])

SELECT '[' || trim('  x  ') || ']', '[' || ltrim('  x  ') || ']', '[' || rtrim('  x  ') || ']';
SELECT trim(BOTH 'x' FROM 'xxaxx'), trim(LEADING 'x' FROM 'xxaxx'), trim(TRAILING 'x' FROM 'xxaxx');
SELECT btrim('yxaxyx', 'xy'), ltrim('yxaxx', 'xy'), rtrim('axyyx', 'xy');

[x] | [x  ] | [  x]
a | axx | xxa
a | axx | a

SELECT btrim('\x0000ff1000'::bytea, '\x00'), trim(LEADING '\x00' FROM '\x0000ff1000'::bytea), rtrim('\x0000ff1000'::bytea, '\x00');

\xff10 | \xff1000 | \x0000ff10

substring(string FROM start [FOR length])
substring(string FOR length)
substring(string, start [, length])
substr(string, start [, length])
substring(string FROM pattern)

SELECT substring('abcdef' FROM 2 FOR 3), substring('abcdef' FROM 3), substring('abcdef' FOR 2), substr('abcdef', 4);
SELECT substring(email FROM position('@' IN email) + 1) FROM client WHERE id = 1;

bcd | cdef | ab | def
exemple.fr

SELECT substring('abc123def' FROM '[0-9]+'), substring('abc123def' FROM 'c(\d)'), substring('abc' FROM 'z');

123 | 1 | NULL

left(string, n)    right(string, n)

SELECT left('abcdef', 2), right('abcdef', 2), left('abcdef', -2), right('abcdef', -2);

ab | ef | abcd | cdef

position(substring IN string)    strpos(string, substring)    starts_with(string, prefix)

SELECT position('cd' IN 'abcdef'), strpos('abcdef', 'cd'), position('z' IN 'abc'), starts_with('abc', 'ab');

3 | 3 | 0 | t

replace(string, search, replacement)
translate(string, from, to)
overlay(string PLACING new FROM start [FOR length])

SELECT replace('aXbXc', 'X', '-'), translate('12345', '143', 'ax'),
       overlay('abcdef' PLACING 'ZZ' FROM 2 FOR 3), overlay('abcdef' PLACING 'ZZ' FROM 2);

a-b-c | a2x5 | aZZef | aZZdef

lpad(string, length [, fill])    rpad(string, length [, fill])
repeat(string, n)    reverse(string)

SELECT lpad('7', 3, '0'), rpad('7', 3, '*'), lpad('abcdef', 3), '[' || lpad('ab', 5) || ']', repeat('ab', 3), reverse('abc');
SELECT reference, lpad(id::text, 6, '0') AS numero, rpad(designation, 20, '.') || to_char(prix, '990.00') AS ligne
FROM article ORDER BY id;

007 | 7** | abc | [   ab] | ababab | cba

 reference | numero | ligne
-----------+--------+-----------------------------
 STY-001   | 000001 | Stylo bille bleu....   1.20
 CAH-096   | 000002 | Cahier 96 pages.....   2.50
 CLA-A4    | 000003 | Classeur A4.........   4.90
 AGR-024   | 000004 | Agrafeuse...........  12.00

split_part(string, separator, n)

SELECT split_part('a,b,c', ',', 2), split_part('a,b,c', ',', 5), split_part('a,b,c', ',', -1);
SELECT reference, split_part(reference, '-', 1) AS famille FROM article WHERE id = 1;
SELECT split_part(email, '@', 2) AS domaine FROM client WHERE id = 1;

b |  | c
STY-001 | STY
exemple.fr

ascii(string)    chr(code)

SELECT ascii('A'), ascii('é'), ascii('€uro'), chr(66), chr(233), chr(8364);

65 | 233 | 8364 | B | é | €

format(template, value1, value2, ...)

SELECT format('%s : %s €', designation, prix) FROM article WHERE id = 1;
SELECT format('%I = %L', 'nom', nom), format('%2$s %1$s', 'a', 'b'), format('[%5s|%-5s]', id, id) FROM client WHERE id = 1;

Stylo bille bleu : 1.20 €
nom = 'Dupont' | b a | [    1|1    ]

quote_ident(text)    quote_literal(value)    quote_nullable(value)

SELECT quote_ident('Mon Id'), quote_ident('abc'), quote_literal('it''s'), quote_literal(42), quote_nullable(NULL);

"Mon Id" | abc | 'it''s' | '42' | NULL

normalize(string [, NFC | NFD | NFKC | NFKD])
unistr(string)

SELECT length('é'), length(normalize('é', NFD)), normalize('fi', NFKC), unistr('caf\00e9 \+01F600');

1 | 2 | fi | café 😀

parse_ident(qualified_name [, strict])

SELECT parse_ident('ventes."Mon Client"'), parse_ident('Ventes.Client'), parse_ident('ventes.client()', false);

{ventes,"Mon Client"} | {ventes,client} | {ventes,client}

SELECT levenshtein('kitten', 'sitting'), levenshtein('kitten', 'sitting', 1, 1, 2), levenshtein_less_equal('kitten', 'sitting', 2),
       soundex('Robert'), soundex('Rupert'), difference('Robert', 'Rupert'), similarity('word', 'words'), unaccent('Élodie à Noël');
SELECT nom FROM client WHERE levenshtein(lower(nom), 'dupond') <= 1;
SELECT nom FROM client WHERE unaccent(lower(nom)) LIKE 'elo%';

3 | 5 | 3 | R163 | R163 | 4 | 0.5714286 | Elodie a Noel
Dupont
Élodie Petit

string [NOT] LIKE pattern [ESCAPE character]
string [NOT] ILIKE pattern
string ~~ pattern    string ~~* pattern    string !~~ pattern    string !~~* pattern

SELECT 'abc' LIKE 'a_c', 'a%b' LIKE 'a\%b', 'a%b' LIKE 'a#%b' ESCAPE '#', 'axb' LIKE 'a\%b';
SELECT nom FROM client WHERE nom ILIKE 'd%';

t | t | t | f
Dupont

SELECT 'abc' LIKE 'A%', 'abc' ILIKE 'A%', 'abc' = 'ABC', 'abc' COLLATE utf8mb4_general_ci = 'ABC', 'é' COLLATE utf8mb4_general_ci = 'e', 'abc' COLLATE "C" = 'ABC';
SELECT nom FROM client WHERE nom COLLATE utf8mb4_general_ci = 'dupont';

f | t | f | t | t | f
Dupont

SELECT 'a1' ~ '^[a-z]\d$', 'a b' ~ '\s', 'abc' ~ 'a|z', 'aaa' ~ '^a{3}$', 'abc' ~* 'B', 'abc' !~ 'z';
SELECT nom FROM client WHERE nom ~ '^[A-D]' ORDER BY nom;

t | t | t | t | t | t

Bernard
Dupont

regexp_replace(string, pattern, replacement [, start [, n]] [, flags])

SELECT regexp_replace('abc123', '[0-9]+', '#'), regexp_replace('aaa', 'a', 'b', 'g'),
       regexp_replace('Hello', 'L', 'x'), regexp_replace('Hello', 'L', 'x', 'i'),
       regexp_replace('2026-10-11', '(\d+)-(\d+)-(\d+)', '\3/\2/\1'),
       regexp_replace('a1b2c3', '[0-9]', '#', 1, 2),
       regexp_replace('  a   b  ', '\s+', ' ', 'g');

abc# | bbb | Hello | Hexlo | 11/10/2026 | a1b#c3 |  a b 

SELECT reference, regexp_replace(reference, '^([A-Z]+)-0*', '\1:') AS court FROM article ORDER BY id;

 reference | court
-----------+--------
 STY-001   | STY:1
 CAH-096   | CAH:96
 CLA-A4    | CLA:A4
 AGR-024   | AGR:24

regexp_match(string, pattern [, flags])
regexp_matches(string, pattern [, flags])

SELECT regexp_match('Commande 2026-10-11', '(\d+)-(\d+)-(\d+)'), regexp_match('abc', 'z'), (regexp_match('ref STY-001', '([A-Z]+)-(\d+)'))[2];
SELECT regexp_matches('ab12cd34', '([a-z]+)(\d+)', 'g');

{2026,10,11} | NULL | 001

{ab,12}
{cd,34}

regexp_split_to_array(string, pattern [, flags])
regexp_split_to_table(string, pattern [, flags])
regexp_count(string, pattern [, start [, flags]])

SELECT regexp_split_to_array('a, b,c', ',\s*'), regexp_count('a1b22c333', '\d+'), regexp_count('AbAb', 'a', 1, 'i');
SELECT * FROM regexp_split_to_table('Stylo bille  bleu', '\s+') WITH ORDINALITY AS t(mot, rang);

{a,b,c} | 3 | 2

Stylo | 1
bille | 2
bleu | 3

regexp_substr(string, pattern [, start [, n [, flags [, group]]]])
regexp_instr(string, pattern [, start [, n [, end [, flags [, group]]]]])
regexp_like(string, pattern [, flags])

SELECT regexp_substr('a1b22c333', '\d+', 1, 3), regexp_substr('STY-001', '([A-Z]+)-(\d+)', 1, 1, '', 2),
       regexp_instr('a1b22c333', '\d+', 1, 2), regexp_instr('a1b22c333', '\d+', 1, 2, 1),
       regexp_like('abc', 'B'), regexp_like('abc', 'B', 'i');

333 | 001 | 4 | 6 | f | t

SELECT abs(-12.50), abs(-3), sign(-12.5), sign(0), sign(7);

12.50 | 3 | -1 | 0 | 1

round(x [, n])    trunc(x [, n])    ceil(x)    ceiling(x)    floor(x)

SELECT round(2.5), round(-2.5), round(2.567, 2), round(1250, -2), round(2.5::float8), round(3.5::float8);
SELECT trunc(2.567, 2), trunc(-2.567), trunc(1299, -2), ceil(2.1), ceil(-2.1), floor(2.9), floor(-2.1);

3 | -3 | 2.57 | 1300 | 2 | 4
2.56 | -2 | 1200 | 3 | -2 | 2 | -3

SELECT sum(quantite * prix_unitaire) AS total,
       round(sum(quantite * prix_unitaire) * 1.2, 2) AS ttc,
       round(avg(prix_unitaire), 2) AS prix_moyen
FROM ligne_commande;

 total  | ttc    | prix_moyen
--------+--------+------------
 256.10 | 307.32 | 4.03

SELECT mod(17, 5), mod(-17, 5), mod(17.5, 5), 17 % 5, div(17, 5), div(-17, 5), 17 DIV 5;

2 | -2 | 2.5 | 2 | 3 | -3 | 3

SELECT power(2, 10), power(2.5, 2), 10 ^ -2, sqrt(2), exp(1), ln(exp(1)), log(1000), log(2, 1024), log10(1000), log2(1024);

1024 | 6.2500000000000000 | 0.01 | 1.4142135623730951 | 2.718281828459045 | 1 | 3 | 10 | 3 | 10

SELECT cbrt(27), cbrt(-8), scale(1.230), min_scale(1.230), trim_scale(1.230), pow(2, 3);

3 | -2 | 3 | 2 | 1.23 | 8

SELECT pi(), degrees(pi()), radians(180), sin(pi() / 2), cos(0), atan2(1, 1);

3.141592653589793 | 180 | 3.141592653589793 | 1 | 1 | 0.7853981633974483

SELECT gcd(12, 18), lcm(4, 6), factorial(5), factorial(20);
SELECT width_bucket(5.5, 0, 10, 5), width_bucket(-1, 0, 10, 5), width_bucket(10, 0, 10, 5);

6 | 12 | 120 | 2432902008176640000
3 | 0 | 6

greatest(value1, value2, ...)    least(value1, value2, ...)

SELECT greatest(3, 7, 5), least(3, 7, 5), greatest('abc', 'abd'), least(date '2026-01-01', date '2025-01-01'), greatest(1, NULL);

7 | 3 | abd | 2025-01-01 | NULL

SELECT round(random_normal(100, 0)), random_normal() IS NOT NULL;

100 | t

SET TIME ZONE 'Europe/Paris';

SELECT now(), localtimestamp, current_date, current_time;

2026-10-11 21:36:38+02 | 2026-10-11 21:36:38 | 2026-10-11 | 21:36:38

SELECT date '2026-10-11', time '14:30:00', timestamp '2026-10-11 14:30:00',
       timestamptz '2026-10-11 14:30:00+02', '2026-10-11'::date, '2026-10-11T12:30:00Z'::timestamptz;

2026-10-11 | 14:30:00 | 2026-10-11 14:30:00 | 2026-10-11 14:30:00+02 | 2026-10-11 | 2026-10-11 14:30:00+02

SELECT 'today'::date = current_date, 'tomorrow'::date - 'yesterday'::date, 'epoch'::timestamp;

t | 2 | 1970-01-01 00:00:00

SELECT id, date_commande, livree_le,
       livree_le - date_commande AS delai,
       livree_le::date - date_commande AS jours
FROM commande WHERE livree_le IS NOT NULL ORDER BY id;

 id | date_commande | livree_le           | delai           | jours
----+---------------+---------------------+-----------------+-------
  1 | 2026-09-28    | 2026-10-01 10:15:00 | 3 days 10:15:00 | 3
  3 | 2026-10-06    | 2026-10-08 16:40:00 | 2 days 16:40:00 | 2

SELECT timestamptz '2026-10-25 00:30:00+02' + INTERVAL '1 day' AS un_jour,
       timestamptz '2026-10-25 00:30:00+02' + INTERVAL '24 hours' AS vingt_quatre_heures,
       timestamptz '2026-10-26 00:30:00+01' - timestamptz '2026-10-25 00:30:00+02' AS ecart;

 un_jour                | vingt_quatre_heures    | ecart
------------------------+------------------------+----------------
 2026-10-26 00:30:00+01 | 2026-10-25 23:30:00+01 | 1 day 01:00:00

SELECT timestamp '2026-10-11 14:30:00' + INTERVAL '90 minutes',
       timestamp '2026-10-11 14:30:00' + INTERVAL '1 day 2 hours',
       timestamp '2026-10-11 14:30:00' + INTERVAL '1 year 2 months',
       timestamp '2026-10-11 14:30:00' + INTERVAL '500 milliseconds',
       timestamp '2026-10-11 14:30:00' + '2 hours 30 minutes'::interval;

2026-10-11 16:00:00 | 2026-10-12 16:30:00 | 2027-12-11 14:30:00 | 2026-10-11 14:30:00.5 | 2026-10-11 17:00:00

SELECT INTERVAL '45 days', INTERVAL '2 hours 30 minutes', INTERVAL '1 year 2 months', INTERVAL '1 week',
       INTERVAL '1 day 12 hours', INTERVAL '1 month 1 day', INTERVAL '1.5 days';

45 days | 02:30:00 | 1 year 2 mons | 7 days | 1 day 12:00:00 | 1 mon 1 day | 1 day 12:00:00

extract(field FROM source)
date_part('field', source)

SELECT extract(year FROM date '2026-10-11'), extract(second FROM timestamp '2026-10-11 14:30:45.5'),
       extract(dow FROM date '2026-10-11'), extract(week FROM date '2027-01-01'), extract(isoyear FROM date '2027-01-01'),
       date_part('hour', timestamp '2026-10-11 14:30:45.5'), extract(hour FROM time '14:30:45');

2026 | 45.500000 | 0 | 53 | 2026 | 14 | 14

SELECT extract(epoch FROM timestamptz '2026-10-11 14:30:00+02') AS instant,
       extract(epoch FROM timestamp '2026-10-11 14:30:00') AS sans_fuseau,
       extract(epoch FROM date '2026-10-11') AS jour;

 instant           | sans_fuseau       | jour
-------------------+-------------------+------------
 1791721800.000000 | 1791729000.000000 | 1791676800

SELECT id, (extract(epoch FROM livree_le) - extract(epoch FROM date_commande)) / 3600 AS heures
FROM commande WHERE livree_le IS NOT NULL ORDER BY id;

 id | heures
----+---------------------
 1  | 82.2500000000000000
 3  | 64.6666666666666667

date_trunc('field', source)

SELECT date_trunc('month', timestamp '2026-10-11 14:30:45'), date_trunc('week', timestamp '2026-10-11 14:30:45'),
       date_trunc('hour', timestamp '2026-10-11 14:30:45'), date_trunc('quarter', date '2026-08-15')::date;

2026-10-01 00:00:00 | 2026-10-05 00:00:00 | 2026-10-11 14:00:00 | 2026-07-01

SELECT date_trunc('month', date_commande)::date AS mois, count(*) AS commandes
FROM commande GROUP BY 1 ORDER BY 1;

 mois       | commandes
------------+-----------
 2026-09-01 | 1
 2026-10-01 | 3

SELECT ((date_trunc('month', date '2026-02-10') + INTERVAL '1 month') - INTERVAL '1 day')::date;   -- 2026-02-28

make_date(year, month, day)
make_time(hour, minute, second)
make_timestamp(year, month, day, hour, minute, second)
make_timestamptz(year, month, day, hour, minute, second [, zone])
make_interval([years [, months [, weeks [, days [, hours [, mins [, secs]]]]]]])

SELECT make_date(2026, 2, 28), make_time(10, 30, 15.5), make_timestamp(2026, 2, 28, 10, 30, 0);
SELECT make_timestamptz(2026, 10, 11, 14, 30, 0), make_timestamptz(2026, 10, 11, 14, 30, 0, 'UTC');
SELECT make_interval(days => 3, hours => 2), make_interval(1, 2), make_interval(weeks => 2, secs => 1.5);

2026-02-28 | 10:30:15.5 | 2026-02-28 10:30:00
2026-10-11 14:30:00+02 | 2026-10-11 16:30:00+02
3 days 02:00:00 | 1 year 2 mons | 14 days 00:00:01.5

date_bin(stride, source, origin)

SELECT date_bin('15 minutes', timestamp '2026-10-11 14:38:20', timestamp '2026-01-01'),
       date_bin('1 hour', timestamp '2026-10-11 14:38:20', timestamp '2026-01-01 00:30');

2026-10-11 14:30:00 | 2026-10-11 14:30:00

date_add(instant, interval [, zone])
date_subtract(instant, interval [, zone])
timezone(zone, value)

SELECT date_add(timestamptz '2026-10-24 12:00+02', interval '1 day'), date_add(timestamptz '2026-10-24 12:00+02', interval '24 hours'),
       date_subtract(timestamptz '2026-10-25 12:00+01', interval '1 day', 'UTC');
SELECT timezone('America/New_York', timestamptz '2026-10-11 14:30:00+02'), timezone('UTC', timestamp '2026-10-11 14:30:00');

2026-10-25 12:00:00+01 | 2026-10-25 11:00:00+01 | 2026-10-24 13:00:00+02
2026-10-11 08:30:00 | 2026-10-11 16:30:00+02

age(end, start)    age(date_or_timestamp)
justify_days(interval)    justify_hours(interval)    justify_interval(interval)

SELECT age(date '2026-10-11', date '1990-05-20'), age(timestamp '2026-10-11 10:00', timestamp '2026-10-11 08:30'),
       age(timestamp '2024-02-29', timestamp '2026-10-11');
SELECT justify_days(INTERVAL '45 days'), justify_hours(INTERVAL '50 hours'), justify_interval(INTERVAL '400 days');

36 years 4 mons 22 days | 01:30:00 | -2 years -7 mons -11 days
1 mon 15 days | 2 days 02:00:00 | 1 year 1 mon 10 days

SELECT extract(year FROM age(date '2026-10-11', date '1990-05-20')), pg_typeof(age(date '2026-10-11', date '1990-05-20'));

36 | interval

value AT TIME ZONE 'zone'

SELECT timestamptz '2026-10-11 14:30:00+02' AT TIME ZONE 'America/New_York' AS a_new_york,
       timestamptz '2026-07-14 12:00:00+02' AT TIME ZONE 'UTC' AS en_utc,
       timestamp '2026-10-11 14:30:00' AT TIME ZONE 'UTC' AS instant;

 a_new_york          | en_utc              | instant
---------------------+---------------------+------------------------
 2026-10-11 08:30:00 | 2026-07-14 10:00:00 | 2026-10-11 16:30:00+02

SELECT to_timestamp(1760000000), to_timestamp(0) AT TIME ZONE 'UTC';

2025-10-09 10:53:20+02 | 1970-01-01 00:00:00

to_char(date_or_time, template)

SELECT to_char(timestamp '2026-10-11 14:05:09', 'YYYY-MM-DD HH24:MI:SS'),
       to_char(timestamp '2026-10-11 14:05:09', 'DD/MM/YYYY HH12:MI AM'),
       to_char(timestamp '2026-10-11 14:05:09', 'FMDay, FMDD FMMonth YYYY'),
       to_char(date '2026-03-01', 'FMDD/FMMM/YYYY'),
       to_char(time '14:05:09', 'HH24hMI');

2026-10-11 14:05:09 | 11/10/2026 02:05 PM | Sunday, 11 October 2026 | 1/3/2026 | 14h05

SELECT nom, to_char(cree_le, 'DD/MM/YYYY à HH24"h"MI') AS cree, to_char(cree_le, 'YYYY"-T"Q') AS trimestre
FROM client ORDER BY id;

 nom          | cree               | trimestre
--------------+--------------------+-----------
 Dupont       | 15/01/2026 à 09h30 | 2026-T1
 Martin       | 03/02/2026 à 14h05 | 2026-T1
 Bernard      | 21/03/2026 à 08h00 | 2026-T1
 Élodie Petit | 02/04/2026 à 17h45 | 2026-T2

SET TIME ZONE 'Europe/Paris';
SELECT to_char(timestamptz '2026-07-04 05:06:07+00', 'HH24:MI TZ OF'),
       to_char(timestamp '2026-03-04 05:06:07.123456', 'SS.FF3'), to_char(date '2026-11-04', 'FMRM');
SELECT to_char(timestamptz '2026-07-04 05:06:07+00', 'HH24:MI TZH:TZM'), to_char(date '2026-10-11', 'W'), to_char(date '2026-10-11', 'TMDay TMMonth');

07:06 CEST +02 | 07.123 | XI
07:06 +02:00 | 2 | Sunday October

to_char(interval, template)

SELECT to_char(interval '15h 2m 12s', 'HH24:MI:SS'), to_char(interval '50 hours 5.25 seconds', 'HH24 HH12 MI SS MS'),
       to_char(interval '1 year 2 months 3 days', 'YYYY-MM-DD'), to_char(interval 'P2DT3H', 'FMDD "jours" FMHH24 "h"');

15:02:12 | 50 02 00 05 250 | 0001-02-03 | 2 jours 3 h

to_char(number, template)

SELECT '[' || to_char(1234.5, '9999.99') || ']', '[' || to_char(1234.5, 'FM9999.00') || ']',
       '[' || to_char(12, '9999') || ']', '[' || to_char(12, 'FM9999') || ']',
       to_char(7, 'FM000'), to_char(-7, '000'), to_char(1234567.891, 'FM9,999,999.99'),
       to_char(12.5, 'FMS999.9'), to_char(0.126, 'FM0.00');

[ 1234.50] | [1234.50] | [   12] | [12] | 007 | -007 | 1,234,567.89 | +12.5 | 0.13

SELECT reference, to_char(prix, 'FM9990.00') AS prix, to_char(prix * stock, 'FM999G990D00') AS valeur_stock,
       to_char(stock, 'FM000000') AS stock
FROM article ORDER BY id;

 reference | prix  | valeur_stock | stock
-----------+-------+--------------+--------
 STY-001   | 1.20  | 600.00       | 000500
 CAH-096   | 2.50  | 300.00       | 000120
 CLA-A4    | 4.90  | 171.50       | 000035
 AGR-024   | 12.00 | 0.00         | 000000

SELECT '[' || to_char(-485, '999PR') || ']', to_char(485, 'FMRN'), to_char(1994, 'FMrn'),
       '[' || to_char(0.0004859, '9.99EEEE') || ']', '[' || to_char(-1234.5, '9.99EEEE') || ']',
       '[' || to_char(12.45, '99V9') || ']';

[<485>] | CDLXXXV | mcmxciv | [ 4.86e-04] | [-1.23e+03] | [ 125]

to_number(text, template)

SELECT to_number('12,454.8-', '99G999D9S'), to_number('$1,234.50', 'L9,999.99'), to_number('-42', '999'), pg_typeof(to_number('1', '9'));

-12454.8 | 1234.50 | -42 | numeric

to_date(text, template)
to_timestamp(text, template)

SELECT to_date('11/10/2026', 'DD/MM/YYYY'), to_date('20261011', 'YYYYMMDD'), to_date('11 oct 2026', 'DD Mon YYYY'),
       to_date('11 October 2026', 'DD Month YYYY'), to_date('11.10.26', 'DD.MM.YY'), to_date('2026-284', 'YYYY-DDD');
SELECT to_timestamp('2026-10-11 14:30:45', 'YYYY-MM-DD HH24:MI:SS'),
       to_timestamp('11/10/2026 02:30 PM', 'DD/MM/YYYY HH12:MI AM');

2026-10-11 | 2026-10-11 | 2026-10-11 | 2026-10-11 | 2026-10-11 | 2026-10-11
2026-10-11 14:30:45+02 | 2026-10-11 14:30:00+02

CASE WHEN condition THEN result [WHEN ...] [ELSE result] END
CASE expression WHEN value THEN result [WHEN ...] [ELSE result] END

SELECT reference, stock,
       CASE WHEN stock = 0 THEN 'rupture' WHEN stock < 50 THEN 'faible' ELSE 'ok' END AS etat
FROM article ORDER BY id;

 reference | stock | etat
-----------+-------+---------
 STY-001   | 500   | ok
 CAH-096   | 120   | ok
 CLA-A4    | 35    | faible
 AGR-024   | 0     | rupture

coalesce(value1, value2, ...)

SELECT nom, coalesce(ville, 'inconnue') AS ville FROM client ORDER BY id;

 nom          | ville
--------------+----------
 Dupont       | Lyon
 Martin       | Paris
 Bernard      | Lyon
 Élodie Petit | inconnue

nullif(a, b)

SELECT reference, prix / nullif(stock, 0) AS prix_par_unite_en_stock FROM article ORDER BY id;

 reference | prix_par_unite_en_stock
-----------+-------------------------
 STY-001   | 0.0024000000000000
 CAH-096   | 0.0208333333333333
 CLA-A4    | 0.1400000000000000
 AGR-024   | NULL

SELECT 1::boolean, 0::boolean, 'yes'::boolean, 'off'::boolean, true::int, false::int;

t | f | t | f | 1 | 0

SELECT true::text, false::varchar, 'actif : ' || true, ('{"ok": "yes"}'::jsonb ->> 'ok')::boolean;

true | false | actif : true | t

SELECT count(*), count(ville), count(DISTINCT ville) FROM client;
SELECT sum(stock), avg(stock), min(prix), max(prix), sum(prix), avg(prix) FROM article;

4 | 3 | 2
655 | 163.7500000000000000 | 1.20 | 12.00 | 20.60 | 5.1500000000000000

SELECT article_id, sum(quantite) AS quantite, round(avg(prix_unitaire), 2) AS prix_moyen, count(*) AS lignes
FROM ligne_commande GROUP BY article_id ORDER BY article_id;

 article_id | quantite | prix_moyen | lignes
------------+----------+------------+--------
 1          | 150      | 1.15       | 2
 2          | 15       | 2.50       | 2
 3          | 4        | 4.90       | 1
 4          | 2        | 12.00      | 1

aggregate(...) FILTER (WHERE condition)

SELECT count(*) FILTER (WHERE statut = 'livrée') AS livrees,
       count(*) FILTER (WHERE statut <> 'livrée') AS autres
FROM commande;
SELECT count(*) FILTER (WHERE stock > 0), sum(stock) FILTER (WHERE prix < 3), avg(prix) FILTER (WHERE stock > 0) FROM article;

2 | 2
3 | 620 | 2.8666666666666667

SELECT json_agg(nom) FILTER (WHERE ville = 'Lyon') FROM client;
SELECT json_object_agg(reference, stock) FILTER (WHERE stock > 100) FROM article;

["Dupont", "Bernard"]
{"STY-001": 500, "CAH-096": 120}

string_agg(expr, separator [ORDER BY ...])
string_agg(DISTINCT expr, separator [ORDER BY ...])

SELECT string_agg(nom, ', ' ORDER BY nom) FROM client;
SELECT string_agg(DISTINCT ville, '/') FROM client;
SELECT ville, string_agg(nom, ' + ' ORDER BY nom) AS clients FROM client GROUP BY ville ORDER BY ville;

Bernard, Dupont, Martin, Élodie Petit
Lyon/Paris

 ville | clients
-------+------------------
 Lyon  | Bernard + Dupont
 Paris | Martin
 NULL  | Élodie Petit

SELECT string_agg(reference || '=' || stock, '; ' ORDER BY stock DESC) FROM article;

STY-001=500; CAH-096=120; CLA-A4=35; AGR-024=0

array_agg(expr [ORDER BY ...])
array_agg(DISTINCT expr [ORDER BY ...])

SELECT array_agg(nom ORDER BY nom) AS noms, array_agg(DISTINCT ville) AS villes, array_agg(id) FILTER (WHERE ville = 'Lyon') AS lyonnais FROM client;
SELECT c.nom, array_agg(o.id ORDER BY o.id) AS commandes FROM client c JOIN commande o ON o.client_id = c.id GROUP BY c.nom ORDER BY c.nom;

{Bernard,Dupont,Martin,"Élodie Petit"} | {Lyon,Paris,NULL} | {1,3}

 nom     | commandes
---------+-----------
 Bernard | {4}
 Dupont  | {1,2}
 Martin  | {3}

json_agg(expr [ORDER BY ...])    jsonb_agg(expr [ORDER BY ...])
json_object_agg(key, value)    jsonb_object_agg(key, value)

SELECT json_agg(nom ORDER BY nom) FROM client;
SELECT json_object_agg(reference, stock) FROM article;
SELECT c.nom, json_agg(jsonb_build_object('commande', o.id, 'date', o.date_commande) ORDER BY o.id) AS commandes
FROM client c JOIN commande o ON o.client_id = c.id GROUP BY c.nom ORDER BY c.nom;

["Bernard", "Dupont", "Martin", "Élodie Petit"]
{"STY-001": 500, "CAH-096": 120, "CLA-A4": 35, "AGR-024": 0}

 nom     | commandes
---------+--------------------------------------------------------------------------------
 Bernard | [{"date": "2026-10-09", "commande": 4}]
 Dupont  | [{"date": "2026-09-28", "commande": 1}, {"date": "2026-10-05", "commande": 2}]
 Martin  | [{"date": "2026-10-06", "commande": 3}]

SELECT bool_and(stock > 0), bool_or(stock > 0), every(prix > 1) FROM article;

f | t | t

SELECT stddev(quantite), variance(quantite), stddev_pop(quantite), var_pop(quantite), stddev(quantite::float8)
FROM ligne_commande;

39.4246116023988244 | 1554.3000000000000000 | 35.9895818258562154 | 1295.2500000000000000 | 39.42461160239883

SELECT corr(y, x), covar_pop(y, x), covar_samp(y, x), regr_slope(y, x), regr_intercept(y, x), regr_r2(y, x), regr_count(y, x)
FROM (VALUES (1, 3), (2, 5), (3, 7), (4, NULL)) v(x, y);
SELECT regr_avgx(y, x), regr_avgy(y, x), regr_sxx(y, x), regr_syy(y, x), regr_sxy(y, x)
FROM (VALUES (1, 3), (2, 5), (3, 7), (4, NULL)) v(x, y);

1 | 1.3333333333333333 | 2 | 2 | 1 | 1 | 3
2 | 5 | 2 | 8 | 4

percentile_cont(fraction) WITHIN GROUP (ORDER BY expr)
percentile_disc(fraction) WITHIN GROUP (ORDER BY expr)
mode() WITHIN GROUP (ORDER BY expr)
rank(value) WITHIN GROUP (ORDER BY expr)        -- likewise dense_rank, percent_rank, cume_dist

SELECT percentile_cont(ARRAY[0.25, 0.5]) WITHIN GROUP (ORDER BY v),
       percentile_disc(ARRAY[0.5, 1]) WITHIN GROUP (ORDER BY v)
FROM (VALUES (10), (20), (30), (40)) AS w(v);
-- {17.5,25} | {20,40}

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY quantite) AS mediane, percentile_disc(0.5) WITHIN GROUP (ORDER BY quantite) AS mediane_disc,
       percentile_cont(0.9) WITHIN GROUP (ORDER BY quantite) AS p90, mode() WITHIN GROUP (ORDER BY article_id) AS article_frequent
FROM ligne_commande;
SELECT rank(10) WITHIN GROUP (ORDER BY quantite), dense_rank(10) WITHIN GROUP (ORDER BY quantite),
       percent_rank(10) WITHIN GROUP (ORDER BY quantite), cume_dist(10) WITHIN GROUP (ORDER BY quantite)
FROM ligne_commande;

 mediane | mediane_disc | p90 | article_frequent
---------+--------------+-----+------------------
 7.5     | 5            | 75  | 1

4 | 4 | 0.5 | 0.7142857142857143

SELECT grouping(ville) AS total, ville, count(*) FROM client GROUP BY ROLLUP (ville);

 total | ville | count(*)
-------+-------+----------
 0     | NULL  | 1
 0     | Lyon  | 2
 0     | Paris | 1
 1     | NULL  | 4

function(...) OVER ([PARTITION BY ...] [ORDER BY ...] [frame])
function(...) OVER window_name

SELECT id, designation, prix,
       row_number() OVER (ORDER BY prix DESC) AS n,
       rank() OVER (ORDER BY prix DESC) AS rang,
       ntile(2) OVER (ORDER BY prix DESC) AS moitie
FROM article ORDER BY id;

 id | designation      | prix  | n | rang | moitie
----+------------------+-------+---+------+--------
 1  | Stylo bille bleu | 1.20  | 4 | 4    | 2
 2  | Cahier 96 pages  | 2.50  | 3 | 3    | 2
 3  | Classeur A4      | 4.90  | 2 | 2    | 1
 4  | Agrafeuse        | 12.00 | 1 | 1    | 1

SELECT id, percent_rank() OVER w, cume_dist() OVER w, dense_rank() OVER w
FROM article WINDOW w AS (ORDER BY prix) ORDER BY id;

1 | 0                  | 0.25 | 1
2 | 0.3333333333333333 | 0.5  | 2
3 | 0.6666666666666666 | 0.75 | 3
4 | 1                  | 1    | 4

SELECT * FROM (SELECT id, prix, rank() OVER (ORDER BY prix DESC) AS r FROM article) x WHERE r <= 2 ORDER BY r;
SELECT id, prix FROM article QUALIFY row_number() OVER (ORDER BY prix DESC) <= 2 ORDER BY id;

4 | 12.00 | 1
3 | 4.90  | 2

3 | 4.90
4 | 12.00

SELECT id, prix,
       lag(prix) OVER (ORDER BY id) AS precedent,
       lead(prix) OVER (ORDER BY id) AS suivant,
       lag(prix, 2, 0) OVER (ORDER BY id) AS avant_dernier
FROM article ORDER BY id;

 id | prix  | precedent | suivant | avant_dernier
----+-------+-----------+---------+---------------
 1  | 1.20  | NULL      | 2.50    | 0.00
 2  | 2.50  | 1.20      | 4.90    | 0.00
 3  | 4.90  | 2.50      | 12.00   | 1.20
 4  | 12.00 | 4.90      | NULL    | 2.50

SELECT id, date_commande, date_commande - lag(date_commande) OVER (ORDER BY date_commande) AS jours
FROM commande ORDER BY id;

 id | date_commande | jours
----+---------------+-------
 1  | 2026-09-28    | NULL
 2  | 2026-10-05    | 7
 3  | 2026-10-06    | 1
 4  | 2026-10-09    | 3

SELECT id, prix,
       first_value(prix) OVER (ORDER BY id) AS premier,
       last_value(prix) OVER (ORDER BY id) AS dernier_du_cadre,
       last_value(prix) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS dernier,
       nth_value(prix, 2) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS deuxieme
FROM article ORDER BY id;

 id | prix  | premier | dernier_du_cadre | dernier | deuxieme
----+-------+---------+------------------+---------+----------
 1  | 1.20  | 1.20    | 1.20             | 12.00   | 2.50
 2  | 2.50  | 1.20    | 2.50             | 12.00   | 2.50
 3  | 4.90  | 1.20    | 4.90             | 12.00   | 2.50
 4  | 12.00 | 1.20    | 12.00            | 12.00   | 2.50

SELECT commande_id, article_id, quantite,
       sum(quantite) OVER (PARTITION BY commande_id) AS total_commande,
       count(*) OVER (PARTITION BY commande_id) AS lignes
FROM ligne_commande ORDER BY 1, 2;

 commande_id | article_id | quantite | total_commande | lignes
-------------+------------+----------+----------------+--------
 1           | 1          | 100      | 110            | 2
 1           | 2          | 10       | 110            | 2
 2           | 3          | 4        | 4              | 1
 3           | 1          | 50       | 52             | 2
 3           | 4          | 2        | 52             | 2
 4           | 2          | 5        | 5              | 1

SELECT id, prix,
       sum(prix) OVER (ORDER BY id) AS cumul,
       avg(prix) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moyenne_glissante,
       round(100 * prix / sum(prix) OVER (), 1) AS part
FROM article ORDER BY id;

 id | prix  | cumul | moyenne_glissante  | part
----+-------+-------+--------------------+------
 1  | 1.20  | 1.20  | 1.2000000000000000 | 5.8
 2  | 2.50  | 3.70  | 1.8500000000000000 | 12.1
 3  | 4.90  | 8.60  | 3.7000000000000000 | 23.8
 4  | 12.00 | 20.60 | 8.4500000000000000 | 58.3

SELECT id, prix, percentile_cont(0.5) WITHIN GROUP (ORDER BY prix) OVER () AS mediane FROM article ORDER BY id;

 id | prix  | mediane
----+-------+---------
 1  | 1.20  | 3.7
 2  | 2.50  | 3.7
 3  | 4.90  | 3.7
 4  | 12.00 | 3.7

SELECT DISTINCT ON (client_id) client_id, id, date_commande FROM commande ORDER BY client_id, date_commande DESC;

 client_id | id | date_commande
-----------+----+---------------
 1         | 2  | 2026-10-05
 2         | 3  | 2026-10-06
 3         | 4  | 2026-10-09

SELECT reference,
       attributs -> 'couleur' AS json,
       attributs ->> 'couleur' AS texte,
       attributs -> 'tags' ->> 0 AS premier_tag,
       attributs #>> '{dim,l}' AS longueur
FROM article ORDER BY id;

 reference | json    | texte | premier_tag | longueur
-----------+---------+-------+-------------+----------
 STY-001   | "bleu"  | bleu  | bureau      | 14
 CAH-096   | "rouge" | rouge | papier      | NULL
 CLA-A4    | "noir"  | noir  | NULL        | NULL
 AGR-024   | NULL    | NULL  | NULL        | NULL

SELECT reference FROM article WHERE attributs ->> 'couleur' = 'bleu';              -- STY-001
SELECT reference FROM article WHERE attributs @> '{"couleur": "noir"}';            -- CLA-A4
SELECT reference FROM article WHERE attributs -> 'tags' @> '["papier"]';           -- CAH-096
SELECT reference FROM article WHERE (attributs ->> 'pages')::int > 50;             -- CAH-096
SELECT reference FROM article WHERE attributs -> 'pages' IS NOT NULL;              -- CAH-096
SELECT attributs ->> 'couleur' AS couleur, count(*) FROM article GROUP BY attributs ->> 'couleur' ORDER BY 1;

 couleur | count(*)
---------+----------
 bleu    | 1
 noir    | 1
 rouge   | 1
 NULL    | 1

SELECT reference, attributs ? 'pages' AS a_pages, attributs ?| ARRAY['pages', 'recycle'] AS l_un,
       attributs ?& ARRAY['couleur', 'tags'] AS les_deux, attributs -> 'tags' ? 'papier' AS tag_papier
FROM article ORDER BY id;

 reference | a_pages | l_un | les_deux | tag_papier
-----------+---------+------+----------+------------
 STY-001   | f       | f    | t        | f
 CAH-096   | t       | t    | t        | t
 CLA-A4    | f       | t    | t        | f
 AGR-024   | NULL    | NULL | NULL     | NULL

UPDATE article SET attributs = attributs || '{"promo": true}'::jsonb WHERE id = 3 RETURNING attributs;
UPDATE article SET attributs = attributs - 'promo' WHERE id = 3 RETURNING attributs;

{"tags": [], "promo": true, "couleur": "noir", "recycle": true}
{"tags": [], "couleur": "noir", "recycle": true}

SELECT jsonb_build_object('a', 1, 'b', 'x', 'c', true, 'd', NULL, 'e', 1.5), jsonb_build_array(1, 'x', true, NULL),
       jsonb_build_object('n', jsonb_build_object('x', 1), 't', jsonb_build_array(1, 2));
SELECT to_jsonb('txt'::text), to_json(42), to_jsonb(true), to_jsonb(date '2026-10-11'), to_jsonb(NULL::text);
SELECT jsonb_build_object('id', id, 'nom', nom, 'ville', ville) FROM client WHERE id IN (1, 4) ORDER BY id;
SELECT json_object('{a, 1, b, 2}'), json_object('{a, b}', '{x, y}');

{"a": 1, "b": "x", "c": true, "d": null, "e": 1.5} | [1, "x", true, null] | {"n": {"x": 1}, "t": [1, 2]}
"txt" | 42 | true | "2026-10-11" | null
{"id": 1, "nom": "Dupont", "ville": "Lyon"}
{"id": 4, "nom": "Élodie Petit", "ville": null}
{"a": "1", "b": "2"} | {"a": "x", "b": "y"}

jsonb_set(document, path, value [, create])

SELECT jsonb_set('{"a": 1}'::jsonb, '{a}', '5'), jsonb_set('{"a": {"b": 1}}'::jsonb, '{a,b}', '"x"'),
       jsonb_set('{"a": 1}'::jsonb, '{b}', '2'), jsonb_set('{"a": 1}'::jsonb, '{b}', '2', false),
       jsonb_set('{"a": [1, 2]}'::jsonb, '{a,0}', '9');
SELECT jsonb_set(attributs, '{stock}', to_jsonb(stock)) FROM article WHERE id = 1;

{"a": 5} | {"a": {"b": "x"}} | {"a": 1, "b": 2} | {"a": 1} | {"a": [9, 2]}
{"dim": {"d": 0.8, "l": 14}, "tags": ["bureau", "ecriture"], "stock": 500, "couleur": "bleu"}

jsonb_insert(document, path, value [, after])
jsonb_strip_nulls(document)    json_strip_nulls(document)

SELECT jsonb_insert('{"a": [1, 2]}', '{a, 1}', '9'), jsonb_insert('{"a": [1, 2]}', '{a, 1}', '9', true), jsonb_insert('{"a": 1}', '{b}', '2');
SELECT jsonb_strip_nulls('{"a": 1, "b": null, "c": {"d": null}}'), json_strip_nulls('{"a": null, "b": 2}');

{"a": [1, 9, 2]} | {"a": [1, 2, 9]} | {"a": 1, "b": 2}
{"a": 1, "c": {}} | {"b":2}

SELECT jsonb_typeof('{"a": 1}'::jsonb), jsonb_typeof('[1]'::jsonb), jsonb_typeof('1'::jsonb),
       jsonb_typeof('"a"'::jsonb), jsonb_typeof('true'::jsonb), jsonb_typeof('null'::jsonb);
SELECT reference, jsonb_array_length(attributs -> 'tags') AS tags, jsonb_typeof(attributs -> 'dim') AS dim
FROM article ORDER BY id;

object | array | number | string | boolean | null

 reference | tags | dim
-----------+------+--------
 STY-001   | 2    | object
 CAH-096   | 1    | NULL
 CLA-A4    | 0    | NULL
 AGR-024   | NULL | NULL

SELECT jsonb_extract_path(attributs, 'dim', 'l'), jsonb_extract_path_text(attributs, 'tags', '1') FROM article WHERE id = 1;
SELECT jsonb_pretty(attributs) FROM article WHERE id = 2;

14 | ecriture

{
    "tags": [
        "papier"
    ],
    "pages": 96,
    "couleur": "rouge"
}

jsonb_path_exists(doc, path [, variables [, silent]])
jsonb_path_match(doc, path [, variables [, silent]])
jsonb_path_query_array(doc, path [, variables [, silent]])
jsonb_path_query_first(doc, path [, variables [, silent]])

SELECT reference, jsonb_path_exists(attributs, '$.tags[*] ? (@ == "papier")') AS papier, jsonb_path_match(attributs, '$.pages > 50') AS gros,
       jsonb_path_query_array(attributs, '$.dim.*') AS dims, jsonb_path_query_first(attributs, '$.tags[last]') AS dernier_tag
FROM article ORDER BY id;
SELECT jsonb_path_query_array('{"a": [1, 2, 3, 4]}', '$.a[*] ? (@ > $min && @ < $max)', '{"min": 1, "max": 4}');
SELECT jsonb_path_exists('{"a": 1}', 'strict $.b', '{}', true), jsonb_path_exists('{"a": 1}', 'lax $.b');

 reference | papier | gros | dims      | dernier_tag
-----------+--------+------+-----------+-------------
 STY-001   | f      | f    | [0.8, 14] | "ecriture"
 CAH-096   | t      | t    | []        | "papier"
 CLA-A4    | f      | f    | []        | NULL
 AGR-024   | NULL   | NULL | NULL      | NULL

[2, 3]
NULL | f

SELECT * FROM jsonb_each_text('{"a": 1, "b": "x", "c": null}');
SELECT * FROM jsonb_array_elements('[1, "x", {"k": true}]') WITH ORDINALITY AS e(valeur, rang);
SELECT a.reference, t.tag FROM article a, jsonb_array_elements_text(a.attributs -> 'tags') AS t(tag) ORDER BY a.id, t.tag;
SELECT a.reference, k.cle FROM article a, jsonb_object_keys(a.attributs) AS k(cle) WHERE a.id = 2 ORDER BY 2;

a | 1
b | x
c | NULL

1 | 1
"x" | 2
{"k": true} | 3

 reference | tag
-----------+----------
 STY-001   | bureau
 STY-001   | ecriture
 CAH-096   | papier

 reference | cle
-----------+---------
 CAH-096   | couleur
 CAH-096   | pages
 CAH-096   | tags

SELECT json_keys(attributs), json_length(attributs), json_remove(attributs, '$.dim'), json_value(attributs, '$.couleur')
FROM article WHERE id = 1;
SELECT a.reference, t.tag
FROM article a, json_table(a.attributs, '$.tags[*]' COLUMNS (tag text PATH '$')) AS t ORDER BY a.id, t.tag;

["dim", "tags", "couleur"] | 3 | {"tags": ["bureau", "ecriture"], "couleur": "bleu"} | bleu

 reference | tag
-----------+----------
 STY-001   | bureau
 STY-001   | ecriture
 CAH-096   | papier

expression::type
CAST(expression AS type)
type 'text'

SELECT '42'::int + 1, CAST('42' AS integer), 42::text || '!', '3.14'::numeric(5,1), '3.14'::float8,
       int4 '5', boolean 't', '  42  '::int;

43 | 42 | 42! | 3.1 | 3.14 | 5 | t | 42

SELECT pg_typeof(1), pg_typeof(3000000000), pg_typeof(1.5), pg_typeof(1.5::float8), pg_typeof('a'::text),
       pg_typeof(true), pg_typeof(now()), pg_typeof(current_date);

integer | bigint | numeric | double precision | text | boolean | timestamp with time zone | date

SELECT md5('abc'), to_hex(255), to_hex(-1);
SELECT encode('abc'::bytea, 'hex'), encode('abc'::bytea, 'base64'), decode('616263', 'hex'), decode('YWJj', 'base64'),
       length(decode('ff00', 'hex'));
SELECT encode(nom::bytea, 'base64'), decode('w4lsb2RpZSBQZXRpdA==', 'base64')::text FROM client WHERE id = 4;

900150983cd24fb0d6963f7d28e17f72 | ff | ffffffff
616263 | YWJj | \x616263 | \x616263 | 2
w4lsb2RpZSBQZXRpdA== | Élodie Petit

SELECT sha256('abc'), encode(sha224('abc'), 'hex'), length(sha384('abc')), length(sha512('abc'));
SELECT encode(digest('abc', 'sha256'), 'hex'), digest('abc', 'md5') = decode(md5('abc'), 'hex'), sha2('a', 256);

\xba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad | 23097d223405d8228642a477bda255b32aadbce4bda0b3f7e36c9da7 | 48 | 64
ba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad | t | ca978112ca1bbdcafac231b39a23dc4da786eff8147c4e72b9807785afee48bb

SELECT convert_to('café', 'LATIN1'), convert_to('café', 'UTF8'), convert_from('\x636166e9'::bytea, 'LATIN1'), convert_from('\x80'::bytea, 'WIN1252');
SELECT get_byte('\x1234ff'::bytea, 2), set_byte('\x1234ff'::bytea, 0, 0);

\x636166e9 | \x636166c3a9 | café | €
255 | \x0034ff

SELECT crypt('secret', '$1$abcdefgh'), crypt('secret', '$1$abcdefgh$cHJi5PXp/ki/ktXzqlk6I1') = '$1$abcdefgh$cHJi5PXp/ki/ktXzqlk6I1',
       length(gen_salt('des')), gen_salt('md5') LIKE '$1$%', length(gen_random_bytes(16));

$1$abcdefgh$cHJi5PXp/ki/ktXzqlk6I1 | t | 2 | t | 16

SELECT current_database(), current_schema, current_user, current_setting('server_version'), current_setting('TimeZone'),
       current_setting('inconnu.param', true);

miraj | ventes | root | 16.4 | Europe/Paris | NULL

SELECT pg_input_is_valid('42', 'integer'), pg_input_is_valid('4x', 'integer'), pg_input_is_valid('{"a":', 'jsonb'),
       pg_input_is_valid('1 day', 'interval'), pg_input_is_valid('maybe', 'boolean');
SELECT pg_column_size('abc'::text), pg_column_size(true), inet_client_addr(), pg_postmaster_start_time() <= now();

t | f | f | t | f
4 | 1 | 127.0.0.1 | t

SELECT gen_random_uuid();   -- fe6bc144-804b-49de-9ad2-555c3f027f68

CREATE SEQUENCE seq_facture START 100 INCREMENT 10;
SELECT nextval('seq_facture'), nextval('seq_facture');      -- 100 | 110
SELECT currval('seq_facture');                              -- 110
SELECT setval('seq_facture', 500), nextval('seq_facture');  -- 500 | 510
SELECT setval('seq_facture', 1000, false), nextval('seq_facture');   -- 1000 | 1000
DROP SEQUENCE seq_facture;

generate_series(start, stop [, step])

SELECT n, n * n AS carre FROM generate_series(1, 3) AS s(n);
SELECT * FROM generate_series(10, 1, -3) WITH ORDINALITY AS s(n, rang);
SELECT * FROM generate_series(0.5, 1.5, 0.5);
SELECT * FROM generate_series(timestamp '2026-10-11 08:00', timestamp '2026-10-11 10:00', interval '1 hour');
SELECT generate_series(1, 3) AS n, 'x' AS lettre;

1 | 1
2 | 4
3 | 9

10 | 1
7 | 2
4 | 3
1 | 4

0.5
1.0
1.5

2026-10-11 08:00:00
2026-10-11 09:00:00
2026-10-11 10:00:00

1 | x
2 | x
3 | x

SELECT j.jour::date AS jour, count(o.id) AS commandes
FROM generate_series(date '2026-10-04', date '2026-10-07', interval '1 day') AS j(jour)
LEFT JOIN commande o ON o.date_commande = j.jour
GROUP BY j.jour ORDER BY 1;

 jour       | commandes
------------+-----------
 2026-10-04 | 0
 2026-10-05 | 1
 2026-10-06 | 1
 2026-10-07 | 0

SELECT pg_try_advisory_lock(42), pg_try_advisory_lock(42), pg_advisory_unlock(42), pg_advisory_unlock(42), pg_advisory_unlock(42);
BEGIN;
SELECT pg_try_advisory_xact_lock(8);
COMMIT;

t | t | t | t | f
t

SELECT ARRAY[1, 2, 3], ARRAY['a', 'b c', NULL], ARRAY[1, 2.5], '{1,2,3}'::int[], ARRAY[]::text[], pg_typeof(ARRAY[1, 2]);
SELECT ARRAY[true, false], ARRAY['', 'a,b', 'q"x'], ARRAY[date '2026-10-11'];