For 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 [])Сейчас разработчики пишут портал
melkij=> create type gender as enum('M','W');
CREATE TYPE
melkij=> create table foo (f gender);
CREATE TABLE
melkij=> insert into foo values('M');
INSERT 0 1
melkij=> insert into foo values('F');
ОШИБКА: неверное значение для перечисления gender: "F"
СТРОКА 1: insert into foo values('F');
^
melkij=> insert into foo values('');
ОШИБКА: неверное значение для перечисления gender: ""
СТРОКА 1: insert into foo values('');create unlogged table list_for_delete (id int);
insert into list_for_delete values ....
with to_rm as (
select id from list_for_delete limit 10000
), rm as (
delete from list_for_delete where id in (select id from to_rm)
)
delete from tablename where id in (select id from to_rm);
vacuum analyze tablename;
drop table list_for_delete ;postgres=# create schema garage;
CREATE SCHEMA
postgres=# CREATE TABLE garage.users
(
car_id text COLLATE pg_catalog."default"
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
CREATE TABLE
postgres=# insert into garage.users values ('1'), ($$'1'$$), (null);
INSERT 0 3
postgres=# select car_id, car_id is null from garage.users ;
car_id | ?column?
--------+----------
1 | f
'1' | f
| t