Cast postgresql: PostgreSQL CAST Convert From One Data Type Into Another

Содержание

PostgreSQL | Типы данных

Типы данных в PostgreSQL

Последнее обновление: 17.03.2018

При определении таблицы для всех ее столбцов необходимо указать тип данных. Тип данных определяет диапазон значений, которые могут храниться в столбце, сколько они будут занимать места в памяти. PostgreSQL поддерживает богатую палитру различных типов данных, среди которые условно можно разделить на подгруппы: числовые, символьные, логические, дата и время, бинарные и ряд других.

Числовые типы данных

serial: представляет автоинкрементирующееся числовое значение, которое занимает 4 байта и может хранить числа от 1 до 2147483647. Значение данного типа образуется путем автоинкремента значения предыдущей строки. Поэтому, как правило, данный тип используется для определения идентификаторов строки.
smallserial: представляет автоинкрементирующееся числовое значение, которое занимает 2 байта и может хранить числа от 1 до 32767.
Аналог типа serial для небольших чисел.
bigserial: представляет автоинкрементирующееся числовое значение, которое занимает 8 байт и может хранить числа от 1 до 9223372036854775807. Аналог типа serial для больших чисел.
smallint: хранит числа от -32768 до +32767. Занимает 2 байта. Имеет псевдоним int2.
integer: хранит числа от -2147483648 до +2147483647. Занимает 4 байта. Имеет псевдонимы int и int4.
bigint: хранит числа от -9223372036854775808 до +9223372036854775807. Занимает 8 байт. Имеет псевдоним int8.
numeric: хранит числа с фиксированной точностью, которые могут иметь до 131072 знаков в целой части и до 16383 знаков после запятой.

Данный тип может принимать два параметра precision и scale: numeric(precision, scale)
.

Параметр precision указывает на максимальное количество цифр, которые может хранить число.

Параметр scale представляет максимальное количество цифр, которые может содержать число после запятой. Это значение должно находиться в диапазоне от 0 до значения параметра precision. По умолчанию оно равно 0.

Например, для числа 23.5141 precision равно 6, а scale — 4.
decimal: хранит числа с фиксированной точностью, которые могут иметь до 131072 знаков в целой части и до 16383 знаков в дробной части. То же самое, что и numeric.
real: хранит числа с плавающей точкой из диапазона от 1E-37 до 1E+37. Занимает 4 байта. Имеет псевдоним float4.
double precision: хранит числа с плавающей точкой из диапазона от 1E-307 до 1E+308. Занимает 8 байт. Имеет псевдоним
float8.

Примеры использования:


Id SERIAL,
TotalWeight NUMERIC(9,2),
Age INTEGER,
Surplus REAL

Типы для работы с валютой (денежными единицами)

Для работы с денежными единицами определен тип money, который может принимать значения в диапазоне от -92233720368547758. 08 до +92233720368547758.07 и занимает 8 байт.

Символьные типы

character(n): представляет строку из фиксированного количества символов. С помощью параметра задается задается количетво символов в строке. Имеет псевдоним char(n).
character varying(n): представляет строку из фиксированного количества символов. С помощью параметра задается задается количетво символов в строке. Имеет псевдоним varchar(n).
text: представляет текст произвольной длины.

Бинарные данные

Для хранения бинарных данных определен тип bytea. Он хранит данные в виде бинарных строк, которые представляют последовательность октетов или байт.

Типы для работы с датами и временем

timestamp: хранит дату и время. Занимает 8 байт. Для дат самое нижнее значение — 4713 г до н.э., самое верхнее значение — 294276 г н.э.
timestamp with time zone: то же самое, что и timestamp, только добавляет данные о часовом поясе.
date: представляет дату от 4713 г. до н.э. до 5874897 г н.э. Занимает 4 байта.
time: хранит время с точностью до 1 микросекунды без указания часового пояса. Принимает значения от 00:00:00 до 24:00:00. Занимает 8 байт.
time with time zone: хранит время с точностью до 1 микросекунды с указанием часового пояса. Принимает значения от 00:00:00+1459 до 24:00:00-1459. Занимает 12 байт.
interval: представляет временной интервал. Занимает 16 байт.

Распространенные форматы дат:

Распространенные форматы времени:

hh:mi — 13:21
hh:mi am/pm — 1:21 pm
hh:mi:ss — 1:21:34

Логический тип

Тип boolean может хранить одно из двух значений: true или false.

Вместо true можно указывать следующие значения: TRUE, ‘t’, ‘true’, ‘y’, ‘yes’, ‘on’, ‘1’.

Вместо false можно указывать следующие значения: FALSE, ‘f’, ‘false’, ‘n’, ‘no’, ‘off’, ‘0’.

Типы для представления интернет-адресов

cidr: интернет-адрес в формате IPv4 и IPv6. Например, 192.168.0.1. Занимает от 7 до 19 байт.
inet: интернет-адрес в формате cidr/y, где
cidr это адрес в формате IPv4 или IPv6, а /y — количество бит в адресе (если этот параметр не указан, то используется 34 для IPv4, 128 для IPv6). Например, 192.168.0.1/24 или 2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128. Занимает от 7 до 19 байт.
macaddr: хранит MAC-адрес. Занимает 6 байт.
macaddr8: хранит MAC-адрес в формате EUI-64. Занимает 8 байт.

Геометрические типы

point: представляет точку на плоскости в формате (x,y). Занимает 16 байт.
line: представляет линию неопределенной длины в формате {A,B,C}. Занимает 32 байта.
lseg: представляет отрезок в формате ((x1,y1),(x2,y2)). Занимает 32 байта.
box: представляет прямоугольник в формате ((x1,y1),(x2,y2)). Занимает 32 байта.
path: представляет набор содиненных точек. В формате ((x1,y1),...) путь является закрытым (первая и последняя точка соединяются линией) и фактически представляет многоугольник. В формате [(x1,y1),...] путь является открытым Занимает 16+16n байт.
polygon: представляет многоугольник в формате ((x1,y1),...). Занимает 40+16n байт.
circle: представляет окружность в формате <(x,y),r>. Занимает 24 байта.

Остальные типы данных

json: хранит данные json в текстовом виде.
jsonb: хранит данные json в бинарном формате.
uuid: хранит универсальный уникальный идентификатор (UUID), например, a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11. Занимает 32 байта.
xml: хранит даные в формате XML.

PostgreSQL — синтаксис — CoderLessons.com

В этой главе приведен список команд PostgreSQL SQL, а также точные правила синтаксиса для каждой из этих команд. Этот набор команд взят из инструмента командной строки psql. Теперь, когда у вас установлен Postgres, откройте psql как —

Программные файлы → PostgreSQL 9.2 → Оболочка SQL (psql).

Используя psql, вы можете создать полный список команд с помощью команды \ help. Для синтаксиса конкретной команды используйте следующую команду —

 postgres-# \help <command_name>

Оператор SQL

Оператор SQL состоит из токенов, где каждый токен может представлять собой ключевое слово, идентификатор, заключенный в кавычки идентификатор, константу или символ специального символа. В приведенной ниже таблице используется простая инструкция SELECT, чтобы проиллюстрировать базовую, но полную инструкцию SQL и ее компоненты.

	ВЫБРАТЬ	идентификатор, имя	ОТ	состояния
Тип токена	Ключевое слово	Идентификаторы	Ключевое слово	Идентификатор
Описание	команда	Столбцы идентификатора и имени	пункт	Имя таблицы

SQL-команды PostgreSQL

ABORT

Прервать текущую транзакцию.

ABORT [ WORK | TRANSACTION ]

ALTER AGGREGATE

Измените определение агрегатной функции.

ALTER AGGREGATE name ( type ) RENAME TO new_name
ALTER AGGREGATE name ( type ) OWNER TO new_owner

АЛЬТЕР КОНВЕРСИЯ

Изменить определение конверсии.

ALTER CONVERSION name RENAME TO new_name
ALTER CONVERSION name OWNER TO new_owner

ALTER DATABASE

Изменить параметр базы данных.

ALTER DATABASE name SET parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name RESET parameter
ALTER DATABASE name RENAME TO new_name
ALTER DATABASE name OWNER TO new_owner

ALTER DOMAIN

Измените определение определенного для домена параметра.

ALTER DOMAIN name { SET DEFAULT expression | DROP DEFAULT }
ALTER DOMAIN name { SET | DROP } NOT NULL
ALTER DOMAIN name ADD domain_constraint
ALTER DOMAIN name DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
ALTER DOMAIN name OWNER TO new_owner

ALTER FUNCTION

Изменить определение функции.

ALTER FUNCTION name ( [ type [, ...] ] ) RENAME TO new_name
ALTER FUNCTION name ( [ type [, ...] ] ) OWNER TO new_owner

ALTER GROUP

Изменить группу пользователей.

ALTER GROUP groupname ADD USER username [, ... ]
ALTER GROUP groupname DROP USER username [, ... ]
ALTER GROUP groupname RENAME TO new_name

ALTER INDEX

Изменить определение индекса.

ALTER INDEX name OWNER TO new_owner
ALTER INDEX name SET TABLESPACE indexspace_name
ALTER INDEX name RENAME TO new_name

АЛЬТЕРСКИЙ ЯЗЫК

Изменить определение процедурного языка.

ALTER LANGUAGE name RENAME TO new_name

ALTER OPERATOR

Изменить определение оператора.

ALTER OPERATOR name ( { lefttype | NONE }, { righttype | NONE } )
OWNER TO new_owner

ALTER ОПЕРАТОР КЛАСС

Изменить определение класса оператора.

ALTER OPERATOR CLASS name USING index_method RENAME TO new_name
ALTER OPERATOR CLASS name USING index_method OWNER TO new_owner

ALTER SCHEMA

Измените определение схемы.

ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO new_owner

ALTER SEQUENCE

Измените определение генератора последовательности.

ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ]
[ MAXVALUE maxvalue | NO MAXVALUE ]
[ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

ALTER TABLE

Изменить определение таблицы.

ALTER TABLE [ ONLY ] name [ * ]
action [, ... ]
ALTER TABLE [ ONLY ] name [ * ]
RENAME [ COLUMN ] column TO new_column
ALTER TABLE name
RENAME TO new_name

Где действие — одна из следующих строк —

ADD [ COLUMN ] column_type [ column_constraint [ . .. ] ]
DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] column TYPE type [ USING expression ]
ALTER [ COLUMN ] column SET DEFAULT expression
ALTER [ COLUMN ] column DROP DEFAULT
ALTER [ COLUMN ] column { SET | DROP } NOT NULL
ALTER [ COLUMN ] column SET STATISTICS integer
ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD table_constraint
DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET WITHOUT OIDS
OWNER TO new_owner
SET TABLESPACE tablespace_name

ALTER TABLESPACE

Изменить определение табличного пространства.

ALTER TABLESPACE name RENAME TO new_name
ALTER TABLESPACE name OWNER TO new_owner

ALTER TRIGGER

Изменить определение триггера.

ALTER TRIGGER name ON table RENAME TO new_name

ALTER TYPE

Изменить определение типа.

ALTER TYPE name OWNER TO new_owner

ALTER USER

Изменить учетную запись пользователя базы данных.

ALTER USER name [ [ WITH ] option [ ... ] ]
ALTER USER name RENAME TO new_name
ALTER USER name SET parameter { TO | = } { value | DEFAULT }
ALTER USER name RESET parameter

Где вариант может быть —

[ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| VALID UNTIL 'abstime'

ПРОАНАЛИЗИРУЙТЕ

Сбор статистики о базе данных.

ANALYZE [ VERBOSE ] [ table [ (column [, ...] ) ] ]

НАЧАТЬ

Начать блок транзакции.

BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]

Где транзакция_мод является одним из —

ISOLATION LEVEL { 
   SERIALIZABLE | REPEATABLE READ | READ COMMITTED
   | READ UNCOMMITTED
}
READ WRITE | READ ONLY

КОНТРОЛЬНО-ПРОПУСКНОЙ ПУНКТ

Принудительно установить контрольную точку журнала транзакций.

CHECKPOINT

БЛИЗКО

Закройте курсор.

CLOSE name

Кластер

Сгруппируйте таблицу в соответствии с индексом.

CLUSTER index_name ON table_name
CLUSTER table_name
CLUSTER

КОММЕНТАРИЙ

Определите или измените комментарий объекта.

COMMENT ON {
   TABLE object_name |
   COLUMN table_name.column_name |
   AGGREGATE agg_name (agg_type) |
   CAST (source_type AS target_type) |
   CONSTRAINT constraint_name ON table_name |
   CONVERSION object_name |
   DATABASE object_name |
   DOMAIN object_name |
   FUNCTION func_name (arg1_type, arg2_type, ...) |
   INDEX object_name |
   LARGE OBJECT large_object_oid |
   OPERATOR op (left_operand_type, right_operand_type) |
   OPERATOR CLASS object_name USING index_method |
   [ PROCEDURAL ] LANGUAGE object_name |
   RULE rule_name ON table_name |
   SCHEMA object_name |
   SEQUENCE object_name |
   TRIGGER trigger_name ON table_name |
   TYPE object_name |
   VIEW object_name
} 
IS 'text'

COMMIT

Зафиксируйте текущую транзакцию.

COMMIT [ WORK | TRANSACTION ]

COPY

Скопируйте данные между файлом и таблицей.

COPY table_name [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }
[ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE NOT NULL column [, ...] ]
COPY table_name [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE QUOTE column [, ...] ]

СОЗДАТЬ АГРЕГАТ

Определите новую агрегатную функцию.

CREATE AGGREGATE name (
   BASETYPE = input_data_type,
   SFUNC = sfunc,
   STYPE = state_data_type
   [, FINALFUNC = ffunc ]
   [, INITCOND = initial_condition ]
)

СОЗДАТЬ АКТЕРЫ

Определите новый состав.

CREATE CAST (source_type AS target_type)
WITH FUNCTION func_name (arg_types)
[ AS ASSIGNMENT | AS IMPLICIT ]
CREATE CAST (source_type AS target_type)
WITHOUT FUNCTION
[ AS ASSIGNMENT | AS IMPLICIT ]

СОЗДАТЬ ОГРАНИЧЕННЫЙ ТРИГГЕР

Определите новый триггер ограничения.

CREATE CONSTRAINT TRIGGER name
AFTER events ON
table_name constraint attributes
FOR EACH ROW EXECUTE PROCEDURE func_name ( args )

СОЗДАТЬ КОНВЕРСИЯ

Определите новую конверсию.

CREATE [DEFAULT] CONVERSION name
FOR source_encoding TO dest_encoding FROM func_name

СОЗДАТЬ БАЗУ ДАННЫХ

Создать новую базу данных.

CREATE DATABASE name
[ [ WITH ] [ OWNER [=] db_owner ]
   [ TEMPLATE [=] template ]
   [ ENCODING [=] encoding ]
   [ TABLESPACE [=] tablespace ] 
]

СОЗДАТЬ ДОМЕН

Определите новый домен.

CREATE DOMAIN name [AS] data_type
[ DEFAULT expression ]
[ constraint [ ... ] ]

Где ограничение —

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression) }

СОЗДАТЬ ФУНКЦИЮ

Определите новую функцию.

CREATE [ OR REPLACE ] FUNCTION name ( [ [ arg_name ] arg_type [, ...] ] )
RETURNS ret_type
{ LANGUAGE lang_name
   | IMMUTABLE | STABLE | VOLATILE
   | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
   | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
   | AS 'definition'
   | AS 'obj_file', 'link_symbol'
} ...
[ WITH ( attribute [, ...] ) ]

СОЗДАТЬ ГРУППУ

Определите новую группу пользователей.

CREATE GROUP name [ [ WITH ] option [ ... ] ]
Where option can be:
SYSID gid
| USER username [, ...]

СОЗДАТЬ ИНДЕКС

Определите новый индекс.

CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [, ...] )
[ TABLESPACE tablespace ]
[ WHERE predicate ]

СОЗДАТЬ ЯЗЫК

Определите новый процедурный язык.

CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name
HANDLER call_handler [ VALIDATOR val_function ]

СОЗДАТЬ ОПЕРАТОР

Определите нового оператора.

CREATE OPERATOR name (
   PROCEDURE = func_name
   [, LEFTARG = left_type ] [, RIGHTARG = right_type ]
   [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]
   [, RESTRICT = res_proc ] [, JOIN = join_proc ]
   [, HASHES ] [, MERGES ]
   [, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ]
   [, LTCMP = less_than_op ] [, GTCMP = greater_than_op ]
)

СОЗДАТЬ КЛАСС ОПЕРАТОРА

Определите новый класс операторов.

CREATE OPERATOR CLASS name [ DEFAULT ] FOR TYPE data_type
USING index_method AS
{ OPERATOR strategy_number operator_name [ ( op_type, op_type ) ] [ RECHECK ]
   | FUNCTION support_number func_name ( argument_type [, ...] )
   | STORAGE storage_type
} [, ... ]

СОЗДАТЬ ПРАВИЛО

Определите новое правило перезаписи.

CREATE [ OR REPLACE ] RULE name AS ON event
TO table [ WHERE condition ]
DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

СОЗДАТЬ СХЕМУ

Определите новую схему.

CREATE SCHEMA schema_name
[ AUTHORIZATION username ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION username
[ schema_element [ ... ] ]

СОЗДАТЬ ПОСЛЕДОВАТЕЛЬНОСТЬ

Определите новый генератор последовательности.

CREATE [ TEMPORARY | TEMP ] SEQUENCE name
[ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ]
[ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

СОЗДАТЬ СТОЛ

Определите новую таблицу.

CREATE [ [ GLOBAL | LOCAL ] { 
   TEMPORARY | TEMP } ] TABLE table_name ( { 
      column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
      | table_constraint
      | LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] 
   } [, ... ]
)
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]

Где находится column_constraint —

[ CONSTRAINT constraint_name ] { 
   NOT NULL |
   NULL |
   UNIQUE [ USING INDEX TABLESPACE tablespace ] |
   PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] |
   CHECK (expression) |
   REFERENCES ref_table [ ( ref_column ) ]
   [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
   [ ON DELETE action ] [ ON UPDATE action ] 
}
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

И ограничение таблицы —

[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
PRIMARY KEY ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
CHECK ( expression ) |
FOREIGN KEY ( column_name [, ... ] )
REFERENCES ref_table [ ( ref_column [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

СОЗДАТЬ ТАБЛИЦУ КАК

Определите новую таблицу из результатов запроса.

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
[ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]
AS query

СОЗДАТЬ СТОЛ

Определите новое табличное пространство.

CREATE TABLESPACE tablespace_name [ OWNER username ] LOCATION 'directory'

СОЗДАТЬ ТРИГГЕР

Определите новый триггер.

CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE func_name ( arguments )

СОЗДАТЬ ТИП

Определите новый тип данных.

CREATE TYPE name AS
( attribute_name data_type [, ... ] )
CREATE TYPE name (
INPUT = input_function,
OUTPUT = output_function
[, RECEIVE = receive_function ]
[, SEND = send_function ]
[, ANALYZE = analyze_function ]
[, INTERNALLENGTH = { internal_length | VARIABLE } ]
[, PASSEDBYVALUE ]
[, ALIGNMENT = alignment ]
[, STORAGE = storage ]
[, DEFAULT = default ]
[, ELEMENT = element ]
[, DELIMITER = delimiter ]
)

СОЗДАТЬ ПОЛЬЗОВАТЕЛЯ

Определите новую учетную запись пользователя базы данных.

CREATE USER name [ [ WITH ] option [ ... ] ]

Где вариант может быть —

SYSID uid
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| IN GROUP group_name [, ...]
| VALID UNTIL 'abs_time'

СОЗДАТЬ ВИД

Определить новый вид.

CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ] AS query

DEALLOCATE

Распределите подготовленное заявление.

DEALLOCATE [ PREPARE ] plan_name

DECLARE

Определить курсор.

DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]

УДАЛЯТЬ

Удалить строки таблицы.

DELETE FROM [ ONLY ] table [ WHERE condition ]

DROP AGGREGATE

Удалить агрегатную функцию.

DROP AGGREGATE name ( type ) [ CASCADE | RESTRICT ]

КАПЛИ КАПИТАЛА

Удалить актерский состав.

DROP CAST (source_type AS target_type) [ CASCADE | RESTRICT ]

КАПЛИВНАЯ КОНВЕРСИЯ

Удалить конверсию

DROP CONVERSION name [ CASCADE | RESTRICT ]

DROP DATABASE

Удалить базу данных.

DROP DATABASE name

DROP DOMAIN

Удалить домен.

DROP DOMAIN name [, ...] [ CASCADE | RESTRICT ]

ФУНКЦИЯ КАПЛИ

Удалить функцию.

DROP FUNCTION name ( [ type [, ...] ] ) [ CASCADE | RESTRICT ]

DROP GROUP

Удалить группу пользователей.

DROP GROUP name

Индекс капли

Удалить индекс.

DROP INDEX name [, ...] [ CASCADE | RESTRICT ]

УСТАРЕТЬ ЯЗЫК

Удалить процедурный язык.

DROP [ PROCEDURAL ] LANGUAGE name [ CASCADE | RESTRICT ]

КАПЕЛЬНЫЙ ОПЕРАТОР

Удалить оператора.

DROP OPERATOR name ( { left_type | NONE }, { right_type | NONE } )
[ CASCADE | RESTRICT ]

КАПЛЯЖ ОПЕРАТОРА

Удалить класс оператора.

DROP OPERATOR CLASS name USING index_method [ CASCADE | RESTRICT ]

DROP RULE

Удалить правило перезаписи.

DROP RULE name ON relation [ CASCADE | RESTRICT ]

DROP SCHEMA

Удалить схему.

DROP SCHEMA name [, ...] [ CASCADE | RESTRICT ]

DROP SEQUENCE

Удалить последовательность.

DROP SEQUENCE name [, ...] [ CASCADE | RESTRICT ]

DROP TABLE

Удалить стол.

DROP TABLE name [, ...] [ CASCADE | RESTRICT ]

DROP TABLESPACE

Удалить табличное пространство.

DROP TABLESPACE tablespace_name

DROP TRIGGER

Удалить триггер.

DROP TRIGGER name ON table [ CASCADE | RESTRICT ]

DROP TYPE

Удалить тип данных.

DROP TYPE name [, ...] [ CASCADE | RESTRICT ]

DROP USER

Удалить учетную запись пользователя базы данных.

DROP USER name

DROP VIEW

Удалить вид.

DROP VIEW name [, ...] [ CASCADE | RESTRICT ]

КОНЕЦ

Зафиксируйте текущую транзакцию.

END [ WORK | TRANSACTION ]

ВЫПОЛНИТЬ

Выполните подготовленное заявление.

EXECUTE plan_name [ (parameter [, ...] ) ]

EXPLAIN

Показать план выполнения выписки.

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

FETCH

Получить строки из запроса с помощью курсора.

FETCH [ direction { FROM | IN } ] cursor_name

Где направление может быть пустым или одно из —

NEXT
PRIOR
FIRST
LAST
ABSOLUTE count
RELATIVE count
count
ALL
FORWARD
FORWARD count
FORWARD ALL
BACKWARD
BACKWARD count
BACKWARD ALL

ГРАНТ

Определите права доступа.

GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] table_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE db_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTION func_name ([type, ...]) [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

ВСТАВИТЬ

Создать новые строки в таблице.

INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }

СЛУШАТЬ

Слушайте уведомления.

LISTEN name

НАГРУЗКИ

Загрузите или перезагрузите файл общей библиотеки.

LOAD 'filename'

ЗАМОК

Заблокировать стол

LOCK [ TABLE ] name [, ...] [ IN lock_mode MODE ] [ NOWAIT ]

Где lock_mode является одним из —

ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
| SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

ПЕРЕЕХАТЬ

Поместите курсор.

MOVE [ direction { FROM | IN } ] cursor_name

ПОСТАВИТЬ В ИЗВЕСТНОСТЬ

Создать уведомление.

NOTIFY name

ПОДГОТОВИТЬ

Подготовьте заявление к исполнению.

PREPARE plan_name [ (data_type [, ...] ) ] AS statement

REINDEX

Перестройте индексы.

REINDEX { DATABASE | TABLE | INDEX } name [ FORCE ]

RELEASE SAVEPOINT

Уничтожить ранее определенную точку сохранения.

RELEASE [ SAVEPOINT ] savepoint_name

СБРОС

Восстановите значение параметра времени выполнения до значения по умолчанию.

RESET name
RESET ALL

КЕУОКЕ

Удалить права доступа.

REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] table_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE db_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTION func_name ([type, ...]) [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

ROLLBACK

Прервать текущую транзакцию.

ROLLBACK [ WORK | TRANSACTION ]

Откат к SAVEPOINT

Откат к точке сохранения.

ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name

SAVEPOINT

Определите новую точку сохранения в текущей транзакции.

SAVEPOINT savepoint_name

ВЫБРАТЬ

Получить строки из таблицы или представления.

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF table_name [, ...] ] ]

Где from_item может быть одним из:

[ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
function_name ( [ argument [, ...] ] )
[ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]
function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
from_item [ NATURAL ] join_type from_item
[ ON join_condition | USING ( join_column [, ...] ) ]

ВЫБРАТЬ В

Определите новую таблицу из результатов запроса.

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF table_name [, ...] ] ]

ЗАДАВАТЬ

Измените параметр времени выполнения.

SET [ SESSION | LOCAL ] name { TO | = } { value | 'value' | DEFAULT }
SET [ SESSION | LOCAL ] TIME ZONE { time_zone | LOCAL | DEFAULT }

УСТАНОВИТЬ ОГРАНИЧЕНИЯ

Установите режимы проверки ограничений для текущей транзакции.

SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }

УСТАНОВКА АВТОРИЗАЦИИ СЕССИИ

Установите идентификатор пользователя сеанса и текущий идентификатор пользователя текущего сеанса.

SET [ SESSION | LOCAL ] SESSION AUTHORIZATION username
SET [ SESSION | LOCAL ] SESSION AUTHORIZATION DEFAULT
RESET SESSION AUTHORIZATION

УСТАНОВИТЬ СДЕЛКУ

Установите характеристики текущей транзакции.

SET TRANSACTION transaction_mode [, ...]
SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]

Где транзакция_мод является одним из —

ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED
| READ UNCOMMITTED }
READ WRITE | READ ONLY

ШОУ

Показать значение параметра времени выполнения.

SHOW name
SHOW ALL

НАЧАТЬ СДЕЛКУ

Начать блок транзакции.

START TRANSACTION [ transaction_mode [, ...] ]

Где транзакция_мод является одним из —

ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED
| READ UNCOMMITTED }
READ WRITE | READ ONLY

TRUNCATE

Пустой стол.

TRUNCATE [ TABLE ] name

UNLISTEN

Хватит слушать уведомления.

UNLISTEN { name | * }

ОБНОВИТЬ

Обновите строки таблицы.

UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...]
[ FROM from_list ]
[ WHERE condition ]

ВАКУУМНАЯ

Сборка мусора и при необходимости анализ базы данных.

Работаем с PostgreSQL из psql

Оригинал: ‘PostgreSQL on the Command Line’ by Philipe Fatio

Мне настолько понравилась эта статья, что я решил её перевести. Получилось весьма вольно, но сама суть сохранена.

Я уже больше 4 лет работаю с PostgreSQL. В начале использовал графические программы, сейчас же обхожусь только встроенными утилитами командной строки. Так получается более эффективно.

Этот пост разделён на несколько секций согласно задачам, которые можно решать с помощью psql:

Настройка psql

psql отлично настроена и из коробки, однако я бы хотел остановиться на 2х вещах, которые использую каждый день и которые делают psql более удобным инструментом. Первая по важности — получение более удобочитаемового вывода. По умолчанию psql старается представить данные как таблицу:

db=# SELECT 'hello' AS foo, bar FROM generate_series(1, 2) AS bar;
  foo  | bar
-------+-----
 hello |   1
 hello |   2
(2 rows)

Пока ширина таблицы меньше ширины экрана всё нормально, но потом строки начнут переноситься и вывод станет просто отвратительным:

db=# SELECT 'really long string messing with the output' AS foo, bar, 
'another long string making things worse' AS baz FROM generate_series(1,
 2) AS bar;
                    foo                     | bar |                   ba
z
--------------------------------------------+-----+---------------------
--------------------
 really long string messing with the output |   1 | another long string 
making things worse
 really long string messing with the output |   2 | another long string 
making things worse
(2 rows)

Чтобы избежать этого воспользуемся командой активации “расширенного дисплея” (expanded display):

db=# \x
Expanded display is on.

Теперь запрос будет выводиться в две колонки: в первой имя поля, во второй значение.

db=# SELECT 'really long string messing with the output' AS foo, bar, 
'another long string making things worse' AS baz FROM generate_series(1,
 2) AS bar;
-[ RECORD 1 ]-----------------------------------
foo | really long string messing with the output
bar | 1
baz | another long string making things worse
-[ RECORD 2 ]-----------------------------------
foo | really long string messing with the output
bar | 2
baz | another long string making things worse

Ещё одна хитрость: можно включать такой режим только когда он действительно нужен (\x auto). В таком случае если таблица помещается по ширине, то будет табличный вывод, в противном случае — построчный.

Вторая возможность — указать как будет выводиться значение NULL. По умолчанию оно неотличимо от пустой строки.

db=# SELECT '', NULL;
 ?column? | ?column?
----------+----------
          |
(1 row)

Это можно исправить задав в psql символ для визуализации: \pset null ¤. Теперь можно быть уверенным, что если в строке пусто, то это пустая строка, а не NULL.

db=# SELECT '', NULL;
 ?column? | ?column?
----------+----------
          | ¤
(1 row)

Хотел бы рассказать ещё об одной фиче psql. Если вам, как и мне, нравятся SQL выражения в ALL-CAPS, то это можно настроить в автодополнении командой \set COMP_KEYWORD_CASE upper. Теперь, когда будете набирать SQL-запрос, последовательность sel<tab> будет автоматически преобразовываться в SELECT<space>.

Конечно, прописывать все эти команды каждый раз при запуске psql будет слишком утомительным, так что я предлагаю прописать их один раз в ~/.psqlrc, который выполняется при каждой новой сессии psql.

\set COMP_KEYWORD_CASE upper
\x auto
\pset null ¤

Это лишь самая малость команд для настройки psql. Гораздо больше вы можете найти в th и psql’s doc.

Получение справки

Каждый раз когда мне нужно выполнить какую-нибудь редкую или сложную команду SQL, я лезу в справку, благо она всегда доступна через \h.

\h без аргументов покажет список доступных разделов. Это уже само по себе полезно, чтобы подсмотреть синтаксис SELECT или ALTER TABLE. Допустим, вы хотите удалить ограничение NOT NULL для столбца, но не помните точно как это делается через команду ALTER. \h alter поможет в этом и предоставит более чем подробную информацию. psql использует пагинацию для большого вывода, так что там работает поиск. Наберите / + текст для поиска + <enter> и увидите все упоминания, с помощью n и N можно переходить к следующему и предыдущему соответственно. Поискав NULL, находится вот такая команда: ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL. Теперь можно составить нужную команду, не заглядывая в Google.

ALTER TABLE table_name ALTER column_name DROP NOT NULL;

Как видите, \h даёт справку только по SQL командам. Для внутренних команд psql, которые начинаются с обратного слэша, есть \?.

Ваша песочница

В psql заданы настройки по умолчанию для подключения к базе данных. Чтобы подключиться к локальной БД достаточно набрать psql db_name. Вы можете не указывать даже имя базы данных. psql трактует это как подключение к БД с именем текущего пользователя. Это позволяет быстро попасть в psql-сессию.

$ psql
psql: FATAL:  database "phil" does not exist
$ createdb `whoami`
$ psql
phil=#

Мне удобно бывает просто набрать psql и проверить синтаксис запроса или прочитать справку без необходимости соединения с какой-нибудь реальной БД.

Описание БД

psql имеет достаточное количество специальных команд, которые помогают проще ориентироваться в базе данных. Для списка таблиц наберите \dt, для списка индексов — \di, представлений — \dv и т.д. Приятно, что все эти команды параметром принимают шаблон, по которому будет производиться фильтрация. Т.е. если вас интересуют только таблицы, начинающиеся на user, то выполните \dt user\*. Там, где я работаю, частенько используются схемы. Грубо говоря это пространство имён для таблиц. По умолчанию есть только одна схема public. Когда вы создаёте там таблицу foo, обратиться к ней можно будет через public.foo. В нашей же компании для каждого клиента используется своя схема. Иногда мне нужно проверить есть ли в схеме клиента определённая таблица (например, users). Для этого мне достаточно набрать \dt \*.users, и я получу список всех таблиц с указанием схем.

db=# \dt *.users
         List of relations
  Schema  | Name  | Type  | Owner
----------+-------+-------+-------
 apple    | users | table | phil
 google   | users | table | phil
 facebook | users | table | phil
(3 rows)

Для получения информации о конкретном объекте таблицы или представления служит команда \d. Она показывает следующие вещи,

список столбцов вместе с их типами и значениями по умолчанию
индексы
ограничения
внешние ключи
триггеры

db=# \d users
                          Table "public.users"
  Column  |  Type   |                     Modifiers
----------+---------+----------------------------------------------------
 id       | integer | not null default nextval('users_id_seq'::regclass)
 name     | text    | not null
 email    | text    | not null
 group_id | integer | not null
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)
Check constraints:
    "users_email_check" CHECK (email ~ '[email protected]'::text)
    "users_name_check" CHECK (name <> ''::text)
Foreign-key constraints:
    "users_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(id)
Referenced by:
    TABLE "posts" CONSTRAINT "posts_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)

Для функций есть подобная команда \df. При вызове без аргументов, она покажет список всех функций. У меня их слишком много, так что с вашего позволения воспользуюсь фильтром:

db=# \df *regexp*
                                    List of functions
   Schema   |         Name          | Result data type |  Argument data types   |  Type
------------+-----------------------+------------------+------------------------+--------
 pg_catalog | regexp_matches        | SETOF text[]     | text, text             | normal
 pg_catalog | regexp_matches        | SETOF text[]     | text, text, text       | normal
 pg_catalog | regexp_replace        | text             | text, text, text       | normal
 pg_catalog | regexp_replace        | text             | text, text, text, text | normal
 pg_catalog | regexp_split_to_array | text[]           | text, text             | normal
 pg_catalog | regexp_split_to_array | text[]           | text, text, text       | normal
 pg_catalog | regexp_split_to_table | SETOF text       | text, text             | normal
 pg_catalog | regexp_split_to_table | SETOF text       | text, text, text       | normal
(8 rows)

Когда работаете со своими собственными функциями, то простого вывода сигнатуры оказывается мало. Тут на помощь приходит команда \ef для редактирования. Передайте ей имя редактируемой функции в качестве первого параметра, и она откроется в $EDITOR. Если определение функции было задано с помощью CREATE OR REPLACE FUNCTION, то после сохранения и закрытия редактора она будет обновлена. Если же вам нужно только посмотреть описание функции, то закройте редактор с помощью ненулевого кода возврата (для vim это :cq). Таким образом она не будет обновлена и выполнена. psql в таком случае понимает, что что-то пошло не так, и не будет ничего делать.

Построение запросов

psql превосходно подходит для построения небольших запросов, однако для многострочных и вложенных надо использовать более адекватный инс

Оптимизация настроек PostgreSQL (postgresql.conf) | материализация идей

Здесь будут настройки для PostgreSQL, работающей в виртуальной машине ESXi 6.5.

Ресурсы выделенные для ВМ:

процессор — 8 vCPU;

память — 48 GB;

диск для ОС — 50 GB на LUN аппаратном RAID1 из SAS HDD;

диск для БД — 170 GB на программном RAID1 из SSD

диск для логов — 100 GB на программном RAID1 из SSD

Для редактирования настроек выполним команду:

mcedit /var/lib/pgsql/9.6/data/postgresql.conf

Закомментированные параметры, которые будем изменять необходимо активировать.

Процессор

autovacuum_max_workers = 4

autovacuum_max_workers = NCores/4..2 но не меньше 4
Количество процессов автовакуума. Общее правило — чем больше write-запросов, тем больше процессов. На read-only базе данных достаточно одного процесса.

ssl = off

Выключение шифрования. Для защищенных ЦОД’ов шифрование бессмысленно, но приводит к увеличению загрузки CPU

Память

shared_buffers = 12GB

shared_buffers = RAM/4
Количество памяти, выделенной PgSQL для совместного кеша страниц. Эта память разделяется между всеми процессами PgSQL. Операционная система сама кеширует данные, поэтому нет необходимости отводить под кэш всю наличную оперативную память.

temp_buffers = 256MB

Максимальное количество страниц для временных таблиц. Т.е. это верхний лимит размера временных таблиц в каждой сессии.

work_mem = 64MB

work_mem = RAM/32..64 или 32MB..128MB
Лимит памяти для обработки одного запроса. Эта память индивидуальна для каждой сессии. Теоретически, максимально потребная память равна max_connections * work_mem, на практике такого не встречается потому что большая часть сессий почти всегда висит в ожидании. Это рекомендательное значение используется оптимайзером: он пытается предугадать размер необходимой памяти для запроса, и, если это значение больше work_mem, то указывает экзекьютору сразу создать временную таблицу. work_mem не является в полном смысле лимитом: оптимайзер может и промахнуться, и запрос займёт больше памяти, возможно в разы. Это значение можно уменьшать, следя за количеством создаваемых временных файлов:

maintenance_work_mem = 2GB

maintenance_work_mem = RAM/16..32 или work_mem * 4 или 256MB..4GB

Лимит памяти для обслуживающих задач, например по сбору статистики (ANALYZE), сборке мусора (VACUUM), создания индексов (CREATE INDEX) и добавления внешних ключей. Размер выделяемой под эти операции памяти должен быть сравним с физическим размером самого большого индекса на диске.

effective_cache_size = 36GB

effective_cache_size = RAM — shared_buffers
Оценка размера кеша файловой системы. Увеличение параметра увеличивает склонность системы выбирать IndexScan планы. И это хорошо.

Диски

effective_io_concurrency = 5

Оценочное значение одновременных запросов к дисковой системе, которые она может обслужить единовременно. Для одиночного диска = 1, для RAID — 2 или больше.

random_page_cost = 1.3

random_page_cost = 1.5-2.0 для RAID, 1.1-1.3 для SSD
Стоимость чтения рандомной страницы (по-умолчанию 4). Чем меньше seek time дисковой системы тем меньше (но > 1.0) должен быть этот параметр. Излишне большое значение параметра увеличивает склонность PgSQL к выбору планов с сканированием всей таблицы (PgSQL считает, что дешевле последовательно читать всю таблицу, чем рандомно индекс). И это плохо.

autovacuum = on

Включение автовакуума.

autovacuum_naptime = 20s

Время сна процесса автовакуума. Слишком большая величина будет приводить к тому, что таблицы не будут успевать вакуумиться и, как следствие, вырастет bloat и размер таблиц и индексов. Малая величина приведет к бесполезному нагреванию.

bgwriter_delay = 20ms

Время сна между циклами записи на диск фонового процесса записи. Данный процесс ответственен за синхронизацию страниц, расположенных в shared_buffers с диском. Слишком большое значение этого параметра приведет к возрастанию нагрузки на checkpoint процесс и процессы, обслуживающие сессии (backend). Малое значение приведет к полной загрузке одного из ядер.

bgwriter_lru_multiplier = 4.0

bgwriter_lru_maxpages = 400

Параметры, управляющие интенсивностью записи фонового процесса записи. За один цикл bgwriter записывает не больше, чем было записано в прошлый цикл, умноженное на bgwriter_lru_multiplier, но не больше чем bgwriter_lru_maxpages.

synchronous_commit = off

Выключение синхронизации с диском в момент коммита. Создает риск потери последних нескольких транзакций (в течении 0.5-1 секунды), но гарантирует целостность базы данных, в цепочке коммитов гарантированно отсутствуют пропуски. Но значительно увеличивает производительность.

wal_keep_segments = 256

wal_keep_segments = 32..256
Максимальное количество сегментов WAL между checkpoint. Слишком частые checkpoint приводят к значительной нагрузке на дисковую подсистему по записи. Каждый сегмент имеет размер 16MB

wal_buffers = 16MB

Объём разделяемой памяти, который будет использоваться для буферизации данных WAL, ещё не записанных на диск. Значение по умолчанию, равное -1, задаёт размер, равный 1/32 (около 3%) от shared_buffers, но не меньше, чем 64 КБ и не больше, чем размер одного сегмента WAL (обычно 16 МБ). Это значение можно задать вручную, если выбираемое автоматически слишком мало или велико, но при этом любое положительное число меньше 32 КБ будет восприниматься как 32 КБ. Этот параметр можно задать только при запуске сервера.
Содержимое буферов WAL записывается на диск при фиксировании каждой транзакции, так что очень большие значения вряд ли принесут значительную пользу. Однако значение как минимум в несколько мегабайт может увеличить быстродействие при записи на нагруженном сервере, когда сразу множество клиентов фиксируют транзакции. Автонастройка, действующая при значении по умолчанию (-1), в большинстве случаев выбирает разумные значения.

default_statistics_target = 1000

Устанавливает целевое ограничение статистики по умолчанию, распространяющееся на столбцы, для которых командой ALTER TABLE SET STATISTICS не заданы отдельные ограничения. Чем больше установленное значение, тем больше времени требуется для выполнения ANALYZE, но тем выше может быть качество оценок планировщика. Значение этого параметра по умолчанию — 100.

checkpoint_completion_target = 0.9

Степень «размазывания» checkpoint’a. Скорость записи во время checkpoint’а регулируется так, что бы время checkpoint’а было равно времени, прошедшему с прошлого, умноженному на checkpoint_completion_target.

min_wal_size = 4G
max_wal_size = 8G

min_wal_size = 512MB .. 4G
max_wal_size = 2 * min_wal_size
Минимальное и максимальный объем WAL файлов. Аналогично checkpoint_segments

fsync = on

Выключение параметра приводит к росту производительности, но появляется значительный риск потери всех данных при внезапном выключении питания. Внимание: если RAID имеет кеш и находиться в режиме write-back, проверьте наличие и функциональность батарейки кеша RAID контроллера! Иначе данные записанные в кеш RAID могут быть потеряны при выключении питания, и, как следствие, PgSQL не гарантирует целостность данных.

row_security = off

Отключение контроля разрешения уровня записи

enable_nestloop = off

Включает или отключает использование планировщиком планов соединения с вложенными циклами. Полностью исключить вложенные циклы невозможно, но при выключении этого параметра планировщик не будет использовать данный метод, если можно применить другие. По умолчанию этот параметр имеет значение on.

Блокировки

max_locks_per_transaction = 256

Максимальное число блокировок индексов/таблиц в одной транзакции

Настройки под платформу 1С

standard_conforming_strings = off

Разрешить использовать символ \ для экранирования

escape_string_warning = off

Не выдавать предупреждение о использовании символа \ для экранирования

Настройка безопасности

Сделаем так, чтобы сервер PostgreSQL был виден только для сервера 1С: Предприятие, установленного на этой же машине.

listen_addresses = ‘localhost’

Если сервер 1С: Предприятие установлен на другой машине или существует необходимость подключиться подключиться к серверу СУБД с помощью оснастки PGAdmin, то вместо localhost нужно указать адрес этой машины.

Хранение базы данных

PostgreSQL как и почти любая СУБД критична к дисковой подсистеме, поэтому для повышения быстродействия СУБД разместим систему PostgreSQL, логи и сами базы на разные диски.

Останавливаем сервер

 systemctl stop postgresql-9.6

Переносим логи на созданный RAID1 из 120GB SSD:

mv /var/lib/pgsql/9.6/data/pg_xlog /raid120
mv /var/lib/pgsql/9.6/data/pg_clog /raid120
mv /var/lib/pgsql/9.6/data/pg_log /raid120

Создаем символьные ссылки:

ln -s /raid120/pg_xlog /var/lib/pgsql/9.6/data/pg_xlog
ln -s /raid120/pg_clog /var/lib/pgsql/9.6/data/pg_clog
ln -s /raid120/pg_log /var/lib/pgsql/9.6/data/pg_log

Так же перенесем каталог с базами:

mv /var/lib/pgsql/9.6/data/base /raid200

и создадим символьную ссылку:

ln -s /raid200/base /var/lib/pgsql/9.6/data/base

запустим сервер и проверим его статус

Ускорение работы 1С с postgresql и диагностика проблем производительности

Некоторое время назад я настраивал работу 1С предприятия с базой данных postgresql. Во время тестирования столкнулся с проблемой медленной работы некоторых запросов. Хочу поделиться полезной информацией, которая позволит разобраться в таких ситуациях и попытаться ускорить работу и избавиться от узких мест в базе.

Хочешь научиться автоматически разворачивать и поддерживать высоконагруженные проекты? Тогда рекомендую познакомиться с онлайн курсом «Ansible. Infrastructure as a code.» в OTUS. Актуально для системных администраторов и devops инженеров. Подробности по .

Данная статья является частью единого цикла статьей про сервер Debian.

Введение

Сервер postgresql настроен по предыдущей статье — Установка и настройка postgresql на debian 8 для работы с 1С. Основные моменты по ускорению работы базы там приведены. Они существенно увеличивают производительность по сравнению с настройками по-умолчанию. В большинстве случаев этого бывает достаточно. Если нет — то у вас уже не типичный случай и надо разбираться более детально.

Проблема, с которой столкнулся я, кроется в особенности работы postgresql и отсутствии оптимизации 1С для работы с этой бд. База данных postgresql, в отличие от mssql, не умеет распараллеливать выполнение одного запроса не несколько ядер процессора. Даже если у вас очень высокопроизводительный сервер с большим числом ядер, вы можете попасть в ситуацию, когда какой-то тяжелый запрос будет очень сильно тормозить, нагружая только одно ядро. Остальные мощности процессора будут простаивать при этом. Увеличение ресурсов сервера никак не поможет вам ускорить работу базы. Она будет всегда спотыкаться на этом запросе.

Параллельное выполнение запросов на нескольких ядрах в postgresql

Я использовал версию postgresql 9.6. Если верить новости — http://www.opennet.ru/opennews/art.shtml?num=43313 в ней добавлена поддержка распараллеливания запросов. Я стал пробовать на практике это распараллеливание. Информации в интернете, к моему сожалению, не так много. Вроде проблема популярная, много где видел вопросов на эту тему. Например, вот тут обсуждают тему использования нескольких ядер процессора для выполнения запроса — http://www.sql.ru/forum/1002408/zadeystvovanie-neskolkih-processorov.

Наиболее популярные рекомендации, это изменить запросы и логику работы приложения с БД, чтобы не попадать в ситуацию, когда возникает один большой запрос, который невозможно разбить и обработать параллельно на нескольких ядрах. Пример такого подхода есть на хабре — https://habrahabr.ru/post/76309/. У меня нет ни должных знаний sql, ни тем более 1С, чтобы на уровне приложения что-то менять. Стал разбираться с возможностями postgresql.

Есть несколько параметров, которые как раз отвечают за параллельную обработку запросов:

max_worker_processes = 16
max_parallel_workers_per_gather = 8
min_parallel_relation_size = 0
parallel_tuple_cost = 0.05
parallel_setup_cost = 1000

Их необходимо подбирать под свое количество ядер. В данном случае настройки представлены для 16-ти ядерной системы. Далее необходимо применить скрипт на базе 1С, который позволит оптимизатору постгреса использовать параллельную обработку тех запросов 1С где участвуют текстовые поля (большинство запросов), путём изменения определений функций. Текст скрипта очень длинный, поэтому не привожу его здесь, чтобы не нагружать статью. Качаем его с сайта — postgre.sql.

Запрос необходимо выполнить в базе, которую использует 1С. Для этого можно воспользоваться либо программой pgAdmin, либо напрямую подключиться к базе, через консоль сервера. Опишу второй вариант в подробностях.

Подключаемся к серверу с postgresql по ssh. Заходим под юзером postgres:

# su postgres

Переходим в домашний каталог пользователя:

# cd

Создаем файл с запросом, который будем выполнять. В данном случае можете сразу скопировать файл, который скачали ранее, либо создайте вручную и скопируйте в него текст запроса.

# touch postgre.sql

Если будете копировать готовый файл, убедитесь, что у пользователя postgres есть доступ к этому файлу.

Подключаемся к серверу бд:

# psql -U postgres

Подключаемся к нужной базе данных:

\connect base1c

Выполняем sql запрос из файла:

\i postgre.sql

Все, можно идти проверять. Мы должны были увеличить быстродействие 1С запросов в базе postgresql, разрешив использовать параллельную обработку некоторых запросов. В моем случае это не дало никакого прироста по проблемным запросам. Сама база в целом работала нормально, но спотыкалась на определенных запросах. Разбираемся дальше.

Логирование sql запросов в postgresql

Для того, чтобы разобраться, что же конкретно у нас тормозит, надо посмотреть на сами запросы. Для этого нам нужно включить логирование запросов к базе данных. Запросов будет очень много, нам не нужны все подряд. Сделаем ограничение на логирование только тех запросов, которые выполняются дольше, чем 3 секунды. Для этого рисуем следующие параметры в конфиге БД:

log_destination = 'syslog'
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
log_min_duration_statement = 3000  # 3000 мс = 3 секунды
log_duration = off
log_statement = 'none'

И добавляем описание канала для логов LOCAL0 в конфиг rsyslog в файле /etc/rsyslog.conf, в самый конец:

LOCAL0.*                -/var/log/postgresql/sql.log

Если оставить настройки rsyslog в таком виде, то лог запросов будет писаться не только в файл /var/log/postgresql/sql.log, но и в messages, и в syslog. Я не люблю спамить в системные логи, поэтому отключим запись sql логов туда. Добавляем в описание этих лог файлов значение LOCAL0.none. Должно получиться примерно так:

*.*;auth,authpriv.none;LOCAL0.none              -/var/log/syslog
*.=info;*.=notice;*.=warn;\
        auth,authpriv.none;\
        cron,daemon.none;\
        mail,news.none;\
        LOCAL0.none             -/var/log/messages

Перезапускаем postgresql и rsyslog:

# systemctl restart postgresql
# systemctl restart rsyslog

Идем в базу 1С и вызываем свой запрос, который тормозит. Если его выполнение занимает больше, чем 3 секунды, вы увидите текст запроса в лог файле. Можете подольше попользоваться базой, чтобы собрать список запросов для анализа. Запросы 1С настолько громоздкие, что даже просто скопировать их из лога и обработать непростая задача. Воспользуемся для этого специальной программой.

Включение логирования запросов замедляет работу системы. Я рекомендую заниматься диагностикой либо в нерабочее время, либо на тестовой базе и сервере, если есть такая возможность. Для применения настроек базы данных, необходимо перезапускать ее. Это может доставить проблем, если с другими базами сервера кто-то работает. Примите это к сведению.

Анализ запросов postgresql с помощью pgFouine

Устанавливаем pgFouine в debian:

# apt-get install pgfouine

Это старая программа, но для наших целей сойдет. Пользоваться ей очень просто. Я не вдавался в подробности настройки и не смотрел возможные параметры. Мне было достаточно сделать вот так:

# pgfouine -file /var/log/postgresql/sql.log > /root/report.html

Забираем файл report.html к себе на компьютер и открываем в браузере. У меня получилось примерно так:

Запрос впечатляет 🙂 Не удивительно, что он тормозит! Сказать, что я был удивлен, это ничего не сказать. Глядя на эти запросы, я понимал, что никакой оптимизации в 1С для работы с postgresql нет. Хотя я очень плохо разбираюсь в sql, знаком поверхностно с синтаксисом, и сам составлял только очень простые запросы. Но даже я вижу, что проблема тормозов в том, что этот запрос просто безобразно огромный. Парсер запроса нахватал в код мусорных символов. В моем случае это символы #011, они присутствую в логе sql.log. Я не знаю, откуда они там берутся, но чтобы получить чистый запрос, их надо убрать. Я скопировал текст запроса в текстовый редактор и сделал замену символов #011 на пробел. В итоге получился синтаксически корректный запрос. В моем случае он выглядит таким образом:

SELECT CASE WHEN (T1._Folder = FALSE) THEN CASE WHEN T1._Marked = TRUE THEN 13 ELSE 12 END ELSE ((-1 + CASE WHEN T1._Marked = TRUE THEN 1 ELSE 0 END) + CASE WHEN (T1._Fld607 = FALSE) THEN 1 ELSE 3 END) END, T1._IDRRef, '\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000'::bytea, T1._ParentIDRRef, T1._Description, CASE WHEN T2.Fld4011_TYPE IS NULL THEN CASE WHEN T1._Fld591RRef IS NOT NULL THEN '\\010'::bytea END ELSE T2.Fld4011_TYPE END, CASE WHEN T2.Fld4011_TYPE IS NULL THEN CASE WHEN T1._Fld591RRef IS NOT NULL THEN '\\000\\000\\000%'::bytea END ELSE T2.Fld4011_RTRef END, CASE WHEN T2.Fld4011_TYPE IS NULL THEN T1._Fld591RRef ELSE T2.Fld4011_RRRef END, T1._Fld595RRef, T1._Fld601RRef, T1._Fld606RRef, T1._Fld607, T1._Fld608, T1._Fld4737RRef, T1._Fld610, COALESCE(CAST(CAST((CAST(CAST((T2.Fld4009_ * 1) AS NUMERIC(22, 8)) / 1 AS NUMERIC(22, 8))) AS NUMERIC(15, 2)) AS NUMERIC(15, 2)),0), CASE WHEN (T2.Fld4011_TYPE = '\\010'::bytea AND T2.Fld4011_RTRef = '\\000\\000\\000 '::bytea) THEN (CAST(CAST(COALESCE(CAST(T6.Fld4265Balance_ AS NUMERIC(27, 3)),0) AS NUMERIC(35, 8)) / CASE WHEN T2.Fld4011_TYPE = '\\010'::bytea AND T2.Fld4011_RTRef = '\\000\\000\\000 '::bytea THEN T10._Fld483 ELSE CAST(NULL AS NUMERIC) END AS NUMERIC(35, 8))) ELSE COALESCE(CAST(T6.Fld4265Balance_ AS NUMERIC(27, 3)),0) END, CASE WHEN (COALESCE(CAST(T8.Fld4212Balance_ AS NUMERIC(27, 3)),0) = 0) THEN 1 ELSE 0 END, CASE WHEN (T2.Fld4011_TYPE = '\\010'::bytea AND T2.Fld4011_RTRef = '\\000\\000\\000 '::bytea) THEN (CAST(CAST((COALESCE(CAST(T6.Fld4265Balance_ AS NUMERIC(27, 3)),0) - COALESCE(CAST(T8.Fld4212Balance_ AS NUMERIC(27, 3)),0)) AS NUMERIC(36, 8)) / CASE WHEN T2.Fld4011_TYPE = '\\010'::bytea AND T2.Fld4011_RTRef = '\\000\\000\\000 '::bytea THEN T10._Fld483 ELSE CAST(NULL AS NUMERIC) END AS NUMERIC(36, 8))) ELSE (COALESCE(CAST(T6.Fld4265Balance_ AS NUMERIC(27, 3)),0) - COALESCE(CAST(T8.Fld4212Balance_ AS NUMERIC(27, 3)),0)) END, T1._Marked, CASE WHEN (T1._Folder = FALSE) THEN TRUE ELSE FALSE END FROM _Reference44 T1 LEFT OUTER JOIN (SELECT T5._Fld4007RRef AS Fld4007RRef, T5._Fld4011_TYPE AS Fld4011_TYPE, T5._Fld4011_RTRef AS Fld4011_RTRef, T5._Fld4011_RRRef AS Fld4011_RRRef, T5._Fld4009 AS Fld4009_ FROM (SELECT T4._Fld4006RRef AS Fld4006RRef, T4._Fld4007RRef AS Fld4007RRef, T4._Fld4008RRef AS Fld4008RRef, MAX(T4._Period) AS MAXPERIOD_ FROM _InfoRg4005 T4 WHERE ((T4._Fld5554 = 0)) AND (T4._Period <= '2017-01-27 00:00:00'::TIMESTAMP AND (((T4._Fld4010 = TRUE AND (T4._Fld4006RRef = '\\204\\232\\225\\2473l\\375\\305J\\023bNdY&s'::bytea)) AND (T4._Fld4008RRef = '\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000'::bytea)))) GROUP BY T4._Fld4006RRef, T4._Fld4007RRef, T4._Fld4008RRef) T3 INNER JOIN _InfoRg4005 T5 ON T3.Fld4006RRef = T5._Fld4006RRef AND T3.Fld4007RRef = T5._Fld4007RRef AND T3.Fld4008RRef = T5._Fld4008RRef AND T3.MAXPERIOD_ = T5._Period WHERE (T5._Fld5554 = 0)) T2 ON (T1._IDRRef = T2.Fld4007RRef) LEFT OUTER JOIN (SELECT T7._Fld4260RRef AS Fld4260RRef, SUM(T7._Fld4265) AS Fld4265Balance_ FROM _AccumRgT4266 T7 WHERE ((T7._Fld5554 = 0)) AND (T7._Period = '3999-11-01 00:00:00'::TIMESTAMP AND ((T7._Fld4259RRef = '\\224\\206\\245\\237\\200\\356j\\370Kp\\252IFC\\324a'::bytea)) AND (T7._Fld4265 <> 0) AND (T7._Fld4265 <> 0)) GROUP BY T7._Fld4260RRef HAVING (SUM(T7._Fld4265)) <> 0) T6 ON (T1._IDRRef = T6.Fld4260RRef) LEFT OUTER JOIN (SELECT T9._Fld4208RRef AS Fld4208RRef, SUM(T9._Fld4212) AS Fld4212Balance_ FROM _AccumRgT4232 T9 WHERE ((T9._Fld5554 = 0)) AND (T9._Period = '3999-11-01 00:00:00'::TIMESTAMP AND ((((T9._Fld4205RRef = '\\224\\206\\245\\237\\200\\356j\\370Kp\\252IFC\\324a'::bytea) AND (T9._Fld4206_TYPE = '\\010'::bytea AND T9._Fld4206_RTRef = '\\000\\000\\000B'::bytea)) AND (T9._Fld4211RRef <> '\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000'::bytea))) AND (T9._Fld4212 <> 0) AND (T9._Fld4212 <> 0)) GROUP BY T9._Fld4208RRef HAVING (SUM(T9._Fld4212)) <> 0) T8 ON (T1._IDRRef = T8.Fld4208RRef) LEFT OUTER JOIN _Reference32 T10 ON (T2.Fld4011_TYPE = '\\010'::bytea AND T2.Fld4011_RTRef = '\\000\\000\\000 '::bytea AND T2.Fld4011_RRRef = T10._IDRRef) AND (T10._Fld5554 = 0) WHERE ((T1._Fld5554 = 0)) AND ((T1._Fld604RRef IN ('\\256")\\314\\021{V}G\\321=\\343U\\243\\367\\344'::bytea, '\\236\\273\\035\\371;t\\035kC{\\024b\\273W\\037\\206'::bytea)) AND (T1._Folder) = TRUE AND (T1._Fld14883 = FALSE) AND (T1._ParentIDRRef IN (SELECT T11._REFFIELDRRef AS REFFIELDRRef FROM tt9 T11))) ORDER BY (T1._Description), (T1._IDRRef) LIMIT 25;

Дальше вы можете разбираться со своими запросами, в зависимости от ваших знаний и возможностей. Я не знал, что делать дальше, для решения своей проблемы. Попытался построить карту запроса с помощью EXPLAIN ANALYZE, но не получилось. Запрос использует какие-то временные таблицы, так что просто скопировать и повторить его не получалось. Выходила ошибка, что какой-то таблицы не существует.

В настоящий момент я получил совет на профильном форуме по моей проблеме. Мне сказали, что ситуация известная и достаточно типичная для 1С. Исправлять ее нужно на стороне самой 1С, изменяя код запроса выборки из виртуальных таблиц на запросы из временных таблиц, соединяя их потом с основной. Это уже задача для программиста. Я в самой 1С не разбираюсь вообще.

Заключение

На текущий момент моя проблема не решена, но стало понятно, в каком направлении двигаться и что делать. В принципе, я изначально, когда стал заниматься этой задачей, предполагал, что проблема именно на стороне 1С из-за сложного запроса и отсутствии оптимизации работы 1С именно с postgresql. Я это понял, потому что с mssql таких тормозов никогда наблюдал на базах такого размера. В данном случае объем базы всего 10 гб, она не очень большая. 15 секунд лопатить запрос на такой базе можно только, если этот запрос ужасен. На деле все так и оказалось.

В процессе разбора ситуации приобрел определенный опыт, который постарался зафиксировать в этой статье. Думаю, он пригодится в будущем, как мне, так и другим пользователям. В интернете не нашел хороших статей по анализу производительности постгрес. Пришлось все собирать по крохам в разных статьях, но больше на форумах. С учетом стоимости лицензии mssql, замена ее на postgresql выглядит весьма обоснованной, так что тема актуальна.

Буду рад любым замечаниям и советам в комментариях. Тема для меня новая, но полезная. Хотелось бы разобраться в работе постгрес.

Напоминаю, что данная статья является частью единого цикла статьей про сервер Debian.

Онлайн курс Основы сетевых технологий

Теоретический курс с самыми базовыми знаниями по сетям. Курс подходит и начинающим, и людям с опытом. Практикующим системным администраторам курс поможет упорядочить знания и восполнить пробелы. А те, кто только входит в профессию, получат на курсе базовые знания и навыки, без воды и избыточной теории. После обучения вы сможете ответить на вопросы:
На каком уровне модели OSI могут работать коммутаторы;
Как лучше организовать работу сети организации с множеством отделов;
Для чего и как использовать технологию VLAN;
Для чего сервера стоит выносить в DMZ;
Как организовать объединение филиалов и удаленный доступ сотрудников по vpn;
и многое другое.
Уже знаете ответы на вопросы выше? Или сомневаетесь? Попробуйте пройти тест по основам сетевых технологий. Всего 53 вопроса, в один цикл теста входит 10 вопросов в случайном порядке. Поэтому тест можно проходить несколько раз без потери интереса. Бесплатно и без регистрации. Все подробности на странице .
Помогла статья? Подписывайся на telegram канал автора
Анонсы всех статей, плюс много другой полезной и интересной информации, которая не попадает на сайт.
Начало работы с типами данных PostgreSQL
Резюме : в этом руководстве вы узнаете о типах данных PostgreSQL , включая логические, символьные, числовые, временные, массивы, json, uuid и специальные типы.
Обзор типов данных PostgreSQL
PostgreSQL поддерживает следующие типы данных:
Boolean
Тип данных Boolean может содержать одно из трех возможных значений: true, false или null. Вы используете boolean или bool ключевое слово для объявления столбца с логическим типом данных.
Когда вы вставляете данные в логический столбец, PostgreSQL преобразует их в логическое значение
1 , да , y , t , true значения преобразуются в true
0 , нет , false , f значения преобразуются в false .
Когда вы выбираете данные из логического столбца, PostgreSQL преобразует значения обратно, например, t в true, f в false и space to null .
Character
PostgreSQL предоставляет три типа символьных данных: CHAR (n) , VARCHAR (n) и TEXT
CHAR (n) — это символ фиксированной длины с заполненным пробелом. Если вы вставляете строку короче, чем длина столбца, PostgreSQL заполняет пробелы. Если вы вставите строку, длина которой превышает длину столбца, PostgreSQL выдаст ошибку.
VARCHAR (n) — символьная строка переменной длины.С VARCHAR (n) вы можете сохранить до n символов. PostgreSQL не заполняет пробелы, если хранимая строка короче, чем длина столбца.
ТЕКСТ — символьная строка переменной длины. Теоретически текстовые данные представляют собой символьную строку неограниченной длины.
Числовые
PostgreSQL предоставляет два разных типа чисел:
Целые
В PostgreSQL есть три вида целых чисел:
Маленькое целое ( SMALLINT ) — это 2-байтовое целое число со знаком, которое имеет диапазон от — От 32 768 до 32 767.
Целое число ( INT ) — 4-байтовое целое число в диапазоне от -2 147 483 648 до 2 147 483 647.
Serial — это то же самое, что и integer, за исключением того, что PostgreSQL автоматически генерирует и заполняет значения в столбце SERIAL . Это похоже на столбец AUTO_INCREMENT в MySQL или столбец AUTOINCREMENT в SQLite.
Число с плавающей запятой
Существует три основных типа чисел с плавающей запятой:
float (n) — число с плавающей запятой, точность которого не менее n, но не более 8 байтов.
вещественное или float8 — это 4-байтовое число с плавающей запятой.
числовое или числовое (p, s) — действительное число с p цифрами с числом s после десятичной точки. Числовое значение (p, s) — точное число.
Типы временных данных
Типы временных данных позволяют хранить данные даты и / или времени. PostgreSQL имеет пять основных типов временных данных:
DATE хранит только даты.
ВРЕМЯ хранит значения времени суток.
TIMESTAMP хранит значения даты и времени.
TIMESTAMPTZ — это тип данных метки времени с учетом часовых поясов. Это сокращение от метки времени с указанием часового пояса.
ИНТЕРВАЛ хранит периоды времени.
TIMESTAMPTZ — это расширение PostgreSQL для временных типов данных стандарта SQL.
Массивы
В PostgreSQL вы можете хранить массив строк, массив целых чисел и т. Д., в столбцах массива. Массив может пригодиться в некоторых ситуациях, например, при хранении дней недели, месяцев в году.
JSON
PostgreSQL предоставляет два типа данных JSON: JSON и JSONB для хранения данных JSON.
Тип данных JSON хранит простые данные JSON, которые требуют повторного анализа для каждой обработки, а тип данных JSONB хранит данные JSON в двоичном формате, который быстрее обрабатывается, но медленнее вставляется.Кроме того, JSONB поддерживает индексацию, что может быть преимуществом.
UUID
Тип данных UUID позволяет хранить универсальные уникальные идентификаторы, определенные RFC 4122. Значения UUID гарантируют лучшую уникальность, чем SERIAL , и могут использоваться для скрытия конфиденциальных данных, открытых для публики, таких как значения id в URL.
Специальные типы данных
Помимо примитивных типов данных, PostgreSQL также предоставляет несколько специальных типов данных, связанных с геометрическими и сетевыми.
ящик — прямоугольный ящик.
строка — набор точек.
точка — геометрическая пара чисел.
lseg — отрезок линии.
многоугольник — замкнутый геометрический.
inet — IP4-адрес.
macaddr — MAC-адрес.
В этом руководстве мы познакомили вас с типами данных PostgreSQL, чтобы вы могли использовать их для создания таблиц в следующем руководстве.
Было ли это руководство полезным?
Да Нет
Postgres String to Date and Timestamp
Строка для даты и отметки времени
Функция TO_DATE в PostgreSQL используется для преобразования строк в даты . Его синтаксис: TO_DATE (текст, текст) , а тип возврата — , дата .
Функция TO_TIMESTAMP преобразует строковые данные в метки времени с часовым поясом. Его синтаксис: to_timestamp (текст, текст) .
Примеры использования:
Дата как ввод Вызов функции
2017/02/08 TO_DATE (дата; ГГГГ / ММ / ДД)
02.08.2017 TO_DATE (дата; 'ДД / ММ / ГГГГ')
08-02-2017 TO_DATE (дата, «ДД-ММ-ГГГГ»)
02082017 TO_DATE (дата ',' MMDDYY ')
8 февраля 2017 TO_DATE (дата ',' Месяц ДД, ГГГГ ')
Дата и время как ввод Функциональный вызов
08.02.2017 10:59:00 TO_TIMESTAMP (datetime, 'MM / DD / YYYY Hh34: MI: SS') или TO_TIMESTAMP (datetime, 'MM / DD / YYYY Hh22: MI: SS') , если в 12-часовом формате
2017/08/02 10:59:00 TO_TIMESTAMP (datetime, ‘YYYY / MM / DD Hh34: MI: SS ') или TO_TIMESTAMP (datetime,‘ YYYY / MM / DD Hh22: MI: SS ») , если в 12-часовом формате
08-02-2017 10:59:00 TO_TIMESTAMP (datetime, ‘DD-MM-YYYY Hh34: MI: SS ') или TO_TIMESTAMP (datetime,‘ DD-MM-YYYY Hh22: MI: SS ») , если в 12-часовом формате
10:15:20 TO_TIMESTAMP (datetime, 'Hh34: MI: SS') :: TIME или TO_TIMESTAMP (datetime, 'Hh22: MI: SS') :: TIME , если в 12-часовом формате
Строка в число
В PostgreSQL строки могут быть преобразованы в целое число или двойное с помощью функции CAST или аннотации :: .Оба эквивалентны, и вы можете выбрать то, что вам нравится.
Пример использования:
Число как ввод Функциональный вызов
5 CAST (число AS int) или альтернативно number :: int
2,3 CAST (число как двойная точность) или альтернативно number :: double precision
Если столбец содержит денежных данных , вы должны помнить, что числа с плавающей запятой не следует использовать для обработки денег из-за возможности ошибок округления.
Денежное значение можно преобразовать в числовое без потери точности. Преобразование в другие типы может потенциально потерять точность и также должно выполняться в два этапа:
Число как ввод Функциональный вызов
13,4 Number :: money :: numeric :: float8
Number :: money Избегайте преобразования его в float из-за возможных ошибок округления
Миграция базы данных PostgreSQL в PostgreSQL — pgloader 3.4.1 документация
Эта команда инструктирует pgloader загружать данные из соединения с базой данных. Поддерживается автоматическое обнаружение схемы, включая сборку индексы, ограничения первичных и внешних ключей. Набор литья по умолчанию правила предоставляются и могут быть перегружены и добавлены командой.
Вот небольшой пример переноса базы данных с сервера PostgreSQL на другой:
загрузить базу данных из pgsql: // localhost / pgloader в pgsql: // localhost / copy включая только имена таблиц, соответствующие 'битам', ~ / utilisateur / в схеме 'mysql' включая только имена таблиц, соответствующие ~ / geolocations / в схеме 'public' ;
Спецификация источника базы данных PostgreSQL: FROM
Должен быть URL-адрес подключения, указывающий на базу данных PostgreSQL.
См. Раздел SOURCE CONNECTION STRING выше для получения подробной информации о том, как писать строка подключения.
pgsql: // [пользователь [: пароль] @] [netloc] [: порт] [/ dbname] [? Option = value & ...]
Варианты миграции базы данных
PostgreSQL: с
При загрузке из базы данных PostgreSQL доступны следующие параметры: поддерживается, а предложение по умолчанию WITH : без усечения , создать схему , создать таблицы , включить удаление , создать индексы , последовательности сброса , внешних ключей , идентификаторов нижнего регистра , уникальные имена индексов , переиндексировать .
включая падение
Когда этот параметр указан, pgloader удаляет все таблицы в целевом объекте. База данных PostgreSQL, имена которых присутствуют в базе данных MySQL. Этот опция позволяет использовать одну и ту же команду несколько раз подряд, пока вы выясняете все варианты, начиная автоматически с чистого Окружающая среда. Обратите внимание, что CASCADE используется для обеспечения того, чтобы таблицы отбрасываются, даже если на них указывают внешние ключи. Это именно для чего предназначен include drop : удалить все целевые таблицы и воссоздайте их.
Следует проявлять особую осторожность при использовании , включая дроп , так как он каскадировать на все объекта, ссылающиеся на целевые таблицы, возможно включая другие таблицы, которые не загружаются из исходной БД.
без дропа
Когда эта опция указана, pgloader не будет включать DROP заявление при загрузке данных.
усечь
Когда эта опция указана, pgloader выдает команду TRUNCATE против каждой таблицы PostgreSQL непосредственно перед загрузкой в нее данных.
без усечения
Когда эта опция указана, pgloader не выдает команду TRUNCATE .
отключить триггеры
Когда эта опция указана, pgloader выдает ALTER TABLE… DISABLE Команда TRIGGER ALL для целевой таблицы PostgreSQL перед копированием данные, затем команда ALTER TABLE… ENABLE TRIGGER ALL , как только КОПИРОВАНИЕ выполнено.
Эта опция позволяет загружать данные в уже существующую таблицу, игнорируя ограничений внешнего ключа и определяемых пользователем триггеров и может привести к недопустимые ограничения внешнего ключа после загрузки данных.Использовать с забота.
создать таблицы
Когда указан этот параметр, pgloader создает таблицу, используя мета данные, найденные в файле MySQL , который должен содержать список полей с их тип данных. Стандартное преобразование типа данных из DBF в PostgreSQL сделано.
не создавать таблицы
Когда указан этот параметр, pgloader пропускает создание таблицы перед При загрузке данных целевые таблицы должны уже существовать.
Кроме того, при использовании не создавать таблицы pgloader извлекает метаданные из текущая целевая база данных и проверяет приведение типов, затем удаляет ограничения и индексы перед загрузкой данных и их установка обратно снова после завершения загрузки.
создать индексы
Когда этот параметр указан, pgloader получает определения всех индексы, найденные в базе данных MySQL, и создают тот же набор индексов определения базы данных PostgreSQL.
не создавать индексы
Когда указан этот параметр, pgloader пропускает создание индексов.
индексы падения
Когда эта опция указана, pgloader удаляет индексы в целевой база данных перед загрузкой данных, и создает их снова в конце копии данных.
переиндекс
Когда используется эта опция, pgloader удаляет индексы и перед загрузка данных и создают индексы после загрузки данных.
схема удаления
Когда указан этот параметр, pgloader удаляет целевую схему в целевая база данных PostgreSQL перед ее повторным созданием и все объекты это содержит. По умолчанию целевые схемы не отбрасываются.
внешние ключи
Когда этот параметр указан, pgloader получает определения всех внешние ключи, найденные в базе данных MySQL, и создают тот же набор определения внешнего ключа для базы данных PostgreSQL.
без внешних ключей
Когда указан этот параметр, pgloader пропускает создание внешних ключей.
последовательности сброса
Когда этот параметр указан, в конце загрузки данных и после индексы были созданы, pgloader сбрасывает все PostgreSQL последовательности, созданные до текущего максимального значения столбца, в котором они прикреплен к.
Параметры только схема и только данные не влияют на это вариант.
сброс без последовательностей
Когда указан этот параметр, pgloader пропускает последовательность сброса после нагрузка.
Параметры только схема и только данные не влияют на это вариант.
идентификаторы нижнего регистра
Когда указан этот параметр, pgloader преобразует все идентификаторы MySQL. (имена таблиц, имена индексов, имена столбцов) до в нижнем регистре , за исключением PostgreSQL зарезервировал ключевых слова.
Зарезервированные ключевые слова PostgreSQL определяются динамически с помощью системная функция pg_get_
PostgreSQL — общие — Как преобразовать целое число в строку в функциях
2012/3/20 Крис Анджелико [скрытый адрес электронной почты]>:
> во вторник, 20 марта 2012 г., в 19:14, Стефан Келлер [скрытый адрес электронной почты]> написал:
>> Но это работает, только если ввод уже является чистым списком цифровых символов!
>> Любое другое, кроме этого, вызовет ошибку:
>>
>> postgres = # ВЫБРАТЬ ’10’ :: int;
>>
>> После серьезных попыток справиться с чем-либо, возможно, в качестве входной строки, я
>> нашел это:
>>
>> postgres = # ВЫБРАТЬ to_number (‘0’ || mytextcolumn,
>> 99999999999.000 ‘) :: int FROM mytable;
>
> Я столкнулся с той же проблемой, особенно с семантикой типа C
> функция атой. Некоторое обсуждение этого списка привело к появлению нескольких вариантов.
>
> Начало потока, включая одну возможность:
> http://osdir.com/ml/postgresql-pgsql-general/2012-03/msg00090.html
> Еще один хорошо проработанный вариант с немного другой семантикой:
> http://osdir.com/ml/postgresql-pgsql-general/2012-03/msg00094.html
>
> Надеюсь, это поможет!
>
> ChrisA
Ссылаясь на вашу последнюю подсказку, это был ответ Тома:
> Можете ли вы использовать здесь to_number ()? Это похоже на что-то вроде
> cast (to_number (‘0’ || field :: varchar, ‘999999999.’) как int)
> может дать то поведение, которое вам нужно, и быстрый тест, кажется, указывает
> что это примерно в 4 раза быстрее, чем исходная функция:
Мне очень приятно, что я нашел почти то же решение, что и
Том с моим предложением:
to_number (‘0’ || mytextcolumn, ‘99999999999.000 ‘) :: int
> …
> Надеюсь, есть более чистый способ написать это без длинного списка девяток в
> строка формата, и если поле допускает значение NULL, я думаю, вам, вероятно, понадобится
> объединить (…, 0) вокруг этого.
Был бы рад найти более чистый способ, но пока это серебряная пуля: ->
— Стефан
—
Отправлено через список рассылки pgsql-general ([скрытый адрес электронной почты])
Чтобы внести изменения в подписку:
http: // www.postgresql.org/mailpref/pgsql-general
Типы данных PostgreSQL
Домашняя страница
Тестирование
Назад
Agile Testing
BugZilla
Cucumber
Тестирование базы данных ETL
JIRA
Назад
JUnit
LoadRunner
Ручное тестирование
Мобильное тестирование
Mantis
Почтальон
QTP
Назад
Центр качества (ALM)
RPA
Тестирование SAP
Selenium
SoapUI
Управление тестированием
TestLink
SAP
Назад
ABAP
APO
Начинающий
Basis
BODS
BI
BPC
CO
Назад
CRM
Crystal Reports
FICO
HANA
HR
MM
QM
Заработная плата
Назад
PI / PO
PP
SD
SAPUI5
Безопасность
Менеджер решений
Successfactors
SAP Tutorials
Web
Назад
Apache
AngularJS
ASP.Net
C
C #
C ++
CodeIgniter
СУБД
JavaScript
Назад
Java
JSP
Kotlin
Linux
MariaDB
MS Access
MYSQL
Node. js
Perl
Назад
PHP
PL / SQL
PostgreSQL
Python
ReactJS
Ruby & Rails
Scala
SQL
SQLite
Назад
SQL Server
UML
VB.Net
VBScript
Веб-службы
WPF
Обязательно изучите!
Назад
Бухгалтерский учет
Алгоритмы
Android
Блокчейн
Бизнес-аналитик
Создание веб-сайта
Облачные вычисления
COBOL
Дизайн компилятора
Назад
Встроенные системы
Этический взлом
Учебники Excel
Программирование Go
IoT
ITIL
Jenkins
MIS
Сеть
Операционная система
Назад
Подготовка
PMP
Photoshop
Управление проектами
Обзоры
Salesforce
SEO
Разработка программного обеспечения
VBA
900 36
Big Data
Назад
AWS
BigData
Cassandra
Cognos
Хранилище данных
DevOps
HBase
Назад
Hive
Inform
MongoDB
NiFi
Поддерживаемые типы и их сопоставления
Ниже перечислены встроенные сопоставления при чтении и записи типов CLR в типы PostgreSQL.
Обратите внимание, что в дополнение к приведенному ниже, enum и составные сопоставления задокументированы на отдельной странице. Также обратите внимание, что существует несколько плагинов для добавления поддержки большего количества сопоставлений (например, пространственная поддержка PostGIS), они перечислены в меню Типы.
Чтение сопоставлений
Ниже показаны сопоставления, используемые при чтении значений.
Тип по умолчанию возвращается при использовании NpgsqlCommand.ExecuteScalar () , NpgsqlDataReader.GetValue () и подобных методов.
Вы можете читать как другие типы, вызвав NpgsqlDataReader.GetFieldValue () .
Типы, зависящие от поставщика, возвращаются NpgsqlDataReader.GetProviderSpecificValue () .
Тип PostgreSQL Тип .NET по умолчанию Тип, зависящий от поставщика Другие типы .NET
логический булев
smallint короткий байт, sbyte, int, long, float, double, decimal
целое внутренний байт, короткий, длинный, плавающий, двойной, десятичный
bigint длинный длинный, байт, короткий, int, float, double, десятичный
реальный поплавок двойной
двойная точность двойной
числовой десятичный байт, короткое, целое, длинное, с плавающей точкой, двойное число
деньги десятичный
текст строка символ []
знак различный строка символ []
символ строка символ []
citext строка символ []
JSON строка символ []
jsonb строка символ []
xml строка символ []
точка NpgsqlPoint
lseg NpgsqlLSeg
путь NpgsqlPath
многоугольник NpgsqlPolygon
линия NpgsqlLine
круг NpgsqlCircle
ящик NpgsqlBox
бит (1) булев BitArray
бит (n) BitArray
переменная долота BitArray
hstore Словарь <строка, строка>
uuid Guid
cidr (IP-адрес, целое) NpgsqlInet
инет IP-адрес (IP-адрес, интервал) NpgsqlInet
macaddr Физический адрес
tsquery NpgsqlTsQuery
цвектор NpgsqlTsVector
дата DateTime Npgsql Дата
интервал TimeSpan NpgsqlTimeSpan
метка времени без часового пояса DateTime (не указано) NpgsqlDateTime
метка времени с часовым поясом DateTime (местное) NpgsqlDateTime DateTimeOffset
время без часового пояса TimeSpan
время с часовым поясом DateTimeOffset DateTimeOffset, DateTime, TimeSpan
байт байт []
oid uint
xid uint
cid uint
oidvector uint []
название строка символ []
(внутренний) символ символ байт, короткое, целое, длинное
геометрия (PostGIS) PostgisGeometry
запись объект []
композитные типы Т
диапазон подтипов NpgsqlRange <элемент>
типы перечислений TEnum
типы массивов Массив (типа элемента)
По умолчанию.Столбец типа NET указывает тип данных NpgsqlDataReader.GetValue () будет возвращать.
NpgsqlDataReader.GetProviderSpecificValue вернет значение типа данных, указанного в столбце типа, зависящего от поставщика, или типа .NET по умолчанию, если специализация отсутствует.
Наконец, третий столбец определяет другие типы CLR, которые Npgsql поддерживает для типа данных PostgreSQL. Их можно получить, вызвав NpgsqlDataReader.GetBoolean () , GetByte () , GetDouble () и т. Д.или через GetFieldValue () .
Запись сопоставлений
Есть три правила, которые определяют тип PostgreSQL, отправляемый для параметра:
Если установлен параметр NpgsqlDbType , он используется.
Если установлен параметр DataType , он используется.
Если задан параметр DbType , он используется.
Если ничего из вышеперечисленного не установлено, тип серверной части будет выведен из типа значения CLR.
Обратите внимание, что для DateTime и NpgsqlDateTime атрибут Kind определяет, использовать ли временную метку или timestamptz .
NpgsqlDbType DbType Тип PostgreSQL Допустимые типы .NET
Логическое логический логический булев
Смоллинт Int16 smallint короткий
Целое число Int32 целое число внутренний
Bigint Int64 bigint длинный
Реальный Одноместный реал поплавок
Двойной Двойной двойная точность двойной
Числовой Десятичное, VarNumeric числовой десятичный
Деньги Валюта деньги десятичный
Текст Строка, StringFixedLength, AnsiString, AnsiStringFixedLength текст строка, char [], char
Варчар различный символ строка, char [], char
Char символ строка, char [], char
Citext citext строка, char [], char
Json JSON строка, char [], char
Jsonb jsonb строка, char [], char
Xml xml строка, char [], char
точка балл NpgsqlPoint
LSeg lseg NpgsqlLSeg
Путь путь NpgsqlPath
Многоугольник многоугольник NpgsqlPolygon
Линия строка NpgsqlLine
Круг круг NpgsqlCircle
Ящик ящик NpgsqlBox
Насадка бит BitArray, bool, строка
Варбит бит разная BitArray, bool, строка
Hstore hstore IDictionary <строка, строка>
Uuid uuid Guid
Сидр cidr ValueTuple , IP-адрес, NpgsqlInet
инет инет ValueTuple , IP-адрес, NpgsqlInet
MacAddr macaddr Физический адрес
TsQuery tsquery NpgsqlTsQuery
ЦВектор цвектор NpgsqlTsVector
Дата Дата дата DateTime, NpgsqlDate
Интервал интервал TimeSpan, NpgsqlTimeSpan
Отметка времени DateTime, DateTime2 метка времени без часового пояса DateTime, NpgsqlDateTime
Отметка времениTz DateTimeOffset метка времени с часовым поясом DateTime, DateTimeOffset, NpgsqlDateTime
Время Время время без часового пояса TimeSpan
TimeTz время с часовым поясом DateTimeOffset, DateTime, TimeSpan
Байт двоичный байт байт [], сегмент массива <байт>
Oid oid uint
Xid xid uint
Сид cid uint
Oidvector oidvector uint []
Имя название строка, char [], char
InternalChar (внутренний) символ байт
Композитный составные типы Т
Диапазон | (другой NpgsqlDbType) диапазон типов NpgsqlRange <элемент>
Enum перечисления типов TEnum
Массив | (другой NpgsqlDbType) типы массивов Массив, IList, IList
Примечания при использовании диапазона и массива, побитового или NpgsqlDbType.Range или NpgsqlDbType.Array с дочерним типом. Например, чтобы построить NpgsqlDbType для int4range , напишите NpgsqlDbType.Range | NpgsqlDbType.Integer . Чтобы построить NpgsqlDbType для int [] , напишите NpgsqlDbType.Array | NpgsqlDbType.Integer .
Для получения информации о перечислениях см. Страницу «Перечисления и составы».
Тип .NET Автоматический вывод типа PostgreSQL
булев логический
байт smallint
сбайт smallint
короткий smallint
внутренний целое число
длинный bigint
поплавок реал
двойной двойная точность
десятичный числовой
строка текст
символ [] текст
знак текст
NpgsqlPoint балл
NpgsqlLSeg lseg
NpgsqlPath путь
NpgsqlPolygon многоугольник
NpgsqlLine строка
NpgsqlCircle круг
NpgsqlBox ящик
BitArray бит разная
Guid uuid
IP-адрес инет
NpgsqlInet инет
Физический адрес macaddr
NpgsqlTsQuery tsquery
NpgsqlTsVector цвектор
NpgsqlDate дата
NpgsqlDateTime метка времени без часового пояса
DateTime метка времени без часового пояса
DateTimeOffset метка времени с часовым поясом
TimeSpan интервал
байт [] байт
Пользовательский композитный тип составные типы
NpgsqlRange диапазон типов
Типы перечислений перечисления типов
Типы массивов типы массивов
.

Дата как ввод	Вызов функции
2017/02/08	`TO_DATE (дата; ГГГГ / ММ / ДД)`
02.08.2017	`TO_DATE (дата; 'ДД / ММ / ГГГГ')`
08-02-2017	`TO_DATE (дата, «ДД-ММ-ГГГГ»)`
02082017	`TO_DATE (дата ',' MMDDYY ')`
8 февраля 2017	`TO_DATE (дата ',' Месяц ДД, ГГГГ ')`

Дата и время как ввод	Функциональный вызов
08.02.2017 10:59:00	`TO_TIMESTAMP (datetime, 'MM / DD / YYYY Hh34: MI: SS')` или `TO_TIMESTAMP (datetime, 'MM / DD / YYYY Hh22: MI: SS')` , если в 12-часовом формате
2017/08/02 10:59:00	`TO_TIMESTAMP (datetime, ‘YYYY / MM / DD Hh34: MI: SS ')` или `TO_TIMESTAMP (datetime,‘ YYYY / MM / DD Hh22: MI: SS »)` , если в 12-часовом формате
08-02-2017 10:59:00	`TO_TIMESTAMP (datetime, ‘DD-MM-YYYY Hh34: MI: SS ')` или `TO_TIMESTAMP (datetime,‘ DD-MM-YYYY Hh22: MI: SS »)` , если в 12-часовом формате
10:15:20	`TO_TIMESTAMP (datetime, 'Hh34: MI: SS') :: TIME` или `TO_TIMESTAMP (datetime, 'Hh22: MI: SS') :: TIME` , если в 12-часовом формате

Число как ввод	Функциональный вызов
5	`CAST (число AS int) или альтернативно number :: int`
2,3	`CAST (число как двойная точность) или альтернативно number :: double precision`

Число как ввод	Функциональный вызов
13,4	`Number :: money :: numeric :: float8`

Тип PostgreSQL	Тип .NET по умолчанию	Тип, зависящий от поставщика	Другие типы .NET
логический	булев
smallint	короткий		байт, sbyte, int, long, float, double, decimal
целое	внутренний		байт, короткий, длинный, плавающий, двойной, десятичный
bigint	длинный		длинный, байт, короткий, int, float, double, десятичный
реальный	поплавок		двойной
двойная точность	двойной
числовой	десятичный		байт, короткое, целое, длинное, с плавающей точкой, двойное число
деньги	десятичный
текст	строка		символ []
знак различный	строка		символ []
символ	строка		символ []
citext	строка		символ []
JSON	строка		символ []
jsonb	строка		символ []
xml	строка		символ []
точка	NpgsqlPoint
lseg	NpgsqlLSeg
путь	NpgsqlPath
многоугольник	NpgsqlPolygon
линия	NpgsqlLine
круг	NpgsqlCircle
ящик	NpgsqlBox
бит (1)	булев		BitArray
бит (n)	BitArray
переменная долота	BitArray
hstore	Словарь <строка, строка>
uuid	Guid
cidr	(IP-адрес, целое)		NpgsqlInet
инет	IP-адрес	(IP-адрес, интервал)	NpgsqlInet
macaddr	Физический адрес
tsquery	NpgsqlTsQuery
цвектор	NpgsqlTsVector
дата	DateTime	Npgsql Дата
интервал	TimeSpan	NpgsqlTimeSpan
метка времени без часового пояса	DateTime (не указано)	NpgsqlDateTime
метка времени с часовым поясом	DateTime (местное)	NpgsqlDateTime	DateTimeOffset
время без часового пояса	TimeSpan
время с часовым поясом	DateTimeOffset		DateTimeOffset, DateTime, TimeSpan
байт	байт []
oid	uint
xid	uint
cid	uint
oidvector	uint []
название	строка		символ []
(внутренний) символ	символ		байт, короткое, целое, длинное
геометрия (PostGIS)	PostgisGeometry
запись	объект []
композитные типы	Т
диапазон подтипов	NpgsqlRange <элемент>
типы перечислений	TEnum
типы массивов	Массив (типа элемента)

NpgsqlDbType	DbType	Тип PostgreSQL	Допустимые типы .NET
Логическое	логический	логический	булев
Смоллинт	Int16	smallint	короткий
Целое число	Int32	целое число	внутренний
Bigint	Int64	bigint	длинный
Реальный	Одноместный	реал	поплавок
Двойной	Двойной	двойная точность	двойной
Числовой	Десятичное, VarNumeric	числовой	десятичный
Деньги	Валюта	деньги	десятичный
Текст	Строка, StringFixedLength, AnsiString, AnsiStringFixedLength	текст	строка, char [], char
Варчар		различный символ	строка, char [], char
Char		символ	строка, char [], char
Citext		citext	строка, char [], char
Json		JSON	строка, char [], char
Jsonb		jsonb	строка, char [], char
Xml		xml	строка, char [], char
точка		балл	NpgsqlPoint
LSeg		lseg	NpgsqlLSeg
Путь		путь	NpgsqlPath
Многоугольник		многоугольник	NpgsqlPolygon
Линия		строка	NpgsqlLine
Круг		круг	NpgsqlCircle
Ящик		ящик	NpgsqlBox
Насадка		бит	BitArray, bool, строка
Варбит		бит разная	BitArray, bool, строка
Hstore		hstore	IDictionary <строка, строка>
Uuid		uuid	Guid
Сидр		cidr	ValueTuple , IP-адрес, NpgsqlInet
инет		инет	ValueTuple , IP-адрес, NpgsqlInet
MacAddr		macaddr	Физический адрес
TsQuery		tsquery	NpgsqlTsQuery
ЦВектор		цвектор	NpgsqlTsVector
Дата	Дата	дата	DateTime, NpgsqlDate
Интервал		интервал	TimeSpan, NpgsqlTimeSpan
Отметка времени	DateTime, DateTime2	метка времени без часового пояса	DateTime, NpgsqlDateTime
Отметка времениTz	DateTimeOffset	метка времени с часовым поясом	DateTime, DateTimeOffset, NpgsqlDateTime
Время	Время	время без часового пояса	TimeSpan
TimeTz		время с часовым поясом	DateTimeOffset, DateTime, TimeSpan
Байт	двоичный	байт	байт [], сегмент массива <байт>
Oid		oid	uint
Xid		xid	uint
Сид		cid	uint
Oidvector		oidvector	uint []
Имя		название	строка, char [], char
InternalChar		(внутренний) символ	байт
Композитный		составные типы	Т
Диапазон \| (другой NpgsqlDbType)		диапазон типов	NpgsqlRange <элемент>
Enum		перечисления типов	TEnum
Массив \| (другой NpgsqlDbType)		типы массивов	Массив, IList, IList