-- A exécuter en tant que postgres

DROP ROLE pierrot ;
DROP ROLE arlequin ;

CREATE ROLE pierrot LOGIN ;
CREATE ROLE arlequin LOGIN ;

-- Les 2 utilisent le même mot de passe « colombine »

-- pierrot se connecte avec une vieille application
-- qui a besoin d'un mot de passe MD5

SET password_encryption TO md5 ;
\password pierrot

-- arlequin utilise un client récent
SET password_encryption TO "scram-sha-256" ;

\password arlequin

SELECT rolname, rolpassword
FROM   pg_authid
WHERE  rolname IN ('pierrot', 'arlequin') \gx

host    all             pierrot           192.168.88.0/24           md5
host    all             arlequin          192.168.88.0/24           scram-sha-256

-- A exécuter en tant que postgres

SET password_encryption TO "scram-sha-256" ;

\password pierrot

SELECT rolname, rolpassword
FROM   pg_authid
WHERE  rolname IN ('pierrot', 'arlequin') \gx

host    all             pierrot           192.168.88.0/24           scram-sha-256
host    all             arlequin          192.168.88.0/24           scram-sha-256

GRANT pg_read_all_data TO dump_user;

GRANT USAGE ON SCHEMA public TO dump_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dump_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO dump_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO dump_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO dump_user;

GRANT pg_database_owner TO atelier;
-- ERROR:  role "pg_database_owner" cannot have explicit members

CREATE TABLE tab (id int);
ALTER TABLE tab OWNER TO pg_database_owner;

-- avec un compte superutilisateur
ALTER DATABASE test OWNER TO role1;

SET role = role1;
INSERT INTO tab VALUES (1), (2), (3);
-- INSERT 0 3

-- avec un compte superutilisateur
ALTER DATABASE test OWNER TO role2;

SET role = role1;
INSERT INTO tab VALUES (4), (5), (6);
-- ERROR:  permission denied for table tab

compute_query_id = on
log_temp_files = 0
log_min_duration_statement = 0
max_parallel_workers_per_gather = 8
log_line_prefix = '[%P-%p]: id=%Q '

[20969-20995]: id=-8329068551672606797 
    LOG:  temporary file: path "pgsql_tmp20995.0", size 29450240
[20969-20993]: id=-8329068551672606797 
    LOG:  temporary file: path "pgsql_tmp20993.0", size 52682752
[20969-20994]: id=-8329068551672606797 
    LOG:  temporary file: path "pgsql_tmp20994.0", size 53387264
[20969-20992]: id=-8329068551672606797 
    LOG:  temporary file: path "pgsql_tmp20992.0", size 53477376
[-20969]: id=-8329068551672606797 
    LOG:  temporary file: path "pgsql_tmp20969.0", size 29384704
[-20969]: id=-8329068551672606797 
    LOG:  duration: 2331.661 ms  
    statement:  select trunc(montant/1000) as quintile, avg(montant) 
                from ventes group by quintile;

SET idle_session_timeout TO '5s';
-- Attendre 5 secondes.

SELECT 1;

FATAL:  terminating connection due to idle-session timeout
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

FATAL:  terminating connection due to idle-session timeout

           Partitioned table "ventes"
   Column   |  Type   | Collation | Nullable | Default 
------------+---------+-----------+----------+---------
 date_vente | date    |           | not null | 
 projet_id  | bigint  |           |          | 
 montant    | numeric |           |          | 
Partition key: RANGE (date_vente)
Number of partitions: 22 (Use \d+ to list them.)
Partitions: ventes_y2001 FOR VALUES FROM ('2001-01-01') TO ('2002-01-01'),
            ventes_y2002 FOR VALUES FROM ('2002-01-01') TO ('2003-01-01'),
            ...
            ventes_y2021 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01'),
            ventes_y2022 FOR VALUES FROM ('2022-01-01') TO ('2023-01-01')

pg_dump -Fc -d workshop -t ventes_y2001 -f ventes.dump
# pg_dump: dumping contents of table "ventes_y2001"

pg_restore -l ventes.dump

; Selected TOC Entries:
;
210; 1259 18728 TABLE public ventes_y2001 user
2449; 0 0 TABLE ATTACH public ventes_y2001 user
2589; 0 18728 TABLE DATA public ventes_y2001 user

pg_restore -v -d staging -t ventes_y2001 ventes.dump

pg_restore: connecting to database for restore
pg_restore: creating TABLE "ventes_y2001"
pg_restore: processing data for table "ventes_y2001"

pg_restore: connecting to database for restore
pg_restore: creating TABLE "ventes_y2001"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 201; 1259 25987 TABLE ventes_y2001 user
pg_restore: error: could not execute query:
ERROR:  relation "ventes" does not exist
Command was: CREATE TABLE ventes_y2001 (
    date_vente date NOT NULL,
    projet_id bigint,
    montant numeric
);
ALTER TABLE ONLY ventes ATTACH PARTITION ventes_y2001 
  FOR VALUES FROM ('2001-01-01') TO ('2002-01-01');

pg_restore: error: could not execute query: 
ERROR:  relation "ventes_y2001" does not exist
Command was: ALTER TABLE ventes_y2001 OWNER TO user;

pg_restore: processing data for table "ventes_y2001"
pg_restore: from TOC entry 2286; 0 25987 TABLE DATA ventes_y2001 user
pg_restore: error: could not execute query: 
ERROR:  relation "ventes_y2001" does not exist
Command was: COPY ventes_y2001 (date_vente, projet_id, montant) FROM stdin;
pg_restore: warning: errors ignored on restore: 3

pg_dump --format=custom \
        --dbname=workshop \
        --schema=encrypted_data > /backup/workshop_encrypted_data.dmp

pg_dump --format=custom \
        --dbname=workshop \
        --extension=pgcrypto \
        --schema=encrypted_data > /backup/workshop_encrypted_data_with_ext.dmp

dropdb workshop

pg_restore --dbname postgres \
           --create \
           /backup/encrypted_data.dmp

workshop=# \dx

Liste des extensions installées
-[ RECORD 1 ]-----------------------------
Nom         | plpgsql
Version     | 1.0
Schéma      | pg_catalog
Description | PL/pgSQL procedural language

dropdb workshop

pg_restore --dbname postgres \
           --create \
           /backup/workshop_encrypted_data_with_ext.dmp

workshop=# \dx

Liste des extensions installées
-[ RECORD 1 ]----------------------------------------------------
Nom         | pgcrypto
Version     | 1.9
Schéma      | public
Description | track planning and execution statistics of all SQL
            | statements executed
-[ RECORD 2 ]----------------------------------------------------
Nom         | plpgsql
Version     | 1.0
Schéma      | pg_catalog
Description | PL/pgSQL procedural language

\d+ parent

                     Table partitionnée « public.parent »
 Colonne |  Type   | Collationnement | NULL-able | Par défaut 
---------+---------+-----------------+-----------+------------
 id      | integer |                 |           |            
Clé de partition : RANGE (id)
Partitions: enfant_1 FOR VALUES FROM (0) TO (5000000),
            enfant_2 FOR VALUES FROM (5000000) TO (11000000)

\d enfant_2

                     Table « public.enfant_2 »
 Colonne |  Type   | Collationnement | NULL-able | Par défaut 
---------+---------+-----------------+-----------+------------
 id      | integer |                 |           |            
Partition de : parent FOR VALUES FROM (5000000) TO (11000000)
Index :
    "enfant_2_id_idx" btree (id)

ALTER TABLE parent DETACH PARTITION enfant_2 CONCURRENTLY ;

-- Une contrainte CHECK a été créée
-- Celle-ci correspond à la contrainte de partition
\d enfant_2

                     Table « public.enfant_2 »
 Colonne |  Type   | Collationnement | NULL-able | Par défaut 
---------+---------+-----------------+-----------+------------
 id      | integer |                 |           |            
Index :
    "enfant_2_id_idx" btree (id)
Contraintes de vérification :
    "enfant_2_id_check" CHECK (id IS NOT NULL AND id >= 5000000 AND id < 11000000)

BEGIN;
ALTER TABLE parent DETACH PARTITION enfant_2 CONCURRENTLY;
-- ERROR:  ALTER TABLE ... DETACH CONCURRENTLY cannot run inside a transaction block

ALTER TABLE parent DETACH PARTITION enfant_1 CONCURRENTLY;
-- ERROR:  cannot detach partitions concurrently when a default partition exists

REINDEX INDEX parent_index;
-- ERROR:  REINDEX is not yet implemented for partitioned indexes

REINDEX TABLE parent;
-- WARNING:  REINDEX of partitioned tables is not yet implemented, skipping "parent"
-- REINDEX

CREATE EXTENSION pgstattuple;
SELECT avg_leaf_density, leaf_fragmentation FROM pgstatindex('enfant_1_id_idx');

 avg_leaf_density | leaf_fragmentation 
------------------+--------------------
            74.18 |                 50

SELECT avg_leaf_density, leaf_fragmentation FROM pgstatindex('enfant_2_id_idx');

 avg_leaf_density | leaf_fragmentation 
------------------+--------------------
            74.17 |                 50

REINDEX INDEX parent_index;

SELECT avg_leaf_density, leaf_fragmentation FROM pgstatindex('enfant_1_id_idx');

 avg_leaf_density | leaf_fragmentation 
------------------+--------------------
            90.23 |                  0

SELECT avg_leaf_density, leaf_fragmentation FROM pgstatindex('enfant_2_id_idx');

 avg_leaf_density | leaf_fragmentation 
------------------+--------------------
            90.23 |                  0

BEGIN;
REINDEX INDEX parent_index;
-- ERROR:  REINDEX INDEX cannot run inside a transaction block
-- CONTEXT: while reindexing partitioned index "public.parent_index"

# Vérification des options de compilation de PostgreSQL
postgres@pop-os:~$ pg_config | grep 'with-lz4'
CONFIGURE =  [...] '--with-lz4' [...]

test=# CREATE TABLE t1 (champ1 text COMPRESSION lz4);

test=# \d+ t1
                                                   Table « public.t1 »
 Colonne | Type | Collationnement | NULL-able | Par défaut | Stockage | Compression 
---------+------+-----------------+-----------+------------+----------+-------------
 champ1  | text |                 |           |            | extended | lz4         

test=# ALTER TABLE t1 ALTER COLUMN champ1 SET COMPRESSION pglz;

test=# \d+ t1
                                                   Table « public.t1 »
 Colonne | Type | Collationnement | NULL-able | Par défaut | Stockage | Compression 
---------+------+-----------------+-----------+------------+----------+-------------
 champ1  | text |                 |           |            | extended | pglz        

test=# SHOW default_toast_compression;
 default_toast_compression 
---------------------------
 pglz

test=# SET default_toast_compression TO lz4;

test=# SHOW default_toast_compression;
 default_toast_compression 
---------------------------
 lz4

test=# SHOW default_toast_compression ;
 default_toast_compression 
---------------------------
 pglz
test=# CREATE TABLE t2 (champ2 text);

test=# INSERT INTO t2 VALUES (repeat('123456789', 5000));

test=# SET default_toast_compression TO lz4;

test=# INSERT INTO t2 VALUES (repeat('123456789', 5000));

test=# SELECT pg_column_compression(champ2) FROM t2;
 pg_column_compression 
-----------------------
 pglz
 lz4

test=# SELECT pg_column_compression(champ2) FROM t2;
 pg_column_compression 
-----------------------
 pglz
 lz4

test=# CREATE TABLE t3 AS SELECT * FROM t2;

test=# SELECT pg_column_compression(champ2) FROM t3;
 pg_column_compression 
-----------------------
 pglz
 lz4

test=# \d+ compress_pglz
                                              Table « public.compress_pglz »
 Colonne | Type | Collationnement | NULL-able | Par défaut | Stockage | Compression 
---------+------+-----------------+-----------+------------+----------+-------------
 champ1  | text |                 |           |            | extended | pglz        

test=# \d+ compress_lz4 
                                              Table « public.compress_lz4 »
 Colonne | Type | Collationnement | NULL-able | Par défaut | Stockage | Compression 
---------+------+-----------------+-----------+------------+----------+-------------
 champ1  | text |                 |           |            | extended | lz4         

-- Comparaison à l'insertion des données
test=# INSERT INTO compress_pglz
          SELECT repeat('123456789', 100000) FROM generate_series(1,10000);
Durée : 36934,700 ms

test=# INSERT INTO compress_lz4
          SELECT repeat('123456789', 100000) FROM generate_series(1,10000);
Durée : 2367,150 ms

# SELECT
    c.relnamespace::regnamespace || '.' || relname AS TABLE,
    reltoastrelid::regclass::text AS table_toast,
    reltuples AS nb_lignes_estimees,
    pg_size_pretty(pg_relation_size(c.oid)) AS "  Heap",
    pg_size_pretty(pg_relation_size(reltoastrelid)) AS "  Toast",
    pg_size_pretty(pg_indexes_size(reltoastrelid)) AS  "  Toast (PK)",
    pg_size_pretty(pg_total_relation_size(c.oid)) AS "Total"
FROM  pg_class c
WHERE relkind = 'r'
AND   relname LIKE 'compress%' \gx

-[ RECORD 1 ]------+-------------------------
table              | public.compress_lz4
table_toast        | pg_toast.pg_toast_357496
nb_lignes_estimees | 10000
  Heap             | 512 kB
  Toast            | 39 MB
  Toast (PK)       | 456 kB
Total              | 40 MB
-[ RECORD 2 ]------+-------------------------
table              | public.compress_pglz
table_toast        | pg_toast.pg_toast_357491
nb_lignes_estimees | 10000
  Heap             | 512 kB
  Toast            | 117 MB
  Toast (PK)       | 1328 kB
Total              | 119 MB

test=# SELECT relname, reltoastrelid::regclass AS reltoastname
         FROM pg_class WHERE relname = 'blog';

test=# \d blog

 relname |      reltoastname       
---------+-------------------------
 blog    | pg_toast.pg_toast_16565

                     Table « public.blog »
 Colonne |  Type   | Collationnement | NULL-able | Par défaut 
---------+---------+-----------------+-----------+------------
 id      | integer |                 |           |            
 title   | text    |                 |           |            
 content | text    |                 |           |            

test=# VACUUM blog;
test=# SELECT relname, last_vacuum FROM pg_stat_all_tables
        WHERE relname IN ('blog', 'pg_toast_16565');

test=# VACUUM (PROCESS_TOAST false) blog;
test=# SELECT relname, last_vacuum FROM pg_stat_all_tables
        WHERE relname IN ('blog', 'pg_toast_16565');

-- On dispose d'une table t1 avec un index idx_col1 dans le tablespace pg_default.
test=# \d t1
                     Table « public.t1 »
 Colonne |  Type   | Collationnement | NULL-able | Par défaut  
---------+---------+-----------------+-----------+------------
 col1    | integer |                 |           |            
Index :
    "idx_col1" btree (col1)

-- Réindexation de la table t1 et déplacement de l'index idx_col1 dans le tablespace tbs.
test=# REINDEX (TABLESPACE tbs) TABLE t1 ;

-- L'index a bien été déplacé.
test=# SELECT c.relname, t.spcname FROM pg_class c 
         JOIN pg_tablespace t ON (c.reltablespace = t.oid) 
        WHERE c.relname = 'index_col1';

  relname   | spcname 
------------+---------
 index_col1 | tbs

-- On dispose de la table partionnée suivante.
test=# SELECT * FROM pg_partition_tree('parent');
  relid   | parentrelid | isleaf | level 
----------+-------------+--------+-------
 parent   |             | f      |     0
 enfant_1 | parent      | t      |     1
 enfant_2 | parent      | t      |     1

-- Avec un index dans la table parent et dans chaque partition.
test=# SELECT * FROM pg_partition_tree('parent_index');
      relid      | parentrelid  | isleaf | level 
-----------------+--------------+--------+-------
 parent_index    |              | f      |     0
 enfant_1_id_idx | parent_index | t      |     1
 enfant_2_id_idx | parent_index | t      |     1

-- Tous les index sont dans le tablespace pg_default.
test=# SELECT c.relname, CASE 
                WHEN c.reltablespace = 0 THEN td.spcname 
                ELSE tr.spcname
              END spcname
         FROM pg_partition_tree('parent_index') p
         JOIN pg_class c ON (c.oid = p.relid)
         JOIN pg_database d ON (d.datname = current_database())
         JOIN pg_tablespace td ON (d.dattablespace = td.oid)
    LEFT JOIN pg_tablespace tr ON (c.reltablespace = tr.oid);

     relname     |  spcname   
-----------------+------------
 parent_index    | pg_default
 enfant_1_id_idx | pg_default
 enfant_2_id_idx | pg_default

-- Reindexation de la table parent avec l'option TABLESPACE.
test=# REINDEX (TABLESPACE tbs) TABLE parent;

-- Seuls les index des partitions ont été déplacés.
test=# SELECT c.relname, CASE 
                WHEN c.reltablespace = 0 THEN td.spcname 
                ELSE tr.spcname
              END spcname
         FROM pg_partition_tree('parent_index') p
         JOIN pg_class c ON (c.oid = p.relid)
         JOIN pg_database d ON (d.datname = current_database())
         JOIN pg_tablespace td ON (d.dattablespace = td.oid)
    LEFT JOIN pg_tablespace tr ON (c.reltablespace = tr.oid);

     relname     |  spcname   
-----------------+------------
 parent_index    | pg_default
 enfant_1_id_idx | tbs
 enfant_2_id_idx | tbs

-- On dispose d'une table blog avec une table TOAST.
test=# \d blog
                     Table « public.blog »
 Colonne |  Type   | Collationnement | NULL-able | Par défaut 
---------+---------+-----------------+-----------+------------
 id      | integer |                 |           |            
 title   | text    |                 |           |            
 content | text    |                 |           |            
Index :
    "blog_title_idx" btree (title)

test=# \d+ pg_toast.pg_toast_16417
Table TOAST « pg_toast.pg_toast_16417 »
  Colonne   |  Type   | Stockage 
------------+---------+----------
 chunk_id   | oid     | plain
 chunk_seq  | integer | plain
 chunk_data | bytea   | plain
Table propriétaire : « public.blog »
Index :
    "pg_toast_16417_index" PRIMARY KEY, btree (chunk_id, chunk_seq)

-- réindexation de la table blog
test=# REINDEX (TABLESPACE tbs) TABLE blog;

-- Seul l'index de la table blog à été déplacé.
-- Celui de la table TOAST a uniquement été reconstruit.
test=# SELECT c.relname, t.spcname FROM pg_class c 
         JOIN pg_tablespace t ON (c.reltablespace = t.oid)
        WHERE t.spcname = 'tbs';

     relname     | spcname 
-----------------+---------
blog_title_idx   | tbs

-- Test de déplacement d'un index directement sur une table TOAST.
test=# reindex (tablespace tbs) table pg_toast.pg_toast_16417;
ERROR:  cannot move system relation "pg_toast_16417_index"

-- Test d'un déplacement d'index sur une table système.
test=# REINDEX (TABLESPACE tbs) TABLE pg_aggregate;
ERROR:  cannot move system relation "pg_aggregate_fnoid_index"

-- Test d'une réindexation de BDD avec déplacement des index.
test=# REINDEX (TABLESPACE tbs) DATABASE test;
WARNING:  cannot move system relations, skipping all
REINDEX

# \df pg_terminate_backend

List of functions
-[ RECORD 1 ]-------+--------------------------------------
Schema              | pg_catalog
Name                | pg_terminate_backend
Result data type    | boolean
Argument data types | pid integer, timeout bigint DEFAULT 0
Type                | fun

# SELECT pg_terminate_backend(358855, 200);

WARNING:  backend with PID 358818 did not terminate within 200 milliseconds
-[ RECORD 1 ]--------+--
pg_terminate_backend | f

'postgresql://host1:123,host2:456/somedb?target_session_attrs=any'
'postgresql://host1:123,host2:456/somedb?target_session_attrs=read-write'
'host=serveur1,serveur2,serveur3 port=5432,5433,5434 target_session_attrs=read-only'

CREATE SUBSCRIPTION sub_stream 
  CONNECTION 'connection string' 
  PUBLICATION pub WITH (streaming = on);

ALTER SUBSCRIPTION sub_stream SET (streaming = on);

-- En version 13
ERROR:  logical replication target relation "public.t" 
        is missing some replicated columns

-- En version 14
ERROR:  logical replication target relation "public.t" 
        is missing replicated column: "champ"

-- on dispose d'une souscription avec 2 publications
\dRs

          Liste des souscriptions
 Nom | Propriétaire | Activé | Publication 
-----+--------------+--------+-------------
 sub | postgres     | t      | {pub,pub2}

-- en version 13 et inférieures, pour ajouter une nouvelle publication, il était
-- nécessaire de connaître les autres publications pour actualiser la souscription
ws14=# ALTER SUBSCRIPTION sub SET PUBLICATION pub,pub2,pub3;

-- en version 14, les clauses ADD et DROP simplifient ces modifications
ws14=# ALTER SUBSCRIPTION sub ADD PUBLICATION pub3;

ALTER SERVER srv1 OPTIONS (ADD truncatable 'false');
ALTER FOREIGN TABLE tbl1 OPTIONS (ADD truncatable 'false');

TRUNCATE tbl1;

ERROR:  foreign table "tbl1" does not allow truncates

EXPLAIN (verbose, costs off) SELECT * FROM t1 WHERE b % 100 = 0;

                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Append
   ->  Async Foreign Scan on public.async_p1 t1_1
         Output: t1_1.a, t1_1.b, t1_1.c
         Remote SQL: SELECT a, b, c FROM public.base_tbl1 WHERE (((b % 100) = 0))
   ->  Async Foreign Scan on public.async_p2 t1_2
         Output: t1_2.a, t1_2.b, t1_2.c
         Remote SQL: SELECT a, b, c FROM public.base_tbl2 WHERE (((b % 100) = 0))

CREATE SERVER distant3
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'machine3', dbname 'bi', port 5432, async_capable 'on') ;

ALTER SERVER distant1 OPTIONS (ADD async_capable 'on');

CREATE FOREIGN TABLE donnees1
PARTITION OF …
OPTIONS (async_capable 'on') ;

ALTER FOREIGN TABLE donnees1  OPTIONS (DROP async_capable);

\df string_to_table

                              List of functions
   Schema   |      Name       | Result data type | Argument data types | Type
------------+-----------------+------------------+---------------------+------
 pg_catalog | string_to_table | SETOF text       | text, text          | func
 pg_catalog | string_to_table | SETOF text       | text, text, text    | func
(2 rows)

\pset null '¤'
SELECT string_to_table('une chaine à ignorer', ' ', 'ignorer');

 string_to_table
-----------------
 une
 chaine
 à
 ¤
(4 rows)

ERROR:  CREATE OR REPLACE CONSTRAINT TRIGGER is not supported

CREATE PROCEDURE assign(IN a int, OUT b int)
  LANGUAGE plpgsql
AS $$
BEGIN
  -- assigner une valeur à b si a = 10
  IF a = 10 THEN
    b := a;
  END IF;
END;
$$;
-- CREATE PROCEDURE

DO $$
DECLARE _a int; _b int;
BEGIN
  _a := 10;
  CALL assign(_a, _b);
  RAISE NOTICE '_a: %, _b: %', _a, _b;

  _a := 100;
  CALL assign(_a, _b);
  RAISE NOTICE '_a: %, _b: %', _a, _b;
END
$$;
-- NOTICE:  _a: 10, _b: 10
-- NOTICE:  _a: 100, _b: <NULL>
-- DO

-- assigner les valeurs d'une portion d'un tableau
-- où "a" est de type int[]
a[2:3] := array[3,4];

-- assigner la valeur d'un champ de record personnalisé
-- où "a" est de type complex[]
CREATE TYPE complex AS (r float8, i float8);
a[1].r := 1;
a[1].i := 2;

-- assigner la valeur d'une clé hstore
-- où "h" est de type hstore
CREATE EXTENSION hstore;
h['a'] := 'b';

DO $$ DECLARE x int := 1/0; BEGIN END $$ ;
-- ERROR:  division by zero
-- CONTEXT:  SQL statement "SELECT 1/0"

DO $$ DECLARE x int := 1/0; BEGIN END $$ ;
-- ERROR:  division by zero
-- CONTEXT:  SQL expression "1/0"

SELECT id, product->'name' AS product, product->'price' AS price 
  FROM products WHERE product->>'brand' = 'AniOne';

SELECT id, product['name'] AS product, product['price'] AS price
  FROM products WHERE product['brand'] = '"AniOne"';

 id  |            product             | price 
-----+--------------------------------+-------
 100 | "Arbre à chat tonneau Aurelio" | 189
 101 | "Griffoir tonneau Tobi"        | 169

UPDATE products SET product['dimension']['L'] = '50' WHERE id = 100; 

SELECT x,
       lower(x) as "borne inf",
       upper(x) as "borne sup",
       x @> 2 as "contient 2",
       x @> 4 as "contient 4",
       x * '[1,2]'::int4range AS "intersection avec [1,2]"
  FROM (VALUES ('[1,4)'::int4range) ) AS F(x) \gx

-[ RECORD 1 ]-----------+------
x                       | [1,4)
borne inf               | 1
borne sup               | 4
contient 2              | t
contient 4              | f
intersection avec [1,2] | [1,3)

SELECT '{ [1,2), (2,3]}'::int4multirange \gx

-[ RECORD 1 ]--+--------
int4multirange | {[1,2),[3,4)}

SELECT '{[1,5], [2,6]}'::int4multirange \gx

-[ RECORD 1 ]--+--------
int4multirange | {[1,7)}

SELECT x,
       lower(x) as "borne inf",
       upper(x) as "borne sup",
       x @> 2 as "contient 2",
       x @> 4 as "contient 4",
       x * '{[1,2],[6,7]}'::int4multirange 
         AS "intersection avec {[1,2], [6,7]}"
  FROM (VALUES ('{[1,4), [5,8)}'::int4multirange) ) 
    AS F(x) \gx

-[ RECORD 1 ]--------------------+--------------
x                                | {[1,4),[5,8)}
borne inf                        | 1
borne sup                        | 8
contient 2                       | t
contient 4                       | f
intersection avec {[1,2], [6,7]} | {[1,3),[6,8)}

SELECT '[1,5]'::int4range - '[2,3)'::int4range AS RESULT;
-- ERROR:  result of range difference would not be contiguous

SELECT '{[1,5]}'::int4multirange - '{[2,3)}'::int4multirange AS result;

    result
---------------
 {[1,2),[3,6)}

CREATE TABLE planning (
  classe text,
  salle text,
  plage_horaire tsrange,
  matiere text
);

INSERT INTO planning(classe, salle, plage_horaire, matiere)
VALUES
  (1, 'Salle 1', '[2021-07-19  9:00, 2021-07-19 10:00)'::tsrange, 'math'),
  (1, NULL,      '[2021-07-19 10:00, 2021-07-19 10:15)'::tsrange, 'recreation'),
  (1, 'Salle 2', '[2021-07-19 10:15, 2021-07-19 12:15)'::tsrange, 'français'),
  (1, NULL,      '[2021-07-19 12:15, 2021-07-19 14:15)'::tsrange, 'repas / recreation'),
  (1, 'Salle 2', '[2021-07-19 14:15, 2021-07-19 15:15)'::tsrange, 'anglais'),
  (2, 'Salle 1', '[2021-07-19  8:00, 2021-07-19 10:00)'::tsrange, 'physique'),
  (2, NULL,      '[2021-07-19 10:00, 2021-07-19 10:15)'::tsrange, 'recreation'),
  (2, 'Salle 1', '[2021-07-19 10:15, 2021-07-19 12:45)'::tsrange, 'technologie'),
  (2, NULL,      '[2021-07-19 12:45, 2021-07-19 14:15)'::tsrange, 'repas / recreation'),
  (2, 'Salle 1', '[2021-07-19 14:15, 2021-07-19 16:15)'::tsrange, 'math'),
  (3, 'Salle 2', '[2021-07-19 14:15, 2021-07-19 15:15)'::tsrange, 'allemand')
;

SELECT classe, salle, range_agg(plage_horaire) AS plages_horaires
  FROM planning
 WHERE salle IS NOT NULL 
 GROUP BY classe, salle
 ORDER BY classe, salle;

 classe |  salle  |               plages_horaires
--------+---------+--------------------------------------------------
 1      | Salle 1 | {["2021-07-19 09:00:00","2021-07-19 10:00:00")}
 1      | Salle 2 | {["2021-07-19 10:15:00","2021-07-19 12:15:00"),
        |         |  ["2021-07-19 14:15:00","2021-07-19 15:15:00")}
 2      | Salle 1 | {["2021-07-19 08:00:00","2021-07-19 10:00:00"),
        |         |  ["2021-07-19 10:15:00","2021-07-19 12:45:00"),
        |         |  ["2021-07-19 14:15:00","2021-07-19 16:15:00")}
 3      | Salle 2 | {["2021-07-19 14:15:00","2021-07-19 15:15:00")}
(4 rows)

WITH planning_par_classe_et_salle (classe, salle, plages_horaires) AS (
  SELECT classe, salle, range_agg(plage_horaire) AS plages_horaires
    FROM planning
   WHERE salle IS NOT NULL
   GROUP BY classe, salle
)
SELECT salle, 
       range_intersect_agg(plages_horaires) as plages_horaires,
       array_agg(classe) as classes
  FROM planning_par_classe_et_salle
 GROUP BY salle HAVING count(*) > 1
 ORDER BY salle;

  salle  |                 plages_horaires                 | classes
---------+-------------------------------------------------+---------
 Salle 1 | {["2021-07-19 09:00:00","2021-07-19 10:00:00")} | {1,2}
 Salle 2 | {["2021-07-19 14:15:00","2021-07-19 15:15:00")} | {3,1}
(2 rows)

SELECT a.amname, of.opfname, t1.typname as lefttype, 
        t2.typname as righttyp, o.oprname, o.oprcode
  FROM pg_amop ao
 INNER JOIN pg_am a ON ao.amopmethod = a.oid
 INNER JOIN pg_opfamily of ON ao.amopfamily = of.oid
 INNER JOIN pg_type t1 ON ao.amoplefttype = t1.oid
 INNER JOIN pg_type t2 ON ao.amoplefttype = t2.oid
 INNER JOIN pg_operator o ON ao.amopopr = o.oid
 WHERE of.opfname LIKE '%multirange%';

amname |    opfname     |   lefttype    |   righttyp    |oprname|          oprcode
-------+----------------+---------------+---------------+-------+---------------------------------
gist   | multirange_ops | anymultirange | anymultirange | <<    |multirange_before_multirange
gist   | multirange_ops | anymultirange | anymultirange | <<    |multirange_before_range
gist   | multirange_ops | anymultirange | anymultirange | &<    |multirange_overleft_multirange
gist   | multirange_ops | anymultirange | anymultirange | &<    |multirange_overleft_range
gist   | multirange_ops | anymultirange | anymultirange | &&    |multirange_overlaps_multirange
gist   | multirange_ops | anymultirange | anymultirange | &&    |multirange_overlaps_range
gist   | multirange_ops | anymultirange | anymultirange | &>    |multirange_overright_multirange
gist   | multirange_ops | anymultirange | anymultirange | &>    |multirange_overright_range
gist   | multirange_ops | anymultirange | anymultirange | >>    |multirange_after_multirange
gist   | multirange_ops | anymultirange | anymultirange | >>    |multirange_after_range
gist   | multirange_ops | anymultirange | anymultirange | -|-   |multirange_adjacent_multirange
gist   | multirange_ops | anymultirange | anymultirange | -|-   |multirange_adjacent_range
gist   | multirange_ops | anymultirange | anymultirange | @>    |multirange_contains_multirange
gist   | multirange_ops | anymultirange | anymultirange | @>    |multirange_contains_range
gist   | multirange_ops | anymultirange | anymultirange | <@    |multirange_contained_by_multirange
gist   | multirange_ops | anymultirange | anymultirange | <@    |multirange_contained_by_range
gist   | multirange_ops | anymultirange | anymultirange | @>    |multirange_contains_elem
gist   | multirange_ops | anymultirange | anymultirange | =     |multirange_eq
btree  | multirange_ops | anymultirange | anymultirange | <     |multirange_lt
btree  | multirange_ops | anymultirange | anymultirange | <=    |multirange_le
btree  | multirange_ops | anymultirange | anymultirange | =     |multirange_eq
btree  | multirange_ops | anymultirange | anymultirange | >=    |multirange_ge
btree  | multirange_ops | anymultirange | anymultirange | >     |multirange_gt
hash   | multirange_ops | anymultirange | anymultirange | =     |multirange_eq

cat <<_EOF_ | psql
CREATE TABLE entreprise(nom text, departement int, ville text, creation date, montant int);
COPY entreprise FROM STDIN WITH DELIMITER ',' CSV;
entreprise1,44,Nantes,20210506,1000
entreprise2,44,Nantes,20200506,200
entreprise3,29,Brest,20200605,3000
entreprise4,29,Brest,20200406,4000
\.
_EOF_

SELECT row_number() OVER(), departement, ville, 
       extract(YEAR FROM  creation) as year, 
       avg(montant)::int as montant
  FROM entreprise 
 GROUP BY rollup(departement, ville),
          rollup(departement, year);

 row_number | departement |  ville  | year | montant 
------------+-------------+---------+------+---------
          1 |           ¤ | ¤       |    ¤ |    2050
          2 |          44 | Nantes  | 2021 |    1000
          3 |          44 | Nantes  | 2020 |     200
          4 |          29 | Brest   | 2020 |    3500
          5 |          29 | Brest   |    ¤ |    3500
          6 |          44 | Nantes  |    ¤ |     600
          7 |          29 | Brest   |    ¤ |    3500 << DOUBLON DE 5
          8 |          44 | Nantes  |    ¤ |     600 << DOUBLON DE 6
          9 |          44 | ¤       |    ¤ |     600
         10 |          29 | ¤       |    ¤ |    3500
         11 |          44 | ¤       |    ¤ |     600 << DOUBLON DE 9
         12 |          29 | ¤       |    ¤ |    3500 << DOUBLON DE 10
         13 |          44 | ¤       |    ¤ |     600 << DOUBLON DE 9
         14 |          29 | ¤       |    ¤ |    3500 << DOUBLON DE 10
         15 |          29 | ¤       | 2020 |    3500
         16 |          44 | ¤       | 2020 |     200
         17 |          44 | ¤       | 2021 |    1000
         18 |          29 | ¤       | 2020 |    3500 << DOUBLON DE 15
         19 |          44 | ¤       | 2020 |     200 << DOUBLON DE 16 
         20 |          44 | ¤       | 2021 |    1000 << DOUBLON DE 17
(20 rows)

SELECT departement, ville, extract(YEAR FROM  creation) as year, 
       avg(montant)::int as montant
  FROM entreprise 
 GROUP BY DISTINCT rollup(departement, ville),
                   rollup(departement, year);

 departement |  ville  | year | montant
-------------+---------+------+---------
           ¤ | ¤       |    ¤ |    2050
          44 |  Nantes | 2021 |    1000
          44 |  Nantes | 2020 |     200
          29 |  Brest  | 2020 |    3500
          29 |  Brest  |    ¤ |    3500
          44 |  Nantes |    ¤ |     600
          44 | ¤       |    ¤ |     600
          29 | ¤       |    ¤ |    3500
          29 | ¤       | 2020 |    3500
          44 | ¤       | 2020 |     200
          44 | ¤       | 2021 |    1000
(11 rows)

-- RETURN
CREATE FUNCTION add(a integer, b integer) RETURNS integer
  LANGUAGE SQL
  RETURN a + b;

-- BEGIN ATOMIC .. END
CREATE PROCEDURE insert_data(a integer, b integer)
  LANGUAGE SQL
BEGIN ATOMIC
  INSERT INTO tbl VALUES (a);
  INSERT INTO tbl VALUES (b);
END;

CREATE OR REPLACE FUNCTION display_type(a anyelement) RETURNS text
LANGUAGE SQL
RETURN 'The input type is ' || pg_typeof(a);

ERROR:  SQL function with unquoted function body cannot have polymorphic arguments

CREATE OR REPLACE FUNCTION display_type(a anyelement) RETURNS text
LANGuAGE SQL
AS $$ SELECT 'The input type is ' || pg_typeof(a); $$
-- CREATE FUNCTION

CREATE TABLE tbl1(i int);
CREATE TABLE tbl2(i int);

-- Procédure avec la nouvelle syntaxe
CREATE OR REPLACE PROCEDURE insert_data_new(a integer, b integer)
  LANGUAGE SQL
BEGIN ATOMIC
  INSERT INTO tbl1 VALUES (a);
  INSERT INTO tbl2 VALUES (a);
END;

-- Procédure avec l'ancienne syntaxe
CREATE OR REPLACE PROCEDURE insert_data_old(a integer, b integer)
  LANGUAGE SQL
AS $$
  INSERT INTO tbl1 VALUES (a);
  INSERT INTO tbl2 VALUES (a);
$$;

DROP TABLE tbl1, tbl2 CASCADE; 
-- NOTICE:  drop cascades to function insert_data_new(integer,integer)
-- DROP TABLE

ERROR:  relation "tbl1" does not exist
LINE 4:   INSERT INTO tbl1 VALUES (a);

CREATE TABLE tree(id int, parent_id int, name text);
ALTER TABLE tree ADD PRIMARY KEY (id);
INSERT INTO tree(id, parent_id, name)
VALUES (1, NULL, 'Albert'),
       (2, 1, 'Bob'),
       (3, 1, 'Barbara'),
       (4, 1, 'Britney'),
       (5, 3, 'Clara'),
       (6, 3, 'Clement'),
       (7, 2, 'Craig'),
       (8, 5, 'Debby'),
       (9, 5, 'Dave'),
       (10, 9, 'Edwin');

--- ajout d'un champ profondeur (depth)
WITH RECURSIVE mtree(id, name, depth) AS (
   -- initialisation de la profondeur à 0 pour le point de départ
   SELECT id, name, 0
     FROM tree
    WHERE id = 1

   UNION ALL

   -- Incrémenter la profondeur de 1
   SELECT t.id, t.name, m.depth + 1
     FROM tree AS t
          INNER JOIN mtree AS m ON t.parent_id = m.id
)
SELECT * FROM mtree ORDER BY depth DESC LIMIT 1;

 id | name  | depth
----+-------+-------
 10 | Edwin |     4
(1 row)

with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( query )
  [ SEARCH BREADTH FIRST BY column_name [, ...] SET search_seq_col_name ];

query: ( select | values | insert | update | delete )

WITH RECURSIVE mtree(id, name) AS (
   SELECT id, name
     FROM tree
    WHERE id = 1

   UNION ALL

   SELECT t.id, t.name
     FROM tree AS t
          INNER JOIN mtree AS m ON t.parent_id = m.id
) SEARCH BREADTH FIRST BY name SET morder
SELECT * FROM mtree ORDER BY morder DESC;

 id |  name   |   morder
----+---------+-------------
 10 | Edwin   | (4,Edwin)
  8 | Debby   | (3,Debby)
  9 | Dave    | (3,Dave)
  7 | Craig   | (2,Craig)
  6 | Clement | (2,Clement)
  5 | Clara   | (2,Clara)
  4 | Britney | (1,Britney)
  2 | Bob     | (1,Bob)
  3 | Barbara | (1,Barbara)
  1 | Albert  | (0,Albert)
(10 rows)

UPDATE tree SET parent_id = 10 WHERE id = 1;
-- UPDATE 1

-- ajout de deux champs:
-- * un booleen qui permet de détecter les cycles (is_cycle)
-- * un tableau qui contient le chemin parcouru (path)
WITH RECURSIVE mtree(id, name, depth, is_cycle, path) AS (
   -- initialisations
   SELECT id, name, 0,
          false,          -- initialement, on ne boucle pas
          ARRAY[ROW(id)]  -- le premier élément du chemin
     FROM tree
    WHERE id = 1

   UNION ALL

   SELECT t.id, t.name, m.depth + 1,
          ROW(t.id) = ANY(m.path), -- déja traitré ?
          m.path || ROW(t.id)      -- ajouter le tuple au chemin
   FROM tree AS t
          INNER JOIN mtree AS m ON t.parent_id = m.id

   -- stopper l'itération si on détecte un cycle
   WHERE NOT m.is_cycle
)
SELECT * FROM mtree ORDER BY depth DESC LIMIT 1;

 id |  name  | depth | is_cycle |            path
----+--------+-------+----------+----------------------------
  1 | Albert |     5 | t        | {(1),(3),(5),(9),(10),(1)}
(1 row)

with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( query )
  [ CYCLE column_name [, ...] SET cycle_mark_col_name
                              [ TO cycle_mark_value DEFAULT cycle_mark_default ]
                              USING cycle_path_col_name ]

query: ( select | values | insert | update | delete )

WITH RECURSIVE mtree(id, name) AS (
   SELECT id, name
     FROM tree
    WHERE id = 1
   UNION ALL
   SELECT t.id, t.name
     FROM tree AS t
          INNER JOIN mtree AS m ON t.parent_id = m.id
) SEARCH BREADTH FIRST BY name SET morder
  CYCLE id SET is_cycle USING path
SELECT * FROM mtree ORDER BY morder DESC LIMIT 1;

 id |  name  |   morder   | is_cycle |            path
----+--------+------------+----------+----------------------------
  1 | Albert | (5,Albert) | t        | {(1),(3),(5),(9),(10),(1)}
(1 row)

with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( query )
  [ SEARCH DEPTH FIRST BY column_name [, ...] SET search_seq_col_name ];

query: ( select | values | insert | update | delete )

WITH RECURSIVE mtree(id, name) AS (
   SELECT id, name
     FROM tree
    WHERE id = 1

   UNION ALL

   SELECT t.id, t.name
     FROM tree AS t
          INNER JOIN mtree AS m ON t.parent_id = m.id
) SEARCH DEPTH FIRST BY name SET morder
  CYCLE id SET is_cycle USING path
SELECT * FROM mtree WHERE not is_cycle ORDER BY morder DESC;

 id |  name   |                   morder                    | is_cycle |          path
----+---------+---------------------------------------------+----------+------------------------
  4 | Britney | {(Albert),(Britney)}                        | f        | {(1),(4)}
  7 | Craig   | {(Albert),(Bob),(Craig)}                    | f        | {(1),(2),(7)}
  2 | Bob     | {(Albert),(Bob)}                            | f        | {(1),(2)}
  6 | Clement | {(Albert),(Barbara),(Clement)}              | f        | {(1),(3),(6)}
  8 | Debby   | {(Albert),(Barbara),(Clara),(Debby)}        | f        | {(1),(3),(5),(8)}
 10 | Edwin   | {(Albert),(Barbara),(Clara),(Dave),(Edwin)} | f        | {(1),(3),(5),(9),(10)}
  9 | Dave    | {(Albert),(Barbara),(Clara),(Dave)}         | f        | {(1),(3),(5),(9)}
  5 | Clara   | {(Albert),(Barbara),(Clara)}                | f        | {(1),(3),(5)}
  3 | Barbara | {(Albert),(Barbara)}                        | f        | {(1),(3)}
  1 | Albert  | {(Albert)}                                  | f        | {(1)}
(10 rows)

WITH RECURSIVE mtree(id, name) AS (
   SELECT id, name
     FROM tree
    WHERE id = 1
   UNION ALL
   SELECT t.id, t.name
     FROM tree AS t
          INNER JOIN mtree AS m ON t.parent_id = m.id
) SEARCH BREADTH FIRST BY name SET morder
  CYCLE id SET is_cycle USING path
SELECT id, name, (morder).* FROM mtree ORDER BY morder DESC LIMIT 1;

 WITH RECURSIVE mtree(id, name) AS (
   SELECT id, name
     FROM tree
    WHERE id = 1
   UNION ALL
   SELECT t.id, t.name
     FROM tree AS t
          INNER JOIN mtree AS m ON t.parent_id = m.id
) SEARCH BREADTH FIRST BY name SET morder
  CYCLE id SET is_cycle USING path
SELECT id, name, row_to_json(morder) -> '*DEPTH*' AS depth
  FROM mtree ORDER BY morder DESC LIMIT 1;

 id |  name  | depth
----+--------+-------
  1 | Albert | 5
(1 row)

\df date_bin

List of functions
-[ RECORD 1 ]-------+-------------------------------------------------------------------
Schema              | pg_catalog
Name                | date_bin
Result data type    | timestamp with time zone
Argument data types | interval, timestamp with time zone, timestamp with time zone
Type                | func
-[ RECORD 2 ]-------+-------------------------------------------------------------------
Schema              | pg_catalog
Name                | date_bin
Result data type    | timestamp without time zone
Argument data types | interval, timestamp without time zone, timestamp without time zone
Type                | func

-- Génération des données
CREATE TABLE sonde(t timestamp with time zone, id_sonde int, mesure int);
INSERT INTO sonde(t, id_sonde, mesure)
SELECT '2021-06-01 00:00:00'::timestamp with time zone + INTERVAL '1s' * x,
       1,
       sin(x*3.14/86401)*30
  FROM generate_series(0, 60*60*24) AS F(x);

-- création de buckets de 1h30 commençant à minuit le premier juin
SELECT date_bin('1 hour 30 minutes', t, '2021-06-01 00:00:00'::timestamp with time zone),
       id_sonde, avg(mesure)
  FROM sonde GROUP BY 1, 2 ORDER BY 1 ASC;

        date_bin        | id_sonde |          avg
------------------------+----------+------------------------
 2021-06-01 00:00:00+02 |        1 |     2.9318518518518519
 2021-06-01 01:30:00+02 |        1 |     8.6712962962962963
 2021-06-01 03:00:00+02 |        1 |    14.1218518518518519
 2021-06-01 04:30:00+02 |        1 |    19.0009259259259259
 2021-06-01 06:00:00+02 |        1 |    23.1514814814814815
 2021-06-01 07:30:00+02 |        1 |    26.3951851851851852
 2021-06-01 09:00:00+02 |        1 |    28.6138888888888889
 2021-06-01 10:30:00+02 |        1 |    29.9274074074074074
 2021-06-01 12:00:00+02 |        1 |    29.9359259259259259
 2021-06-01 13:30:00+02 |        1 |    28.6224074074074074
 2021-06-01 15:00:00+02 |        1 |    26.4207407407407407
 2021-06-01 16:30:00+02 |        1 |    23.1851851851851852
 2021-06-01 18:00:00+02 |        1 |    19.0346296296296296
 2021-06-01 19:30:00+02 |        1 |    14.1690740740740741
 2021-06-01 21:00:00+02 |        1 |     8.7175925925925926
 2021-06-01 22:30:00+02 |        1 |     2.9829629629629630
 2021-06-02 00:00:00+02 |        1 | 0.00000000000000000000
(17 rows)

SELECT date_bin('1 hour 30 minutes', t, '2021-06-01 00:11:00'::timestamp with time zone),
       id_sonde,
       avg(mesure)
FROM sonde GROUP BY 1, 2 ORDER BY 1 ASC;

        date_bin        | id_sonde |          avg
------------------------+----------+------------------------
 2021-05-31 22:41:00+02 |        1 | 0.30454545454545454545
 2021-06-01 00:11:00+02 |        1 |     3.6372222222222222
 2021-06-01 01:41:00+02 |        1 |     9.3907407407407407
 2021-06-01 03:11:00+02 |        1 |    14.7375925925925926
 2021-06-01 04:41:00+02 |        1 |    19.5405555555555556
 2021-06-01 06:11:00+02 |        1 |    23.5896296296296296
 2021-06-01 07:41:00+02 |        1 |    26.7618518518518519
 2021-06-01 09:11:00+02 |        1 |    28.7857407407407407
 2021-06-01 10:41:00+02 |        1 |    30.0000000000000000
 2021-06-01 12:11:00+02 |        1 |    29.8137037037037037
 2021-06-01 13:41:00+02 |        1 |    28.4772222222222222
 2021-06-01 15:11:00+02 |        1 |    26.0770370370370370
 2021-06-01 16:41:00+02 |        1 |    22.6962962962962963
 2021-06-01 18:11:00+02 |        1 |    18.4644444444444444
 2021-06-01 19:41:00+02 |        1 |    13.5207407407407407
 2021-06-01 21:11:00+02 |        1 |     8.0494444444444444
 2021-06-01 22:41:00+02 |        1 |     2.6230753005695001
(17 rows)

SELECT date_bin('1 year', '2021-06-01 10:05:10', '2021-06-01');
-- ERROR:  timestamps cannot be binned into intervals containing months or years

SELECT date_bin('1 month', '2021-06-01 10:05:10', '2021-06-01');
-- ERROR:  timestamps cannot be binned into intervals containing months or years

SELECT date_bin('12 weeks', '2021-06-01 10:05:10', '2021-06-01');

        date_bin
------------------------
 2021-06-01 00:00:00+02
(1 row)

SELECT date_bin('365 days', '2021-06-01 10:05:10', '2021-06-01');

        date_bin
------------------------
 2021-06-01 00:00:00+02
(1 row)

SELECT date_bin('1 hour', '2021-06-01 10:05:10'::timestamp, '2021-06-01'),
       date_trunc('hour', '2021-06-01 10:05:10'::timestamp);

      date_bin       |     date_trunc
---------------------+---------------------
 2021-06-01 10:00:00 | 2021-06-01 10:00:00
(1 row)

SELECT date_bin('1 minute', '2021-06-01 10:05:10'::timestamp, '2021-06-01'),
       date_trunc('minute', '2021-06-01 10:05:10'::timestamp);

      date_bin       |     date_trunc
---------------------+---------------------
 2021-06-01 10:05:00 | 2021-06-01 10:05:00
(1 row)

CREATE TABLE region (
  region_id int,
  region_name text
);

CREATE TABLE ville (
  ville_id int,
  region_id int,
  ville_name text
);

SELECT c.*
  FROM ville a INNER JOIN region b USING (region_id) AS c \gdesc

  Column   |  Type
-----------+---------
 region_id | integer

CREATE TABLE test_copy (i int);
INSERT INTO test_copy SELECT generate_series (1,10000000);
COPY test_copy TO '/tmp/test_copy';

SELECT * FROM pg_stat_progress_copy \gx

-[ RECORD 1 ]----+---------
pid              | 39148
datid            | 16384
datname          | test
relid            | 36500
command          | COPY TO
type             | FILE
bytes_processed  | 43454464
bytes_total      | 0
tuples_processed | 5570696
tuples_excluded  | 0

COPY test_copy FROM '/tmp/test_copy' WHERE i > 1000;

SELECT * FROM pg_stat_progress_copy \gx

-[ RECORD 1 ]----+----------
pid              | 39148
datid            | 16384
datname          | test
relid            | 36500
command          | COPY FROM
type             | FILE
bytes_processed  | 17563648
bytes_total      | 78888897
tuples_processed | 2329752
tuples_excluded  | 1000

\copy test_copy FROM '/tmp/test_copy' WHERE i > 1000;

SELECT * FROM pg_stat_progress_copy \gx

-[ RECORD 1 ]----+----------
pid              | 39148
datid            | 16384
datname          | test
relid            | 36500
command          | COPY FROM
type             | PIPE
bytes_processed  | 17150600
bytes_total      | 0
tuples_processed | 2281713
tuples_excluded  | 1000

SELECT pid, p.datname, relid::regclass, command, type, a.application_name,
bytes_processed, bytes_total, tuples_processed, tuples_excluded,
a.query, a.query_start
FROM pg_stat_progress_copy p INNER JOIN pg_stat_activity a USING(pid) \gx

-[ RECORD 1 ]----+-------------------------------------------------------
pid              | 223301
datname          | scratch
relid            | textes
command          | COPY FROM
type             | PIPE
application_name | pg_restore
bytes_processed  | 111194112
bytes_total      | 0
tuples_processed | 839619
tuples_excluded  | 0
query            | COPY public.textes (livre, ligne, contenu) FROM stdin;+
                 | 
query_start      | 2021-11-23 11:11:15.685557+01

-[ RECORD 1 ]----+--------------------------------------------------------------
pid              | 223652
datname          | magasin
relid            | 256305
command          | COPY TO
type             | PIPE
application_name | pg_dump
bytes_processed  | 22817772
bytes_total      | 0
tuples_processed | 402261
tuples_excluded  | 0
query            | COPY magasin.lots (numero_lot, transporteur_id, numero_suivi,
                   date_depot, date_expedition, date_reception) TO stdout;
query_start      | 2021-11-23 11:14:09.755587+01