Get first 10 row and total rows from table

SELECT stuff,
       count(*) OVER() AS total_count
FROM table
WHERE ....
ORDER BY ... OFFSET 0 LIMIT 10
17 мая 2024, 22:34

Fix postgres sequence after insert

Run the output of this sql command
SELECT 
    'SELECT SETVAL(' ||
       quote_literal(quote_ident(sequence_namespace.nspname) || '.' || quote_ident(class_sequence.relname)) ||
       ', COALESCE(MAX(' ||quote_ident(pg_attribute.attname)|| '), 1) ) FROM ' ||
       quote_ident(table_namespace.nspname)|| '.'||quote_ident(class_table.relname)|| ';'
FROM pg_depend 
    INNER JOIN pg_class AS class_sequence
        ON class_sequence.oid = pg_depend.objid 
            AND class_sequence.relkind = 'S'
    INNER JOIN pg_class AS class_table
        ON class_table.oid = pg_depend.refobjid
    INNER JOIN pg_attribute 
        ON pg_attribute.attrelid = class_table.oid
            AND pg_depend.refobjsubid = pg_attribute.attnum
    INNER JOIN pg_namespace as table_namespace
        ON table_namespace.oid = class_table.relnamespace
    INNER JOIN pg_namespace AS sequence_namespace
        ON sequence_namespace.oid = class_sequence.relnamespace
ORDER BY sequence_namespace.nspname, class_sequence.relname;
Alternatives List of all sequence
SELECT c.relname FROM pg_class c WHERE c.relkind = 'S' order BY c.relname;
Fix sequence (if it's named id)
SELECT 
concat('SELECT SETVAL(''public.', c.relname,''', COALESCE(MAX(id), 1) ) FROM public.', replace(c.relname, '_id_seq', ''), ';') FROM pg_class c WHERE c.relkind = 'S' order BY c.relname;
4 марта 2024, 17:26

Copy schema table in postgres

CREATE TABLE new_table_name (LIKE old_table_name INCLUDING ALL);
2 ноября 2021, 11:33

Get estimated count rows

explain (format json) select * from tbl
28 апреля 2021, 17:38

Generate date ranges in postgresql

SELECT d::date
FROM generate_series(
  timestamp without time zone '2021-01-01',
  timestamp without time zone '2021-02-01',
  '1 day'
) AS gs(d);

19 января 2021, 12:33

Knex join with subquery

knex(
  knex('A').where('A.id',1).as('t1')
).leftJoin(
  knex('B').where('B.id', 2).as('t2'), 
  't1.c', 
  't2.d'
)
15 января 2021, 10:33

Postgresql reset sequence

SELECT setval('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1), false)

or 

SELECT
	setval(pg_get_serial_sequence('tbl', 'id'), COALESCE(max(id) + 1, 1), FALSE)
FROM
	tbl;
19 декабря 2020, 20:04

Show enum types in postgress

select enum_range(null::my_type)
16 сентября 2020, 18:20

Update multiple rows with one query in postgresql

CREATE TABLE table1 ( 
	"id" Integer NOT NULL,
	"a" Text NOT NULL,
	"b" Text NOT NULL );
insert into table1 values  
(1, 'a', 'b'),
(2, 'a', 'b')
update table1 as t set 
  a = data.a,
  b = data.b
from (values
  (1, 'a1', 'b1'),
  (2, 'a2', 'b2')
) as data(id, a, b)
where t.id = data.id
29 августа 2020, 21:41

Difference between ROW_NUBER(), RANK(), DENSE_RANK() in postgresql

WITH T(StyleID, ID)
     AS (SELECT 1,1 UNION ALL
         SELECT 1,1 UNION ALL
         SELECT 1,1 UNION ALL
         SELECT 1,2)
SELECT *,
       RANK() OVER(PARTITION BY StyleID ORDER BY ID)       AS 'RANK',
       ROW_NUMBER() OVER(PARTITION BY StyleID ORDER BY ID) AS 'ROW_NUMBER',
       DENSE_RANK() OVER(PARTITION BY StyleID ORDER BY ID) AS 'DENSE_RANK'
FROM   T  
Returns

StyleID     ID       RANK      ROW_NUMBER      DENSE_RANK
----------- -------- --------- --------------- ----------
1           1        1         1               1
1           1        1         2               1
1           1        1         3               1
1           2        4         4               2
6 августа 2020, 20:48