PostgreSQL - Краткое руководство

PostgreSQL - это мощная объектно-реляционная база данных с открытым исходным кодом. У него более 15 лет активной фазы разработки и проверенная архитектура, которая заработала прочную репутацию за надежность, целостность и корректность данных.

Это руководство поможет вам быстро начать работу с PostgreSQL и научит вас программировать на PostgreSQL.

Что такое PostgreSQL?

PostgreSQL (произносится как post-gress-Q-L) - это система управления реляционными базами данных (СУБД) с открытым исходным кодом, разработанная всемирной командой добровольцев. PostgreSQL не контролируется какой-либо корпорацией или другим частным лицом, и исходный код доступен бесплатно.

Краткая история PostgreSQL

PostgreSQL, первоначально называвшийся Postgres, был создан в UCB профессором компьютерных наук Майклом Стоунбрейкером. Stonebraker основал Postgres в 1986 году как продолжение своего предшественника Ingres, который теперь принадлежит Computer Associates.

1977-1985 - Разработан проект INGRES.
- Доказательство концепции для реляционных баз данных
- Основал компанию Ingres в 1980 году.
- Куплен Computer Associates в 1994 году.
1986-1994 - ПОСТГРЭС
- Разработка концепций INGRES с упором на объектную ориентацию и язык запросов - Quel
- Кодовая база INGRES не использовалась в качестве основы для POSTGRES
- Коммерциализируется как Illustra (куплено Informix, куплено IBM)
1994-1995 - Postgres95
- Поддержка SQL была добавлена в 1994 г.
- Выпущен как Postgres95 в 1995 г.
- Переиздан как PostgreSQL 6.0 в 1996 г.
- Создание глобальной группы разработчиков PostgreSQL

Ключевые особенности PostgreSQL

PostgreSQL работает во всех основных операционных системах, включая Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64) и Windows. Он поддерживает текст, изображения, звуки и видео и включает программные интерфейсы для C / C ++, Java, Perl, Python, Ruby, Tcl и Open Database Connectivity (ODBC).

PostgreSQL поддерживает большую часть стандарта SQL и предлагает множество современных функций, включая следующие:

Сложные SQL-запросы
Подвыборки SQL
Внешние ключи
Trigger
Views
Transactions
Многоверсионный контроль параллелизма (MVCC)
Потоковая репликация (начиная с 9.0)
Горячее резервирование (по состоянию на 9.0)

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

Типы данных
Functions
Operators
Агрегатные функции
Индексные методы

Поддержка процедурных языков

PostgreSQL поддерживает четыре стандартных процедурных языка, что позволяет пользователям писать собственный код на любом из языков и может выполняться сервером базы данных PostgreSQL. Эти процедурные языки - PL / pgSQL, PL / Tcl, PL / Perl и PL / Python. Кроме того, также поддерживаются другие нестандартные процедурные языки, такие как PL / PHP, PL / V8, PL / Ruby, PL / Java и др.

Чтобы начать понимать основы PostgreSQL, сначала давайте установим PostgreSQL. В этой главе рассказывается об установке PostgreSQL на платформах Linux, Windows и Mac OS.

Установка PostgreSQL в Linux / Unix

Следуйте приведенным ниже инструкциям, чтобы установить PostgreSQL на свой компьютер с Linux. Убедитесь, что вы вошли какroot перед тем, как продолжить установку.

Выберите номер версии PostgreSQL, которую вы хотите, и, насколько это возможно, платформу, которую вы хотите от EnterpriseDB.
Я загрузил postgresql-9.2.4-1-linux-x64.runдля моей 64-битной машины CentOS-6. Теперь давайте выполним это следующим образом -

[root@host]# chmod +x postgresql-9.2.4-1-linux-x64.run
[root@host]# ./postgresql-9.2.4-1-linux-x64.run
------------------------------------------------------------------------
Welcome to the PostgreSQL Setup Wizard.

------------------------------------------------------------------------
Please specify the directory where PostgreSQL will be installed.

Installation Directory [/opt/PostgreSQL/9.2]:

После того, как вы запустите установщик, он задаст вам несколько основных вопросов, таких как местоположение установки, пароль пользователя, который будет использовать базу данных, номер порта и т. Д. Поэтому оставьте все значения по умолчанию, кроме пароля, который вы можете указать. по вашему выбору. Он установит PostgreSQL на ваш компьютер с Linux и отобразит следующее сообщение:

Please wait while Setup installs PostgreSQL on your computer.

 Installing
 0% ______________ 50% ______________ 100%
 #########################################

-----------------------------------------------------------------------
Setup has finished installing PostgreSQL on your computer.

Выполните следующие шаги после установки, чтобы создать свою базу данных -

[root@host]# su - postgres
Password:
bash-4.1$ createdb testdb bash-4.1$ psql testdb
psql (8.4.13, server 9.2.4)

test=#

Вы можете запустить / перезапустить сервер postgres, если он не запущен, используя следующую команду -

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

Если ваша установка была правильной, у вас будет приглашение PotsgreSQL test=# как показано выше.

Установка PostgreSQL в Windows

Следуйте приведенным ниже инструкциям, чтобы установить PostgreSQL на свой компьютер с Windows. Убедитесь, что вы отключили сторонний антивирус во время установки.

Выберите номер версии PostgreSQL, которую вы хотите, и, насколько это возможно, платформу, которую вы хотите от EnterpriseDB.
Я загрузил postgresql-9.2.4-1-windows.exe для своего ПК с Windows, работающего в 32-битном режиме, поэтому давайте запустим postgresql-9.2.4-1-windows.exeкак администратор для установки PostgreSQL. Выберите место, где вы хотите его установить. По умолчанию он устанавливается в папке Program Files.

Следующим шагом процесса установки будет выбор каталога, в котором будут храниться ваши данные. По умолчанию он хранится в каталоге «данные».

Затем программа установки запрашивает пароль, чтобы вы могли использовать свой любимый пароль.

Следующий шаг; оставьте порт по умолчанию.

На следующем шаге, когда меня спросили «Locale», я выбрал «English, United States».
Установка PostgreSQL в вашу систему занимает некоторое время. По завершении процесса установки вы увидите следующий экран. Снимите флажок и нажмите кнопку Готово.

После завершения процесса установки вы можете получить доступ к pgAdmin III, StackBuilder и оболочке PostgreSQL из меню программ в PostgreSQL 9.2.

Установка PostgreSQL на Mac

Следуйте приведенным ниже инструкциям, чтобы установить PostgreSQL на свой компьютер Mac. Убедитесь, что вы вошли какadministrator перед тем, как продолжить установку.

Выберите номер последней версии PostgreSQL для Mac OS, доступной на EnterpriseDB.
Я загрузил postgresql-9.2.4-1-osx.dmgдля моей Mac OS, работающей с OS X версии 10.8.3. Теперь позвольте нам открыть изображение dmg в поисковике и просто дважды щелкнуть по нему, что даст вам установщик PostgreSQL в следующем окне -

Затем нажмите кнопку postgres-9.2.4-1-osxзначок, который выдаст предупреждающее сообщение. Примите предупреждение и продолжайте установку. Он запросит пароль администратора, как показано в следующем окне -

Введите пароль, продолжите установку и после этого перезагрузите компьютер Mac. Если вы не видите следующее окно, запустите установку еще раз.

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

Теперь вы можете запустить любую программу для начала. Начнем с SQL Shell. Когда вы запускаете SQL Shell, просто используйте все значения по умолчанию, которые она отображает, за исключением ввода пароля, который вы выбрали во время установки. Если все пойдет нормально, то вы попадете в базу данных postgres иpostgress# приглашение будет отображаться, как показано ниже -

Поздравляем !!! Теперь у вас есть среда, готовая начать программирование базы данных PostgreSQL.

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

Program Files → PostgreSQL 9.2 → SQL Shell(psql).

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

postgres-# \help <command_name>

Заявление SQL

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

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

Команды PostgreSQL SQL

ABORT

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

ABORT [ WORK | TRANSACTION ]

ИЗМЕНИТЬ АГРЕГАТ

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

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 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 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 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 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 name ( { lefttype | NONE }, { righttype | NONE } )
OWNER TO new_owner

ИЗМЕНЕНИЕ КЛАССА ОПЕРАТОРА

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

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

ИЗМЕНИТЬ СХЕМУ

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

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

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

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

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

ИЗМЕНИТЬ ТАБЛИЦУ

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

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 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 name OWNER TO new_owner

ИЗМЕНИТЬ ПОЛЬЗОВАТЕЛЯ

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

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 [, ...] ]

Где 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 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 ]

И table_constraint -

[ 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 [ PREPARE ] plan_name

ЗАЯВИТЬ

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

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 name ( type ) [ CASCADE | RESTRICT ]

УДАЛЕНИЕ

Снимите гипс.

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

УДАЛЕНИЕ КОНВЕРСИИ

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

DROP CONVERSION name [ CASCADE | RESTRICT ]

УДАЛИТЬ БАЗУ ДАННЫХ

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

DROP DATABASE name

УДАЛИТЬ ДОМЕН

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

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

ФУНКЦИЯ ПАДЕНИЯ

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

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

ОТПРАВИТЬ ГРУППУ

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

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 name ON relation [ CASCADE | RESTRICT ]

DROP SCHEMA

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

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

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

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

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

ТАБЛИЦА ПАДЕНИЯ

Убрать стол.

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

DROP TABLESPACE

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

DROP TABLESPACE tablespace_name

КАПЕЛЬНЫЙ ТРИГГЕР

Снимите курок.

DROP TRIGGER name ON table [ CASCADE | RESTRICT ]

ТИП ПАДЕНИЯ

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

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

УДАЛИТЬ ПОЛЬЗОВАТЕЛЯ

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

DROP USER name

ПОКАЗАТЬ ВИД

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

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

КОНЕЦ

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

END [ WORK | TRANSACTION ]

ВЫПОЛНИТЬ

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

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

ОБЪЯСНИТЕ

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

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

ПОЛУЧИТЬ

Извлекайте строки из запроса с помощью курсора.

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 { DATABASE | TABLE | INDEX } name [ FORCE ]

ВЫПУСТИТЬ 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 [ 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 [, ...]

Где transaction_mode является одним из -

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

ПОКАЗАТЬ

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

SHOW name
SHOW ALL

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

Запустить блок транзакции.

START TRANSACTION [ transaction_mode [, ...] ]

Где transaction_mode является одним из -

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

TRUNCATE

Очистите таблицу.

TRUNCATE [ TABLE ] name

НЕ СЛУШАТЬ

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

UNLISTEN { name | * }

ОБНОВИТЬ

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

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

ВАКУУМ

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

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]

В этой главе мы обсудим типы данных, используемые в PostgreSQL. При создании таблицы для каждого столбца вы указываете тип данных, т. Е. Какие данные вы хотите хранить в полях таблицы.

Это дает несколько преимуществ -

Consistency - Операции со столбцами одного типа данных дают согласованные результаты и обычно являются самыми быстрыми.
Validation - Правильное использование типов данных подразумевает проверку формата данных и отклонение данных, выходящих за рамки типа данных.
Compactness - Поскольку столбец может хранить значения одного типа, он хранится компактно.
Performance- Правильное использование типов данных обеспечивает наиболее эффективное хранение данных. Сохраненные значения можно быстро обработать, что повышает производительность.

PostgreSQL поддерживает широкий набор типов данных. Кроме того, пользователи могут создавать свои собственные типы данных с помощью команды CREATE TYPE SQL. В PostgreSQL есть разные категории типов данных. Они обсуждаются ниже.

Числовые типы

Числовые типы состоят из двух-, четырех- и восьмибайтовых целых чисел, четырех- и восьмибайтовых чисел с плавающей запятой и десятичных дробей с выбираемой точностью. В следующей таблице перечислены доступные типы.

имя	Размер хранилища	Описание	Спектр
Smallint	2 байта	целое число малого диапазона	От -32768 до +32767
целое число	4 байта	типичный выбор для целого числа	От -2147483648 до +2147483647
Bigint	8 байт	целое число большого диапазона	От -9223372036854775808 до 9223372036854775807
десятичный	переменная	указанная пользователем точность, точная	до 131072 знаков до десятичной точки; до 16383 знаков после запятой
числовой	переменная	указанная пользователем точность, точная	до 131072 знаков до десятичной точки; до 16383 знаков после запятой
настоящий	4 байта	переменной точности, неточный	Точность 6 десятичных знаков
двойная точность	8 байт	переменной точности, неточный	Точность до 15 десятичных знаков
smallserial	2 байта	маленькое целое с автоинкрементом	1 к 32767
серийный	4 байта	целое число с автоинкрементом	1 к 2147483647
bigserial	8 байт	большое целое с автоинкрементом	1 к 9223372036854775807

Денежные типы

Тип money хранит денежную сумму с фиксированной дробной точностью. Значения типов данных numeric, int и bigint можно преобразовать в деньги . Для работы с деньгами не рекомендуется использовать числа с плавающей запятой из-за возможности ошибок округления.

имя	Размер хранилища	Описание	Спектр
Деньги	8 байт	сумма валюты	От -92233720368547758,08 до +92233720368547758,07

Типы персонажей

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

С. Нет.	Имя и описание
1	character varying(n), varchar(n) переменная длина с ограничением
2	character(n), char(n) фиксированной длины, с мягкой подкладкой
3	text переменная неограниченная длина

Типы двоичных данных

BYTEA тип данных позволяет хранить двоичные строки , как в таблице , приведенной ниже.

имя	Размер хранилища	Описание
байт	1 или 4 байта плюс фактическая двоичная строка	двоичная строка переменной длины

Типы даты / времени

PostgreSQL поддерживает полный набор типов даты и времени SQL, как показано в таблице ниже. Даты отсчитываются по григорианскому календарю. Здесь все типы имеют разрешение1 microsecond / 14 digits Кроме date типа, разрешение которого day.

имя	Размер хранилища	Описание	Низкое значение	Высокое значение
отметка времени [(p)] [без часового пояса]	8 байт	дата и время (без часового пояса)	4713 г. до н.э.	294276 н.э.
TIMESTAMPTZ	8 байт	дата и время с часовым поясом	4713 г. до н.э.	294276 н.э.
свидание	4 байта	дата (без времени суток)	4713 г. до н.э.	5874897 нашей эры
время [(p)] [без часового пояса]	8 байт	время дня (без даты)	00:00:00	24:00:00
время [(p)] с часовым поясом	12 байт	только время суток, с часовым поясом	00: 00: 00 + 1459	24: 00: 00-1459
интервал [поля] [(p)]	12 байт	интервал времени	-178000000 лет	178000000 лет

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

PostgreSQL предоставляет стандартный логический тип SQL. Тип данных Boolean может иметь состояния « истина» , « ложь» и третье состояние, « неизвестно» , которое представлено нулевым значением SQL.

имя	Размер хранилища	Описание
логический	1 байт	состояние истина или ложь

Нумерованный тип

Перечислимые (перечисляемые) типы - это типы данных, которые составляют статический упорядоченный набор значений. Они эквивалентны типам перечислений, поддерживаемым в ряде языков программирования.

В отличие от других типов, перечислимые типы необходимо создавать с помощью команды CREATE TYPE. Этот тип используется для хранения статического упорядоченного набора значений. Например, направления по компасу, например, СЕВЕР, ЮГ, ВОСТОК и ЗАПАД или дни недели, как показано ниже -

CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');

После создания Enumerated можно использовать как любые другие типы.

Геометрический Тип

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

имя	Размер хранилища	Представление	Описание
точка	16 байт	Точка на самолете	(х, у)
линия	32 байта	Бесконечная линия (реализовано не полностью)	((x1, y1), (x2, y2))
lseg	32 байта	Конечный отрезок линии	((x1, y1), (x2, y2))
коробка	32 байта	Прямоугольная коробка	((x1, y1), (x2, y2))
путь	16 + 16n байт	Замкнутый путь (похож на многоугольник)	((x1, y1), ...)
путь	16 + 16n байт	Открытый путь	[(x1, y1), ...]
многоугольник	40 + 16н	Многоугольник (аналог замкнутого пути)	((x1, y1), ...)
круг	24 байта	Круг	<(x, y), r> (центральная точка и радиус)

Тип сетевого адреса

PostgreSQL предлагает типы данных для хранения IPv4, IPv6 и MAC-адресов. Для хранения сетевых адресов лучше использовать эти типы вместо типов обычного текста, поскольку эти типы предлагают проверку ошибок ввода и специальные операторы и функции.

имя	Размер хранилища	Описание
Сидр	7 или 19 байт	Сети IPv4 и IPv6
инет	7 или 19 байт	Хосты и сети IPv4 и IPv6
Macaddr	6 байтов	MAC-адреса

Тип битовой строки

Типы битовых строк используются для хранения битовых масок. Они либо 0, либо 1. Есть два типа битов SQL:bit(n) и bit varying(n), где n - натуральное число.

Тип текстового поиска

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

С. Нет.	Имя и описание
1	tsvector Это отсортированный список отдельных слов, которые были нормализованы для объединения различных вариантов одного и того же слова, называемых «лексемами».
2	tsquery Здесь хранятся лексемы, которые нужно искать, и объединяются их с учетом логических операторов & (AND), \| (ИЛИ), и! (НЕ). Круглые скобки могут использоваться для принудительного группирования операторов.

Тип UUID

UUID (универсальные уникальные идентификаторы) записывается как последовательность строчных шестнадцатеричных цифр в нескольких группах, разделенных дефисами, в частности, группа из восьми цифр, за которыми следуют три группы из четырех цифр, за которыми следует группа из 12 цифр, для всего 32 цифры, представляющие 128 бит.

Пример UUID - 550e8400-e29b-41d4-a716-446655440000.

Тип XML

Тип данных XML можно использовать для хранения данных XML. Для хранения данных XML сначала необходимо создать значения XML с помощью функции xmlparse следующим образом:

XMLPARSE (DOCUMENT '<?xml version="1.0"?>
<tutorial>
<title>PostgreSQL Tutorial </title>
   <topics>...</topics>
</tutorial>')

XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')

Тип JSON

Тип данных json можно использовать для хранения данных JSON (JavaScript Object Notation). Такие данные также могут быть сохранены как текст , но тип данных json имеет то преимущество, что проверяет, является ли каждое сохраненное значение допустимым значением JSON. Доступны также связанные функции поддержки, которые можно использовать непосредственно для обработки типа данных JSON следующим образом.

пример	Пример результата
array_to_json ('{{1,5}, {99,100}}' :: int [])	[[1,5], [99,100]]
row_to_json (строка (1, 'foo'))	{"f1": 1, "f2": "foo"}

Тип массива

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

Объявление массивов

Тип массива можно объявить как

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer[],
   scheme text[][]
);

или используя ключевое слово "ARRAY" как

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer ARRAY[4],
   scheme text[][]
);

Вставка значений

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

INSERT INTO monthly_savings 
VALUES (‘Manisha’, 
‘{20000, 14600, 23500, 13250}’, 
‘{{“FD”, “MF”}, {“FD”, “Property”}}’);

Доступ к массивам

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

SELECT name FROM monhly_savings WHERE saving_per_quarter[2] > saving_per_quarter[4];

Изменение массивов

Пример изменения массивов показан ниже.

UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Manisha';

или используя синтаксис выражения ARRAY -

UPDATE monthly_savings SET saving_per_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Manisha';

Поиск в массивах

Пример поиска по массивам показан ниже.

SELECT * FROM monthly_savings WHERE saving_per_quarter[1] = 10000 OR
saving_per_quarter[2] = 10000 OR
saving_per_quarter[3] = 10000 OR
saving_per_quarter[4] = 10000;

Если размер массива известен, можно использовать приведенный выше метод поиска. Кроме того, в следующем примере показано, как выполнять поиск, когда размер неизвестен.

SELECT * FROM monthly_savings WHERE 10000 = ANY (saving_per_quarter);

Составные типы

Этот тип представляет собой список имен полей и их типов данных, т. Е. Структуру строки или записи таблицы.

Объявление составных типов

В следующем примере показано, как объявить составной тип.

CREATE TYPE inventory_item AS (
   name text,
   supplier_id integer,
   price numeric
);

Этот тип данных можно использовать в таблицах создания, как показано ниже -

CREATE TABLE on_hand (
   item inventory_item,
   count integer
);

Ввод составного значения

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

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

Это действительно для элемента inventory_item, определенного выше. Ключевое слово ROW на самом деле необязательно, если в выражении есть более одного поля.

Доступ к составным типам

Чтобы получить доступ к полю составного столбца, используйте точку, за которой следует имя поля, как при выборе поля из имени таблицы. Например, чтобы выбрать некоторые подполя из нашей таблицы примера on_hand, запрос будет таким, как показано ниже -

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

Вы даже можете использовать имя таблицы (например, в многотабличном запросе), например:

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

Типы диапазонов

Типы диапазонов представляют собой типы данных, которые используют диапазон данных. Тип диапазона может быть дискретным (например, все целочисленные значения от 1 до 10) или непрерывным диапазоном (например, любой момент времени между 10:00 и 11:00).

Доступные встроенные типы диапазонов включают следующие диапазоны:

int4range - Диапазон целых чисел
int8range - Ассортимент bigint
numrange - Диапазон числовых
tsrange - Диапазон метки времени без часового пояса
tstzrange - Диапазон метки времени с часовым поясом
daterange - Диапазон дат

Пользовательские типы диапазонов могут быть созданы, чтобы сделать доступными новые типы диапазонов, например диапазоны IP-адресов, использующие тип inet в качестве основы, или диапазоны с плавающей запятой, использующие тип данных float в качестве основы.

Типы диапазонов поддерживают включающие и исключающие границы диапазона с использованием символов [] и () соответственно. Например, «[4,9)» представляет все целые числа, начиная с 4 и заканчивая 9, но не включая.

Типы идентификаторов объектов

Идентификаторы объектов (OID) используются внутри PostgreSQL как первичные ключи для различных системных таблиц. Если указан WITH OIDS или включена конфигурационная переменная default_with_oids , только тогда в таких случаях OID добавляются в созданные пользователем таблицы. В следующей таблице перечислены несколько типов псевдонимов. Типы псевдонимов OID не имеют собственных операций, за исключением специализированных процедур ввода и вывода.

имя	Рекомендации	Описание	Пример значения
Oid	Любые	числовой идентификатор объекта	564182
regproc	pg_proc	имя функции	сумма
regprocedure	pg_proc	функция с типами аргументов	сумма (int4)
регопер	pg_operator	имя оператора	+
регоператор	pg_operator	оператор с типами аргументов	* (целое, целое) или - (НЕТ, целое)
regclass	pg_class	имя отношения	pg_type
regtype	pg_type	имя типа данных	целое число
regconfig	pg_ts_config	конфигурация текстового поиска	английский
regdictionary	pg_ts_dict	словарь текстового поиска	просто

Псевдотипы

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

В приведенной ниже таблице перечислены существующие псевдотипы.

С. Нет.	Имя и описание
1	any Указывает, что функция принимает любой тип входных данных.
2	anyelement Указывает, что функция принимает любой тип данных.
3	anyarray Указывает, что функция принимает любой тип данных массива.
4	anynonarray Указывает, что функция принимает любой тип данных, не являющийся массивом.
5	anyenum Указывает, что функция принимает любой тип данных перечисления.
6	anyrange Указывает, что функция принимает любой тип данных диапазона.
7	cstring Указывает, что функция принимает или возвращает строку C с завершающим нулем.
8	internal Указывает, что функция принимает или возвращает внутренний тип данных сервера.
9	language_handler Объявлен обработчик вызова процедурного языка, возвращающий language_handler.
10	fdw_handler Объявлен обработчик оболочки сторонних данных, возвращающий fdw_handler.
11	record Идентифицирует функцию, возвращающую неуказанный тип строки.
12	trigger Объявлена функция триггера для возврата триггера.
13	void Указывает, что функция не возвращает значения.

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

Использование команды SQL CREATE DATABASE.
Использование createdb исполняемого файла командной строки.

Использование CREATE DATABASE

Эта команда создаст базу данных из приглашения оболочки PostgreSQL, но у вас должны быть соответствующие права для создания базы данных. По умолчанию новая база данных будет создана путем клонирования стандартного системного шаблона базы данных1 .

Синтаксис

Базовый синтаксис оператора CREATE DATABASE следующий:

CREATE DATABASE dbname;

где dbname - имя создаваемой базы данных.

пример

Ниже приводится простой пример, который создаст testdb в вашей схеме PostgreSQL

postgres=# CREATE DATABASE testdb;
postgres-#

Использование команды createdb

Исполняемый файл командной строки PostgreSQL createdb представляет собой оболочку для команды SQL CREATE DATABASE . Единственное различие между этой командой и SQL-командой CREATE DATABASE состоит в том, что первую можно запустить напрямую из командной строки, и она позволяет добавлять комментарий в базу данных одной командой.

Синтаксис

Синтаксис для createdb показан ниже -

createdb [option...] [dbname [description]]

Параметры

В таблице ниже перечислены параметры с их описанием.

С. Нет.	Параметр и описание
1	dbname Имя создаваемой базы данных.
2	description Задает комментарий, который будет связан с вновь созданной базой данных.
3	options аргументы командной строки, которые принимает createdb.

Параметры

В следующей таблице перечислены аргументы командной строки, которые принимает createdb -

С. Нет.	Вариант и описание
1	-D tablespace Задает табличное пространство по умолчанию для базы данных.
2	-e Эхо команд, которые createdb генерирует и отправляет на сервер.
3	-E encoding Задает схему кодировки символов, которая будет использоваться в этой базе данных.
4	-l locale Задает языковой стандарт, который будет использоваться в этой базе данных.
5	-T template Задает базу данных шаблонов, из которой следует построить эту базу данных.
6	--help Показать справку об аргументах командной строки createdb и выйти.
7	-h host Задает имя хоста машины, на которой работает сервер.
8	-p port Задает TCP-порт или расширение файла локального сокета домена Unix, на котором сервер прослушивает соединения.
9	-U username Имя пользователя для подключения.
10	-w Никогда не запрашивайте пароль.
11	-W Заставить createdb запрашивать пароль перед подключением к базе данных.

Откройте командную строку и перейдите в каталог, в котором установлен PostgreSQL. Перейдите в каталог bin и выполните следующую команду, чтобы создать базу данных.

createdb -h localhost -p 5432 -U postgres testdb
password ******

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

После создания базы данных любым из вышеупомянутых методов вы можете проверить ее в списке баз данных, используя \l, то есть обратная косая черта el следующая команда -

postgres-# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     | 
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 testdb    | postgres | UTF8     | C       | C     | 
(4 rows)

postgres-#

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

Подсказка SQL базы данных
Командная строка ОС

Подсказка SQL базы данных

Предположим, вы уже запустили свой клиент PostgreSQL и попали в следующее приглашение SQL -

postgres=#

Вы можете проверить список доступных баз данных, используя \l, то есть обратная косая черта el следующая команда -

postgres-# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     | 
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 testdb    | postgres | UTF8     | C       | C     | 
(4 rows)

postgres-#

Теперь введите следующую команду для подключения / выбора нужной базы данных; здесь мы подключимся к базе данных testdb .

postgres=# \c testdb;
psql (9.2.4)
Type "help" for help.
You are now connected to database "testdb" as user "postgres".
testdb=#

Командная строка ОС

Вы можете выбрать свою базу данных из самой командной строки во время входа в свою базу данных. Ниже приводится простой пример -

psql -h localhost -p 5432 -U postgress testdb
Password for user postgress: ****
psql (9.2.4)
Type "help" for help.
You are now connected to database "testdb" as user "postgres".
testdb=#

Теперь вы вошли в PostgreSQL testdb и готовы выполнять свои команды внутри testdb. Для выхода из базы данных вы можете использовать команду \ q.

В этой главе мы обсудим, как удалить базу данных в PostgreSQL. Есть два варианта удаления базы данных -

Использование команды SQL DROP DATABASE.
Использование dropdb исполняемого файла командной строки.

Будьте осторожны перед использованием этой операции, поскольку удаление существующей базы данных приведет к потере всей информации, хранящейся в базе данных.

Использование DROP DATABASE

Эта команда удаляет базу данных. Он удаляет записи каталога для базы данных и удаляет каталог, содержащий данные. Его может выполнить только владелец базы данных. Эта команда не может быть выполнена, пока вы или кто-либо еще подключен к целевой базе данных (подключитесь к postgres или любой другой базе данных, чтобы выполнить эту команду).

Синтаксис

Синтаксис DROP DATABASE приведен ниже -

DROP DATABASE [ IF EXISTS ] name

Параметры

В таблице перечислены параметры с их описанием.

С. Нет.	Параметр и описание
1	IF EXISTS Не выдавать ошибку, если база данных не существует. В этом случае выдается уведомление.
2	name Имя удаляемой базы данных.

Мы не можем удалить базу данных, в которой есть открытые соединения, включая наше собственное соединение с psql или pgAdmin III . Мы должны переключиться на другую базу данных или template1, если мы хотим удалить базу данных, к которой мы в настоящее время подключены. Таким образом, было бы удобнее использовать программу dropdb , которая является оболочкой для этой команды.

пример

Ниже приведен простой пример, который удалит testdb из вашей схемы PostgreSQL -

postgres=# DROP DATABASE testdb;
postgres-#

Использование команды dropdb

Исполняемый файл командной строки PostgresSQL dropdbпредставляет собой оболочку командной строки для команды SQL DROP DATABASE . Эффективной разницы между удалением баз данных с помощью этой утилиты и других методов доступа к серверу нет. dropdb уничтожает существующую базу данных PostgreSQL. Пользователь, выполняющий эту команду, должен быть суперпользователем базы данных или владельцем базы данных.

Синтаксис

Синтаксис dropdb показан ниже -

dropdb  [option...] dbname

Параметры

В следующей таблице перечислены параметры с их описанием.

С. Нет.	Параметр и описание
1	dbname Имя удаляемой базы данных.
2	option аргументы командной строки, которые принимает dropdb.

Параметры

В следующей таблице перечислены аргументы командной строки, которые принимает dropdb:

С. Нет.	Вариант и описание
1	-e Показывает команды, отправляемые на сервер.
2	-i Перед выполнением каких-либо разрушительных действий выдает запрос на подтверждение.
3	-V Распечатайте версию dropdb и выйдите.
4	--if-exists Не выдавать ошибку, если база данных не существует. В этом случае выдается уведомление.
5	--help Показать справку об аргументах командной строки dropdb и выйти.
6	-h host Задает имя хоста машины, на которой работает сервер.
7	-p port Задает порт TCP или расширение файла локального сокета домена UNIX, на котором сервер прослушивает соединения.
8	-U username Имя пользователя для подключения.
9	-w Никогда не запрашивайте пароль.
10	-W Заставьте dropdb запрашивать пароль перед подключением к базе данных.
11	--maintenance-db=dbname Задает имя базы данных, к которой нужно подключиться, чтобы удалить целевую базу данных.

пример

В следующем примере показано удаление базы данных из командной строки ОС -

dropdb -h localhost -p 5432 -U postgress testdb
Password for user postgress: ****

Приведенная выше команда удаляет базу данных testdb. Здесь я использовалpostgres (находится в pg_roles шаблона 1) имя пользователя для удаления базы данных.

Оператор PostgreSQL CREATE TABLE используется для создания новой таблицы в любой из данных баз данных.

Синтаксис

Базовый синтаксис оператора CREATE TABLE следующий:

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

CREATE TABLE - это ключевое слово, сообщающее системе базы данных о необходимости создания новой таблицы. Уникальное имя или идентификатор таблицы следует за оператором CREATE TABLE. Изначально пустая таблица в текущей базе данных принадлежит пользователю, вводящему команду.

Затем в скобках идет список, в котором определяется каждый столбец в таблице и тип данных. Синтаксис станет понятен на примере, приведенном ниже.

Примеры

Ниже приведен пример, который создает таблицу COMPANY с идентификатором в качестве первичного ключа, а NOT NULL - это ограничения, показывающие, что эти поля не могут быть NULL при создании записей в этой таблице.

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Давайте создадим еще одну таблицу, которую мы будем использовать в наших упражнениях в следующих главах -

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

Вы можете проверить, успешно ли создана ваша таблица, используя \d команда, которая будет использоваться для вывода списка всех таблиц в присоединенной базе данных.

testdb-# \d

Приведенный выше оператор PostgreSQL даст следующий результат:

List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+----------
 public | company    | table | postgres
 public | department | table | postgres
(2 rows)

Использовать \d tablename чтобы описать каждую таблицу, как показано ниже -

testdb-# \d company

Приведенный выше оператор PostgreSQL даст следующий результат:

Table "public.company"
  Column   |     Type      | Modifiers
-----------+---------------+-----------
 id        | integer       | not null
 name      | text          | not null
 age       | integer       | not null
 address   | character(50) |
 salary    | real          |
 join_date | date          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)

Оператор PostgreSQL DROP TABLE используется для удаления определения таблицы и всех связанных данных, индексов, правил, триггеров и ограничений для этой таблицы.

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

Синтаксис

Базовый синтаксис оператора DROP TABLE следующий:

DROP TABLE table_name;

пример

Мы создали таблицы DEPARTMENT и COMPANY в предыдущей главе. Сначала проверьте эти таблицы (используйте\d перечислить таблицы) -

testdb-# \d

Это даст следующий результат -

List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+----------
 public | company    | table | postgres
 public | department | table | postgres
(2 rows)

Это означает, что присутствуют таблицы DEPARTMENT и COMPANY. Так что давайте отбросим их следующим образом -

testdb=# drop table department, company;

Это даст следующий результат -

DROP TABLE
testdb=# \d
relations found.
testdb=#

Возвращенное сообщение DROP TABLE указывает, что команда сброса выполнена успешно.

А schemaпредставляет собой именованный набор таблиц. Схема также может содержать представления, индексы, последовательности, типы данных, операторы и функции. Схемы аналогичны каталогам на уровне операционной системы, за исключением того, что схемы не могут быть вложенными. Оператор PostgreSQL CREATE SCHEMA создает схему.

Синтаксис

Основной синтаксис CREATE SCHEMA следующий:

CREATE SCHEMA name;

Где имя - это имя схемы.

Синтаксис для создания таблицы в схеме

Основной синтаксис для создания таблицы в схеме следующий:

CREATE TABLE myschema.mytable (
...
);

пример

Давайте посмотрим на пример создания схемы. Подключитесь к базе данных testdb и создайте схему myschema следующим образом:

testdb=# create schema myschema;
CREATE SCHEMA

Сообщение «CREATE SCHEMA» означает, что схема создана успешно.

Теперь давайте создадим таблицу в приведенной выше схеме следующим образом:

testdb=# create table myschema.company(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25),
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Это создаст пустую таблицу. Вы можете проверить таблицу, созданную с помощью команды, приведенной ниже -

testdb=# select * from myschema.company;

Это даст следующий результат -

id | name | age | address | salary
----+------+-----+---------+--------
(0 rows)

Синтаксис для отбрасывания схемы

Чтобы удалить схему, если она пуста (все объекты в ней удалены), используйте команду -

DROP SCHEMA myschema;

Чтобы удалить схему, включающую все содержащиеся объекты, используйте команду -

DROP SCHEMA myschema CASCADE;

Преимущества использования схемы

Это позволяет многим пользователям использовать одну базу данных, не мешая друг другу.
Он организует объекты базы данных в логические группы, чтобы сделать их более управляемыми.
Сторонние приложения можно поместить в отдельные схемы, чтобы они не конфликтовали с именами других объектов.

PostgreSQL INSERT INTOоператор позволяет вставлять новые строки в таблицу. Можно вставить одну строку за раз или несколько строк в результате запроса.

Синтаксис

Базовый синтаксис оператора INSERT INTO следующий:

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);

Здесь column1, column2, ... columnN - это имена столбцов в таблице, в которые вы хотите вставить данные.
Имена целевых столбцов могут быть перечислены в любом порядке. Значения, предоставленные предложением или запросом VALUES, связаны с явным или неявным списком столбцов слева направо.

Возможно, вам не потребуется указывать имя столбца (столбцов) в запросе SQL, если вы добавляете значения для всех столбцов таблицы. Однако убедитесь, что порядок значений соответствует порядку столбцов в таблице. Синтаксис SQL INSERT INTO будет следующим:

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

Вывод

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

С. Нет.	Выходное сообщение и описание
1	INSERT oid 1 Сообщение возвращается, если была вставлена только одна строка. oid - числовой OID вставленной строки.
2	INSERT 0 # Сообщение возвращается, если было вставлено более одной строки. # - количество вставленных строк.

Примеры

Давайте создадим таблицу КОМПАНИЯ в testdb следующим образом -

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   JOIN_DATE	  DATE
);

В следующем примере вставляется строка в таблицу COMPANY -

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Paul', 32, 'California', 20000.00,'2001-07-13');

В следующем примере вставляется строка; здесь столбец зарплаты опущен, поэтому он будет иметь значение по умолчанию -

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,JOIN_DATE) VALUES (2, 'Allen', 25, 'Texas', '2007-12-13');

В следующем примере вместо указания значения используется предложение DEFAULT для столбца JOIN_DATE:

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (3, 'Teddy', 23, 'Norway', 20000.00, DEFAULT );

В следующем примере вставляется несколько строк с использованием многострочного синтаксиса VALUES -

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00, '2007-12-13' ), (5, 'David', 27, 'Texas', 85000.00, '2007-12-13');

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

ID        NAME        AGE        ADDRESS     SALARY	  JOIN_DATE
----      ----------  -----      ----------  -------      --------
1         Paul        32         California  20000.0      2001-07-13
2         Allen       25         Texas                    2007-12-13
3         Teddy       23         Norway      20000.0
4         Mark        25         Rich-Mond   65000.0      2007-12-13
5         David       27         Texas       85000.0      2007-12-13

PostgreSQL SELECTОператор используется для извлечения данных из таблицы базы данных, которая возвращает данные в виде таблицы результатов. Эти таблицы результатов называются наборами результатов.

Синтаксис

Основной синтаксис оператора SELECT следующий:

SELECT column1, column2, columnN FROM table_name;

Здесь column1, column2 ... - это поля таблицы, значения которых вы хотите получить. Если вы хотите получить все поля, доступные в поле, вы можете использовать следующий синтаксис -

SELECT * FROM table_name;

пример

Рассмотрим таблицу КОМПАНИЯ, имеющую следующие записи:

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Ниже приведен пример, который извлекает поля идентификатора, имени и зарплаты клиентов, доступных в таблице CUSTOMERS.

testdb=# SELECT ID, NAME, SALARY FROM COMPANY ;

Это даст следующий результат -

id | name  | salary
 ----+-------+--------
   1 | Paul  |  20000
   2 | Allen |  15000
   3 | Teddy |  20000
   4 | Mark  |  65000
   5 | David |  85000
   6 | Kim   |  45000
   7 | James |  10000
(7 rows)

Если вы хотите получить все поля таблицы CUSTOMERS, используйте следующий запрос -

testdb=# SELECT * FROM COMPANY;

Это даст следующий результат -

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Что такое оператор в PostgreSQL?

Оператор - это зарезервированное слово или символ, используемый в основном в предложении WHERE оператора PostgreSQL для выполнения операции (операций), таких как сравнения и арифметические операции.

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

Арифметические операторы
Операторы сравнения
Логические операторы
Побитовые операторы

Арифметические операторы PostgreSQL

Предположим переменную a содержит 2 и переменную b держит 3, то -

пример

Оператор	Описание	пример
+	Сложение - добавляет значения по обе стороны от оператора.	a + b даст 5
-	Вычитание - вычитает правый операнд из левого операнда.	a - b даст -1
*	Умножение - умножает значения по обе стороны от оператора.	a * b даст 6
/	Деление - делит левый операнд на правый операнд	б / у даст 1
%	Модуль - делит левый операнд на правый и возвращает остаток	b% a даст 1
^	Возведение в степень - дает значение экспоненты правого операнда	a ^ b даст 8
\| /	квадратный корень	\| / 25.0 даст 5
\|\| /	кубический корень	\|\| / 27.0 даст 3
!	факториал	5! даст 120
!!	факториал (префиксный оператор)	!! 5 даст 120

Операторы сравнения PostgreSQL

Предположим, что переменная a содержит 10, а переменная b содержит 20, тогда -

Показать примеры

Оператор	Описание	пример
знак равно	Проверяет, равны ли значения двух операндов или нет, если да, то условие становится истинным.	(a = b) неверно.
знак равно	Проверяет, равны ли значения двух операндов или нет, если значения не равны, условие становится истинным.	(a! = b) верно.
<>	Проверяет, равны ли значения двух операндов или нет, если значения не равны, условие становится истинным.	(a <> b) верно.
>	Проверяет, больше ли значение левого операнда, чем значение правого операнда, если да, то условие становится истинным.	(a> b) неверно.
<	Проверяет, меньше ли значение левого операнда, чем значение правого операнда, если да, то условие становится истинным.	(a <b) верно.
> =	Проверяет, больше ли значение левого операнда или равно значению правого операнда, если да, то условие становится истинным.	(a> = b) неверно.
<=	Проверяет, меньше ли значение левого операнда или равно значению правого операнда, если да, то условие становится истинным.	(a <= b) верно.

Логические операторы PostgreSQL

Вот список всех логических операторов, доступных в PostgresSQL.

Показать примеры

С. Нет.	Оператор и описание
1	AND Оператор AND допускает наличие нескольких условий в предложении WHERE оператора PostgresSQL.
2	NOT Оператор НЕ меняет значение логического оператора, с которым он используется. Например. НЕ СУЩЕСТВУЕТ, НЕ МЕЖДУ, НЕ В И т.д.This is negate operator.
3	OR Оператор OR используется для объединения нескольких условий в предложении WHERE оператора PostgresSQL.

Операторы битовой строки PostgreSQL

Побитовый оператор работает с битами и выполняет побитовую операцию. Таблица истинности для & и | выглядит следующим образом -

п	q	p & q	p \| q
0	0	0	0
0	1	0	1
1	1	1	1
1	0	0	1

Допустим, если A = 60; и B = 13; теперь в двоичном формате они будут такими -

А = 0011 1100

В = 0000 1101

-----------------

A&B = 0000 1100

А | В = 0011 1101

~ А = 1100 0011

Показать примеры

Побитовые операторы, поддерживаемые PostgreSQL, перечислены в следующей таблице -

Оператор	Описание	пример
&	Двоичный оператор И копирует бит в результат, если он существует в обоих операндах.	(A и B) даст 12, что составляет 0000 1100
\|	Оператор двоичного ИЛИ копирует бит, если он существует в любом из операндов.	(A \| B) даст 61, что составляет 0011 1101
~	Оператор дополнения двоичных единиц является унарным и имеет эффект «переворачивания» битов.	(~ A) даст -61, что составляет 1100 0011 в форме дополнения до 2 из-за двоичного числа со знаком.
<<	Оператор двоичного сдвига влево. Значение левого операнда сдвигается влево на количество битов, указанное правым операндом.	<< 2 даст 240, что составляет 1111 0000
>>	Оператор двоичного сдвига вправо. Значение левого операнда перемещается вправо на количество битов, указанное правым операндом.	A >> 2 даст 15, что равно 0000 1111
#	побитовое XOR.	A # B даст 49, что составляет 0100 1001

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

ВЫРАЖЕНИЯ PostgreSQL похожи на формулы и написаны на языке запросов. Вы также можете использовать для запроса базы данных для определенного набора данных.

Синтаксис

Рассмотрим основной синтаксис оператора SELECT следующим образом:

SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION | EXPRESSION];

Существуют различные типы выражений PostgreSQL, которые упомянуты ниже -

PostgreSQL - логические выражения

Логические выражения PostgreSQL извлекают данные на основе совпадающего единственного значения. Ниже приводится синтаксис -

SELECT column1, column2, columnN
FROM table_name
WHERE SINGLE VALUE MATCHTING EXPRESSION;

Рассмотрим таблицу КОМПАНИЯ, имеющую следующие записи:

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Вот простой пример, показывающий использование логических выражений PostgreSQL:

testdb=# SELECT * FROM COMPANY WHERE SALARY = 10000;

Приведенный выше оператор PostgreSQL даст следующий результат:

id | name  | age | address  | salary
----+-------+-----+----------+--------
  7 | James |  24 | Houston  |  10000
(1 row)

PostgreSQL - Числовое выражение

Эти выражения используются для выполнения любых математических операций в любом запросе. Ниже приводится синтаксис -

SELECT numerical_expression as  OPERATION_NAME
[FROM table_name WHERE CONDITION] ;

Здесь числовое_выражение используется для математического выражения или любой формулы. Ниже приведен простой пример, показывающий использование числовых выражений SQL.

testdb=# SELECT (15 + 6) AS ADDITION ;

Приведенный выше оператор PostgreSQL даст следующий результат:

addition
----------
       21
(1 row)

Существует несколько встроенных функций, таких как avg (), sum (), count (), для выполнения так называемых вычислений агрегированных данных для таблицы или определенного столбца таблицы.

testdb=# SELECT COUNT(*) AS "RECORDS" FROM COMPANY;

Приведенный выше оператор PostgreSQL даст следующий результат:

RECORDS
---------
       7
(1 row)

PostgreSQL - Выражения даты

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

testdb=#  SELECT CURRENT_TIMESTAMP;

Приведенный выше оператор PostgreSQL даст следующий результат:

now
-------------------------------
 2013-05-06 14:38:28.078+05:30
(1 row)

Предложение WHERE в PostgreSQL используется для указания условия при выборке данных из одной таблицы или соединении с несколькими таблицами.

Только если данное условие выполняется, возвращается конкретное значение из таблицы. Вы можете отфильтровать строки, которые не хотите включать в набор результатов, используя предложение WHERE.

Предложение WHERE используется не только в операторе SELECT, но также в операторе UPDATE, DELETE и т. Д., Которые мы рассмотрим в следующих главах.

Синтаксис

Базовый синтаксис оператора SELECT с предложением WHERE следующий:

SELECT column1, column2, columnN
FROM table_name
WHERE [search_condition]

Вы можете указать условие поиска, используя сравнение или логические операторы. как>, <, =, LIKE, NOT и т. д. Следующие примеры поясняют эту концепцию.

пример

Рассмотрим таблицу КОМПАНИЯ, имеющую следующие записи:

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Вот простые примеры, показывающие использование логических операторов PostgreSQL. После оператора SELECT будут перечислены все записи, в которых AGE больше или равен 25.AND зарплата больше или равна 65000.00 -

testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

Приведенный выше оператор PostgreSQL даст следующий результат:

id | name  | age |  address   | salary
----+-------+-----+------------+--------
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(2 rows)

Следующий оператор SELECT перечисляет все записи, в которых AGE больше или равен 25. OR зарплата больше или равна 65000.00 -

testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

Приведенный выше оператор PostgreSQL даст следующий результат:

id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
(4 rows)

Следующий оператор SELECT перечисляет все записи, в которых AGE не равен NULL, что означает все записи, потому что ни одна из записей не имеет AGE, равного NULL -

testdb=#  SELECT * FROM COMPANY WHERE AGE IS NOT NULL;

Приведенный выше оператор PostgreSQL даст следующий результат:

id | name  | age | address    | salary
 ----+-------+-----+------------+--------
   1 | Paul  |  32 | California |  20000
   2 | Allen |  25 | Texas      |  15000
   3 | Teddy |  23 | Norway     |  20000
   4 | Mark  |  25 | Rich-Mond  |  65000
   5 | David |  27 | Texas      |  85000
   6 | Kim   |  22 | South-Hall |  45000
   7 | James |  24 | Houston    |  10000
(7 rows)

Следующий оператор SELECT перечисляет все записи, в которых NAME начинается с «Pa», независимо от того, что идет после «Pa».

testdb=# SELECT * FROM COMPANY WHERE NAME LIKE 'Pa%';

Приведенный выше оператор PostgreSQL даст следующий результат:

id | name | age |address    | salary
----+------+-----+-----------+--------
  1 | Paul |  32 | California|  20000

Следующий оператор SELECT перечисляет все записи, в которых значение AGE равно 25 или 27:

testdb=# SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );

Приведенный выше оператор PostgreSQL даст следующий результат:

id | name  | age | address    | salary
----+-------+-----+------------+--------
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(3 rows)

Следующий оператор SELECT перечисляет все записи, в которых значение AGE не равно 25 или 27:

testdb=# SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );

Приведенный выше оператор PostgreSQL даст следующий результат:

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  3 | Teddy |  23 | Norway     |  20000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
(4 rows)

Следующий оператор SELECT перечисляет все записи, в которых значение AGE находится в МЕЖДУ 25 И 27 -

testdb=# SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

Приведенный выше оператор PostgreSQL даст следующий результат:

id | name  | age | address    | salary
----+-------+-----+------------+--------
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(3 rows)

В следующем операторе SELECT используется подзапрос SQL, в котором подзапрос находит все записи с полем AGE, имеющим SALARY> 65000 и более поздних версий. Предложение WHERE используется вместе с оператором EXISTS для вывода списка всех записей, в которых в возвращаемом результате существует AGE из внешнего запроса. по подзапросу -

testdb=# SELECT AGE FROM COMPANY
        WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

Приведенный выше оператор PostgreSQL даст следующий результат:

age
-----
  32
  25
  23
  25
  27
  22
  24
(7 rows)

Следующий оператор SELECT использует подзапрос SQL, где подзапрос находит все записи с полем AGE, имеющим SALARY> 65000 и более поздних версий. Предложение WHERE используется вместе с оператором> для вывода списка всех записей, для которых AGE из внешнего запроса больше, чем возраст в результат, возвращаемый подзапросом -

testdb=# SELECT * FROM COMPANY
        WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

Приведенный выше оператор PostgreSQL даст следующий результат:

id | name | age | address    | salary
----+------+-----+------------+--------
  1 | Paul |  32 | California |  20000

PostgreSQL AND и ORоператоры используются для объединения нескольких условий для сужения выбранных данных в операторе PostgreSQL. Эти два оператора называются конъюнктивными операторами.

Эти операторы позволяют выполнять несколько сравнений с разными операторами в одном операторе PostgreSQL.

Оператор AND

В ANDОператор допускает наличие нескольких условий в предложении WHERE оператора PostgreSQL. При использовании оператора AND полное условие будет считаться истинным, если все условия верны. Например, [условие1] И [условие2] будет истинным только тогда, когда истинны и условие1, и условие2.

Синтаксис

Основной синтаксис оператора AND с предложением WHERE следующий:

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

Вы можете объединить N условий, используя оператор AND. Для выполнения оператором PostgreSQL действия, будь то транзакция или запрос, все условия, разделенные оператором AND, должны быть ИСТИНА.

пример

Рассмотрим таблицу КОМПАНИЯ, имеющую следующие записи:

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Следующий оператор SELECT перечисляет все записи, в которых AGE больше или равен 25. AND зарплата больше или равна 65000.00 -

testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

Приведенный выше оператор PostgreSQL даст следующий результат:

id | name  | age | address    | salary
----+-------+-----+------------+--------
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(2 rows)

Оператор OR

Оператор OR также используется для объединения нескольких условий в предложении WHERE оператора PostgreSQL. При использовании оператора OR полное условие будет считаться истинным, если хотя бы одно из условий истинно. Например, [условие1] ИЛИ [условие2] будет истинным, если истинно либо условие1, либо условие2.

Синтаксис

Основной синтаксис оператора OR с предложением WHERE следующий:

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

Вы можете объединить N условий, используя оператор ИЛИ. Для выполнения оператором PostgreSQL действия, будь то транзакция или запрос, только любое ОДНО из условий, разделенных ИЛИ, должно быть ИСТИНА.

пример

Рассмотрим таблицу КОМПАНИИ , имеющую следующие записи -

# select * from COMPANY;
  id | name  | age | address   | salary
 ----+-------+-----+-----------+--------
   1 | Paul  |  32 | California|  20000
   2 | Allen |  25 | Texas     |  15000
   3 | Teddy |  23 | Norway    |  20000
   4 | Mark  |  25 | Rich-Mond |  65000
   5 | David |  27 | Texas     |  85000
   6 | Kim   |  22 | South-Hall|  45000
   7 | James |  24 | Houston   |  10000
(7 rows)

testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

Приведенный выше оператор PostgreSQL даст следующий результат:

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(4 rows)

PostgreSQL UPDATEЗапрос используется для изменения существующих записей в таблице. Вы можете использовать предложение WHERE с запросом UPDATE для обновления выбранных строк. В противном случае обновятся все строки.

Синтаксис

Основной синтаксис запроса UPDATE с предложением WHERE выглядит следующим образом:

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

Вы можете объединить N условий, используя операторы И или ИЛИ.

пример

Рассмотрим таблицу КОМПАНИЯ , имеющую следующие записи -

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Ниже приведен пример, который обновит АДРЕС для клиента с идентификатором 6:

testdb=# UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;

Теперь в таблице КОМПАНИЯ будут следующие записи -

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
  3 | Teddy |  23 | Norway     |  15000
(7 rows)

Если вы хотите изменить все значения столбцов ADDRESS и SALARY в таблице COMPANY, вам не нужно использовать предложение WHERE, и запрос UPDATE будет следующим:

testdb=# UPDATE COMPANY SET ADDRESS = 'Texas', SALARY=20000;

Теперь в таблице COMPANY будут следующие записи -

id | name  | age | address | salary
----+-------+-----+---------+--------
  1 | Paul  |  32 | Texas   |  20000
  2 | Allen |  25 | Texas   |  20000
  4 | Mark  |  25 | Texas   |  20000
  5 | David |  27 | Texas   |  20000
  6 | Kim   |  22 | Texas   |  20000
  7 | James |  24 | Texas   |  20000
  3 | Teddy |  23 | Texas   |  20000
(7 rows)

PostgreSQL DELETEЗапрос используется для удаления существующих записей из таблицы. Вы можете использовать предложение WHERE с запросом DELETE для удаления выбранных строк. В противном случае все записи будут удалены.

Синтаксис

Основной синтаксис запроса DELETE с предложением WHERE следующий:

DELETE FROM table_name
WHERE [condition];

Вы можете объединить N условий, используя операторы И или ИЛИ.

пример

Рассмотрим таблицу КОМПАНИЯ , имеющую следующие записи -

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Ниже приведен пример УДАЛЕНИЯ клиента с идентификатором 7 -

testdb=# DELETE FROM COMPANY WHERE ID = 2;

Теперь в таблице COMPANY будут следующие записи -

id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
(6 rows)

Если вы хотите УДАЛИТЬ все записи из таблицы COMPANY, вам не нужно использовать предложение WHERE с запросами DELETE, которые будут следующими:

testdb=# DELETE FROM COMPANY;

Теперь в таблице COMPANY нет записей, потому что все записи были удалены оператором DELETE.

PostgreSQL LIKEОператор используется для сопоставления текстовых значений с шаблоном с использованием подстановочных знаков. Если выражение поиска может быть сопоставлено с выражением шаблона, оператор LIKE вернет истину, т.е.1.

В сочетании с оператором LIKE используются два подстановочных знака:

Знак процента (%)
Подчеркивание (_)

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

Если любой из этих двух знаков не используется в сочетании с предложением LIKE, то LIKE действует как оператор равенства.

Синтаксис

Основной синтаксис% и _ выглядит следующим образом:

SELECT FROM table_name
WHERE column LIKE 'XXXX%'

or

SELECT FROM table_name
WHERE column LIKE '%XXXX%'

or

SELECT FROM table_name
WHERE column LIKE 'XXXX_'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX_'

Вы можете объединить N условий, используя операторы И или ИЛИ. Здесь XXXX может быть любым числовым или строковым значением.

пример

Вот несколько примеров, показывающих, что часть WHERE имеет другое предложение LIKE с операторами '%' и '_':

С. Нет.	Заявление и описание
1	WHERE SALARY::text LIKE '200%' Находит любые значения, начинающиеся с 200
2	WHERE SALARY::text LIKE '%200%' Находит любые значения, у которых 200 в любой позиции
3	WHERE SALARY::text LIKE '_00%' Находит любые значения, у которых на второй и третьей позициях стоит 00
4	WHERE SALARY::text LIKE '2_%_%' Находит любые значения, которые начинаются с 2 и имеют длину не менее 3 символов
5	WHERE SALARY::text LIKE '%2' Находит любые значения, заканчивающиеся на 2
6	WHERE SALARY::text LIKE '_2%3' Находит любые значения, у которых 2 во второй позиции и заканчивается на 3
7	WHERE SALARY::text LIKE '2___3' Находит любые значения в пятизначном числе, начинающиеся с 2 и заканчивающиеся на 3

Postgres LIKE - это только сравнение строк. Следовательно, нам нужно явно преобразовать целочисленный столбец в строку, как в приведенных выше примерах.

Давайте возьмем реальный пример, рассмотрим таблицу COMPANY , имеющую следующие записи:

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Ниже приведен пример, в котором будут отображаться все записи из таблицы COMPANY, где AGE начинается с 2 -

testdb=# SELECT * FROM COMPANY WHERE AGE::text LIKE '2%';

Это даст следующий результат -

id | name  | age | address     | salary
----+-------+-----+-------------+--------
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  8 | Paul  |  24 | Houston     |  20000
(7 rows)

Ниже приведен пример, в котором будут отображаться все записи из таблицы КОМПАНИЯ, где АДРЕС будет иметь дефис (-) внутри текста -

testdb=# SELECT * FROM COMPANY WHERE ADDRESS  LIKE '%-%';

Это даст следующий результат -

id | name | age |                      address              | salary
----+------+-----+-------------------------------------------+--------
  4 | Mark |  25 | Rich-Mond                                 |  65000
  6 | Kim  |  22 | South-Hall                                |  45000
(2 rows)

PostgreSQL LIMIT Предложение используется для ограничения количества данных, возвращаемых оператором SELECT.

Синтаксис

Основной синтаксис оператора SELECT с предложением LIMIT следующий:

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]

Ниже приводится синтаксис предложения LIMIT, когда он используется вместе с предложением OFFSET:

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]

LIMIT и OFFSET позволяют получить только часть строк, которые генерируются остальной частью запроса.

пример

Рассмотрим таблицу КОМПАНИЯ, имеющую следующие записи:

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Ниже приведен пример, который ограничивает строку в таблице в соответствии с количеством строк, которые вы хотите получить из таблицы.

testdb=# SELECT * FROM COMPANY LIMIT 4;

Это даст следующий результат -

id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
(4 rows)

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

testdb=# SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

Это даст следующий результат -

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
(3 rows)

PostgreSQL ORDER BY Предложение используется для сортировки данных в порядке возрастания или убывания на основе одного или нескольких столбцов.

Синтаксис

Базовый синтаксис предложения ORDER BY следующий:

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

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

пример

Рассмотрим таблицу КОМПАНИЯ, имеющую следующие записи:

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Ниже приведен пример сортировки результатов в порядке возрастания по ЗАРПЛАТЕ.

testdb=# SELECT * FROM COMPANY ORDER BY AGE ASC;

Это даст следующий результат -

id | name  | age | address    | salary
 ----+-------+-----+------------+--------
   6 | Kim   |  22 | South-Hall |  45000
   3 | Teddy |  23 | Norway     |  20000
   7 | James |  24 | Houston    |  10000
   8 | Paul  |  24 | Houston    |  20000
   4 | Mark  |  25 | Rich-Mond  |  65000
   2 | Allen |  25 | Texas      |  15000
   5 | David |  27 | Texas      |  85000
   1 | Paul  |  32 | California |  20000
   9 | James |  44 | Norway     |   5000
  10 | James |  45 | Texas      |   5000
(10 rows)

Ниже приведен пример сортировки результатов в порядке возрастания по ИМЕНИ и ЗАРПЛАТЕ.

testdb=# SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;

Это даст следующий результат -

id | name  | age | address      | salary
----+-------+-----+--------------+--------
  2 | Allen |  25 | Texas        |  15000
  5 | David |  27 | Texas        |  85000
 10 | James |  45 | Texas        |   5000
  9 | James |  44 | Norway       |   5000
  7 | James |  24 | Houston      |  10000
  6 | Kim   |  22 | South-Hall   |  45000
  4 | Mark  |  25 | Rich-Mond    |  65000
  1 | Paul  |  32 | California   |  20000
  8 | Paul  |  24 | Houston      |  20000
  3 | Teddy |  23 | Norway       |  20000
(10 rows)

Ниже приведен пример сортировки результата в порядке убывания по ИМЕНИ:

testdb=# SELECT * FROM COMPANY ORDER BY NAME DESC;

Это даст следующий результат -

id | name  | age | address    | salary
----+-------+-----+------------+--------
  3 | Teddy |  23 | Norway     |  20000
  1 | Paul  |  32 | California |  20000
  8 | Paul  |  24 | Houston    |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
  9 | James |  44 | Norway     |   5000
 10 | James |  45 | Texas      |   5000
  5 | David |  27 | Texas      |  85000
  2 | Allen |  25 | Texas      |  15000
(10 rows)

PostgreSQL GROUP BYПредложение используется совместно с оператором SELECT для группировки тех строк в таблице, которые имеют идентичные данные. Это сделано для устранения избыточности в выходных данных и / или вычислений агрегатов, которые применяются к этим группам.

Предложение GROUP BY следует за предложением WHERE в операторе SELECT и предшествует предложению ORDER BY.

Синтаксис

Базовый синтаксис предложения GROUP BY приведен ниже. Предложение GROUP BY должно соответствовать условиям в предложении WHERE и должно предшествовать предложению ORDER BY, если оно используется.

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

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

пример

Рассмотрим таблицу КОМПАНИЯ, имеющую следующие записи:

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Если вы хотите узнать общую сумму зарплаты каждого клиента, запрос GROUP BY будет следующим:

testdb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;

Это даст следующий результат -

name  |  sum
 -------+-------
  Teddy | 20000
  Paul  | 20000
  Mark  | 65000
  David | 85000
  Allen | 15000
  Kim   | 45000
  James | 10000
(7 rows)

Теперь давайте создадим еще три записи в таблице COMPANY, используя следующие инструкции INSERT -

INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);

Теперь в нашей таблице есть следующие записи с повторяющимися именами -

id | name  | age | address      | salary
 ----+-------+-----+--------------+--------
   1 | Paul  |  32 | California   |  20000
   2 | Allen |  25 | Texas        |  15000
   3 | Teddy |  23 | Norway       |  20000
   4 | Mark  |  25 | Rich-Mond    |  65000
   5 | David |  27 | Texas        |  85000
   6 | Kim   |  22 | South-Hall   |  45000
   7 | James |  24 | Houston      |  10000
   8 | Paul  |  24 | Houston      |  20000
   9 | James |  44 | Norway       |   5000
  10 | James |  45 | Texas        |   5000
(10 rows)

Опять же, давайте использовать тот же оператор для группировки всех записей с использованием столбца NAME следующим образом:

testdb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;

Это даст следующий результат -

name  |  sum
-------+-------
 Allen | 15000
 David | 85000
 James | 20000
 Kim   | 45000
 Mark  | 65000
 Paul  | 40000
 Teddy | 20000
(7 rows)

Давайте использовать предложение ORDER BY вместе с предложением GROUP BY следующим образом:

testdb=#  SELECT NAME, SUM(SALARY)
         FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;

Это даст следующий результат -

name  |  sum
-------+-------
 Teddy | 20000
 Paul  | 40000
 Mark  | 65000
 Kim   | 45000
 James | 20000
 David | 85000
 Allen | 15000
(7 rows)

В PostgreSQL запрос WITH предоставляет способ написать вспомогательные операторы для использования в более крупном запросе. Это помогает разбивать сложные и большие запросы на более простые, легко читаемые формы. Эти операторы, часто называемые общими табличными выражениями или CTE, можно рассматривать как определение временных таблиц, которые существуют только для одного запроса.

Запрос WITH, являющийся запросом CTE, особенно полезен, когда подзапрос выполняется несколько раз. Это также полезно вместо временных таблиц. Он вычисляет агрегацию один раз и позволяет нам ссылаться на нее по имени (может быть несколько раз) в запросах.

Предложение WITH необходимо определить до его использования в запросе.

Синтаксис

Базовый синтаксис запроса WITH следующий:

WITH
   name_for_summary_data AS (
      SELECT Statement)
   SELECT columns
   FROM name_for_summary_data
   WHERE conditions <=> (
      SELECT column
      FROM name_for_summary_data)
   [ORDER BY columns]

Где name_for_summary_data - это имя, данное предложению WITH. Name_for_summary_data может быть таким же, как имя существующей таблицы, и будет иметь приоритет.

В WITH можно использовать операторы изменения данных (INSERT, UPDATE или DELETE). Это позволяет выполнять несколько разных операций в одном запросе.

Рекурсивный WITH

пример

Рассмотрим таблицу КОМПАНИЯ, имеющую следующие записи:

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Теперь давайте напишем запрос, используя предложение WITH, чтобы выбрать записи из приведенной выше таблицы, следующим образом:

With CTE AS
(Select
 ID
, NAME
, AGE
, ADDRESS
, SALARY
FROM COMPANY )
Select * From CTE;

Приведенный выше оператор PostgreSQL даст следующий результат:

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Теперь давайте напишем запрос, используя ключевое слово RECURSIVE вместе с предложением WITH, чтобы найти сумму зарплат меньше 20000, следующим образом:

WITH RECURSIVE t(n) AS (
   VALUES (0)
   UNION ALL
   SELECT SALARY FROM COMPANY WHERE SALARY < 20000
)
SELECT sum(n) FROM t;

Приведенный выше оператор PostgreSQL даст следующий результат:

sum
-------
 25000
(1 row)

Давайте напишем запрос, используя операторы изменения данных вместе с предложением WITH, как показано ниже.

Сначала создайте таблицу COMPANY1, аналогичную таблице COMPANY. Запрос в примере эффективно перемещает строки из COMPANY в COMPANY1. DELETE в WITH удаляет указанные строки из COMPANY, возвращая их содержимое с помощью предложения RETURNING; а затем основной запрос считывает этот вывод и вставляет его в ТАБЛИЦУ COMPANY1 -

CREATE TABLE COMPANY1(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

WITH moved_rows AS (
   DELETE FROM COMPANY
   WHERE
      SALARY >= 30000
   RETURNING *
)
INSERT INTO COMPANY1 (SELECT * FROM moved_rows);

Приведенный выше оператор PostgreSQL даст следующий результат:

INSERT 0 3

Теперь записи в таблицах COMPANY и COMPANY1 следующие:

testdb=# SELECT * FROM COMPANY;
 id | name  | age |  address   | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  7 | James |  24 | Houston    |  10000
(4 rows)


testdb=# SELECT * FROM COMPANY1;
 id | name  | age | address | salary
----+-------+-----+-------------+--------
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
(3 rows)

Предложение HAVING позволяет нам выбирать конкретные строки, в которых результат функции соответствует некоторому условию.

Предложение WHERE помещает условия в выбранные столбцы, тогда как предложение HAVING помещает условия в группы, созданные предложением GROUP BY.

Синтаксис

Ниже приводится позиция предложения HAVING в запросе SELECT:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

Предложение HAVING должно следовать за предложением GROUP BY в запросе, а также должно предшествовать предложению ORDER BY, если оно используется. Ниже приводится синтаксис оператора SELECT, включая предложение HAVING:

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

пример

Рассмотрим таблицу КОМПАНИЯ, имеющую следующие записи:

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

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

testdb-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) < 2;

Это даст следующий результат -

name
 -------
  Teddy
  Paul
  Mark
  David
  Allen
  Kim
  James
(7 rows)

Теперь давайте создадим еще три записи в таблице COMPANY, используя следующие инструкции INSERT -

INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);

Теперь в нашей таблице есть следующие записи с повторяющимися именами -

id | name  | age | address      | salary
 ----+-------+-----+--------------+--------
   1 | Paul  |  32 | California   |  20000
   2 | Allen |  25 | Texas        |  15000
   3 | Teddy |  23 | Norway       |  20000
   4 | Mark  |  25 | Rich-Mond    |  65000
   5 | David |  27 | Texas        |  85000
   6 | Kim   |  22 | South-Hall   |  45000
   7 | James |  24 | Houston      |  10000
   8 | Paul  |  24 | Houston      |  20000
   9 | James |  44 | Norway       |   5000
  10 | James |  45 | Texas        |   5000
(10 rows)

Ниже приведен пример, в котором будет отображаться запись, для которой счетчик имен больше 1:

testdb-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) > 1;

Это даст следующий результат -

name
-------
 Paul
 James
(2 rows)

PostgreSQL DISTINCT ключевое слово используется в сочетании с оператором SELECT для удаления всех повторяющихся записей и получения только уникальных записей.

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

Синтаксис

Основной синтаксис ключевого слова DISTINCT для устранения повторяющихся записей выглядит следующим образом:

SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]

пример

Рассмотрим таблицу КОМПАНИЯ, имеющую следующие записи:

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Давайте добавим еще две записи в эту таблицу следующим образом:

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (8, 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (9, 'Allen', 25, 'Texas', 15000.00 );

Теперь записи в таблице COMPANY будут -

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
  8 | Paul  |  32 | California |  20000
  9 | Allen |  25 | Texas      |  15000
(9 rows)

Во-первых, давайте посмотрим, как следующий запрос SELECT возвращает повторяющиеся записи о зарплате:

testdb=# SELECT name FROM COMPANY;

Это даст следующий результат -

name
-------
 Paul
 Allen
 Teddy
 Mark
 David
 Kim
 James
 Paul
 Allen
(9 rows)

Теперь давайте использовать DISTINCT ключевое слово с указанным выше запросом SELECT и посмотрите результат -

testdb=# SELECT DISTINCT name FROM COMPANY;

Это приведет к следующему результату, когда у нас нет повторяющейся записи:

name
-------
 Teddy
 Paul
 Mark
 David
 Allen
 Kim
 James
(7 rows)

Ограничения - это правила, применяемые к столбцам данных в таблице. Они используются для предотвращения ввода неверных данных в базу данных. Это обеспечивает точность и надежность данных в базе данных.

Ограничения могут быть на уровне столбца или таблицы. Ограничения уровня столбца применяются только к одному столбцу, тогда как ограничения уровня таблицы применяются ко всей таблице. Определение типа данных для столбца само по себе является ограничением. Например, столбец типа DATE ограничивает столбец допустимыми датами.

Ниже перечислены обычно используемые ограничения, доступные в PostgreSQL.

NOT NULL Constraint - Гарантирует, что столбец не может иметь значение NULL.
UNIQUE Constraint - Гарантирует, что все значения в столбце различны.
PRIMARY Key - Однозначно идентифицирует каждую строку / запись в таблице базы данных.
FOREIGN Key - Ограничивает данные на основе столбцов в других таблицах.
CHECK Constraint - Ограничение CHECK гарантирует, что все значения в столбце удовлетворяют определенным условиям.
EXCLUSION Constraint - Ограничение EXCLUDE гарантирует, что при сравнении любых двух строк в указанном столбце (ах) или выражении (ах) с использованием указанного оператора (ов) не все эти сравнения вернут TRUE.

NOT NULL ограничение

По умолчанию столбец может содержать значения NULL. Если вы не хотите, чтобы столбец имел значение NULL, вам необходимо определить такое ограничение для этого столбца, указав, что NULL теперь не разрешен для этого столбца. Ограничение NOT NULL всегда записывается как ограничение столбца.

NULL - это не то же самое, что отсутствие данных; скорее, он представляет собой неизвестные данные.

пример

Например, следующий оператор PostgreSQL создает новую таблицу с именем COMPANY1 и добавляет пять столбцов, три из которых, ID, NAME и AGE, указывают не принимать значения NULL:

CREATE TABLE COMPANY1(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Уникальное ограничение

Ограничение UNIQUE не позволяет двум записям иметь одинаковые значения в определенном столбце. В таблице КОМПАНИЯ, например, вы можете запретить, чтобы два или более человека имели одинаковый возраст.

пример

Например, следующий оператор PostgreSQL создает новую таблицу с именем COMPANY3 и добавляет пять столбцов. Здесь для столбца AGE установлено значение UNIQUE, поэтому у вас не может быть двух записей с одинаковым возрастом -

CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

Ограничение PRIMARY KEY

Ограничение PRIMARY KEY однозначно идентифицирует каждую запись в таблице базы данных. В таблице может быть больше УНИКАЛЬНЫХ столбцов, но только один первичный ключ. Первичные ключи важны при разработке таблиц базы данных. Первичные ключи - это уникальные идентификаторы.

Мы используем их для обозначения строк таблицы. Первичные ключи становятся внешними ключами в других таблицах при создании отношений между таблицами. Из-за «давнего надзора за кодированием» первичные ключи в SQLite могут иметь значение NULL. Это не относится к другим базам данных.

Первичный ключ - это поле в таблице, которое однозначно идентифицирует каждую строку / запись в таблице базы данных. Первичные ключи должны содержать уникальные значения. Столбец первичного ключа не может иметь значения NULL.

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

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

пример

Вы уже видели различные примеры выше, где мы создали таблицу COMAPNY4 с идентификатором в качестве первичного ключа -

CREATE TABLE COMPANY4(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Ограничение FOREIGN KEY

Ограничение внешнего ключа указывает, что значения в столбце (или группе столбцов) должны соответствовать значениям, появляющимся в некоторой строке другой таблицы. Мы говорим, что это поддерживает ссылочную целостность между двумя связанными таблицами. Они называются внешними ключами, потому что ограничения являются внешними; то есть вне стола. Внешние ключи иногда называют ссылочным ключом.

пример

Например, следующий оператор PostgreSQL создает новую таблицу с именем COMPANY5 и добавляет пять столбцов.

CREATE TABLE COMPANY6(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Например, следующий оператор PostgreSQL создает новую таблицу с именем DEPARTMENT1, которая добавляет три столбца. Столбец EMP_ID является внешним ключом и ссылается на поле ID таблицы COMPANY6.

CREATE TABLE DEPARTMENT1(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      references COMPANY6(ID)
);

ПРОВЕРИТЬ ограничение

Ограничение CHECK позволяет условию проверять значение, вводимое в запись. Если условие оценивается как ложное, запись нарушает ограничение и не вводится в таблицу.

пример

Например, следующий оператор PostgreSQL создает новую таблицу с именем COMPANY5 и добавляет пять столбцов. Здесь мы добавляем ПРОВЕРКУ с столбцом ЗАРПЛАТА, чтобы вы не могли иметь ЗПЛАТУ как Ноль.

CREATE TABLE COMPANY5(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
);

Исключение Ограничение

Ограничения исключения гарантируют, что при сравнении любых двух строк в указанных столбцах или выражениях с использованием указанных операторов по крайней мере одно из этих сравнений операторов вернет false или null.

пример

Например, следующий оператор PostgreSQL создает новую таблицу с именем COMPANY7 и добавляет пять столбцов. Здесь мы добавляем ограничение EXCLUDE -

CREATE TABLE COMPANY7(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT,
   AGE            INT  ,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   EXCLUDE USING gist
   (NAME WITH =,
   AGE WITH <>)
);

Здесь USING gist - это тип индекса, который нужно создать и использовать для принудительного применения.

Вам необходимо выполнить команду CREATE EXTENSION btree_gist один раз для каждой базы данных. Это установит расширение btree_gist, которое определяет ограничения исключения для простых скалярных типов данных.

Поскольку мы установили, что возраст должен быть таким же, давайте посмотрим на это, вставив записи в таблицу -

INSERT INTO COMPANY7 VALUES(1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY7 VALUES(2, 'Paul', 32, 'Texas', 20000.00 );
INSERT INTO COMPANY7 VALUES(3, 'Paul', 42, 'California', 20000.00 );

Для первых двух операторов INSERT записи добавляются в таблицу COMPANY7. Для третьего оператора INSERT отображается следующая ошибка -

ERROR:  conflicting key value violates exclusion constraint "company7_name_age_excl"
DETAIL:  Key (name, age)=(Paul, 42) conflicts with existing key (name, age)=(Paul, 32).

Удаление ограничений

Чтобы снять ограничение, вам нужно знать его имя. Если имя известно, его легко бросить. В противном случае вам нужно узнать имя, созданное системой. Здесь может пригодиться команда psql \ d имя таблицы. Общий синтаксис -

ALTER TABLE table_name DROP CONSTRAINT some_name;

PostgreSQL JoinsПредложение используется для объединения записей из двух или более таблиц в базе данных. JOIN - это средство для объединения полей из двух таблиц с использованием значений, общих для каждой из них.

Типы соединения в PostgreSQL:

КРЕСТ ПРИСОЕДИНЯТЬСЯ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ
ЛЕВОЕ НАРУЖНОЕ СОЕДИНЕНИЕ
ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ
ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ

Прежде чем продолжить, давайте рассмотрим две таблицы: КОМПАНИЯ и ОТДЕЛ. Мы уже видели инструкции INSERT для заполнения таблицы COMPANY. Итак, давайте предположим список записей, доступных в таблице COMPANY -

id | name  | age | address   | salary | join_date
----+-------+-----+-----------+--------+-----------
  1 | Paul  |  32 | California|  20000 | 2001-07-13
  3 | Teddy |  23 | Norway    |  20000 |
  4 | Mark  |  25 | Rich-Mond |  65000 | 2007-12-13
  5 | David |  27 | Texas     |  85000 | 2007-12-13
  2 | Allen |  25 | Texas     |        | 2007-12-13
  8 | Paul  |  24 | Houston   |  20000 | 2005-07-13
  9 | James |  44 | Norway    |   5000 | 2005-07-13
 10 | James |  45 | Texas     |   5000 | 2005-07-13

Другая таблица - ОТДЕЛЕНИЕ, имеет следующее определение -

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

Вот список операторов INSERT для заполнения таблицы DEPARTMENT -

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (1, 'IT Billing', 1 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (2, 'Engineering', 2 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (3, 'Finance', 7 );

Наконец, у нас есть следующий список записей, доступных в таблице DEPARTMENT -

id | dept        | emp_id
----+-------------+--------
  1 | IT Billing  |  1
  2 | Engineering |  2
  3 | Finance     |  7

КРЕСТ ПРИСОЕДИНЯТЬСЯ

CROSS JOIN сопоставляет каждую строку первой таблицы с каждой строкой второй таблицы. Если входные таблицы имеют столбцы x и y, соответственно, итоговая таблица будет иметь столбцы x + y. Поскольку CROSS JOIN могут генерировать очень большие таблицы, следует использовать их только тогда, когда это необходимо.

Ниже приводится синтаксис CROSS JOIN -

SELECT ... FROM table1 CROSS JOIN table2 ...

Основываясь на приведенных выше таблицах, мы можем написать CROSS JOIN следующим образом:

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

Вышеуказанный запрос даст следующий результат -

emp_id| name  |  dept
------|-------|--------------
    1 | Paul  | IT Billing
    1 | Teddy | IT Billing
    1 | Mark  | IT Billing
    1 | David | IT Billing
    1 | Allen | IT Billing
    1 | Paul  | IT Billing
    1 | James | IT Billing
    1 | James | IT Billing
    2 | Paul  | Engineering
    2 | Teddy | Engineering
    2 | Mark  | Engineering
    2 | David | Engineering
    2 | Allen | Engineering
    2 | Paul  | Engineering
    2 | James | Engineering
    2 | James | Engineering
    7 | Paul  | Finance
    7 | Teddy | Finance
    7 | Mark  | Finance
    7 | David | Finance
    7 | Allen | Finance
    7 | Paul  | Finance
    7 | James | Finance
    7 | James | Finance

ВНУТРЕННЕЕ СОЕДИНЕНИЕ

INNER JOIN создает новую таблицу результатов путем объединения значений столбцов двух таблиц (table1 и table2) на основе предиката соединения. Запрос сравнивает каждую строку таблицы table1 с каждой строкой table2, чтобы найти все пары строк, которые удовлетворяют предикату соединения. Когда предикат соединения удовлетворяется, значения столбцов для каждой совпавшей пары строк table1 и table2 объединяются в строку результата.

ВНУТРЕННЕЕ СОЕДИНЕНИЕ - это наиболее распространенный тип соединения и тип соединения по умолчанию. При желании вы можете использовать ключевое слово INNER.

Ниже приводится синтаксис INNER JOIN -

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;

Основываясь на приведенных выше таблицах, мы можем написать INNER JOIN следующим образом:

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;

Вышеуказанный запрос даст следующий результат -

emp_id | name  | dept
--------+-------+------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering

ЛЕВОЕ НАРУЖНОЕ СОЕДИНЕНИЕ

OUTER JOIN - это расширение INNER JOIN. Стандарт SQL определяет три типа ВНЕШНИХ СОЕДИНЕНИЙ: LEFT, RIGHT и FULL, и PostgreSQL поддерживает все это.

В случае LEFT OUTER JOIN сначала выполняется внутреннее соединение. Затем для каждой строки в таблице T1, которая не удовлетворяет условию соединения с какой-либо строкой в таблице T2, добавляется объединенная строка с нулевыми значениями в столбцах T2. Таким образом, в объединенной таблице всегда есть хотя бы одна строка для каждой строки в T1.

Ниже приведен синтаксис LEFT OUTER JOIN -

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

Основываясь на приведенных выше таблицах, мы можем написать внутреннее соединение следующим образом:

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

Вышеуказанный запрос даст следующий результат -

emp_id | name  | dept
--------+-------+------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
        | James |
        | David |
        | Paul  |
        | Mark  |
        | Teddy |
        | James |

ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ

Сначала выполняется внутреннее соединение. Затем для каждой строки в таблице T2, которая не удовлетворяет условию соединения с какой-либо строкой в таблице T1, добавляется объединенная строка с нулевыми значениями в столбцах T1. Это обратное левому соединению; в таблице результатов всегда будет строка для каждой строки в T2.

Ниже приведен синтаксис RIGHT OUTER JOIN -

SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...

Основываясь на приведенных выше таблицах, мы можем написать внутреннее соединение следующим образом:

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

Вышеуказанный запрос даст следующий результат -

emp_id | name  | dept
--------+-------+--------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 |       | Finance

ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ

Сначала выполняется внутреннее соединение. Затем для каждой строки в таблице T1, которая не удовлетворяет условию соединения с какой-либо строкой в таблице T2, добавляется объединенная строка с нулевыми значениями в столбцах T2. Кроме того, для каждой строки T2, которая не удовлетворяет условию соединения с какой-либо строкой в T1, добавляется объединенная строка с нулевыми значениями в столбцах T1.

Ниже приведен синтаксис FULL OUTER JOIN -

SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...

Основываясь на приведенных выше таблицах, мы можем написать внутреннее соединение следующим образом:

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

Вышеуказанный запрос даст следующий результат -

emp_id | name  | dept
--------+-------+---------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 |       | Finance
        | James |
        | David |
        | Paul  |
        | Mark  |
        | Teddy |
        | James |

PostgreSQL UNION Предложение / оператор используется для объединения результатов двух или более операторов SELECT без возврата повторяющихся строк.

Чтобы использовать UNION, каждый SELECT должен иметь одинаковое количество выбранных столбцов, одинаковое количество выражений столбцов, один и тот же тип данных и иметь их в одинаковом порядке, но не обязательно иметь одинаковую длину.

Синтаксис

Базовый синтаксис UNION выглядит следующим образом -

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Здесь заданным условием может быть любое заданное выражение в зависимости от вашего требования.

пример

Рассмотрим следующие две таблицы: (а) Таблица КОМПАНИИ выглядит следующим образом:

testdb=# SELECT * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

(b) Другая таблица - это ОТДЕЛЕНИЕ :

testdb=# SELECT * from DEPARTMENT;
 id | dept        | emp_id
----+-------------+--------
  1 | IT Billing  |      1
  2 | Engineering |      2
  3 | Finance     |      7
  4 | Engineering |      3
  5 | Finance     |      4
  6 | Engineering |      5
  7 | Finance     |      6
(7 rows)

Теперь давайте объединим эти две таблицы, используя оператор SELECT вместе с предложением UNION следующим образом:

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID
   UNION
      SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID;

Это даст следующий результат -

emp_id | name  |  dept
--------+-------+--------------
      5 | David | Engineering
      6 | Kim   | Finance
      2 | Allen | Engineering
      3 | Teddy | Engineering
      4 | Mark  | Finance
      1 | Paul  | IT Billing
      7 | James | Finance
(7 rows)

Статья UNION ALL

Оператор UNION ALL используется для объединения результатов двух операторов SELECT, включая повторяющиеся строки. Те же правила, которые применяются к UNION, применимы и к оператору UNION ALL.

Синтаксис

Базовый синтаксис UNION ALL выглядит следующим образом -

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Здесь заданным условием может быть любое заданное выражение в зависимости от вашего требования.

пример

Теперь давайте объединим две вышеупомянутые таблицы в нашем операторе SELECT следующим образом:

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID
   UNION ALL
      SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID;

Это даст следующий результат -

emp_id | name  | dept
--------+-------+--------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
      3 | Teddy | Engineering
      4 | Mark  | Finance
      5 | David | Engineering
      6 | Kim   | Finance
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
      3 | Teddy | Engineering
      4 | Mark  | Finance
      5 | David | Engineering
      6 | Kim   | Finance
(14 rows)

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

Поле со значением NULL - это поле без значения. Очень важно понимать, что значение NULL отличается от нулевого значения или поля, содержащего пробелы.

Синтаксис

Базовый синтаксис использования NULL при создании таблицы выглядит следующим образом -

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Вот, NOT NULLозначает, что столбец всегда должен принимать явное значение данного типа данных. Есть два столбца, в которых мы не использовали NOT NULL. Следовательно, это означает, что эти столбцы могут иметь значение NULL.

Поле со значением NULL - это поле, которое было оставлено пустым во время создания записи.

пример

Значение NULL может вызвать проблемы при выборе данных, поскольку при сравнении неизвестного значения с любым другим значением результат всегда неизвестен и не включается в окончательные результаты. Рассмотрим следующую таблицу, КОМПАНИЯ имеет следующие записи -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Давайте использовать оператор UPDATE, чтобы установить несколько значений, допускающих значение NULL, как NULL следующим образом:

testdb=# UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);

Теперь в таблице КОМПАНИЯ должны быть следующие записи -

id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 |             |
  7 | James |  24 |             |
(7 rows)

Затем давайте посмотрим, как использовать IS NOT NULL оператор, чтобы вывести список всех записей, где SALARY не NULL -

testdb=#  SELECT  ID, NAME, AGE, ADDRESS, SALARY
   FROM COMPANY
   WHERE SALARY IS NOT NULL;

Приведенный выше оператор PostgreSQL даст следующий результат:

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(5 rows)

Ниже приводится использование IS NULL оператор, который выведет список всех записей, в которых ЗАПЧАСТЬ равна NULL -

testdb=#  SELECT  ID, NAME, AGE, ADDRESS, SALARY
        FROM COMPANY
        WHERE SALARY IS NULL;

Приведенный выше оператор PostgreSQL даст следующий результат:

id | name  | age | address | salary
----+-------+-----+---------+--------
  6 | Kim   |  22 |         |
  7 | James |  24 |         |
(2 rows)

Вы можете временно переименовать таблицу или столбец, присвоив другое имя, известное как ALIAS. Использование псевдонимов таблиц означает переименование таблицы в конкретном операторе PostgreSQL. Переименование - это временное изменение, и фактическое имя таблицы в базе данных не изменяется.

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

Синтаксис

Базовый синтаксис table псевдоним выглядит следующим образом -

SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];

Базовый синтаксис column псевдоним выглядит следующим образом -

SELECT column_name AS alias_name
FROM table_name
WHERE [condition];

пример

Рассмотрим следующие две таблицы: (а) Таблица КОМПАНИИ выглядит следующим образом:

testdb=# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

(b) Другая таблица - это ОТДЕЛЕНИЕ :

id | dept         | emp_id
----+--------------+--------
  1 | IT Billing   |      1
  2 | Engineering  |      2
  3 | Finance      |      7
  4 | Engineering  |      3
  5 | Finance      |      4
  6 | Engineering  |      5
  7 | Finance      |      6
(7 rows)

Теперь следующее использование TABLE ALIAS где мы используем C и D как псевдонимы для таблиц COMPANY и DEPARTMENT соответственно -

testdb=# SELECT C.ID, C.NAME, C.AGE, D.DEPT
   FROM COMPANY AS C, DEPARTMENT AS D
   WHERE  C.ID = D.EMP_ID;

Приведенный выше оператор PostgreSQL даст следующий результат:

id | name  | age |  dept
----+-------+-----+------------
  1 | Paul  |  32 | IT Billing
  2 | Allen |  25 | Engineering
  7 | James |  24 | Finance
  3 | Teddy |  23 | Engineering
  4 | Mark  |  25 | Finance
  5 | David |  27 | Engineering
  6 | Kim   |  22 | Finance
(7 rows)

Давайте посмотрим на пример использования COLUMN ALIAS где COMPANY_ID - это псевдоним столбца идентификатора, а COMPANY_NAME - псевдоним столбца имени -

testdb=# SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT
   FROM COMPANY AS C, DEPARTMENT AS D
   WHERE  C.ID = D.EMP_ID;

Приведенный выше оператор PostgreSQL даст следующий результат:

company_id | company_name | age | dept
------------+--------------+-----+------------
      1     | Paul         |  32 | IT Billing
      2     | Allen        |  25 | Engineering
      7     | James        |  24 | Finance
      3     | Teddy        |  23 | Engineering
      4     | Mark         |  25 | Finance
      5     | David        |  27 | Engineering
      6     | Kim          |  22 | Finance
(7 rows)

PostgreSQL Triggers - это функции обратного вызова базы данных, которые автоматически выполняются / вызываются при наступлении указанного события базы данных.

Ниже приведены важные моменты о триггерах PostgreSQL:

Можно указать триггер PostgreSQL для запуска
- Перед попыткой выполнения операции над строкой (перед проверкой ограничений и попыткой выполнения INSERT, UPDATE или DELETE)
- После завершения операции (после проверки ограничений и выполнения INSERT, UPDATE или DELETE)
- Вместо операции (в случае вставки, обновления или удаления в представлении)
Триггер, помеченный FOR EACH ROW, вызывается один раз для каждой строки, изменяемой операцией. Напротив, триггер, помеченный FOR EACH STATEMENT, выполняется только один раз для любой данной операции, независимо от того, сколько строк он изменяет.
И предложение WHEN, и действия триггера могут обращаться к элементам строки, которая вставляется, удаляется или обновляется, используя ссылки формы NEW.column-name и OLD.column-name, где имя-столбца - это имя столбца из таблицы, с которой связан триггер.
Если указано предложение WHEN, указанные операторы PostgreSQL выполняются только для строк, для которых предложение WHEN истинно. Если предложение WHEN не указано, операторы PostgreSQL выполняются для всех строк.
Если для одного и того же события определено несколько триггеров одного типа, они будут срабатывать в алфавитном порядке по имени.
Ключевое слово BEFORE, AFTER или INSTEAD OF определяет, когда будут выполняться действия триггера относительно вставки, изменения или удаления связанной строки.
Триггеры автоматически удаляются при удалении таблицы, с которой они связаны.
Изменяемая таблица должна существовать в той же базе данных, что и таблица или представление, к которому привязан триггер, и нужно использовать только tablenameне database.tablename.
Опция CONSTRAINT, если она указана, создает триггер ограничения . Это то же самое, что и обычный триггер, за исключением того, что время срабатывания триггера можно настроить с помощью SET CONSTRAINTS. Ожидается, что триггеры ограничения вызовут исключение при нарушении реализуемых ими ограничений.

Синтаксис

Базовый синтаксис создания trigger выглядит следующим образом -

CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
 -- Trigger logic goes here....
];

Вот, event_nameможет быть операцией базы данных INSERT, DELETE, UPDATE и TRUNCATE для указанной таблицыtable_name. Вы можете дополнительно указать FOR EACH ROW после имени таблицы.

Ниже приводится синтаксис создания триггера для операции UPDATE для одного или нескольких указанных столбцов таблицы следующим образом:

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
 -- Trigger logic goes here....
];

пример

Давайте рассмотрим случай, когда мы хотим сохранить контрольную пробу для каждой записи, вставляемой в таблицу COMPANY, которую мы создадим следующим образом (удалите таблицу COMPANY, если она у вас уже есть).

testdb=# CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Чтобы продолжить аудит, мы создадим новую таблицу под названием AUDIT, куда будут вставляться сообщения журнала всякий раз, когда в таблице COMPANY есть запись для новой записи

testdb=# CREATE TABLE AUDIT(
   EMP_ID INT NOT NULL,
   ENTRY_DATE TEXT NOT NULL
);

Здесь ID - это идентификатор записи AUDIT, а EMP_ID - это идентификатор, который будет взят из таблицы COMPANY, а DATE сохранит метку времени, когда запись будет создана в таблице COMPANY. Итак, теперь давайте создадим триггер для таблицы COMPANY следующим образом:

testdb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY
FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();

Где auditlogfunc () - это PostgreSQL procedure и имеет следующее определение -

CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
   BEGIN
      INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
      RETURN NEW;
   END;
$example_table$ LANGUAGE plpgsql;

Теперь приступим к самой работе. Давайте начнем вставлять запись в таблицу COMPANY, что должно привести к созданию записи журнала аудита в таблице AUDIT. Итак, давайте создадим одну запись в таблице COMPANY следующим образом:

testdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

Это создаст одну запись в таблице COMPANY, которая выглядит следующим образом:

id | name | age | address      | salary
----+------+-----+--------------+--------
  1 | Paul |  32 | California   |  20000

В то же время в таблице AUDIT будет создана одна запись. Эта запись является результатом триггера, который мы создали для операции INSERT в таблице COMPANY. Точно так же вы можете создавать свои триггеры для операций UPDATE и DELETE в зависимости от ваших требований.

emp_id |          entry_date
--------+-------------------------------
      1 | 2013-05-05 15:49:59.968+05:30
(1 row)

Листинг ТРИГГЕРОВ

Вы можете перечислить все триггеры в текущей базе данных из pg_trigger таблица следующим образом -

testdb=# SELECT * FROM pg_trigger;

Приведенный выше оператор PostgreSQL перечислит все триггеры.

Если вы хотите перечислить триггеры в определенной таблице, используйте предложение AND с именем таблицы следующим образом:

testdb=# SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';

Вышеуказанный оператор PostgreSQL также перечислит только одну запись следующим образом:

tgname
-----------------
 example_trigger
(1 row)

Отбрасывание ТРИГГЕРОВ

Ниже приводится команда DROP, которую можно использовать для отбрасывания существующего триггера:

testdb=# DROP TRIGGER trigger_name;

Индексы - это специальные таблицы поиска, которые поисковая машина по базам данных может использовать для ускорения поиска данных. Проще говоря, индекс - это указатель на данные в таблице. Указатель в базе данных очень похож на указатель в конце книги.

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

Индекс помогает ускорить запросы SELECT и предложения WHERE; однако он замедляет ввод данных с помощью операторов UPDATE и INSERT. Индексы можно создавать или удалять, не влияя на данные.

Создание индекса включает в себя оператор CREATE INDEX, который позволяет вам назвать индекс, указать таблицу и столбец или столбцы для индексации, а также указать, находится ли индекс в порядке возрастания или убывания.

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

Команда CREATE INDEX

Базовый синтаксис CREATE INDEX выглядит следующим образом -

CREATE INDEX index_name ON table_name;

Типы индексов

PostgreSQL предоставляет несколько типов индексов: B-tree, Hash, GiST, SP-GiST и GIN. Каждый тип индекса использует свой алгоритм, который лучше всего подходит для разных типов запросов. По умолчанию команда CREATE INDEX создает индексы B-дерева, которые подходят для наиболее распространенных ситуаций.

Одностолбцовые индексы

Индекс с одним столбцом - это индекс, который создается на основе только одного столбца таблицы. Основной синтаксис выглядит следующим образом -

CREATE INDEX index_name
ON table_name (column_name);

Многоколоночные индексы

Многоколоночный индекс определяется более чем в одном столбце таблицы. Основной синтаксис выглядит следующим образом -

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

Независимо от того, создавать ли индекс с одним столбцом или индекс с несколькими столбцами, примите во внимание столбцы, которые вы можете очень часто использовать в предложении WHERE запроса в качестве условий фильтрации.

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

Уникальные индексы

Уникальные индексы используются не только для повышения производительности, но и для целостности данных. Уникальный индекс не позволяет вставлять в таблицу повторяющиеся значения. Основной синтаксис выглядит следующим образом -

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Частичные индексы

Частичный индекс - это индекс, построенный по подмножеству таблицы; подмножество определяется условным выражением (называемым предикатом частичного индекса). Индекс содержит записи только для тех строк таблицы, которые удовлетворяют предикату. Основной синтаксис выглядит следующим образом -

CREATE INDEX index_name
on table_name (conditional_expression);

Неявные индексы

Неявные индексы - это индексы, которые автоматически создаются сервером базы данных при создании объекта. Индексы автоматически создаются для ограничений первичного ключа и уникальных ограничений.

пример

Ниже приведен пример, в котором мы создадим индекс в таблице КОМПАНИИ для столбца зарплаты.

# CREATE INDEX salary_index ON COMPANY (salary);

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

# \d company

Это даст следующий результат, где company_pkey - это неявный индекс, который был создан при создании таблицы.

Table "public.company"
 Column  |     Type      | Modifiers
---------+---------------+-----------
 id      | integer       | not null
 name    | text          | not null
 age     | integer       | not null
 address | character(50) |
 salary  | real          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)
    "salary_index" btree (salary)

Вы можете перечислить всю базу данных индексов, используя \di команда -

Команда DROP INDEX

Индекс можно удалить с помощью PostgreSQL DROPкоманда. При удалении индекса следует соблюдать осторожность, поскольку производительность может снизиться или повыситься.

Основной синтаксис выглядит следующим образом -

DROP INDEX index_name;

Вы можете использовать следующий оператор для удаления ранее созданного индекса -

# DROP INDEX salary_index;

Когда следует избегать индексов?

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

Индексы не следует использовать для небольших таблиц.
Таблицы, для которых выполняются частые, большие пакетные операции обновления или вставки.
Индексы не следует использовать для столбцов, содержащих большое количество значений NULL.
Столбцы, которыми часто манипулируют, не следует индексировать.

PostgreSQL ALTER TABLE Команда используется для добавления, удаления или изменения столбцов в существующей таблице.

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

Синтаксис

Базовый синтаксис ALTER TABLE добавить новый столбец в существующую таблицу выглядит следующим образом:

ALTER TABLE table_name ADD column_name datatype;

Базовый синтаксис ALTER TABLE to DROP COLUMN в существующей таблице выглядит следующим образом -

ALTER TABLE table_name DROP COLUMN column_name;

Базовый синтаксис ALTER TABLE для изменения DATA TYPE столбца в таблице выглядит следующим образом -

ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;

Базовый синтаксис ALTER TABLE для добавления NOT NULL ограничение для столбца в таблице выглядит следующим образом:

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

Базовый синтаксис ALTER TABLE to ADD UNIQUE CONSTRAINT к таблице выглядит следующим образом -

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

Базовый синтаксис ALTER TABLE to ADD CHECK CONSTRAINT к таблице выглядит следующим образом -

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

Базовый синтаксис ALTER TABLE to ADD PRIMARY KEY ограничение для таблицы выглядит следующим образом -

ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

Базовый синтаксис ALTER TABLE to DROP CONSTRAINT из таблицы выглядит следующим образом -

ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;

Если вы используете MySQL, код выглядит следующим образом -

ALTER TABLE table_name
DROP INDEX MyUniqueConstraint;

Базовый синтаксис ALTER TABLE to DROP PRIMARY KEY ограничение из таблицы выглядит следующим образом -

ALTER TABLE table_name
DROP CONSTRAINT MyPrimaryKey;

Если вы используете MySQL, код выглядит следующим образом -

ALTER TABLE table_name
DROP PRIMARY KEY;

пример

Учтите, что в нашей таблице КОМПАНИЯ есть следующие записи -

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

Ниже приведен пример ДОБАВЛЕНИЯ нового столбца в существующей таблице.

testdb=# ALTER TABLE COMPANY ADD GENDER char(1);

Теперь таблица COMPANY изменена, и следующий результат будет выводом из оператора SELECT:

id | name  | age | address     | salary | gender
----+-------+-----+-------------+--------+--------
  1 | Paul  |  32 | California  |  20000 |
  2 | Allen |  25 | Texas       |  15000 |
  3 | Teddy |  23 | Norway      |  20000 |
  4 | Mark  |  25 | Rich-Mond   |  65000 |
  5 | David |  27 | Texas       |  85000 |
  6 | Kim   |  22 | South-Hall  |  45000 |
  7 | James |  24 | Houston     |  10000 |
(7 rows)

Ниже приведен пример удаления столбца пола из существующей таблицы.

testdb=# ALTER TABLE COMPANY DROP GENDER;

Теперь таблица COMPANY изменена, и следующий результат будет выводом из оператора SELECT:

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

PostgreSQL TRUNCATE TABLEКоманда используется для удаления полных данных из существующей таблицы. Вы также можете использовать команду DROP TABLE для удаления всей таблицы, но она удалит всю структуру таблицы из базы данных, и вам придется заново создать эту таблицу, если вы хотите сохранить некоторые данные.

Он имеет тот же эффект, что и DELETE для каждой таблицы, но, поскольку он фактически не сканирует таблицы, он работает быстрее. Более того, он немедленно освобождает дисковое пространство, а не требует последующей операции VACUUM. Это наиболее полезно для больших столов.

Синтаксис

Базовый синтаксис TRUNCATE TABLE выглядит следующим образом -

TRUNCATE TABLE  table_name;

пример

Учтите, что в таблице КОМПАНИЯ есть следующие записи -

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
(7 rows)

Ниже приведен пример усечения -

testdb=# TRUNCATE TABLE COMPANY;

Теперь таблица COMPANY усечена, и следующий результат будет выводом оператора SELECT:

testdb=# SELECT * FROM CUSTOMERS;
 id | name | age | address | salary
----+------+-----+---------+--------
(0 rows)

Представления - это псевдотаблицы. То есть это не настоящие таблицы; тем не менее, для SELECT отображаются как обычные таблицы. Представление может представлять подмножество реальной таблицы, выбирая определенные столбцы или определенные строки из обычной таблицы. Представление может даже представлять объединенные таблицы. Поскольку представлениям назначаются отдельные разрешения, вы можете использовать их для ограничения доступа к таблице, чтобы пользователи видели только определенные строки или столбцы таблицы.

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

Представления, которые являются своего рода виртуальными таблицами, позволяют пользователям делать следующее:

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

Поскольку представления не являются обычными таблицами, вы не сможете выполнить оператор DELETE, INSERT или UPDATE для представления. Однако вы можете создать ПРАВИЛО для устранения этой проблемы с использованием DELETE, INSERT или UPDATE в представлении.

Создание представлений

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

Базовый синтаксис CREATE VIEW следующий:

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

Вы можете включить несколько таблиц в свой оператор SELECT так же, как вы используете их в обычном запросе PostgreSQL SELECT. Если присутствует необязательное ключевое слово TEMP или TEMPORARY, представление будет создано во временном пространстве. Временные представления автоматически удаляются в конце текущего сеанса.

пример

Учтите, что в таблице КОМПАНИЯ есть следующие записи -

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000

Теперь ниже приведен пример создания представления из таблицы COMPANY. Это представление будет использоваться, чтобы иметь только несколько столбцов из таблицы COMPANY -

testdb=# CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM  COMPANY;

Теперь вы можете запросить COMPANY_VIEW так же, как вы запрашиваете фактическую таблицу. Ниже приведен пример -

testdb=# SELECT * FROM COMPANY_VIEW;

Это даст следующий результат -

id | name  | age
----+-------+-----
  1 | Paul  |  32
  2 | Allen |  25
  3 | Teddy |  23
  4 | Mark  |  25
  5 | David |  27
  6 | Kim   |  22
  7 | James |  24
(7 rows)

Удаление просмотров

Чтобы удалить представление, просто используйте оператор DROP VIEW с view_name. Базовый синтаксис DROP VIEW следующий:

testdb=# DROP VIEW view_name;

Следующая команда удалит представление COMPANY_VIEW, которое мы создали в последнем разделе -

testdb=# DROP VIEW COMPANY_VIEW;

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

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

Фактически вы объедините множество запросов PostgreSQL в группу и будете выполнять их все вместе как часть транзакции.

Свойства транзакций

Транзакции имеют следующие четыре стандартных свойства, обычно обозначаемых аббревиатурой ACID -

Atomicity- Гарантирует, что все операции в рамках единицы работы завершены успешно; в противном случае транзакция прерывается в момент сбоя, а предыдущие операции возвращаются в исходное состояние.
Consistency - Гарантирует, что база данных правильно меняет состояния после успешно зафиксированной транзакции.
Isolation - Позволяет транзакциям работать независимо и прозрачно друг для друга.
Durability - Гарантирует сохранение результата или эффекта зафиксированной транзакции в случае сбоя системы.

Контроль транзакций

Следующие команды используются для управления транзакциями -

BEGIN TRANSACTION - Начать сделку.
COMMIT - Чтобы сохранить изменения, в качестве альтернативы вы можете использовать END TRANSACTION команда.
ROLLBACK - Отменить изменения.

Команды управления транзакциями используются только с командами DML INSERT, UPDATE и DELETE. Их нельзя использовать при создании таблиц или их удалении, поскольку эти операции автоматически фиксируются в базе данных.

Команда BEGIN TRANSACTION

Транзакции можно запустить с помощью BEGIN TRANSACTION или просто команды BEGIN. Такие транзакции обычно сохраняются до тех пор, пока не встретится следующая команда COMMIT или ROLLBACK. Но транзакция также откатится, если база данных закрыта или возникнет ошибка.

Ниже приведен простой синтаксис для начала транзакции:

BEGIN;

or

BEGIN TRANSACTION;

Команда COMMIT

Команда COMMIT - это транзакционная команда, используемая для сохранения изменений, вызванных транзакцией, в базе данных.

Команда COMMIT сохраняет все транзакции в базе данных с момента последней команды COMMIT или ROLLBACK.

Синтаксис команды COMMIT следующий:

COMMIT;

or

END TRANSACTION;

Команда ROLLBACK

Команда ROLLBACK - это транзакционная команда, используемая для отмены транзакций, которые еще не были сохранены в базе данных.

Команду ROLLBACK можно использовать только для отмены транзакций с момента выполнения последней команды COMMIT или ROLLBACK.

Синтаксис команды ROLLBACK следующий:

ROLLBACK;

пример

Учтите, что в таблице КОМПАНИЯ есть следующие записи:

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

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

testdb=# BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
ROLLBACK;

Если вы проверите, что в таблице КОМПАНИИ все еще есть следующие записи -

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

Теперь давайте запустим еще одну транзакцию и удалим записи из таблицы, имеющие возраст = 25, и, наконец, мы используем команду COMMIT для фиксации всех изменений.

testdb=# BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;

Если вы проверите таблицу КОМПАНИИ, в ней все еще есть следующие записи:

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  3 | Teddy |  23 | Norway     |  20000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
(5 rows)

Замки или исключительные блокировки или блокировка записи предотвратить изменение пользователей строки или всю таблицу. Строки, измененные с помощью UPDATE и DELETE, затем автоматически блокируются исключительно на время транзакции. Это предотвращает изменение строки другими пользователями до тех пор, пока транзакция не будет зафиксирована или откатена.

Единственный раз, когда пользователи должны ждать других пользователей, - это когда они пытаются изменить ту же строку. Если они изменяют разные строки, ждать не нужно. Запросы SELECT никогда не ждут.

База данных выполняет блокировку автоматически. Однако в некоторых случаях блокировкой необходимо управлять вручную. Ручную блокировку можно выполнить с помощью команды LOCK. Это позволяет указать тип и область блокировки транзакции.

Синтаксис команды LOCK

Основной синтаксис команды LOCK следующий:

LOCK [ TABLE ]
name
 IN
lock_mode

name- Имя (возможно, дополненное схемой) существующей таблицы для блокировки. Если перед именем таблицы указано ТОЛЬКО, блокируется только эта таблица. Если ONLY не указан, таблица и все ее дочерние таблицы (если есть) блокируются.
lock_mode- Режим блокировки указывает, с какими блокировками конфликтует эта блокировка. Если режим блокировки не указан, используется ACCESS EXCLUSIVE, наиболее ограничительный режим. Возможные значения: ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE.

После получения блокировка сохраняется до конца текущей транзакции. Нет команды UNLOCK TABLE; блокировки всегда снимаются в конце транзакции.

DeadLocks

Взаимоблокировки могут возникать, когда две транзакции ждут завершения операций друг друга. Хотя PostgreSQL может их обнаружить и завершить с помощью ROLLBACK, взаимоблокировки по-прежнему могут быть неудобными. Чтобы ваши приложения не столкнулись с этой проблемой, убедитесь, что они спроектированы таким образом, чтобы они блокировали объекты в том же порядке.

Консультативные блокировки

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

Например, обычно рекомендательные блокировки используются для имитации стратегий пессимистических блокировок, типичных для так называемых систем управления данными «плоских файлов». Хотя флаг, хранящийся в таблице, можно использовать для той же цели, рекомендательные блокировки выполняются быстрее, предотвращают раздувание таблицы и автоматически очищаются сервером в конце сеанса.

пример

Рассмотрим таблицу КОМПАНИЯ, имеющую следующие записи:

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

В следующем примере таблица COMPANY в базе данных testdb блокируется в режиме ACCESS EXCLUSIVE. Оператор LOCK работает только в режиме транзакции -

testdb=#BEGIN;
LOCK TABLE company1 IN ACCESS EXCLUSIVE MODE;

Приведенный выше оператор PostgreSQL даст следующий результат:

LOCK TABLE

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

Подзапрос, внутренний запрос или вложенный запрос - это запрос в другом запросе PostgreSQL, встроенный в предложение WHERE.

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

Подзапросы можно использовать с операторами SELECT, INSERT, UPDATE и DELETE вместе с такими операторами, как =, <,>,> =, <=, IN и т. Д.

Есть несколько правил, которым должны следовать подзапросы:

Подзапросы должны быть заключены в круглые скобки.
Подзапрос может иметь только один столбец в предложении SELECT, если только несколько столбцов не входят в основной запрос для подзапроса для сравнения выбранных столбцов.
ORDER BY нельзя использовать в подзапросе, хотя в основном запросе можно использовать ORDER BY. GROUP BY может использоваться для выполнения той же функции, что и ORDER BY в подзапросе.
Подзапросы, возвращающие более одной строки, могут использоваться только с операторами нескольких значений, такими как оператор IN, EXISTS, NOT IN, ANY / SOME, ALL.
Оператор BETWEEN нельзя использовать с подзапросом; однако BETWEEN можно использовать в подзапросе.

Подзапросы с оператором SELECT

Подзапросы чаще всего используются с оператором SELECT. Основной синтаксис выглядит следующим образом -

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

пример

Рассмотрим таблицу КОМПАНИИ, имеющую следующие записи -

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Теперь давайте проверим следующий подзапрос с помощью оператора SELECT -

testdb=# SELECT *
   FROM COMPANY
   WHERE ID IN (SELECT ID
      FROM COMPANY
      WHERE SALARY > 45000) ;

Это даст следующий результат -

id | name  | age |  address    | salary
----+-------+-----+-------------+--------
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
(2 rows)

Подзапросы с оператором INSERT

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

Основной синтаксис выглядит следующим образом -

INSERT INTO table_name [ (column1 [, column2 ]) ]
   SELECT [ *|column1 [, column2 ] ]
   FROM table1 [, table2 ]
   [ WHERE VALUE OPERATOR ]

пример

Рассмотрим таблицу COMPANY_BKP со структурой, аналогичную таблице COMPANY, и ее можно создать с помощью той же таблицы CREATE TABLE с использованием COMPANY_BKP в качестве имени таблицы. Теперь, чтобы скопировать полную таблицу COMPANY в COMPANY_BKP, следующий синтаксис:

testdb=# INSERT INTO COMPANY_BKP
   SELECT * FROM COMPANY
   WHERE ID IN (SELECT ID
      FROM COMPANY) ;

Подзапросы с оператором UPDATE

Подзапрос можно использовать вместе с оператором UPDATE. При использовании подзапроса с оператором UPDATE можно обновить один или несколько столбцов в таблице.

Основной синтаксис выглядит следующим образом -

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

пример

Предположим, у нас есть таблица COMPANY_BKP, которая является резервной копией таблицы COMPANY.

В следующем примере SALARY обновляется на 0,50 раза в таблице COMPANY для всех клиентов, возраст которых больше или равен 27.

testdb=# UPDATE COMPANY
   SET SALARY = SALARY * 0.50
   WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
      WHERE AGE >= 27 );

Это повлияет на две строки, и, наконец, в таблице COMPANY будут следующие записи:

id | name  | age | address     | salary
----+-------+-----+-------------+--------
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  1 | Paul  |  32 | California  |  10000
  5 | David |  27 | Texas       |  42500
(7 rows)

Подзапросы с оператором DELETE

Подзапрос можно использовать вместе с оператором DELETE, как и с любыми другими операторами, упомянутыми выше.

Основной синтаксис выглядит следующим образом -

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

пример

Предположим, у нас есть доступная таблица COMPANY_BKP, которая является резервной копией таблицы COMPANY.

В следующем примере удаляются записи из таблицы COMPANY для всех клиентов, возраст которых больше или равен 27.

testdb=# DELETE FROM COMPANY
   WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
      WHERE AGE > 27 );

Это повлияет на две строки, и, наконец, в таблице COMPANY будут следующие записи:

id | name  | age | address     | salary
----+-------+-----+-------------+--------
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  5 | David |  27 | Texas       |  42500
(6 rows)

PostgreSQL имеет типы данных smallserial , serial и bigserial ; это не настоящие типы, а просто удобство записи для создания столбцов уникальных идентификаторов. Они похожи на свойство AUTO_INCREMENT, поддерживаемое некоторыми другими базами данных.

Если вы хотите, чтобы последовательный столбец имел уникальное ограничение или был первичным ключом, его теперь необходимо указать, как и любой другой тип данных.

Серийный номер типа создает целые столбцы. Имя типа bigserial создает столбец bigint . bigserial следует использовать, если вы ожидаете использования более 2 ³¹ идентификаторов за время существования таблицы. Имя типа smallserial создает столбец smallint .

Синтаксис

Основное использование SERIAL тип данных выглядит следующим образом -

CREATE TABLE tablename (
   colname SERIAL
);

пример

Считайте, что таблица КОМПАНИЯ будет создана следующим образом:

testdb=# CREATE TABLE COMPANY(
   ID  SERIAL PRIMARY KEY,
   NAME           TEXT      NOT NULL,
   AGE            INT       NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Теперь вставьте следующие записи в таблицу COMPANY -

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Allen', 25, 'Texas', 15000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Teddy', 23, 'Norway', 20000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'David', 27, 'Texas', 85000.00 );


INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Kim', 22, 'South-Hall', 45000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'James', 24, 'Houston', 10000.00 );

Это вставит семь кортежей в таблицу КОМПАНИЯ, и КОМПАНИЯ будет иметь следующие записи:

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000

Каждый раз, когда объект создается в базе данных, ему назначается владелец. Владелец обычно тот, кто выполнил оператор создания. Для большинства типов объектов начальным состоянием является то, что только владелец (или суперпользователь) может изменять или удалять объект. Чтобы позволить другим ролям или пользователям использовать его, необходимо предоставить привилегии или разрешения.

Различные виды привилегий в PostgreSQL:

SELECT,
INSERT,
UPDATE,
DELETE,
TRUNCATE,
REFERENCES,
TRIGGER,
CREATE,
CONNECT,
TEMPORARY,
ВЫПОЛНИТЬ и
USAGE

В зависимости от типа объекта (таблица, функция и т. Д.) К объекту применяются привилегии. Для назначения привилегий пользователям используется команда GRANT.

Синтаксис для GRANT

Базовый синтаксис команды GRANT следующий:

GRANT privilege [, ...]
ON object [, ...]
TO { PUBLIC | GROUP group | username }

privilege - значения могут быть: SELECT, INSERT, UPDATE, DELETE, RULE, ALL.
object- Имя объекта, к которому нужно предоставить доступ. Возможные объекты: таблица, представление, последовательность
PUBLIC - Краткая форма, представляющая всех пользователей.
ГРУППА group - Группа, которой нужно предоставить привилегии.
username- Имя пользователя, которому нужно предоставить привилегии. PUBLIC - это короткая форма, представляющая всех пользователей.

Привилегии можно отозвать с помощью команды REVOKE.

Синтаксис REVOKE

Базовый синтаксис команды REVOKE следующий:

REVOKE privilege [, ...]
ON object [, ...]
FROM { PUBLIC | GROUP groupname | username }

privilege - значения могут быть: SELECT, INSERT, UPDATE, DELETE, RULE, ALL.
object- Имя объекта, к которому нужно предоставить доступ. Возможные объекты: таблица, представление, последовательность
PUBLIC - Краткая форма, представляющая всех пользователей.
ГРУППА group - Группа, которой нужно предоставить привилегии.
username- Имя пользователя, которому нужно предоставить привилегии. PUBLIC - это короткая форма, представляющая всех пользователей.

пример

Чтобы понять привилегии, давайте сначала создадим ПОЛЬЗОВАТЕЛЯ следующим образом:

testdb=# CREATE USER manisha WITH PASSWORD 'password';
CREATE ROLE

Сообщение СОЗДАТЬ РОЛЬ указывает на создание ПОЛЬЗОВАТЕЛЯ "manisha".

Рассмотрим таблицу КОМПАНИЯ, имеющую следующие записи:

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Затем давайте предоставим все привилегии в таблице COMPANY пользователю «manisha» следующим образом:

testdb=# GRANT ALL ON COMPANY TO manisha;
GRANT

Сообщение GRANT указывает, что все привилегии назначены ПОЛЬЗОВАТЕЛЮ.

Затем давайте отзовем привилегии от ПОЛЬЗОВАТЕЛЯ "manisha" следующим образом:

testdb=# REVOKE ALL ON COMPANY FROM manisha;
REVOKE

Сообщение REVOKE указывает, что все привилегии отозваны у ПОЛЬЗОВАТЕЛЯ.

Вы даже можете удалить пользователя следующим образом -

testdb=# DROP USER manisha;
DROP ROLE

Сообщение DROP ROLE указывает, что ПОЛЬЗОВАТЕЛЬ Manisha удален из базы данных.

Мы обсуждали типы данных Date / Time в главе Типы данных . Теперь давайте посмотрим на операторы даты и времени и функции.

В следующей таблице перечислено поведение основных арифметических операторов -

Оператор	пример	Результат
+	дата '2001-09-28' + целое число '7'	дата '2001-10-05'
+	дата '2001-09-28' + интервал '1 час'	отметка времени '2001-09-28 01:00:00'
+	дата '2001-09-28' + время '03: 00 '	отметка времени '2001-09-28 03:00:00'
+	интервал «1 день» + интервал «1 час»	интервал '1 день 01:00:00'
+	отметка времени '2001-09-28 01:00' + интервал '23 часа'	отметка времени '2001-09-29 00:00:00'
+	время '01: 00 '+ интервал' 3 часа '	время '04: 00: 00 '
-	- интервал 23 часа	интервал '-23: 00: 00'
-	дата '2001-10-01' - дата '2001-09-28'	целое число '3' (дни)
-	дата '2001-10-01' - целое '7'	дата '2001-09-24'
-	дата '2001-09-28' - интервал '1 час'	отметка времени '2001-09-27 23:00:00'
-	время '05: 00 '- время '03: 00'	интервал '02: 00: 00 '
-	время '05: 00 '- интервал' 2 часа '	время '03: 00: 00 '
-	отметка времени '2001-09-28 23:00' - интервал '23 часа'	отметка времени '2001-09-28 00:00:00'
-	интервал '1 день' - интервал '1 час'	интервал '1 день -01: 00: 00'
-	отметка времени '2001-09-29 03:00' - отметка времени '2001-09-27 12:00'	интервал '1 день 15:00:00'
*	900 * интервал '1 секунда'	интервал '00: 15: 00 '
*	21 * интервал '1 день'	интервал 21 день
*	двойная точность '3,5' * интервал '1 час'	интервал '03: 30: 00 '
/	интервал '1 час' / двойная точность '1,5'	интервал '00: 40: 00 '

Ниже приводится список всех важных доступных функций, связанных с датой и временем.

С. Нет.	Описание функции
1	ВОЗРАСТ() Вычесть аргументы
2	ТЕКУЩАЯ ДАТА / ВРЕМЯ () Текущая дата и время
3	DATE_PART () Получить подполе (эквивалент извлечения)
4	ЭКСТРАКТ () Получить подполе
5	ISFINITE () Проверка на конечную дату, время и интервал (не +/- бесконечность)
6	ОБОСНОВАТЬ Отрегулируйте интервал

ВОЗРАСТ (отметка времени, отметка времени), ВОЗРАСТ (отметка времени)

С. Нет.	Описание функции
1	AGE(timestamp, timestamp) Когда вызывается с формой TIMESTAMP второго аргумента, AGE () вычитает аргументы, производя «символический» результат, который использует годы и месяцы и имеет тип INTERVAL.
2	AGE(timestamp) Когда вызывается только с TIMESTAMP в качестве аргумента, AGE () вычитает из current_date (в полночь).

Пример функции AGE (отметка времени, отметка времени):

testdb=# SELECT AGE(timestamp '2001-04-10', timestamp '1957-06-13');

Приведенный выше оператор PostgreSQL даст следующий результат:

age
-------------------------
 43 years 9 mons 27 days

Пример функции AGE (отметка времени):

testdb=# select age(timestamp '1957-06-13');

Приведенный выше оператор PostgreSQL даст следующий результат:

age
--------------------------
 55 years 10 mons 22 days

ТЕКУЩАЯ ДАТА / ВРЕМЯ ()

PostgreSQL предоставляет ряд функций, возвращающих значения, относящиеся к текущей дате и времени. Ниже приведены некоторые функции -

С. Нет.	Описание функции
1	CURRENT_DATE Показывает текущую дату.
2	CURRENT_TIME Поставляет значения с часовым поясом.
3	CURRENT_TIMESTAMP Поставляет значения с часовым поясом.
4	CURRENT_TIME(precision) Необязательно принимает параметр точности, который приводит к округлению результата до такого количества дробных цифр в поле секунд.
5	CURRENT_TIMESTAMP(precision) Необязательно принимает параметр точности, который приводит к округлению результата до такого количества дробных цифр в поле секунд.
6	LOCALTIME Предоставляет значения без часового пояса.
7	LOCALTIMESTAMP Предоставляет значения без часового пояса.
8	LOCALTIME(precision) Необязательно принимает параметр точности, который приводит к округлению результата до такого количества дробных цифр в поле секунд.
9	LOCALTIMESTAMP(precision) Необязательно принимает параметр точности, который приводит к округлению результата до такого количества дробных цифр в поле секунд.

Примеры использования функций из таблицы выше -

testdb=# SELECT CURRENT_TIME;
       timetz
--------------------
 08:01:34.656+05:30
(1 row)


testdb=# SELECT CURRENT_DATE;
    date
------------
 2013-05-05
(1 row)


testdb=# SELECT CURRENT_TIMESTAMP;
              now
-------------------------------
 2013-05-05 08:01:45.375+05:30
(1 row)


testdb=# SELECT CURRENT_TIMESTAMP(2);
         timestamptz
------------------------------
 2013-05-05 08:01:50.89+05:30
(1 row)


testdb=# SELECT LOCALTIMESTAMP;
       timestamp
------------------------
 2013-05-05 08:01:55.75
(1 row)

PostgreSQL также предоставляет функции, которые возвращают время начала текущего оператора, а также фактическое текущее время в момент вызова функции. Эти функции -

С. Нет.	Описание функции
1	transaction_timestamp() Он эквивалентен CURRENT_TIMESTAMP, но назван так, чтобы четко отражать то, что он возвращает.
2	statement_timestamp() Возвращает время начала текущего оператора.
3	clock_timestamp() Он возвращает фактическое текущее время, поэтому его значение изменяется даже в пределах одной команды SQL.
4	timeofday() Он возвращает фактическое текущее время, но в виде отформатированной текстовой строки, а не в виде отметки времени со значением часового пояса.
5	now() Это традиционный PostgreSQL, эквивалент transaction_timestamp ().

DATE_PART (текст, отметка времени), DATE_PART (текст, интервал), DATE_TRUNC (текст, отметка времени)

С. Нет.	Описание функции
1	DATE_PART('field', source) Эти функции получают подполя. Параметр поля должен быть строковым значением, а не именем. Допустимые имена полей: век, день, декада, доу, дой, эпоха, час, isodow, isoyear, микросекунды, тысячелетие, миллисекунды, минута, месяц, квартал, секунда, часовой пояс, timezone_hour, timezone_minute, неделя, год.
2	DATE_TRUNC('field', source) Эта функция концептуально аналогична функции усечения для чисел. source - это выражение значения типа timestamp или interval. поле выбирает, с какой точностью усечь входное значение. Возвращаемое значение имеет тип timestamp или interval . Допустимые значения для поля : микросекунды, миллисекунды, секунда, минута, час, день, неделя, месяц, квартал, год, десятилетие, век, тысячелетие.

Ниже приведены примеры функций DATE_PART ( 'поле' , источник):

testdb=# SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
 date_part
-----------
        16
(1 row)


testdb=# SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
 date_part
-----------
         4
(1 row)

Ниже приведены примеры функций DATE_TRUNC ( 'поле' , источник):

testdb=# SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
     date_trunc
---------------------
 2001-02-16 20:00:00
(1 row)


testdb=# SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
     date_trunc
---------------------
 2001-01-01 00:00:00
(1 row)

EXTRACT (поле из метки времени), EXTRACT (поле из интервала)

В EXTRACT(field FROM source)функция извлекает подполя, такие как год или час, из значений даты / времени. Источник должен быть выражением значения типа метки времени, времени или интервала . Поле является идентификатором или строка , которая выбирает какое поле извлечь из исходного значения. Функция EXTRACT возвращает значения типа двойной точности .

Ниже приведены допустимые имена полей (аналогичные именам полей функции DATE_PART): век, день, декада, dow, doy, эпоха, час, isodow, isoyear, микросекунды, миллениум, миллисекунды, минута, месяц, квартал, секунда, часовой пояс, timezone_hour. , timezone_minute, неделя, год.

Ниже приведены примеры функций EXTRACT ( 'поле' , источник):

testdb=# SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
 date_part
-----------
        20
(1 row)


testdb=# SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
 date_part
-----------
        16
(1 row)

ISFINITE (дата), ISFINITE (отметка времени), ISFINITE (интервал)

С. Нет.	Описание функции
1	ISFINITE(date) Тесты на конечную дату.
2	ISFINITE(timestamp) Тесты на конечную отметку времени.
3	ISFINITE(interval) Тесты на конечный интервал.

Ниже приведены примеры функций ISFINITE () -

testdb=# SELECT isfinite(date '2001-02-16');
 isfinite
----------
 t
(1 row)


testdb=# SELECT isfinite(timestamp '2001-02-16 21:28:30');
 isfinite
----------
 t
(1 row)


testdb=# SELECT isfinite(interval '4 hours');
 isfinite
----------
 t
(1 row)

JUSTIFY_DAYS (интервал), JUSTIFY_HOURS (интервал), JUSTIFY_INTERVAL (интервал)

С. Нет.	Описание функции
1	JUSTIFY_DAYS(interval) Настраивает интервал таким образом, чтобы 30-дневные периоды представлялись в виде месяцев. Вернутьinterval тип
2	JUSTIFY_HOURS(interval) Настраивает интервал таким образом, чтобы 24-часовые периоды времени представлялись в днях. Вернутьinterval тип
3	JUSTIFY_INTERVAL(interval) Регулирует интервал с помощью JUSTIFY_DAYS и JUSTIFY_HOURS, с дополнительными настройками знаков. Вернутьinterval тип

Ниже приведены примеры функций ISFINITE () -

testdb=# SELECT justify_days(interval '35 days');
 justify_days
--------------
 1 mon 5 days
(1 row)


testdb=# SELECT justify_hours(interval '27 hours');
 justify_hours
----------------
 1 day 03:00:00
(1 row)


testdb=# SELECT justify_interval(interval '1 mon -1 hour');
 justify_interval
------------------
 29 days 23:00:00
(1 row)

PostgreSQL functions, также известные как хранимые процедуры, позволяют выполнять операции, которые обычно требуют нескольких запросов и циклов обработки в одной функции в базе данных. Функции позволяют повторно использовать базу данных, поскольку другие приложения могут напрямую взаимодействовать с вашими хранимыми процедурами вместо кода промежуточного уровня или дублирующего кода.

Функции могут быть созданы на любом языке по вашему выбору, например SQL, PL / pgSQL, C, Python и т. Д.

Синтаксис

Основной синтаксис для создания функции следующий:

CREATE [OR REPLACE] FUNCTION function_name (arguments) 
RETURNS return_datatype AS $variable_name$
   DECLARE
      declaration;
      [...]
   BEGIN
      < function_body >
      [...]
      RETURN { variable_name | value }
   END; LANGUAGE plpgsql;

Где,

function-name указывает имя функции.
Опция [OR REPLACE] позволяет изменять существующую функцию.
Функция должна содержать return заявление.
RETURNПредложение определяет тип данных, который вы собираетесь вернуть из функции. Вreturn_datatype может быть базовым, составным или доменным типом или может ссылаться на тип столбца таблицы.
function-body содержит исполняемую часть.
Ключевое слово AS используется для создания автономной функции.
plpgsql- это имя языка, на котором реализована функция. Здесь мы используем эту опцию для PostgreSQL, это может быть SQL, C, внутренний или имя пользовательского процедурного языка. Для обратной совместимости имя может быть заключено в одинарные кавычки.

пример

В следующем примере показано создание и вызов автономной функции. Эта функция возвращает общее количество записей в таблице КОМПАНИЯ. Мы будем использовать таблицу COMPANY , в которой есть следующие записи:

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Функция totalRecords () выглядит следующим образом -

CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS $total$
declare
	total integer;
BEGIN
   SELECT count(*) into total FROM COMPANY;
   RETURN total;
END;
$total$ LANGUAGE plpgsql;

Когда вышеуказанный запрос будет выполнен, результат будет -

testdb# CREATE FUNCTION

Теперь давайте выполним вызов этой функции и проверим записи в таблице COMPANY

testdb=# select totalRecords();

Когда вышеуказанный запрос будет выполнен, результат будет -

totalrecords
--------------
      7
(1 row)

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

Ниже приведен список всех встроенных функций общего назначения PostgreSQL:

Функция PostgreSQL COUNT - агрегатная функция PostgreSQL COUNT используется для подсчета количества строк в таблице базы данных.
Функция PostgreSQL MAX. Агрегатная функция PostgreSQL MAX позволяет выбрать наивысшее (максимальное) значение для определенного столбца.
Функция PostgreSQL MIN - агрегатная функция PostgreSQL MIN позволяет нам выбрать наименьшее (минимальное) значение для определенного столбца.
Функция PostgreSQL AVG - агрегатная функция PostgreSQL AVG выбирает среднее значение для определенного столбца таблицы.
Функция PostgreSQL SUM - агрегатная функция PostgreSQL SUM позволяет выбрать сумму для числового столбца.
Функции PostgreSQL ARRAY - Агрегатная функция PostgreSQL ARRAY помещает входные значения, включая нули, в массив.
Числовые функции PostgreSQL - Полный список функций PostgreSQL, необходимых для работы с числами в SQL.
Строковые функции PostgreSQL - Полный список функций PostgreSQL, необходимых для управления строками в PostgreSQL.

В этом руководстве будет использоваться libpqxxбиблиотека, которая является официальным клиентским API C ++ для PostgreSQL. Исходный код libpqxx доступен под лицензией BSD, поэтому вы можете бесплатно скачать его, передать другим, изменить, продать, включить в свой собственный код и поделиться своими изменениями с кем угодно.

Установка

Последнюю версию libpqxx можно загрузить по ссылке Download Libpqxx . Так что загрузите последнюю версию и выполните следующие действия -

wget http://pqxx.org/download/software/libpqxx/libpqxx-4.0.tar.gz
tar xvfz libpqxx-4.0.tar.gz
cd libpqxx-4.0
./configure
make
make install

Прежде чем начать использовать интерфейс C / C ++ PostgreSQL, найдите pg_hba.conf в каталоге установки PostgreSQL и добавьте следующую строку -

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

Вы можете запустить / перезапустить сервер postgres, если он не запущен, используя следующую команду -

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

Интерфейсные API C / C ++

Ниже приведены важные процедуры интерфейса, которые могут удовлетворить ваши требования для работы с базой данных PostgreSQL из вашей программы C / C ++. Если вы ищете более сложное приложение, вы можете заглянуть в официальную документацию libpqxx или использовать коммерчески доступные API.

С. Нет.	API и описание
1	pqxx::connection C( const std::string & dbstring ) Это typedef, который будет использоваться для подключения к базе данных. Здесь dbstring предоставляет необходимые параметры для подключения к базе данных, напримерdbname = testdb user = postgres password=pass123 hostaddr=127.0.0.1 port=5432. Если соединение установлено успешно, он создает C с объектом соединения, который предоставляет различные полезные публичные функции.
2	C.is_open() Метод is_open () является общедоступным методом объекта подключения и возвращает логическое значение. Если соединение активно, то этот метод возвращает true, иначе возвращает false.
3	C.disconnect() Этот метод используется для отключения открытого соединения с базой данных.
4	pqxx::work W( C ) Это typedef, который будет использоваться для создания транзакционного объекта с использованием соединения C, которое в конечном итоге будет использоваться для выполнения операторов SQL в транзакционном режиме. Если объект транзакции создается успешно, он присваивается переменной W, которая будет использоваться для доступа к общедоступным методам, связанным с транзакционным объектом.
5	W.exec(const std::string & sql) Этот общедоступный метод из транзакционного объекта будет использоваться для выполнения оператора SQL.
6	W.commit() Этот общедоступный метод из транзакционного объекта будет использоваться для фиксации транзакции.
7	W.abort() Этот общедоступный метод из транзакционного объекта будет использоваться для отката транзакции.
8	pqxx::nontransaction N( C ) Это typedef, который будет использоваться для создания нетранзакционного объекта с использованием соединения C, которое в конечном итоге будет использоваться для выполнения операторов SQL в нетранзакционном режиме. Если объект транзакции создается успешно, он присваивается переменной N, которая будет использоваться для доступа к общедоступным методам, связанным с нетранзакционным объектом.
9	N.exec(const std::string & sql) Этот общедоступный метод из нетранзакционного объекта будет использоваться для выполнения оператора SQL и возвращает объект результата, который фактически является интегратором, содержащим все возвращенные записи.

Подключение к базе данных

Следующий сегмент кода C показывает, как подключиться к существующей базе данных, работающей на локальном компьютере через порт 5432. Здесь я использовал обратную косую черту \ для продолжения строки.

#include <iostream>
#include <pqxx/pqxx> 

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }
}

Теперь давайте скомпилируем и запустим указанную выше программу для подключения к нашей базе данных. testdb, который уже доступен в вашей схеме, и к нему можно получить доступ с помощью пользователя postgres и пароля pass123 .

Вы можете использовать идентификатор пользователя и пароль в зависимости от настроек вашей базы данных. Не забудьте сохранить -lpqxx и -lpq в указанном порядке! В противном случае компоновщик будет горько жаловаться на недостающие функции с именами, начинающимися с «PQ».

$g++ test.cpp -lpqxx -lpq $./a.out
Opened database successfully: testdb

Создать таблицу

Следующий сегмент кода C будет использоваться для создания таблицы в ранее созданной базе данных -

#include <iostream>
#include <pqxx/pqxx> 

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;
   
   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }

      /* Create SQL statement */
      sql = "CREATE TABLE COMPANY("  \
      "ID INT PRIMARY KEY     NOT NULL," \
      "NAME           TEXT    NOT NULL," \
      "AGE            INT     NOT NULL," \
      "ADDRESS        CHAR(50)," \
      "SALARY         REAL );";

      /* Create a transactional object. */
      work W(C);
      
      /* Execute SQL query */
      W.exec( sql );
      W.commit();
      cout << "Table created successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

Когда приведенная выше программа скомпилирована и запущена, она создаст таблицу COMPANY в вашей базе данных testdb и отобразит следующие операторы:

Opened database successfully: testdb
Table created successfully

ВСТАВИТЬ операцию

Следующий сегмент кода C показывает, как мы можем создавать записи в нашей таблице COMPANY, созданной в приведенном выше примере.

#include <iostream>
#include <pqxx/pqxx> 

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;
   
   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }

      /* Create SQL statement */
      sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
         "VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
         "VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); "     \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
         "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
         "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";

      /* Create a transactional object. */
      work W(C);
      
      /* Execute SQL query */
      W.exec( sql );
      W.commit();
      cout << "Records created successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

Когда приведенная выше программа скомпилирована и запущена, она создаст заданные записи в таблице COMPANY и отобразит следующие две строки:

Opened database successfully: testdb
Records created successfully

ВЫБРАТЬ операцию

Следующий сегмент кода C показывает, как мы можем извлекать и отображать записи из нашей таблицы COMPANY, созданной в приведенном выше примере.

#include <iostream>
#include <pqxx/pqxx> 

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;
   
   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }

      /* Create SQL statement */
      sql = "SELECT * from COMPANY";

      /* Create a non-transactional object. */
      nontransaction N(C);
      
      /* Execute SQL query */
      result R( N.exec( sql ));
      
      /* List down all the records */
      for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
         cout << "ID = " << c[0].as<int>() << endl;
         cout << "Name = " << c[1].as<string>() << endl;
         cout << "Age = " << c[2].as<int>() << endl;
         cout << "Address = " << c[3].as<string>() << endl;
         cout << "Salary = " << c[4].as<float>() << endl;
      }
      cout << "Operation done successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

Когда приведенная выше программа скомпилирована и выполнена, она выдаст следующий результат:

Opened database successfully: testdb
ID = 1
Name = Paul
Age = 32
Address = California
Salary = 20000
ID = 2
Name = Allen
Age = 25
Address = Texas
Salary = 15000
ID = 3
Name = Teddy
Age = 23
Address = Norway
Salary = 20000
ID = 4
Name = Mark
Age = 25
Address = Rich-Mond
Salary = 65000
Operation done successfully

ОБНОВЛЕНИЕ Операция

Следующий сегмент кода C показывает, как мы можем использовать инструкцию UPDATE для обновления любой записи, а затем извлекать и отображать обновленные записи из нашей таблицы COMPANY.

#include <iostream>
#include <pqxx/pqxx> 

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;
   
   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }
      
      /* Create a transactional object. */
      work W(C);
      /* Create  SQL UPDATE statement */
      sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1";
      /* Execute SQL query */
      W.exec( sql );
      W.commit();
      cout << "Records updated successfully" << endl;
      
      /* Create SQL SELECT statement */
      sql = "SELECT * from COMPANY";

      /* Create a non-transactional object. */
      nontransaction N(C);
      
      /* Execute SQL query */
      result R( N.exec( sql ));
      
      /* List down all the records */
      for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
         cout << "ID = " << c[0].as<int>() << endl;
         cout << "Name = " << c[1].as<string>() << endl;
         cout << "Age = " << c[2].as<int>() << endl;
         cout << "Address = " << c[3].as<string>() << endl;
         cout << "Salary = " << c[4].as<float>() << endl;
      }
      cout << "Operation done successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

Когда приведенная выше программа скомпилирована и выполнена, она выдаст следующий результат:

Opened database successfully: testdb
Records updated successfully
ID = 2
Name = Allen
Age = 25
Address = Texas
Salary = 15000
ID = 3
Name = Teddy
Age = 23
Address = Norway
Salary = 20000
ID = 4
Name = Mark
Age = 25
Address = Rich-Mond
Salary = 65000
ID = 1
Name = Paul
Age = 32
Address = California
Salary = 25000
Operation done successfully

УДАЛИТЬ операцию

Следующий сегмент кода C показывает, как мы можем использовать оператор DELETE для удаления любой записи, а затем извлекать и отображать оставшиеся записи из нашей таблицы COMPANY.

#include <iostream>
#include <pqxx/pqxx> 

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;
   
   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }
      
      /* Create a transactional object. */
      work W(C);
      /* Create  SQL DELETE statement */
      sql = "DELETE from COMPANY where ID = 2";
      /* Execute SQL query */
      W.exec( sql );
      W.commit();
      cout << "Records deleted successfully" << endl;
      
      /* Create SQL SELECT statement */
      sql = "SELECT * from COMPANY";

      /* Create a non-transactional object. */
      nontransaction N(C);
      
      /* Execute SQL query */
      result R( N.exec( sql ));
      
      /* List down all the records */
      for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
         cout << "ID = " << c[0].as<int>() << endl;
         cout << "Name = " << c[1].as<string>() << endl;
         cout << "Age = " << c[2].as<int>() << endl;
         cout << "Address = " << c[3].as<string>() << endl;
         cout << "Salary = " << c[4].as<float>() << endl;
      }
      cout << "Operation done successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

Когда приведенная выше программа скомпилирована и выполнена, она выдаст следующий результат:

Opened database successfully: testdb
Records deleted successfully
ID = 3
Name = Teddy
Age = 23
Address = Norway
Salary = 20000
ID = 4
Name = Mark
Age = 25
Address = Rich-Mond
Salary = 65000
ID = 1
Name = Paul
Age = 32
Address = California
Salary = 25000
Operation done successfully

Установка

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

Загрузите последнюю версию postgresql- (VERSION) .jdbc.jar из репозитория postgresql-jdbc .
Добавьте загруженный файл jar postgresql- (VERSION) .jdbc.jar в свой путь к классу, или вы можете использовать его вместе с опцией -classpath, как описано ниже в примерах.

В следующем разделе предполагается, что вы мало знакомы с концепциями Java JDBC. Если у вас его нет, рекомендуется потратить полчаса на JDBC Tutorial, чтобы освоиться с концепциями, описанными ниже.

Подключение к базе данных

Следующий код Java показывает, как подключиться к существующей базе данных. Если база данных не существует, она будет создана и, наконец, будет возвращен объект базы данных.

import java.sql.Connection;
import java.sql.DriverManager;

public class PostgreSQLJDBC {
   public static void main(String args[]) {
      Connection c = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "postgres", "123");
      } catch (Exception e) {
         e.printStackTrace();
         System.err.println(e.getClass().getName()+": "+e.getMessage());
         System.exit(0);
      }
      System.out.println("Opened database successfully");
   }
}

Прежде чем компилировать и запускать указанную выше программу, найдите pg_hba.conf в каталоге установки PostgreSQL и добавьте следующую строку -

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

Вы можете запустить / перезапустить сервер postgres, если он не запущен, используя следующую команду -

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

Теперь давайте скомпилируем и запустим указанную выше программу для подключения к testdb. Здесь мы используемpostgres как идентификатор пользователя и 123в качестве пароля для доступа к базе данных. Вы можете изменить это в соответствии с конфигурацией и настройкой вашей базы данных. Мы также предполагаем, что текущая версия драйвера JDBCpostgresql-9.2-1002.jdbc3.jar доступен в текущем пути.

C:\JavaPostgresIntegration>javac PostgreSQLJDBC.java
C:\JavaPostgresIntegration>java -cp c:\tools\postgresql-9.2-1002.jdbc3.jar;C:\JavaPostgresIntegration PostgreSQLJDBC
Open database successfully

Создать таблицу

Следующая программа на Java будет использоваться для создания таблицы в ранее открытой базе данных. Убедитесь, что у вас нет этой таблицы в целевой базе данных.

import java.sql.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;


public class PostgreSQLJDBC {
   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "CREATE TABLE COMPANY " +
            "(ID INT PRIMARY KEY     NOT NULL," +
            " NAME           TEXT    NOT NULL, " +
            " AGE            INT     NOT NULL, " +
            " ADDRESS        CHAR(50), " +
            " SALARY         REAL)";
         stmt.executeUpdate(sql);
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Table created successfully");
   }
}

Когда программа скомпилирована и запущена, она создаст таблицу COMPANY в testdb база данных и отобразит следующие две строки -

Opened database successfully
Table created successfully

ВСТАВИТЬ операцию

Следующая программа на Java показывает, как мы можем создавать записи в нашей таблице COMPANY, созданной в приведенном выше примере.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class PostgreSQLJDBC {
   public static void main(String args[]) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (1, 'Paul', 32, 'California', 20000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
         stmt.executeUpdate(sql);

         stmt.close();
         c.commit();
         c.close();
      } catch (Exception e) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Records created successfully");
   }
}

Когда вышеуказанная программа скомпилирована и запущена, она создаст заданные записи в таблице COMPANY и отобразит следующие две строки:

Opened database successfully
Records created successfully

ВЫБРАТЬ операцию

Следующая программа на Java показывает, как мы можем извлекать и отображать записи из нашей таблицы COMPANY, созданной в приведенном выше примере.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class PostgreSQLJDBC {
   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
         while ( rs.next() ) {
            int id = rs.getInt("id");
            String  name = rs.getString("name");
            int age  = rs.getInt("age");
            String  address = rs.getString("address");
            float salary = rs.getFloat("salary");
            System.out.println( "ID = " + id );
            System.out.println( "NAME = " + name );
            System.out.println( "AGE = " + age );
            System.out.println( "ADDRESS = " + address );
            System.out.println( "SALARY = " + salary );
            System.out.println();
         }
         rs.close();
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Operation done successfully");
   }
}

Когда программа скомпилирована и запущена, она выдаст следующий результат:

Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0

ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

ОБНОВЛЕНИЕ Операция

Следующий код Java показывает, как мы можем использовать оператор UPDATE для обновления любой записи, а затем извлекать и отображать обновленные записи из нашей таблицы COMPANY.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class PostgreSQLJDBC {
   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
         stmt.executeUpdate(sql);
         c.commit();

         ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
         while ( rs.next() ) {
            int id = rs.getInt("id");
            String  name = rs.getString("name");
            int age  = rs.getInt("age");
            String  address = rs.getString("address");
            float salary = rs.getFloat("salary");
            System.out.println( "ID = " + id );
            System.out.println( "NAME = " + name );
            System.out.println( "AGE = " + age );
            System.out.println( "ADDRESS = " + address );
            System.out.println( "SALARY = " + salary );
            System.out.println();
         }
         rs.close();
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Operation done successfully");
   }
}

Когда программа скомпилирована и запущена, она выдаст следующий результат:

Opened database successfully
ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0

Operation done successfully

УДАЛИТЬ операцию

Следующий код Java показывает, как мы можем использовать оператор DELETE для удаления любой записи, а затем извлекать и отображать оставшиеся записи из нашей таблицы COMPANY.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class PostgreSQLJDBC6 {
   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "DELETE from COMPANY where ID = 2;";
         stmt.executeUpdate(sql);
         c.commit();

         ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
         while ( rs.next() ) {
            int id = rs.getInt("id");
            String  name = rs.getString("name");
            int age  = rs.getInt("age");
            String  address = rs.getString("address");
            float salary = rs.getFloat("salary");
            System.out.println( "ID = " + id );
            System.out.println( "NAME = " + name );
            System.out.println( "AGE = " + age );
            System.out.println( "ADDRESS = " + address );
            System.out.println( "SALARY = " + salary );
            System.out.println();
         }
         rs.close();
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Operation done successfully");
   }
}

Когда программа скомпилирована и запущена, она выдаст следующий результат:

Opened database successfully
ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0
Operation done successfully

Установка

Расширение PostgreSQL включено по умолчанию в последних выпусках PHP 5.3.x. Его можно отключить, используя--without-pgsqlво время компиляции. Тем не менее вы можете использовать команду yum для установки интерфейса PHP-PostgreSQL -

yum install php-pgsql

Прежде чем вы начнете использовать интерфейс PHP PostgreSQL, найдите pg_hba.conf в каталоге установки PostgreSQL и добавьте следующую строку -

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

Вы можете запустить / перезапустить сервер postgres, если он не запущен, используя следующую команду -

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

Пользователи Windows должны включить php_pgsql.dll, чтобы использовать это расширение. Эта DLL включена в дистрибутив Windows в последних выпусках PHP 5.3.x

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

API интерфейса PHP

Ниже приведены важные процедуры PHP, которые могут удовлетворить ваши требования для работы с базой данных PostgreSQL из вашей программы PHP. Если вы ищете более сложное приложение, вы можете заглянуть в официальную документацию PHP.

С. Нет.	API и описание
1	resource pg_connect ( string $connection_string [, int $connect_type ] ) Это открывает соединение с базой данных PostgreSQL, заданной параметром connection_string. Если PGSQL_CONNECT_FORCE_NEW передается как connect_type, то новое соединение создается в случае второго вызова pg_connect (), даже если connection_string идентичен существующему соединению.
2	bool pg_connection_reset ( resource $connection ) Эта процедура сбрасывает соединение. Это полезно для исправления ошибок. Возвращает TRUE в случае успеха или FALSE в случае неудачи.
3	int pg_connection_status ( resource $connection ) Эта процедура возвращает статус указанного соединения. Возвращает PGSQL_CONNECTION_OK или PGSQL_CONNECTION_BAD.
4	string pg_dbname ([ resource $connection ] ) Эта процедура возвращает имя базы данных, к которой подключен данный ресурс подключения PostgreSQL.
5	resource pg_prepare ([ resource $connection ], string $stmtname, string $query ) Это отправляет запрос на создание подготовленного оператора с заданными параметрами и ожидает завершения.
6	resource pg_execute ([ resource $connection ], string $stmtname, array $params ) Эта процедура отправляет запрос на выполнение подготовленного оператора с заданными параметрами и ожидает результата.
7	resource pg_query ([ resource $connection ], string $query ) Эта процедура выполняет запрос в указанном соединении с базой данных.
8	array pg_fetch_row ( resource $result [, int $row ] ) Эта процедура извлекает одну строку данных из результата, связанного с указанным ресурсом результата.
9	array pg_fetch_all ( resource $result ) Эта процедура возвращает массив, содержащий все строки (записи) в ресурсе результата.
10	int pg_affected_rows ( resource $result ) Эта процедура возвращает количество строк, затронутых запросами INSERT, UPDATE и DELETE.
11	int pg_num_rows ( resource $result ) Эта процедура возвращает количество строк в ресурсе результата PostgreSQL, например количество строк, возвращенных оператором SELECT.
12	bool pg_close ([ resource $connection ] ) Эта процедура закрывает непостоянное соединение с базой данных PostgreSQL, связанной с данным ресурсом соединения.
13	string pg_last_error ([ resource $connection ] ) Эта процедура возвращает последнее сообщение об ошибке для данного соединения.
14	string pg_escape_literal ([ resource $connection ], string $data ) Эта процедура экранирует литерал для вставки в текстовое поле.
15	string pg_escape_string ([ resource $connection ], string $data ) Эта процедура экранирует строку для запроса базы данных.

Подключение к базе данных

Следующий код PHP показывает, как подключиться к существующей базе данных на локальном компьютере, и, наконец, будет возвращен объект подключения к базе данных.

<?php
   $host = "host = 127.0.0.1"; $port        = "port = 5432";
   $dbname = "dbname = testdb"; $credentials = "user = postgres password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials" ); if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }
?>

Теперь давайте запустим указанную выше программу, чтобы открыть нашу базу данных. testdb: если база данных успешно открыта, будет выдано следующее сообщение -

Opened database successfully

Создать таблицу

Следующая программа PHP будет использоваться для создания таблицы в ранее созданной базе данных -

<?php
   $host = "host = 127.0.0.1"; $port        = "port = 5432";
   $dbname = "dbname = testdb"; $credentials = "user = postgres password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials" ); if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }
   
   $sql =<<<EOF CREATE TABLE COMPANY (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL); EOF; $ret = pg_query($db, $sql);
   if(!$ret) { echo pg_last_error($db);
   } else {
      echo "Table created successfully\n";
   }
   pg_close($db);
?>

Когда приведенная выше программа будет выполнена, она создаст таблицу COMPANY в вашем testdb и он отобразит следующие сообщения -

Opened database successfully
Table created successfully

ВСТАВИТЬ операцию

Следующая программа PHP показывает, как мы можем создавать записи в нашей таблице COMPANY, созданной в приведенном выше примере:

<?php
   $host        = "host=127.0.0.1";
   $port = "port=5432"; $dbname      = "dbname = testdb";
   $credentials = "user = postgres password=pass123"; $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) { echo "Error : Unable to open database\n"; } else { echo "Opened database successfully\n"; } $sql =<<<EOF
      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (1, 'Paul', 32, 'California', 20000.00 );

      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 );

      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );

      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
EOF;

   $ret = pg_query($db, $sql); if(!$ret) {
      echo pg_last_error($db); } else { echo "Records created successfully\n"; } pg_close($db);
?>

Когда приведенная выше программа будет выполнена, она создаст данные записи в таблице КОМПАНИИ и отобразит следующие две строки:

Opened database successfully
Records created successfully

ВЫБРАТЬ операцию

Следующая программа PHP показывает, как мы можем извлекать и отображать записи из нашей таблицы COMPANY, созданной в приведенном выше примере:

<?php
   $host = "host = 127.0.0.1"; $port        = "port = 5432";
   $dbname = "dbname = testdb"; $credentials = "user = postgres password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials" ); if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }

   $sql =<<<EOF SELECT * from COMPANY; EOF; $ret = pg_query($db, $sql);
   if(!$ret) { echo pg_last_error($db);
      exit;
   } 
   while($row = pg_fetch_row($ret)) {
      echo "ID = ". $row[0] . "\n"; echo "NAME = ". $row[1] ."\n";
      echo "ADDRESS = ". $row[2] ."\n"; echo "SALARY = ".$row[4] ."\n\n";
   }
   echo "Operation done successfully\n";
   pg_close($db);
?>

Когда вышеуказанная программа будет выполнена, она даст следующий результат. Обратите внимание, что поля возвращаются в той последовательности, в которой они использовались при создании таблицы.

Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  20000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

ОБНОВЛЕНИЕ Операция

Следующий код PHP показывает, как мы можем использовать инструкцию UPDATE для обновления любой записи, а затем извлекать и отображать обновленные записи из нашей таблицы COMPANY.

<?php
   $host        = "host=127.0.0.1";
   $port = "port=5432"; $dbname      = "dbname = testdb";
   $credentials = "user = postgres password=pass123"; $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) { echo "Error : Unable to open database\n"; } else { echo "Opened database successfully\n"; } $sql =<<<EOF
      UPDATE COMPANY set SALARY = 25000.00 where ID=1;
EOF;
   $ret = pg_query($db, $sql); if(!$ret) {
      echo pg_last_error($db); exit; } else { echo "Record updated successfully\n"; } $sql =<<<EOF
      SELECT * from COMPANY;
EOF;

   $ret = pg_query($db, $sql); if(!$ret) {
      echo pg_last_error($db); exit; } while($row = pg_fetch_row($ret)) { echo "ID = ". $row[0] . "\n";
      echo "NAME = ". $row[1] ."\n"; echo "ADDRESS = ". $row[2] ."\n";
      echo "SALARY =  ".$row[4] ."\n\n"; } echo "Operation done successfully\n"; pg_close($db);
?>

Когда приведенная выше программа будет выполнена, она даст следующий результат:

Opened database successfully
Record updated successfully
ID = 2
NAME = Allen
ADDRESS = 25
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = 23
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = 25
SALARY =  65000

ID = 1
NAME = Paul
ADDRESS = 32
SALARY =  25000

Operation done successfully

УДАЛИТЬ операцию

Следующий код PHP показывает, как мы можем использовать оператор DELETE для удаления любой записи, а затем извлечь и отобразить оставшиеся записи из нашей таблицы COMPANY.

<?php
   $host = "host = 127.0.0.1"; $port        = "port = 5432";
   $dbname = "dbname = testdb"; $credentials = "user = postgres password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials" ); if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }
   $sql =<<<EOF DELETE from COMPANY where ID=2; EOF; $ret = pg_query($db, $sql);
   if(!$ret) { echo pg_last_error($db);
      exit;
   } else {
      echo "Record deleted successfully\n";
   }
   
   $sql =<<<EOF SELECT * from COMPANY; EOF; $ret = pg_query($db, $sql);
   if(!$ret) { echo pg_last_error($db);
      exit;
   } 
   while($row = pg_fetch_row($ret)) {
      echo "ID = ". $row[0] . "\n"; echo "NAME = ". $row[1] ."\n";
      echo "ADDRESS = ". $row[2] ."\n"; echo "SALARY = ".$row[4] ."\n\n";
   }
   echo "Operation done successfully\n";
   pg_close($db);
?>

Когда приведенная выше программа будет выполнена, она даст следующий результат:

Opened database successfully
Record deleted successfully
ID = 3
NAME = Teddy
ADDRESS = 23
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = 25
SALARY =  65000

ID = 1
NAME = Paul
ADDRESS = 32
SALARY =  25000

Operation done successfully

Установка

PostgreSQL может быть интегрирован с Perl с помощью модуля Perl DBI, который представляет собой модуль доступа к базе данных для языка программирования Perl. Он определяет набор методов, переменных и соглашений, которые обеспечивают стандартный интерфейс базы данных.

Вот простые шаги для установки модуля DBI на вашу машину Linux / Unix:

$ wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz
$ tar xvfz DBI-1.625.tar.gz $ cd DBI-1.625
$ perl Makefile.PL $ make
$ make install

Если вам нужно установить драйвер SQLite для DBI, его можно установить следующим образом:

$ wget http://search.cpan.org/CPAN/authors/id/T/TU/TURNSTEP/DBD-Pg-2.19.3.tar.gz
$ tar xvfz DBD-Pg-2.19.3.tar.gz $ cd DBD-Pg-2.19.3
$ perl Makefile.PL $ make
$ make install

Прежде чем начать использовать интерфейс Perl PostgreSQL, найдите pg_hba.conf в каталоге установки PostgreSQL и добавьте следующую строку -

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

Вы можете запустить / перезапустить сервер postgres, если он не запущен, используя следующую команду -

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

API интерфейса DBI

Ниже приведены важные процедуры DBI, которые могут удовлетворить ваши требования для работы с базой данных SQLite из вашей программы Perl. Если вы ищете более сложное приложение, вы можете заглянуть в официальную документацию Perl DBI.

С. Нет.	API и описание
1	DBI→connect($data_source, "userid", "password", \%attr) Устанавливает соединение с базой данных или сеанс с запрошенным $ data_source. Возвращает объект дескриптора базы данных, если соединение установлено. Источник данных имеет вид: DBI:Pg:dbname=$database;host=127.0.0.1;port=5432 Pg - это имя драйвера PostgreSQL, а testdb - это имя базы данных.
2	$dbh→do($sql) Эта процедура подготавливает и выполняет один оператор SQL. Возвращает количество затронутых строк или undef в случае ошибки. Возвращаемое значение -1 означает, что количество строк неизвестно, неприменимо или недоступно. Здесь $ dbh - дескриптор, возвращаемый вызовом DBI → connect ().
3	$dbh→prepare($sql) Эта процедура подготавливает оператор для последующего выполнения ядром базы данных и возвращает ссылку на объект дескриптора оператора.
4	$sth→execute() Эта процедура выполняет любую обработку, необходимую для выполнения подготовленного оператора. В случае ошибки возвращается undef. Успешное выполнение всегда возвращает истину независимо от количества затронутых строк. Вот$sth is a statement handle returned by $dbh → подготовить ($ sql) вызов.
5	$sth→fetchrow_array() Эта процедура выбирает следующую строку данных и возвращает ее в виде списка, содержащего значения полей. Нулевые поля возвращаются в списке как значения undef.
6	$DBI::err Это эквивалентно $ h → err, где $h is any of the handle types like $дбх, $sth, or $дрх. Это возвращает код ошибки собственного ядра базы данных из последнего вызванного метода драйвера.
7	$DBI::errstr Это эквивалентно $ h → errstr, где $h is any of the handle types like $дбх, $sth, or $дрх. Это возвращает сообщение об ошибке собственного ядра базы данных из последнего вызванного метода DBI.
8	$dbh->disconnect() Эта подпрограмма закрывает соединение с базой данных, ранее открытое вызовом DBI → connect ().

Подключение к базе данных

Следующий код Perl показывает, как подключиться к существующей базе данных. Если база данных не существует, она будет создана и, наконец, будет возвращен объект базы данных.

#!/usr/bin/perl

use DBI;
use strict;

my $driver  = "Pg"; 
my $database = "testdb"; my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "postgres"; my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) 
   or die $DBI::errstr;

print "Opened database successfully\n";

Теперь давайте запустим указанную выше программу, чтобы открыть нашу базу данных. testdb; если база данных успешно открыта, она выдаст следующее сообщение -

Open database successfully

Создать таблицу

Следующая программа Perl будет использоваться для создания таблицы в ранее созданной базе данных -

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "Pg"; 
my $database = "testdb"; my $dsn = "DBI:$driver:dbname=$database;host=127.0.0.1;port=5432";
my $userid = "postgres"; my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr; print "Opened database successfully\n"; my $stmt = qq(CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL););
my $rv = $dbh->do($stmt); if($rv < 0) {
   print $DBI::errstr; } else { print "Table created successfully\n"; } $dbh->disconnect();

Opened database successfully
Table created successfully

ВСТАВИТЬ операцию

Следующая программа Perl показывает, как мы можем создавать записи в нашей таблице COMPANY, созданной в приведенном выше примере:

#!/usr/bin/perl

use DBI;
use strict;

my $driver = "Pg"; my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432"; my $userid = "postgres";
my $password = "pass123"; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr;
print "Opened database successfully\n";

my $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 )); my $rv = $dbh->do($stmt) or die $DBI::errstr; $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   VALUES (2, 'Allen', 25, 'Texas', 15000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;

$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )); $rv = $dbh->do($stmt) or die $DBI::errstr; $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ););
$rv = $dbh->do($stmt) or die $DBI::errstr;

print "Records created successfully\n";
$dbh->disconnect();

Opened database successfully
Records created successfully

ВЫБРАТЬ операцию

Следующая программа на Perl показывает, как мы можем извлекать и отображать записи из нашей таблицы COMPANY, созданной в приведенном выше примере:

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "Pg"; 
my $database = "testdb"; my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "postgres"; my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr; print "Opened database successfully\n"; my $stmt = qq(SELECT id, name, address, salary  from COMPANY;);
my $sth = $dbh->prepare( $stmt ); my $rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) { print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) { print "ID = ". $row[0] . "\n";
      print "NAME = ". $row[1] ."\n"; print "ADDRESS = ". $row[2] ."\n";
      print "SALARY =  ". $row[3] ."\n\n"; } print "Operation done successfully\n"; $dbh->disconnect();

Когда приведенная выше программа будет выполнена, она даст следующий результат:

Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  20000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

ОБНОВЛЕНИЕ Операция

Следующий код Perl показывает, как мы можем использовать оператор UPDATE для обновления любой записи, а затем извлекать и отображать обновленные записи из нашей таблицы COMPANY.

#!/usr/bin/perl

use DBI;
use strict;

my $driver = "Pg"; my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432"; my $userid = "postgres";
my $password = "pass123"; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr;
print "Opened database successfully\n";

my $stmt = qq(UPDATE COMPANY set SALARY = 25000.00 where ID=1;); my $rv = $dbh->do($stmt) or die $DBI::errstr; if( $rv < 0 ) {
   print $DBI::errstr; }else{ print "Total number of rows updated : $rv\n";
}
$stmt = qq(SELECT id, name, address, salary from COMPANY;); my $sth = $dbh->prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr; if($rv < 0) {
   print $DBI::errstr; } while(my @row = $sth->fetchrow_array()) {
      print "ID = ". $row[0] . "\n"; print "NAME = ". $row[1] ."\n";
      print "ADDRESS = ". $row[2] ."\n"; print "SALARY = ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();

Когда приведенная выше программа будет выполнена, она даст следующий результат:

Opened database successfully
Total number of rows updated : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  25000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

УДАЛИТЬ операцию

Следующий код Perl показывает, как мы можем использовать оператор DELETE для удаления любой записи, а затем извлечь и отобразить оставшиеся записи из нашей таблицы COMPANY.

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "Pg"; 
my $database = "testdb"; my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "postgres"; my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr; print "Opened database successfully\n"; my $stmt = qq(DELETE from COMPANY where ID=2;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ) { print $DBI::errstr;
} else{
   print "Total number of rows deleted : $rv\n"; } $stmt = qq(SELECT id, name, address, salary  from COMPANY;);
my $sth = $dbh->prepare( $stmt ); $rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) { print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) { print "ID = ". $row[0] . "\n";
      print "NAME = ". $row[1] ."\n"; print "ADDRESS = ". $row[2] ."\n";
      print "SALARY =  ". $row[3] ."\n\n"; } print "Operation done successfully\n"; $dbh->disconnect();

Когда приведенная выше программа будет выполнена, она даст следующий результат:

Opened database successfully
Total number of rows deleted : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  25000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

Установка

PostgreSQL может быть интегрирован с Python с помощью модуля psycopg2. sycopg2 - это адаптер базы данных PostgreSQL для языка программирования Python. psycopg2 был написан с целью сделать его очень маленьким, быстрым и стабильным, как скала. Вам не нужно устанавливать этот модуль отдельно, поскольку он по умолчанию поставляется вместе с Python версии 2.5.x и более поздних версий.

Если он не установлен на вашем компьютере, вы можете использовать команду yum для его установки следующим образом:

$yum install python-psycopg2

Чтобы использовать модуль psycopg2, вы должны сначала создать объект Connection, который представляет базу данных, а затем, при желании, вы можете создать объект курсора, который поможет вам в выполнении всех операторов SQL.

API модуля Python psycopg2

Ниже приведены важные процедуры модуля psycopg2, которые могут удовлетворить ваши требования для работы с базой данных PostgreSQL из вашей программы Python. Если вы ищете более сложное приложение, вы можете заглянуть в официальную документацию модуля Python psycopg2.

С. Нет.	API и описание
1	psycopg2.connect(database="testdb", user="postgres", password="cohondob", host="127.0.0.1", port="5432") Этот API открывает соединение с базой данных PostgreSQL. Если база данных открыта успешно, возвращается объект подключения.
2	connection.cursor() Эта процедура создает cursor который будет использоваться на протяжении всего программирования вашей базы данных с помощью Python.
3	cursor.execute(sql [, optional parameters]) Эта подпрограмма выполняет инструкцию SQL. Оператор SQL может быть параметризован (т. Е. Заполнителями вместо литералов SQL). Модуль psycopg2 поддерживает заполнитель с использованием знака% s Например: cursor.execute («вставить в людей значения (% s,% s)», (кто, возраст))
4	cursor.executemany(sql, seq_of_parameters) Эта процедура выполняет команду SQL для всех последовательностей параметров или сопоставлений, найденных в последовательности sql.
5	cursor.callproc(procname[, parameters]) Эта процедура выполняет хранимую процедуру базы данных с заданным именем. Последовательность параметров должна содержать по одной записи для каждого аргумента, ожидаемого процедурой.
6	cursor.rowcount Этот атрибут только для чтения, который возвращает общее количество строк базы данных, которые были изменены, вставлены или удалены последним выполнением * ().
7	connection.commit() Этот метод фиксирует текущую транзакцию. Если вы не вызовете этот метод, все, что вы сделали с момента последнего вызова commit (), не будет видно из других подключений к базе данных.
8	connection.rollback() Этот метод откатывает любые изменения в базе данных с момента последнего вызова commit ().
9	connection.close() Этот метод закрывает соединение с базой данных. Обратите внимание, что это не вызывает автоматического вызова commit (). Если вы просто закроете соединение с базой данных без предварительного вызова commit (), ваши изменения будут потеряны!
10	cursor.fetchone() Этот метод выбирает следующую строку набора результатов запроса, возвращая одну последовательность, или None, если больше нет доступных данных.
11	cursor.fetchmany([size=cursor.arraysize]) Эта процедура выбирает следующий набор строк результата запроса, возвращая список. Когда строк больше нет, возвращается пустой список. Метод пытается получить столько строк, сколько указано в параметре размера.
12	cursor.fetchall() Эта процедура извлекает все (оставшиеся) строки результата запроса, возвращая список. Если строк нет, возвращается пустой список.

Подключение к базе данных

Следующий код Python показывает, как подключиться к существующей базе данных. Если база данных не существует, она будет создана и, наконец, будет возвращен объект базы данных.

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database="testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")

print "Opened database successfully"

Здесь вы также можете указать базу данных testdb в качестве имени, и если база данных успешно открыта, он выдаст следующее сообщение -

Open database successfully

Создать таблицу

Следующая программа Python будет использоваться для создания таблицы в ранее созданной базе данных -

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()
cur.execute('''CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL);''')
print "Table created successfully"

conn.commit()
conn.close()

Когда приведенная выше программа будет выполнена, она создаст таблицу COMPANY в вашем test.db и он отобразит следующие сообщения -

Opened database successfully
Table created successfully

ВСТАВИТЬ операцию

Следующая программа Python показывает, как мы можем создавать записи в нашей таблице COMPANY, созданной в приведенном выше примере:

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Paul', 32, 'California', 20000.00 )");

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");

conn.commit()
print "Records created successfully";
conn.close()

Opened database successfully
Records created successfully

ВЫБРАТЬ операцию

Следующая программа Python показывает, как мы можем извлекать и отображать записи из нашей таблицы COMPANY, созданной в приведенном выше примере:

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()

cur.execute("SELECT id, name, address, salary  from COMPANY")
rows = cur.fetchall()
for row in rows:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"

print "Operation done successfully";
conn.close()

Когда приведенная выше программа будет выполнена, она даст следующий результат:

Opened database successfully
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  20000.0

ID =  2
NAME =  Allen
ADDRESS =  Texas
SALARY =  15000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Operation done successfully

ОБНОВЛЕНИЕ Операция

Следующий код Python показывает, как мы можем использовать оператор UPDATE для обновления любой записи, а затем извлекать и отображать обновленные записи из нашей таблицы COMPANY.

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()

cur.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
conn.commit()
print "Total number of rows updated :", cur.rowcount

cur.execute("SELECT id, name, address, salary  from COMPANY")
rows = cur.fetchall()
for row in rows:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"

print "Operation done successfully";
conn.close()

Когда приведенная выше программа будет выполнена, она даст следующий результат:

Opened database successfully
Total number of rows updated : 1
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  25000.0

ID =  2
NAME =  Allen
ADDRESS =  Texas
SALARY =  15000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Operation done successfully

УДАЛИТЬ операцию

Следующий код Python показывает, как мы можем использовать оператор DELETE для удаления любой записи, а затем извлекать и отображать оставшиеся записи из нашей таблицы COMPANY.

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()

cur.execute("DELETE from COMPANY where ID=2;")
conn.commit()
print "Total number of rows deleted :", cur.rowcount

cur.execute("SELECT id, name, address, salary  from COMPANY")
rows = cur.fetchall()
for row in rows:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"

print "Operation done successfully";
conn.close()

Когда приведенная выше программа будет выполнена, она даст следующий результат:

Opened database successfully
Total number of rows deleted : 1
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  20000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Operation done successfully