UPDATE orders_new
SET client_code=access.title
FROM orders_new O
Do not repeat the target table as a from_item unless you intend a self-join (in which case it must appear with an alias in the from_item).
Day full capitalized day name (blank-padded to 9 chars)
'monday' = 'monday '
select jsonb_agg(r) || jsonb_build_object('city', 'SanAndreas') from tablename r;
select current_date,
current_date - interval '6 day',
date_trunc('month', now()),
now();
melkij=> \da pg_catalog.sum
Список агрегатных функций
Схема | Имя | Тип данных результата | Типы данных аргументов | Описание
------------+-----+-----------------------+------------------------+--------------------------------------------------
pg_catalog | sum | numeric | bigint | sum as numeric across all bigint input values
pg_catalog | sum | double precision | double precision | sum as float8 across all float8 input values
pg_catalog | sum | bigint | integer | sum as bigint across all integer input values
pg_catalog | sum | interval | interval | sum as interval across all interval input values
pg_catalog | sum | money | money | sum as money across all money input values
pg_catalog | sum | numeric | numeric | sum as numeric across all numeric input values
pg_catalog | sum | real | real | sum as float4 across all float4 input values
pg_catalog | sum | bigint | smallint | sum as bigint across all smallint input values
5 September 2017: 2ndQuadrant is proud to announce the release of Barman version 2.3, a Backup and Recovery Manager for PostgreSQL.
This minor release introduces support for PostgreSQL 10.
Order by в запросе вычисляет rating для всей таблицы, а потом делает сортировку.
UPDATE tablename
SET
name = ?,
domain = ?,
in_chats = usersdata_mirror.in_chats || ARRAY[?]::int[]
WHERE
not(usersdata_mirror.in_chats @> ARRAY[?]::int[]) and user_id = ?
in_chats = case when ... then ... else usersdata_mirror.in_chats end
без ее блокировки
select ... from (...) subquery order by ...
select link_id from tablename
group by link_id
having count(*) = sum(flag = 1)
select id from links
where exists(select null from tablename where flag = 1 and link_id = links.id) -- возможно это условие вам по задаче не нужно
and not exists(select null from tablename where flag != 1 and link_id = links.id)
select ...
from tablename t
where t.flag = 1
and not exists(select null from tablename sq where sq.link_id = t.link_id and sq.flag != 1)
select ... from tablename t where t.link_id in (
select q.link_id from tablename q
group by q.link_id
having count(*) = sum(flag = 1)
)
извлечь суммарную информацию: сколько позиций фраз выросло, сколько позиций просело, сколько позиций осталось неизменными
select count(*) filter(where r1.position < r2.position) as pos_down,
count(*) filter(where r1.position = r2.position) as pos_same,
count(*) filter(where r1.position > r2.position) as pos_up from ...
tablename r1 full join tablename r2 on r1.phrase_id = r2.phrase_id where r1.... and r2....
where r2.task_id = 123 and r1.task_id in (select id from tasks where id < 123 order by id desc limit 1)