Александр Шелеметьев

select
*
from table 
order by id
limit 6 -- выбрать 6 строк
offset 4 -- пропустить первые 4 строки

with book_stats_sum as (
select book_id, sum(ctr) ctr
from book_stats
where book_stats.lang = 'ru' and book_stats.category = 'horror'
group by book_id
) -- подстраховаться, чтобы выбиралось только по одной записи на каждый book_id
select books.book_id, coalesce(book_stats_sum.ctr, books.ctr) ctr
from books
left join book_stats_sum on book_stats_sum.book_id = books.book_id
order by coalesce(book_stats_sum.ctr, books.ctr) desc
limit 200

with rec as (
select "recordId" as rec_id 
from catalog_49_links
 where "catalogId" = 36 and "catalogRecordId" = 352 
order by "createdAt" desc 
limit 1
)
select "recordId" as new_rec_id from catalog_49_links where "recordId" in (select rec_id from rec) and "catalogId" = 56 or ("catalogId" = 36 and "catalogRecordId" = 352) order by "createdAt" desc limit 1;

with rec as (
select "recordId" as rec_id 
from catalog_49_links
 where "catalogId" = 36 and "catalogRecordId" = 352 
order by "createdAt" desc 
limit 1
)
select "recordId" as new_rec_id 
from catalog_49_links
join rec on rec.rec_id = catalog_49_links."recordId"
where "catalogId" = 56 or ("catalogId" = 36 and "catalogRecordId" = 352) 
order by "createdAt" desc limit 1;

CREATE INDEX addr_lower_idx
  ON public.addr
  USING btree
  (lower(factaddr) COLLATE pg_catalog."default" text_pattern_ops);

explain analyze
select * from addr 
where lower(factaddr) like lower('Моск%');

Bitmap Heap Scan on addr  (cost=22.40..490.53 rows=194 width=150) (actual time=0.110..0.110 rows=0 loops=1)
  Filter: (lower(factaddr) ~~ 'моск%'::text)
  ->  Bitmap Index Scan on addr_lower_idx  (cost=0.00..22.36 rows=194 width=0) (actual time=0.108..0.108 rows=0 loops=1)
        Index Cond: ((lower(factaddr) ~>=~ 'моск'::text) AND (lower(factaddr) ~<~ 'мосл'::text))
Planning time: 0.174 ms
Execution time: 0.173 ms

select 
current_date - interval '1 month' * s
from 
generate_series(0, 5) s

select 
'SELECT '
|| '"' || string_agg(d, '", "') || '"'
from (
	select 
		to_char((now()::date - (extract(dow from now()::date))::integer + 1 + i::integer),'dd.mm.yyyy') d 
	from 
		generate_series(0, 6) i
) q0

select distinct on (1) -- выбрать только уникальные значения по первому полю (action) из выборки
action, -- вывести action, usr, datetime
usr,
datetime
from table t1
order by 1, datetime desc -- для выборки отсортировать по первому полю (action), затем по дате в обратном порядке

update address a
set a."primary" = q1.is_primary
from (
	select
	id
	,(row_number() over (partition by organizationfk order by nullif("primary", false))) = 1 is_primary
	from address 
) q1
where q1.id=a.id