Postgresql

Domenica, 28 luglio 2019

Postgresql 12 and CTEs

Until now the CTEs have always been materialized by the DB; if I had executed
something like:

explain analyze WITH x as (
SELECT relkind, COUNT(*) FROM pg_class GROUP BY relkind
)
SELECT * FROM x WHERE relkind = 'r';

Continua a leggere " Postgresql 12 and CTEs"

Scritto da scotty in Postgresql at 17:46 | Commenti (32) | Trackbacks (0)

Postgresql 12 e le nuove CTE

Stavo guardando in rete le varie novità introdotte su Postgresql 12, e mi è saltata all'occhio la nuova gestione delle CTE: (https://www.postgresql.org/docs/12/queries-with.html)

La novità introdotta nella versione 12 è questa: la clausola materialized / not materialized.

Continua a leggere "Postgresql 12 e le nuove CTE"

Scritto da scotty in Postgresql at 17:41 | Commenti (0) | Trackbacks (0)

Martedì, 23 aprile 2019

Event Trigger : a way to keep track of table creation time on PostgreSQL

On my github page I published an event trigger to keep track of table creation time on PostgreSQL

https://github.com/sscotty71/alopq/blob/master/ext_queries/save_time_creation_table.sql

Have fun

Scritto da scotty in Postgresql at 21:30 | Commenti (35)

Lunedì, 15 aprile 2019

Some useful postgresql queries

On my github page I published some useful queries, have fun

https://github.com/sscotty71/alopq

Scritto da scotty in Postgresql at 21:17

Lunedì, 27 agosto 2018

PostgreSQL WITH RECURSIVE: count children rows

Given a table as :

CREATE TABLE public.groups ( id INTEGER NOT NULL, parent_id INTEGER, title VARCHAR, CONSTRAINT groups_pkey PRIMARY KEY(id) ) WITH (oids = false);
INSERT INTO public.groups ("id", "parent_id", "title")
VALUES
(1, NULL, 'value1'),
(2, 1, 'value2'),
(3, 1, 'value3'),
(4, 2, 'value4'),
(5, 2, 'value5');

id |parent_id |title |
---|-------------|---------|
1 | |value1 |
2 |1 |value2 |
3 |1 |value3 |
4 |2 |value4 |
5 |2 |value5 |

Let's see how to calculate the number of children and the depth of the level with a single query and using the recursive WITH
Below you can find the query

WITH RECURSIVE tree AS ( SELECT title, id, parent_id, NULL::varchar AS parent_name, array[id] AS path, array_length(array[id], 1) AS depth FROM groups WHERE 1=1 AND groups.parent_id IS null UNION ALL SELECT g1.title, g1.id, g1.parent_id, tree.title AS parent_name, tree.path || g1.id AS path, array_length(tree.path || g1.id, 1) AS depth FROM tree JOIN groups g1 ON g1.parent_id = tree.id ) select tree_1.*,(select count(*) -1 as cnt from tree where path @> array[tree_1.id]) as cnt_children from tree tree_1

Enjoy