select day, data
from generate_series('2017-12-10', '2017-12-12', interval '1 day') as day,
lateral (
select data from tablename
where "timestamp" between day and day + interval '1 day'
order by "timestamp" desc limit 1
) ljd;with recursive t as (
(select "timestamp"::date as day, data from tablename order by "timestamp" desc limit 1)
union all
select bpt.* from t, lateral (
select "timestamp"::date as day, data from tablename where "timestamp" < t.day order by "timestamp" desc limit 1
) as bpt
)
select * from t;select i from unnest(array[1,2,3,4]) as i
where not exists (select 1 from tablename where id = i);with
data (new_user_id, new_name) as (values(%(user_id)s, %(name)s)),
wr as (
INSERT INTO users(user_id, name)
select new_user_id, new_name from data
ON CONFLICT (user_id) DO NOTHING returning user_id
), upd as (
update users set name = new_name from data where users.user_id = data.new_user_id and data.new_user_id not in (
select user_id from wr
)
)
select count(*) from wr;WITH new_tbl_main( id ) AS (
INSERT INTO tbl_main ( institution_id ) VALUES ( 38)
RETURNING id
), chi2 as (
INSERT INTO tbl_child1 ( tbl_main_id )
SELECT new_tbl_main.id FROM new_tbl_main
)
INSERT INTO tbl_child1 ( tbl_main_id )
SELECT new_tbl_main.id FROM new_tbl_main
RETURNING tbl_main_idERROR: cannot use RETURN QUERY in a non-SETOF function
CREATE OR REPLACE FUNCTION checkPermission(text, text) RETURNS BOOLEAN AS
$$
BEGIN
RETURN $1 && $2;
END;
$$ LANGUAGE plpgsqlCREATE OR REPLACE FUNCTION checkPermission(text, text) RETURNS BOOLEAN AS
$$
SELECT $1 && $2;
$$ LANGUAGE sqlЕсть у постгреса оператор &&
select oprkind, l.typname, oprname, r.typname from pg_operator join pg_type l on oprleft = l.oid join pg_type r on oprright = r.oid where oprname = '&&';
oprkind | typname | oprname | typname
---------+-----------+---------+-----------
b | box | && | box
b | polygon | && | polygon
b | tinterval | && | tinterval
b | circle | && | circle
b | inet | && | inet
b | tsquery | && | tsquery
b | anyarray | && | anyarray
b | anyrange | && | anyrangeFor timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.
select ticket_number , min(some_id), max(some_id) from tablename group by ticket_numberКак вы масштабируете базу(под запись)?
Сильно ли нагружает сервер процес синхронной репликация?
чаще всего используеться репликация на уровне приложения
какие есть угрозы при такой балансировке базы?
next_month := date_part( 'year', NEW.dtime)::text ||'-'|| date_part( 'month', NEW.dtime )::text ||'-31';table_part := table_master
|| '' || to_char(NOW(), 'YYYY')::text
|| 'm' || to_char(NOW(), 'MM')::text;INSERT INTO ' || table_part || '
melkij=> create table events2016m11 (check (dtime >= '2016-11-01'::date AND dtime < '2016-12-01'::date)) inherits(events);
melkij=> insert into events2016m11 values ('2016-11-20');
INSERT 0 1
melkij=> explain (analyze) select * from events where dtime > '2016-12-05';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Append (cost=0.00..38.25 rows=754 width=8) (actual time=0.013..0.013 rows=0 loops=1)
-> Seq Scan on events (cost=0.00..0.00 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1)
Filter: (dtime > '2016-12-05 00:00:00+03'::timestamp with time zone)
-> Seq Scan on events2016m11 (cost=0.00..38.25 rows=753 width=8) (actual time=0.009..0.009 rows=0 loops=1)
Filter: (dtime > '2016-12-05 00:00:00+03'::timestamp with time zone)
Rows Removed by Filter: 1
Planning time: 0.127 ms
Execution time: 0.032 ms
(8 строк)
melkij=> drop table events2016m11 ;
DROP TABLE
melkij=> create table events2016m11 (check (dtime >= '2016-11-01'::timestamptz AND dtime < '2016-12-01'::timestamptz)) inherits(events);
CREATE TABLE
melkij=> insert into events2016m11 values ('2016-11-20');INSERT 0 1
melkij=> explain (analyze) select * from events where dtime > '2016-12-05'; QUERY PLAN
------------------------------------------------------------------------------------------------------
Append (cost=0.00..0.00 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1)
-> Seq Scan on events (cost=0.00..0.00 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1)
Filter: (dtime > '2016-12-05 00:00:00+03'::timestamp with time zone)
Planning time: 0.301 ms
Execution time: 0.024 ms
(5 строк)
melkij=> show constraint_exclusion ;
constraint_exclusion
----------------------
partitionSELECT evtname as "Name", evtevent as "Event", pg_catalog.pg_get_userbyid(e.evtowner) as "Owner",
case evtenabled when 'O' then 'enabled' when 'R' then 'replica' when 'A' then 'always' when 'D' then 'disabled' end as "Enabled",
e.evtfoid::pg_catalog.regproc as "Procedure", pg_catalog.array_to_string(array(select x from pg_catalog.unnest(evttags) as t(x)), ', ') as "Tags"
FROM pg_catalog.pg_event_trigger e ORDER BY 1The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" one another's effects on the target tables.
UPDATE "TABLE1"
SET
"Value2" = (NOT EXISTS(
SELECT NULL
FROM "TABLE2"
WHERE "что-то" = "кое-что"
)
AND NOT EXISTS(
SELECT NULL
FROM "TABLE3"
WHERE "что-то" = "кое-что"
))
WHERE "кое-что" = ANY ($1 :: INT [])