begin;
set local statement_timeout to '100ms';
alter table history add constraint partition_key check (clock < :N) not valid;
commit;
alter table history validate constraint partition_key;
begin;
set local statement_timeout to '100ms';
alter table history rename to history_old_data;
create table history (...) partition by range (clock);
alter table history attach partition history_old_data for values from (minvalue) to (:N);
create table history_part_... partition of history for values from (:N) to (...);
commit;create extension pg_trgm;
create index concurrently db_url_trgm_idx on db1 using gin ("Url" gin_trgm_ops);select EXTRACT(YEAR FROM time) AS year,
sum(cost) filter(where EXTRACT('month' FROM time) = 1) as jan,
sum(cost) filter(where EXTRACT('month' FROM time) = 2) as feb,
...
sum(cost) filter(where EXTRACT('month' FROM time) = 12) as dec
from tablename
where ...
group by 1
order by 1melkij=> create or replace function myfunc()
returns setof record
as $$
declare
res record;
begin
execute 'select 1' into res;
return next res;
execute 'select 2' into res;
return next res;
return;
end;
$$
language plpgsql;
CREATE FUNCTION
melkij=> select * from myfunc() as s(i int);
i
---
1
2
(2 строки)
melkij=> select myfunc();
ERROR: materialize mode required, but it is not allowed in this context
КОНТЕКСТ: PL/pgSQL function myfunc() line 6 at RETURN NEXTmelkij=> \df to_tsvector
Список функций
Схема | Имя | Тип данных результата | Типы данных аргументов | Тип
------------+-------------+-----------------------+------------------------+-------
pg_catalog | to_tsvector | tsvector | json | функ.
pg_catalog | to_tsvector | tsvector | jsonb | функ.
pg_catalog | to_tsvector | tsvector | regconfig, json | функ.
pg_catalog | to_tsvector | tsvector | regconfig, jsonb | функ.
pg_catalog | to_tsvector | tsvector | regconfig, text | функ.
pg_catalog | to_tsvector | tsvector | text | функ.
(6 строк)melkij=> select 'hello'::xml;
xml
-------
hello
(1 строка)
melkij=> select '<foo>hello'::xml;
ERROR: invalid XML content
СТРОКА 1: select '<foo>hello'::xml;
^
ПОДРОБНОСТИ: line 1: Premature end of data in tag foo line 1
<foo>hello
^
line 1: chunk is not well balanced
<foo>hellocreate table part (i int) partition by hash (i);
create table part_0 partition of part FOR VALUES WITH (MODULUS 2, REMAINDER 0);
create table part_1 partition of part FOR VALUES WITH (MODULUS 2, REMAINDER 1);
insert into part select generate_series(1,10);
select * from only part_0;
select * from only part_1;архивные логи ДО-момента backup'а уже не нужны
select (select j->>'name' from jsonb_array_elements(jsonb_field) as j where j->>'custom_twine' = 'custom_38') from tablenameinsert into desc (phone, name, nickname)
select phone, name, nickname from src
on conflict (phone) do update name = excluded.name