PostgreSQL — Обзор

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

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

Что такое PostgreSQL?

PostgreSQL (произносится как post-gress-QL ) — это система управления реляционными базами данных (СУБД) с открытым исходным кодом, разработанная всемирной командой добровольцев. 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

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
• Внешние ключи
• Спусковой крючок
• Просмотры
• операции
• Мультиверсионный параллельный контроль (MVCC)
• Потоковая репликация (по состоянию на 9.0)
• Горячий резерв (по состоянию на 9.0)

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

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

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

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

PostgreSQL — настройка среды

Чтобы начать понимать основы 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. Теперь давайте выполним это следующим образом —

Выберите номер версии 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-компьютер и отобразит следующее сообщение:

Когда вы запускаете установщик, он задает вам несколько основных вопросов, таких как место установки, пароль пользователя, который будет использовать базу данных, номер порта и т. Д. Поэтому оставьте для всех их значения по умолчанию, кроме пароля, который вы можете указать в качестве пароля. согласно вашему выбору. Он установит 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, если он не работает, с помощью следующей команды —

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

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

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

Если ваша установка была правильной, у вас будет приглашение 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.

Выберите номер версии PostgreSQL, которую вы хотите, и, насколько это возможно, платформу, которую вы хотите от EnterpriseDB

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

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

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

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

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

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

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

• На следующем шаге, когда меня спросили «Locale», я выбрал «English, United States».

• Установка PostgreSQL в вашей системе занимает некоторое время. По завершении процесса установки вы получите следующий экран. Снимите флажок и нажмите кнопку Готово.

На следующем шаге, когда меня спросили «Locale», я выбрал «English, United States».

Установка PostgreSQL в вашей системе занимает некоторое время. По завершении процесса установки вы получите следующий экран. Снимите флажок и нажмите кнопку Готово.

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

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

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

• Выберите номер последней версии PostgreSQL для Mac OS, доступный на EnterpriseDB

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

Выберите номер последней версии 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 , который выдаст предупреждающее сообщение. Примите предупреждение и приступайте к дальнейшей установке. Он запросит пароль администратора, как показано в следующем окне —

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

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

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

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

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

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

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

PostgreSQL — синтаксис

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

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

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

 postgres-# \help <command_name>

Оператор SQL

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

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

SQL-команды PostgreSQL

ABORT

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

ABORT [ WORK | TRANSACTION ]

ALTER AGGREGATE

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

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

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

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

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

ALTER DATABASE

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

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

ALTER DOMAIN

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

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

ALTER FUNCTION

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

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

ALTER GROUP

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

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

ALTER INDEX

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

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

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

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

ALTER LANGUAGE name RENAME TO new_name

ALTER OPERATOR

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

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

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

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

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

ALTER SCHEMA

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

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

ALTER SEQUENCE

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

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

ALTER TABLE

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

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

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

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

ALTER TABLESPACE

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

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

ALTER TRIGGER

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

ALTER TRIGGER name ON table RENAME TO new_name

ALTER TYPE

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

ALTER TYPE name OWNER TO new_owner

ALTER USER

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

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

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

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

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

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

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

НАЧАТЬ

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

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

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

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

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

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

CHECKPOINT

БЛИЗКО

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

CLOSE name

Кластер

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

CLUSTER index_name ON table_name
CLUSTER table_name
CLUSTER

КОММЕНТАРИЙ

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

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

COMMIT

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

COMMIT [ WORK | TRANSACTION ]

COPY

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

СОЗДАТЬ ЯЗЫК

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

СОЗДАТЬ СТОЛ

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

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

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

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

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

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

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

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

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

СОЗДАТЬ СТОЛ

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

CREATE TABLESPACE tablespace_name [ OWNER username ] LOCATION 'directory'

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

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

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

СОЗДАТЬ ТИП

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

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

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

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

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

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

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

СОЗДАТЬ ВИД

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

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

DEALLOCATE

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

DEALLOCATE [ PREPARE ] plan_name

DECLARE

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

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

УДАЛЯТЬ

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

DELETE FROM [ ONLY ] table [ WHERE condition ]

DROP AGGREGATE

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

DROP AGGREGATE name ( type ) [ CASCADE | RESTRICT ]

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

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

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

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

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

DROP CONVERSION name [ CASCADE | RESTRICT ]

DROP DATABASE

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

DROP DATABASE name

DROP DOMAIN

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

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

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

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

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

DROP GROUP

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

DROP GROUP name

Индекс капли

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

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

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

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

DROP [ PROCEDURAL ] LANGUAGE name [ CASCADE | RESTRICT ]

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

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

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

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

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

DROP OPERATOR CLASS name USING index_method [ CASCADE | RESTRICT ]

DROP RULE

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

DROP RULE name ON relation [ CASCADE | RESTRICT ]

DROP SCHEMA

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

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

DROP SEQUENCE

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

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

DROP TABLE

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

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

DROP TABLESPACE

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

DROP TABLESPACE tablespace_name

DROP TRIGGER

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

DROP TRIGGER name ON table [ CASCADE | RESTRICT ]

DROP TYPE

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

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

DROP USER

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

DROP USER name

DROP VIEW

Удалить вид.

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

КОНЕЦ

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

END [ WORK | TRANSACTION ]

ВЫПОЛНИТЬ

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

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

EXPLAIN

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

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

FETCH

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

FETCH [ direction { FROM | IN } ] cursor_name

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

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

ГРАНТ

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

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

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

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

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

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

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

ВСТАВИТЬ

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

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

СЛУШАТЬ

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

LISTEN name

НАГРУЗКИ

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

LOAD 'filename'

ЗАМОК

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

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

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

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

ПЕРЕЕХАТЬ

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

MOVE [ direction { FROM | IN } ] cursor_name

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

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

NOTIFY name

ПОДГОТОВИТЬ

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

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

REINDEX

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

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

RELEASE SAVEPOINT

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

RELEASE [ SAVEPOINT ] savepoint_name

СБРОС

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

RESET name
RESET ALL

КЕУОКЕ

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

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

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

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

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

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

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

ROLLBACK

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

ROLLBACK [ WORK | TRANSACTION ]

Откат к SAVEPOINT

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

ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name

SAVEPOINT

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

SAVEPOINT savepoint_name

ВЫБРАТЬ

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

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

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

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

ВЫБРАТЬ В

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

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

ЗАДАВАТЬ

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

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

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

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

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

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

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

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

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

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

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

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

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

ШОУ

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

SHOW name
SHOW ALL

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

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

START TRANSACTION [ transaction_mode [, ...] ]

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

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

TRUNCATE

Пустой стол.

TRUNCATE [ TABLE ] name

UNLISTEN

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

UNLISTEN { name | * }

ОБНОВИТЬ

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

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

ВАКУУМНАЯ

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

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

PostgreSQL — тип данных

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

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

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

• Валидация — правильное использование типов данных подразумевает проверку формата данных и отклонение данных вне области действия типа данных.

• Компактность — поскольку столбец может хранить значения одного типа, он хранится в компактном виде.

• Производительность — правильное использование типов данных обеспечивает наиболее эффективное хранение данных. Сохраненные значения можно быстро обработать, что повышает производительность.

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

Валидация — правильное использование типов данных подразумевает проверку формата данных и отклонение данных вне области действия типа данных.

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

Производительность — правильное использование типов данных обеспечивает наиболее эффективное хранение данных. Сохраненные значения можно быстро обработать, что повышает производительность.

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	различные символы (n), varchar (n) переменная длина с ограничением
2	символ (n), символ (n) фиксированная длина, с подкладкой
3	текст переменная неограниченная длина

различные символы (n), varchar (n)

переменная длина с ограничением

символ (n), символ (n)

фиксированная длина, с подкладкой

текст

переменная неограниченная длина

Двоичные типы данных

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

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

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

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

название	Размер хранилища	Описание	Низкое значение	Высокое значение
отметка времени [(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. Тип данных Boolean может иметь состояния true , false и третье состояние неизвестно , которое представлено нулевым значением SQL.

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

Перечислимый тип

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

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

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

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

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

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

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

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

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

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

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

Типы битовых строк используются для хранения битовых масок. Это либо 0, либо 1. Существует два типа битов SQL: bit (n) и bitinging (n) , где n — положительное целое число.

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

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

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

поисковый вектор

Это отсортированный список отдельных слов, которые были нормализованы для объединения различных вариантов одного и того же слова, называемые «лексемы».

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}, {99100}}’ :: Int [])	[[1,5], [99100]]
row_to_json (строка (1, ‘Foo’))	{ «F1»: 1, «F2»: «Foo»}

Тип массива

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

Декларация массивов

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

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);

Это действительно для описанного выше инвентаризационного элемента. Ключевое слово 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 — диапазон меток времени с часовым поясом

• диапазон дат — диапазон дат

int4range — диапазон целого числа

int8range — диапазон от bigint

numrange — диапазон числовых значений

tsrange — диапазон меток времени без часового пояса

tstzrange — диапазон меток времени с часовым поясом

диапазон дат — диапазон дат

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

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

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

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

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

Псевдо-типы

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

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

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

любой

Указывает, что функция принимает любой тип входных данных.

anyelement

Указывает, что функция принимает любой тип данных.

anyarray

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

anynonarray

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

anyenum

Указывает, что функция принимает любой тип данных enum.

anyrange

Указывает, что функция принимает любой тип данных диапазона.

CString

Указывает, что функция принимает или возвращает строку C с нулевым символом в конце.

внутренний

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

language_handler

Обработчик вызова процедурного языка объявляется как возвращающий language_handler.

fdw_handler

Обработчик обёртки сторонних данных объявляется как возвращающий fdw_handler.

запись

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

спусковой крючок

Объявлена ​​триггерная функция для возврата триггера.

недействительным

Указывает, что функция не возвращает значения.

PostgreSQL — СОЗДАТЬ базу данных

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

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

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

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

Синтаксис

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

CREATE DATABASE dbname;

где dbname — это имя базы данных, которую нужно создать.

пример

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

postgres=# CREATE DATABASE testdb;
postgres-# 

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

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

Синтаксис

Синтаксис для созданного B , как показано ниже —

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

параметры

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

С. Нет.	Параметр и описание
1	имя_бд Имя базы данных для создания.
2	описание Определяет комментарий, который будет связан с вновь созданной базой данных.
3	опции аргументы командной строки, которые созданныйb принимает.

имя_бд

Имя базы данных для создания.

описание

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

опции

аргументы командной строки, которые созданныйb принимает.

Опции

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

С. Нет.	Вариант и описание
1	-D табличное пространство Задает табличное пространство по умолчанию для базы данных.
2	-e Выводим команды, которые создалb генерирует и отправляет на сервер.
3	-Е кодировка Определяет схему кодировки символов, которая будет использоваться в этой базе данных.
4	-l локаль Определяет язык, который будет использоваться в этой базе данных.
5	-Т шаблон Определяет базу данных шаблонов, из которой можно построить эту базу данных.
6	—Помогите Показать справку об аргументах командной строки созданной командыb и выйти.
7	-х хозяин Указывает имя хоста компьютера, на котором работает сервер.
8	порт Указывает порт TCP или расширение файла локального сокета домена Unix, на котором сервер прослушивает соединения.
9	-U имя пользователя Имя пользователя для подключения как.
10	-w Никогда не выдавайте запрос пароля.
11	-W Принудительно создайтеb для запроса пароля перед подключением к базе данных.

-D табличное пространство

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

-e

Выводим команды, которые создалb генерирует и отправляет на сервер.

-Е кодировка

Определяет схему кодировки символов, которая будет использоваться в этой базе данных.

-l локаль

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

-Т шаблон

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

—Помогите

Показать справку об аргументах командной строки созданной командыb и выйти.

-х хозяин

Указывает имя хоста компьютера, на котором работает сервер.

порт

Указывает порт TCP или расширение файла локального сокета домена Unix, на котором сервер прослушивает соединения.

-U имя пользователя

Имя пользователя для подключения как.

-w

Никогда не выдавайте запрос пароля.

-W

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

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

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

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

После того, как база данных создана с использованием любого из вышеупомянутых методов, вы можете проверить ее в списке баз данных, используя команду \ l , т.е. команду backslash 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-# 

PostgreSQL — ВЫБРАТЬ базу данных

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

• База данных SQL Prompt
• Командная строка ОС

База данных SQL Prompt

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

postgres=#

Вы можете проверить список доступных баз данных, используя команду \ l , т.е. команду backslash 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 — DROP база данных

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

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

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

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

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

Синтаксис

Синтаксис для DROP DATABASE приведен ниже —

DROP DATABASE [ IF EXISTS ] name

параметры

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

С. Нет.	Параметр и описание
1	ЕСЛИ СУЩЕСТВУЕТ Не выдавайте ошибку, если база данных не существует. В этом случае выдается уведомление.
2	название Имя базы данных для удаления.

ЕСЛИ СУЩЕСТВУЕТ

Не выдавайте ошибку, если база данных не существует. В этом случае выдается уведомление.

название

Имя базы данных для удаления.

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

пример

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

postgres=# DROP DATABASE testdb;
postgres-# 

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

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

Синтаксис

Синтаксис для dropdb , как показано ниже —

dropdb  [option...] dbname

параметры

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

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

имя_бд

Имя базы данных, которая будет удалена.

вариант

аргументы командной строки, которые принимает dropdb.

Опции

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

С. Нет.	Вариант и описание
1	-e Показывает команды, отправляемые на сервер.
2	-я Выдает запрос подтверждения, прежде чем делать что-либо разрушительное.
3	-V Распечатайте версию dropdb и выйдите.
4	—Если-существует Не выдавайте ошибку, если база данных не существует. В этом случае выдается уведомление.
5	—Помогите Показать справку об аргументах командной строки dropdb и завершиться.
6	-х хозяин Указывает имя хоста компьютера, на котором работает сервер.
7	порт Указывает порт TCP или расширение файла локального сокета домена UNIX, на котором сервер прослушивает соединения.
8	-U имя пользователя Имя пользователя для подключения как.
9	-w Никогда не выдавайте запрос пароля.
10	-W Заставьте dropdb запрашивать пароль перед подключением к базе данных.
11	—maintenance-DB = имя_бд Задает имя базы данных, к которой нужно подключиться, чтобы удалить целевую базу данных.

-e

Показывает команды, отправляемые на сервер.

-я

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

-V

Распечатайте версию dropdb и выйдите.

—Если-существует

Не выдавайте ошибку, если база данных не существует. В этом случае выдается уведомление.

—Помогите

Показать справку об аргументах командной строки dropdb и завершиться.

-х хозяин

Указывает имя хоста компьютера, на котором работает сервер.

порт

Указывает порт TCP или расширение файла локального сокета домена UNIX, на котором сервер прослушивает соединения.

-U имя пользователя

Имя пользователя для подключения как.

-w

Никогда не выдавайте запрос пароля.

-W

Заставьте dropdb запрашивать пароль перед подключением к базе данных.

—maintenance-DB = имя_бд

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

пример

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

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

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

PostgreSQL — CREATE Table

Оператор 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 с ID в качестве первичного ключа, а 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

Оператор 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 указывает, что команда удаления выполнена успешно.

PostgreSQL — схема

Схема — это именованная коллекция таблиц. Схема также может содержать представления, индексы, последовательности, типы данных, операторы и функции. Схемы аналогичны каталогам на уровне операционной системы, за исключением того, что схемы не могут быть вложенными. Оператор 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 Query

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

Синтаксис

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

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

• Здесь column1, column2, … columnN — это имена столбцов в таблице, в которую вы хотите вставить данные.

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

Здесь column1, column2, … columnN — это имена столбцов в таблице, в которую вы хотите вставить данные.

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

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

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

Выход

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

С. Нет.	Выходное сообщение и описание
1	ВСТАВИТЬ oid 1 Сообщение возвращается, если была вставлена ​​только одна строка. oid — это числовой OID вставленной строки.
2	ВСТАВИТЬ 0 # Сообщение возвращается, если было вставлено более одной строки. # — количество вставленных строк.

ВСТАВИТЬ oid 1

Сообщение возвращается, если была вставлена ​​только одна строка. oid — это числовой OID вставленной строки.

ВСТАВИТЬ 0 #

Сообщение возвращается, если было вставлено более одной строки. # — количество вставленных строк.

Примеры

Давайте создадим таблицу COMPANY в 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 Query

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

Синтаксис

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

SELECT column1, column2, columnN FROM table_name;

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

SELECT * FROM table_name;

пример

Рассмотрим таблицу 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)

Ниже приведен пример, в котором можно получить поля ID, Имя и Зарплата клиентов, доступных в таблице 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 — операторы

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

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

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

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

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

Предположим, что переменная a содержит 2, а переменная b содержит 3, тогда —

пример

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

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

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

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

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

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

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

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

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

А ТАКЖЕ

Оператор AND допускает существование нескольких условий в предложении WHERE оператора PostgresSQL.

НЕ

Оператор NOT меняет значение логического оператора, с которым он используется. Например. НЕ СУЩЕСТВУЕТ, НЕ МЕЖДУ, НЕ В И т. Д. Это оператор отрицания .

ИЛИ ЖЕ

Оператор OR используется для объединения нескольких условий в предложении WHERE оператора PostgresSQL.

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

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

п	Q	P & Q	р | Q
0	0	0	0
0	1	0	1
1	1	1	1
1	0	0	1

Предположим, если А = 60; и B = 13; теперь в двоичном формате они будут выглядеть следующим образом —

A = 0011 1100

B = 0000 1101

——————

A & B = 0000 1100

A | B = 0011 1101

~ A = 1100 0011

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

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

оператор	Описание	пример
&	Двоичный оператор AND немного копирует результат, если он существует в обоих операндах.	(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

PostgreSQL — выражения

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

PostgreSQL EXPRESSIONS подобны формулам и написаны на языке запросов. Вы также можете использовать для запроса базы данных для конкретного набора данных.

Синтаксис

Рассмотрим основной синтаксис оператора 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;

Рассмотрим таблицу 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)

Вот простой пример, демонстрирующий использование логических выражений 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)

PostgreSQL — предложение WHERE

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

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

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

Синтаксис

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

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

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

пример

Рассмотрим таблицу 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)

Вот простые примеры, показывающие использование логических операторов PostgreSQL. После оператора SELECT будут перечислены все записи, где AGE больше или равно 25 И зарплата больше или равна 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 ИЛИ оклад больше или равен 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 И и ИЛИ используются для объединения нескольких условий для сужения выбранных данных в операторе 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, должны быть ИСТИНА.

пример

Рассмотрим таблицу 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)

Следующий оператор SELECT перечисляет все записи, где AGE больше или равно 25 И зарплата больше или равна 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 также используется для объединения нескольких условий в предложении WHERE оператора PostgreSQL. При использовании оператора ИЛИ полное условие будет считаться истинным, если хотя бы одно из условий является истинным. Например, [условие1] ИЛИ [условие2] будет истинным, если условие 1 или условие 2 истинно.

Синтаксис

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

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

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

пример

Рассмотрим таблицу 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)

Следующий оператор SELECT перечисляет все записи, где AGE больше или равно 25 ИЛИ оклад больше или равен 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)

PostgreSQL — ОБНОВЛЕНИЕ запроса

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

Синтаксис

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

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

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

пример

Рассмотрим таблицу 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)

Ниже приведен пример, который будет обновлять адрес для клиента, чей идентификатор 6 —

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

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

 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 — УДАЛИТЬ Запрос

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

Синтаксис

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

DELETE FROM table_name
WHERE [condition];

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

пример

Рассмотрим таблицу 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)

Ниже приведен пример, который удаляет клиента с идентификатором 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 — как статья

Оператор PostgreSQL LIKE используется для сопоставления текстовых значений с шаблоном с использованием подстановочных знаков. Если поисковое выражение может быть сопоставлено с выражением шаблона, оператор LIKE вернет true, равное 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	Где заработная плата :: текст, как «200%» Находит любые значения, которые начинаются с 200
2	Где заработная плата :: текст, как «% 200%» Находит любые значения, которые имеют 200 в любой позиции
3	Где заработная плата :: текст, как «_00%» Находит любые значения, которые имеют 00 во второй и третьей позиции
4	ГДЕ НАЛОГОВЫЙ :: текст НРАВИТСЯ ‘2 _% _%’ Находит любые значения, которые начинаются с 2 и имеют длину не менее 3 символов
5	Где заработная плата :: текст, как «% 2» Находит любые значения, которые заканчиваются на 2
6	Где заработная плата :: текст как ‘_2% 3’ Находит любые значения, которые имеют 2 во второй позиции и заканчиваются на 3
7	Где заработная плата :: текст, как «2___3» Находит любые значения в пятизначном числе, которые начинаются с 2 и заканчиваются на 3

Где заработная плата :: текст, как «200%»

Находит любые значения, которые начинаются с 200

Где заработная плата :: текст, как «% 200%»

Находит любые значения, которые имеют 200 в любой позиции

Где заработная плата :: текст, как «_00%»

Находит любые значения, которые имеют 00 во второй и третьей позиции

ГДЕ НАЛОГОВЫЙ :: текст НРАВИТСЯ ‘2 _% _%’

Находит любые значения, которые начинаются с 2 и имеют длину не менее 3 символов

Где заработная плата :: текст, как «% 2»

Находит любые значения, которые заканчиваются на 2

Где заработная плата :: текст как ‘_2% 3’

Находит любые значения, которые имеют 2 во второй позиции и заканчиваются на 3

Где заработная плата :: текст, как «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)

Ниже приведен пример, в котором будут отображаться все записи из таблицы COMPANY, в которых ADDRESS будет содержать дефис (-) внутри текста —

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

Предложение LIMIT PostgreSQL используется для ограничения объема данных, возвращаемых оператором 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 позволяют вам получить только часть строк, которые генерируются остальной частью запроса.

пример

Рассмотрим таблицу 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)

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

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

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

Синтаксис

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

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

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

пример

Рассмотрим таблицу 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)

Ниже приведен пример, который сортирует результат в порядке возрастания по SALARY —

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)

Ниже приведен пример, который сортирует результат в порядке возрастания по NAME и SALARY.

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

Предложение 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. Убедитесь, что какой столбец вы используете для группировки, этот столбец должен быть доступен в списке столбцов.

пример

Рассмотрим таблицу 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)

Если вы хотите узнать общую сумму зарплаты каждого клиента, запрос 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

В 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 может совпадать с именем существующей таблицы и иметь приоритет.

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

Рекурсивный СО

Рекурсивные запросы WITH или Иерархические запросы — это форма CTE, в которой CTE может ссылаться на себя, т. Е. Запрос WITH может ссылаться на свой собственный вывод, следовательно, имя рекурсивно.

пример

Рассмотрим таблицу 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)

Теперь давайте напишем запрос, используя предложение 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. Запрос в примере эффективно перемещает строки из КОМПАНИИ в КОМПАНИ1. DELETE в WITH удаляет указанные строки из COMPANY, возвращая их содержимое с помощью предложения RETURNING; а затем первичный запрос читает этот вывод и вставляет его в КОМПАНИЮ TABLE —

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)

PostgreSQL — предложение HAVING

Предложение 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

пример

Рассмотрим таблицу 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)

Ниже приведен пример, в котором будет отображаться запись, для которой количество имен меньше 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

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

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

Синтаксис

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

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

пример

Рассмотрим таблицу 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)

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

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)

PostgreSQL — ОГРАНИЧЕНИЯ

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

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

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

• NOT NULL Ограничение — Гарантирует, что столбец не может иметь значение NULL.

• UNIQUE Constraint — гарантирует, что все значения в столбце разные.

• PRIMARY Key — уникально идентифицирует каждую строку / запись в таблице базы данных.

• Ключ FOREIGN — Ограничивает данные на основе столбцов в других таблицах.

• Ограничение CHECK — ограничение CHECK гарантирует, что все значения в столбце удовлетворяют определенным условиям.

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

NOT NULL Ограничение — Гарантирует, что столбец не может иметь значение NULL.

UNIQUE Constraint — гарантирует, что все значения в столбце разные.

PRIMARY Key — уникально идентифицирует каждую строку / запись в таблице базы данных.

Ключ FOREIGN — Ограничивает данные на основе столбцов в других таблицах.

Ограничение CHECK — ограничение CHECK гарантирует, что все значения в столбце удовлетворяют определенным условиям.

Ограничение EXCLUSION — ограничение 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
);

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

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

пример

Например, следующий оператор 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 однозначно идентифицирует каждую запись в таблице базы данных. Может быть больше УНИКАЛЬНЫХ столбцов, но только один первичный ключ в таблице. Первичные ключи важны при разработке таблиц базы данных. Первичные ключи — это уникальные идентификаторы.

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

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

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

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

пример

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

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

ЗАРУБЕЖНЫЙ КЛЮЧ

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

пример

Например, следующий оператор 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 является внешним ключом и ссылается на поле идентификатора таблицы COMPANY6.

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

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

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

пример

Например, следующий оператор PostgreSQL создает новую таблицу с именем COMPANY5 и добавляет пять столбцов. Здесь мы добавляем столбец CHECK with SALARY, чтобы у вас не было SALARY в качестве нуля.

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, 'Allen', 42, 'California', 20000.00 );

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

ERROR:  duplicate key value violates unique constraint "company7_pkey"
DETAIL:  Key (id)=(3) already exists.

Отбрасывание ограничений

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

ALTER TABLE table_name DROP CONSTRAINT some_name;

PostgreSQL — СОЕДИНЕНИЯ

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

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

• CROSS JOIN
• ВНУТРЕННЕЕ СОЕДИНЕНИЕ
• ЛЕВОЕ НАРУЖНОЕ СОЕДИНЕНИЕ
• ПРАВИЛЬНОЕ НАРУЖНОЕ СОЕДИНЕНИЕ
• ПОЛНОЕ НАРУЖНОЕ СОЕДИНЕНИЕ

Прежде чем мы продолжим, давайте рассмотрим две таблицы, КОМПАНИЯ и ОТДЕЛ. Мы уже видели операторы 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 );

Наконец, у нас есть следующий список записей, доступных в таблице ОТДЕЛ —

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

CROSS JOIN

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

Ниже приводится синтаксис 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

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

НАРУЖНОЕ СОЕДИНЕНИЕ является продолжением ВНУТРЕННЕГО СОЕДИНЕНИЯ. Стандарт SQL определяет три типа OUTER JOIN: 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 — предложение UNIONS

Оператор / оператор 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)

(б) Другая таблица — это ОТДЕЛ следующего содержания:

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 используется для объединения результатов двух операторов 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

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);

Теперь в таблице 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 |             |
  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, который перечислит все записи, где SALARY равен 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)

PostgreSQL — Синтаксис ALIAS

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

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

Синтаксис

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

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

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

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)

(б) Другая таблица — это ОТДЕЛ следующего содержания:

 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 — ТРИГГЕРС

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

Ниже приведены важные моменты, касающиеся триггеров PostgreSQL:

• Триггер PostgreSQL может быть указан для запуска

  • Перед попыткой операции над строкой (перед проверкой ограничений и попыткой INSERT, UPDATE или DELETE)

  • После завершения операции (после проверки ограничений и завершения INSERT, UPDATE или DELETE)

  • Вместо операции (в случае вставки, обновления или удаления в представлении)

• Триггер, помеченный FOR EACH ROW, вызывается один раз для каждой строки, которую изменяет операция. Напротив, триггер, помеченный FOR EACH STATEMENT, выполняется только один раз для любой данной операции, независимо от того, сколько строк он изменяет.

• И предложение WHEN, и действия триггера могут обращаться к элементам вставляемой, удаляемой или обновляемой строки, используя ссылки в форме NEW.column-name и OLD.column-name , где column-name — это имя столбца из таблица, с которой связан триггер.

• Если указано предложение WHEN, указанные операторы PostgreSQL выполняются только для тех строк, для которых предложение WHEN является истинным. Если предложение WHEN не указано, операторы PostgreSQL выполняются для всех строк.

• Если для одного и того же события определены несколько триггеров одного и того же типа, они будут срабатывать в алфавитном порядке по имени.

• Ключевое слово BEFORE, AFTER или INSTEAD OF определяет, когда действия триггера будут выполнены относительно вставки, изменения или удаления связанной строки.

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

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

• Опция CONSTRAINT, когда она указана, создает триггер ограничения . Это то же самое, что и обычный триггер, за исключением того, что время срабатывания триггера можно настроить с помощью SET CONSTRAINTS. Ожидается, что триггеры ограничения вызовут исключение, когда реализуемые ими ограничения нарушены.

Триггер PostgreSQL может быть указан для запуска

Перед попыткой операции над строкой (перед проверкой ограничений и попыткой INSERT, UPDATE или DELETE)

После завершения операции (после проверки ограничений и завершения INSERT, UPDATE или DELETE)

Вместо операции (в случае вставки, обновления или удаления в представлении)

Триггер, помеченный FOR EACH ROW, вызывается один раз для каждой строки, которую изменяет операция. Напротив, триггер, помеченный FOR EACH STATEMENT, выполняется только один раз для любой данной операции, независимо от того, сколько строк он изменяет.

И предложение WHEN, и действия триггера могут обращаться к элементам вставляемой, удаляемой или обновляемой строки, используя ссылки в форме NEW.column-name и OLD.column-name , где column-name — это имя столбца из таблица, с которой связан триггер.

Если указано предложение WHEN, указанные операторы PostgreSQL выполняются только для тех строк, для которых предложение WHEN является истинным. Если предложение WHEN не указано, операторы PostgreSQL выполняются для всех строк.

Если для одного и того же события определены несколько триггеров одного и того же типа, они будут срабатывать в алфавитном порядке по имени.

Ключевое слово BEFORE, AFTER или INSTEAD OF определяет, когда действия триггера будут выполнены относительно вставки, изменения или удаления связанной строки.

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

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

Опция CONSTRAINT, когда она указана, создает триггер ограничения . Это то же самое, что и обычный триггер, за исключением того, что время срабатывания триггера можно настроить с помощью SET CONSTRAINTS. Ожидается, что триггеры ограничения вызовут исключение, когда реализуемые ими ограничения нарушены.

Синтаксис

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

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 и имеет следующее определение:

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;

PostgreSQL — ИНДЕКСЫ

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

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

Индекс помогает ускорить запросы SELECT и предложения WHERE; однако это замедляет ввод данных с помощью операторов UPDATE и INSERT. Индексы могут быть созданы или удалены без влияния на данные.

Создание индекса включает в себя инструкцию CREATE INDEX, которая позволяет указать имя индекса, указать таблицу и столбец или столбцы для индексации и указать, находится ли индекс в порядке возрастания или убывания.

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

Команда CREATE INDEX

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

CREATE INDEX index_name ON table_name;

Типы индексов

PostgreSQL предоставляет несколько типов индексов: B-дерево, 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);

Неявные индексы

Неявные индексы — это индексы, которые автоматически создаются сервером базы данных при создании объекта. Индексы автоматически создаются для ограничений первичного ключа и уникальных ограничений.

пример

Ниже приведен пример, где мы создадим индекс для таблицы COMPANY для столбца зарплаты:

# 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.

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

Индексы не должны использоваться на маленьких столах.

Таблицы с частыми, крупными пакетными обновлениями или операциями вставки.

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

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

PostgreSQL — команда ALTER TABLE

Команда 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 для изменения типа данных столбца в таблице выглядит следующим образом:

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 для ДОБАВЛЕНИЯ УНИКАЛЬНОГО ОГРАНИЧЕНИЯ в таблицу выглядит следующим образом:

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

Команда PostgreSQL TRUNCATE TABLE используется для удаления полных данных из существующей таблицы. Вы также можете использовать команду DROP TABLE для удаления полной таблицы, но это приведет к удалению полной структуры таблицы из базы данных, и вам потребуется заново создать эту таблицу, если вы хотите сохранить некоторые данные.

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

Синтаксис

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

TRUNCATE TABLE  table_name;

пример

Предположим, что таблица 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=# TRUNCATE TABLE COMPANY;

Теперь таблица COMPANY усекается, и следующим будет вывод оператора SELECT —

testdb=# SELECT * FROM CUSTOMERS;
 id | name | age | address | salary
----+------+-----+---------+--------
(0 rows)

PostgreSQL — ВИДЫ

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

Представление может содержать все строки таблицы или выбранные строки из одной или нескольких таблиц. Представление может быть создано из одной или нескольких таблиц, что зависит от написанного запроса 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, представление будет создано во временном пространстве. Временные представления автоматически удаляются в конце текущего сеанса.

пример

Представьте, что таблица 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

Далее приведен пример создания представления из таблицы 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 — СДЕЛКИ

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

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

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

Свойства сделок

Транзакции имеют следующие четыре стандартных свойства, обычно обозначаемых аббревиатурой ACID —

• Атомарность — гарантирует, что все операции внутри рабочего блока успешно завершены; в противном случае транзакция прерывается в точке сбоя, а предыдущие операции возвращаются в прежнее состояние.

• Согласованность — Гарантирует, что база данных корректно меняет состояния при успешной фиксации транзакции.

• Изоляция — позволяет транзакциям работать независимо друг от друга и быть прозрачными друг для друга.

• Долговечность — Гарантирует, что результат или результат совершенной транзакции сохраняется в случае сбоя системы.

Атомарность — гарантирует, что все операции внутри рабочего блока успешно завершены; в противном случае транзакция прерывается в точке сбоя, а предыдущие операции возвращаются в прежнее состояние.

Согласованность — Гарантирует, что база данных корректно меняет состояния при успешной фиксации транзакции.

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

Долговечность — Гарантирует, что результат или результат совершенной транзакции сохраняется в случае сбоя системы.

Контроль транзакций

Следующие команды используются для управления транзакциями —

• BEGIN TRANSACTION — начать транзакцию.

• COMMIT — для сохранения изменений вы также можете использовать команду END TRANSACTION .

• ROLLBACK — для отката изменений.

BEGIN TRANSACTION — начать транзакцию.

COMMIT — для сохранения изменений вы также можете использовать команду END TRANSACTION .

ROLLBACK — для отката изменений.

Команды управления транзакциями используются только с командами DML INSERT, UPDATE и DELETE. Их нельзя использовать при создании таблиц или их удалении, поскольку эти операции автоматически фиксируются в базе данных.

Команда НАЧАЛО СДЕЛКИ

Транзакции могут быть начаты с использованием BEGIN TRANSACTION или просто BEGIN. Такие транзакции обычно сохраняются до следующей команды COMMIT или ROLLBACK. Но транзакция также ROLLBACK, если база данных закрыта или возникает ошибка.

Ниже приведен простой синтаксис для запуска транзакции:

BEGIN;

or

BEGIN TRANSACTION;

Команда COMMIT

Команда COMMIT — это команда транзакций, используемая для сохранения изменений, вызванных транзакцией, в базу данных.

Команда COMMIT сохраняет все транзакции в базе данных с момента последней команды COMMIT или ROLLBACK.

Синтаксис команды COMMIT следующий:

COMMIT;

or

END TRANSACTION;

Команда ROLLBACK

Команда ROLLBACK — это команда транзакций, используемая для отмены транзакций, которые еще не были сохранены в базе данных.

Команду ROLLBACK можно использовать только для отмены транзакций с момента выполнения последней команды COMMIT или ROLLBACK.

Синтаксис команды ROLLBACK следующий:

ROLLBACK;

пример

Предположим, что таблица 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

Теперь давайте запустим транзакцию и удалим записи из таблицы, имеющие возраст = 25, и, наконец, мы используем команду ROLLBACK, чтобы отменить все изменения.

testdb=# BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
ROLLBACK;

Если вы проверите, в таблице 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

Теперь давайте запустим еще одну транзакцию и удалим записи из таблицы, имеющие возраст = 25, и, наконец, мы используем команду COMMIT, чтобы зафиксировать все изменения.

testdb=# BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;

Если вы проверите таблицу COMPANY, она все еще имеет следующие записи —

 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)

PostgreSQL — LOCKS

Блокировки или эксклюзивные блокировки или блокировки записи не позволяют пользователям изменять строку или всю таблицу. Строки, измененные UPDATE и DELETE, затем автоматически блокируются исключительно на время транзакции. Это не позволяет другим пользователям изменять строку, пока транзакция не будет зафиксирована или откатана.

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

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

Синтаксис для команды LOCK

Основной синтаксис команды LOCK следующий:

LOCK [ TABLE ]
name
 IN
lock_mode

• name — Имя (возможно, дополненное схемой) существующей таблицы для блокировки. Если перед именем таблицы указано ТОЛЬКО, блокируется только эта таблица. Если не указано ТОЛЬКО, таблица и все ее дочерние таблицы (если есть) заблокированы.

• lock_mode — режим блокировки определяет, с какими блокировками конфликтует эта блокировка. Если режим блокировки не указан, то используется ACCESS EXCLUSIVE, наиболее ограниченный режим. Возможные значения: ДОСТУП РАЗДЕЛИТЬ, СТРОИТЬ ПОДЕЛИТЬСЯ, СТРОИТЬ ЭКСКЛЮЗИВ, ПОДЕЛИТЬСЯ ОБНОВИТЬ ЭКСКЛЮЗИВ, ПОДЕЛИТЬСЯ, ПОДЕЛИТЬСЯ СТРОК ЭКСКЛЮЗИВНО, ЭКСКЛЮЗИВНО, ДОСТУП ЭКСКЛЮЗИВНО.

name — Имя (возможно, дополненное схемой) существующей таблицы для блокировки. Если перед именем таблицы указано ТОЛЬКО, блокируется только эта таблица. Если не указано ТОЛЬКО, таблица и все ее дочерние таблицы (если есть) заблокированы.

lock_mode — режим блокировки определяет, с какими блокировками конфликтует эта блокировка. Если режим блокировки не указан, то используется ACCESS EXCLUSIVE, наиболее ограниченный режим. Возможные значения: ДОСТУП РАЗДЕЛИТЬ, СТРОИТЬ ПОДЕЛИТЬСЯ, СТРОИТЬ ЭКСКЛЮЗИВ, ПОДЕЛИТЬСЯ ОБНОВИТЬ ЭКСКЛЮЗИВ, ПОДЕЛИТЬСЯ, ПОДЕЛИТЬСЯ СТРОК ЭКСКЛЮЗИВНО, ЭКСКЛЮЗИВНО, ДОСТУП ЭКСКЛЮЗИВНО.

После получения блокировка удерживается до конца текущей транзакции. Нет команды UNLOCK TABLE; блокировки всегда снимаются в конце транзакции.

ТУПИКИ

Взаимные блокировки могут возникать, когда две транзакции ожидают завершения друг друга. Хотя PostgreSQL может обнаружить их и завершить их ROLLBACK, взаимоблокировки могут быть неудобными. Чтобы ваши приложения не сталкивались с этой проблемой, проектируйте их так, чтобы они блокировали объекты в том же порядке.

Консультативные Замки

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

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

пример

Рассмотрим таблицу 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)

В следующем примере таблица COMPANY блокируется в базе данных testdb в режиме ACCESS EXCLUSIVE. Оператор LOCK работает только в режиме транзакции —

testdb=#BEGIN;
LOCK TABLE company1 IN ACCESS EXCLUSIVE MODE;

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

LOCK TABLE

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

PostgreSQL — подзапросы

Подзапрос, Внутренний запрос или Вложенный запрос — это запрос в другом запросе PostgreSQL, встроенный в предложение WHERE.

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

Подзапросы могут использоваться с операторами SELECT, INSERT, UPDATE и DELETE вместе с такими операторами, как =, <,>,> =, <=, IN и т. Д.

Есть несколько правил, которым должны следовать подзапросы —

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

• У подзапроса может быть только один столбец в предложении SELECT, если в основном запросе нет нескольких столбцов для подзапроса для сравнения его выбранных столбцов.

• ORDER BY нельзя использовать в подзапросе, хотя основной запрос может использовать ORDER BY. GROUP BY может использоваться для выполнения той же функции, что и ORDER BY в подзапросе.

• Подзапросы, которые возвращают более одной строки, могут использоваться только с несколькими операторами значений, такими как операторы IN, EXISTS, NOT IN, ANY / SOME, ALL.

• Оператор BETWEEN нельзя использовать с подзапросом; тем не менее, МЕЖДУ можно использовать в подзапросе.

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

У подзапроса может быть только один столбец в предложении SELECT, если в основном запросе нет нескольких столбцов для подзапроса для сравнения его выбранных столбцов.

ORDER BY нельзя использовать в подзапросе, хотя основной запрос может использовать ORDER BY. GROUP BY может использоваться для выполнения той же функции, что и ORDER BY в подзапросе.

Подзапросы, которые возвращают более одной строки, могут использоваться только с несколькими операторами значений, такими как операторы IN, EXISTS, NOT IN, ANY / SOME, ALL.

Оператор BETWEEN нельзя использовать с подзапросом; тем не менее, МЕЖДУ можно использовать в подзапросе.

Подзапросы с оператором SELECT

Подзапросы чаще всего используются с оператором SELECT. Основной синтаксис выглядит следующим образом —

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

пример

Рассмотрим таблицу 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 —

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,5 раза в таблице 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 — АВТОМАТИЧЕСКИЙ УЧЕТ

PostgreSQL имеет типы данных smallserial , serial и bigserial ; это не настоящие типы, а просто удобство для создания столбцов с уникальным идентификатором. Они похожи на свойство AUTO_INCREMENT, поддерживаемое некоторыми другими базами данных.

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

Тип имени serial создает целочисленные столбцы. Имя типа bigserial создает столбец bigint . Следует использовать bigserial , если вы предполагаете использовать более 2 ³¹ идентификаторов за время существования таблицы. Имя типа smallserial создает столбец smallint .

Синтаксис

Основное использование данных SERIAL следующее:

CREATE TABLE tablename (
   colname SERIAL
);

пример

Рассмотрим таблицу COMPANY, которая будет создана следующим образом:

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 );

Это вставит семь кортежей в таблицу COMPANY, и 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

PostgreSQL — ПРИВИЛЕГИИ

Всякий раз, когда объект создается в базе данных, ему назначается владелец. Обычно владельцем является тот, кто выполнил оператор создания. Для большинства типов объектов начальное состояние таково, что только владелец (или суперпользователь) может изменить или удалить объект. Чтобы позволить другим ролям или пользователям использовать его, должны быть предоставлены привилегии или разрешение.

Различные виды привилегий в PostgreSQL —

• ВЫБРАТЬ,
• ВСТАВИТЬ,
• ОБНОВИТЬ,
• УДАЛЯТЬ,
• TRUNCATE,
• РЕКОМЕНДАЦИИ,
• СПУСКОВОЙ КРЮЧОК,
• СОЗДАЙТЕ,
• CONNECT,
• ВРЕМЕННОЕ,
• ВЫПОЛНИТЬ, и
• ИСПОЛЬЗОВАНИЕ

В зависимости от типа объекта (таблицы, функции и т. Д.) К объекту применяются привилегии. Чтобы назначить привилегии пользователям, используется команда GRANT.

Синтаксис для GRANT

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

GRANT privilege [, ...]
ON object [, ...]
TO { PUBLIC | GROUP group | username }

• значениями привилегий могут быть: SELECT, INSERT, UPDATE, DELETE, RULE, ALL.

• object — имя объекта, к которому предоставляется доступ. Возможные объекты: таблица, представление, последовательность

• ПУБЛИЧНЫЙ — краткая форма, представляющая всех пользователей.

• ГРУППА ГРУППА — группа, которой нужно предоставить привилегии.

• username — имя пользователя, которому необходимо предоставить привилегии. PUBLIC — это краткая форма, представляющая всех пользователей.

значениями привилегий могут быть: SELECT, INSERT, UPDATE, DELETE, RULE, ALL.

object — имя объекта, к которому предоставляется доступ. Возможные объекты: таблица, представление, последовательность

ПУБЛИЧНЫЙ — краткая форма, представляющая всех пользователей.

ГРУППА ГРУППА — группа, которой нужно предоставить привилегии.

username — имя пользователя, которому необходимо предоставить привилегии. PUBLIC — это краткая форма, представляющая всех пользователей.

Привилегии могут быть отменены с помощью команды REVOKE.

Синтаксис для REVOKE

Основной синтаксис команды REVOKE следующий:

REVOKE privilege [, ...]
ON object [, ...]
FROM { PUBLIC | GROUP groupname | username }

• значениями привилегий могут быть: SELECT, INSERT, UPDATE, DELETE, RULE, ALL.

• object — имя объекта, к которому предоставляется доступ. Возможные объекты: таблица, представление, последовательность

• ПУБЛИЧНЫЙ — краткая форма, представляющая всех пользователей.

• ГРУППА ГРУППА — группа, которой нужно предоставить привилегии.

• username — имя пользователя, которому необходимо предоставить привилегии. PUBLIC — это краткая форма, представляющая всех пользователей.

значениями привилегий могут быть: SELECT, INSERT, UPDATE, DELETE, RULE, ALL.

object — имя объекта, к которому предоставляется доступ. Возможные объекты: таблица, представление, последовательность

ПУБЛИЧНЫЙ — краткая форма, представляющая всех пользователей.

ГРУППА ГРУППА — группа, которой нужно предоставить привилегии.

username — имя пользователя, которому необходимо предоставить привилегии. PUBLIC — это краткая форма, представляющая всех пользователей.

пример

Чтобы понять привилегии, давайте сначала создадим пользователя USER следующим образом:

testdb=# CREATE USER manisha WITH PASSWORD 'password';
CREATE ROLE

Сообщение CREATE ROLE указывает, что пользователь «manisha» создан.

Рассмотрим таблицу 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)

Далее, давайте предоставим все привилегии на таблицу COMPANY пользователю «manisha» следующим образом —

testdb=# GRANT ALL ON COMPANY TO manisha;
GRANT

Сообщение GRANT указывает, что все привилегии назначены пользователю.

Далее, давайте отзовем привилегии у USER «маниша» следующим образом —

testdb=# REVOKE ALL ON COMPANY FROM manisha;
REVOKE

Сообщение REVOKE указывает, что все привилегии отозваны у пользователя.

Вы даже можете удалить пользователя следующим образом —

testdb=# DROP USER manisha;
DROP ROLE

Сообщение DROP ROLE указывает, что пользователь ‘Manisha’ удален из базы данных.

PostgreSQL — ДАТА / ВРЕМЯ Функции и операторы

Мы обсуждали типы данных 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	ОПРАВДАТЬ Отрегулируйте интервал

Вычесть аргументы

Текущая дата и время

Получить подполе (эквивалентное извлечению)

Получить подполе

Тест на конечную дату, время и интервал (не +/- бесконечность)

Отрегулируйте интервал

ВОЗРАСТ (временная метка, timestamp), ВОЗРАСТ (временная метка)

С. Нет.	Описание функции
1	ВОЗРАСТ (временная метка, timestamp) Когда вызывается с формой TIMESTAMP второго аргумента, AGE () вычитает аргументы, производя «символический» результат, который использует годы и месяцы и имеет тип INTERVAL.
2	AGE (метка времени) Когда вызывается только с TIMESTAMP в качестве аргумента, AGE () вычитает из current_date (в полночь).

ВОЗРАСТ (временная метка, timestamp)

Когда вызывается с формой TIMESTAMP второго аргумента, AGE () вычитает аргументы, производя «символический» результат, который использует годы и месяцы и имеет тип INTERVAL.

AGE (метка времени)

Когда вызывается только с TIMESTAMP в качестве аргумента, AGE () вычитает из current_date (в полночь).

Примером функции AGE (timestamp, timestamp) является —

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	ТЕКУЩАЯ ДАТА Поставляет текущую дату.
2	ТЕКУЩЕЕ ВРЕМЯ Поставляет значения с часовым поясом.
3	CURRENT_TIMESTAMP Поставляет значения с часовым поясом.
4	CURRENT_TIME (точность) При желании принимает параметр точности, который приводит к тому, что результат округляется до такого количества дробных цифр в поле секунд.
5	CURRENT_TIMESTAMP (точность) При желании принимает параметр точности, который приводит к тому, что результат округляется до такого количества дробных цифр в поле секунд.
6	МЕСТНОЕ ВРЕМЯ Доставляет значения без часового пояса.
7	LOCALTIMESTAMP Доставляет значения без часового пояса.
8	МестноеВремя (точность) При желании принимает параметр точности, который приводит к тому, что результат округляется до такого количества дробных цифр в поле секунд.
9	LOCALTIMESTAMP (точность) При желании принимает параметр точности, который приводит к тому, что результат округляется до такого количества дробных цифр в поле секунд.

ТЕКУЩАЯ ДАТА

Поставляет текущую дату.

ТЕКУЩЕЕ ВРЕМЯ

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

CURRENT_TIMESTAMP

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

CURRENT_TIME (точность)

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

CURRENT_TIMESTAMP (точность)

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

МЕСТНОЕ ВРЕМЯ

Доставляет значения без часового пояса.

LOCALTIMESTAMP

Доставляет значения без часового пояса.

МестноеВремя (точность)

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

LOCALTIMESTAMP (точность)

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

Примеры использования функций из таблицы выше —

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	сейчас() Это традиционный PostgreSQL, эквивалентныйaction_timestamp ().

transaction_timestamp ()

Он эквивалентен CURRENT_TIMESTAMP, но назван так, чтобы четко отражать, что он возвращает.

statement_timestamp ()

Возвращает время начала текущего оператора.

clock_timestamp ()

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

TimeOfDay ()

Возвращает текущее текущее время, но в виде отформатированной текстовой строки, а не метки времени со значением часового пояса.

сейчас()

Это традиционный PostgreSQL, эквивалентныйaction_timestamp ().

DATE_PART (текст, метка времени), DATE_PART (текст, интервал), DATE_TRUNC (текст, метка времени)

С. Нет.	Описание функции
1	DATE_PART (‘поле’, источник) Эти функции получают подполя. Параметр поля должен быть строковым значением, а не именем. Допустимые имена полей: век, день, декада, доу, дой, эпоха, час, изода, год, микросекунды, тысячелетия, миллисекунды, минуты, месяц, квартал, секунда, часовой пояс, часовой пояс_час, часовой пояс_минута, неделя, год.
2	DATE_TRUNC (‘field’, source) Эта функция концептуально аналогична функции усечения для чисел. source — это выражение значения типа timestamp или interval. поле выбирает, с какой точностью обрезать входное значение. Возвращаемое значение имеет тип отметки времени или интервала . Допустимые значения для поля : микросекунды, миллисекунды, секунды, минуты, часы, день, неделя, месяц, квартал, год, десятилетие, век, тысячелетия

DATE_PART (‘поле’, источник)

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

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

DATE_TRUNC (‘field’, source)

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

Допустимые значения для поля : микросекунды, миллисекунды, секунды, минуты, часы, день, неделя, месяц, квартал, год, десятилетие, век, тысячелетия

Ниже приведены примеры функций DATE_PART ( ‘field’ , source):

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 ( ‘field’ , source):

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 (поле FROM source) извлекает подполя, такие как год или час, из значений даты / времени. Источник должен быть выражением значения типа timestamp, time или interval . Поле является идентификатором или строкой, которая выбирает, какое поле извлечь из исходного значения. Функция EXTRACT возвращает значения типа двойной точности .

Ниже приведены допустимые имена полей (аналогично именам полей функции DATE_PART): век, день, декада, доу, дой, эпоха, час, изода, год, микросекунды, тысячелетия, миллисекунды, минуты, месяц, квартал, секунда, часовой пояс, часовой пояс_час , timezone_minute, неделя, год.

Ниже приведены примеры функций EXTRACT ( ‘field’ , source):

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 (дата) Тесты на конечную дату.
2	ISFINITE (метка времени) Тесты на конечную отметку времени.
3	ISFINITE (интервал) Тесты на конечный интервал.

ISFINITE (дата)

Тесты на конечную дату.

ISFINITE (метка времени)

Тесты на конечную отметку времени.

ISFINITE (интервал)

Тесты на конечный интервал.

Ниже приведены примеры функций 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 (интервал) Настраивает интервал так, чтобы 30-дневные периоды времени были представлены месяцами. Вернуть тип интервала
2	JUSTIFY_HOURS (интервал) Настраивает интервал, чтобы 24-часовой период времени отображался в днях. Вернуть тип интервала
3	JUSTIFY_INTERVAL (интервал) Регулирует интервал с помощью JUSTIFY_DAYS и JUSTIFY_HOURS, с дополнительными настройками знака. Вернуть тип интервала

JUSTIFY_DAYS (интервал)

Настраивает интервал так, чтобы 30-дневные периоды времени были представлены месяцами. Вернуть тип интервала

JUSTIFY_HOURS (интервал)

Настраивает интервал, чтобы 24-часовой период времени отображался в днях. Вернуть тип интервала

JUSTIFY_INTERVAL (интервал)

Регулирует интервал с помощью JUSTIFY_DAYS и JUSTIFY_HOURS, с дополнительными настройками знака. Вернуть тип интервала

Ниже приведены примеры для функций 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 — Функции

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

Функции могут быть созданы на любом языке по вашему выбору, например, 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;

Куда,

• имя-функции указывает имя функции.

• Опция [ИЛИ ЗАМЕНА] позволяет изменить существующую функцию.

• Функция должна содержать инструкцию возврата .

• Предложение RETURN указывает тип данных, который вы собираетесь возвращать из функции. Аргумент return_datatype может быть базовым, составным или доменным типом или может ссылаться на тип столбца таблицы.

• Функция body содержит исполняемую часть.

• Ключевое слово AS используется для создания автономной функции.

• plpgsql — это имя языка, на котором реализована функция. Здесь мы используем эту опцию для PostgreSQL, это может быть SQL, C, внутренний или имя пользовательского процедурного языка. Для обратной совместимости имя может быть заключено в одинарные кавычки.

имя-функции указывает имя функции.

Опция [ИЛИ ЗАМЕНА] позволяет изменить существующую функцию.

Функция должна содержать инструкцию возврата .

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

Функция body содержит исполняемую часть.

Ключевое слово AS используется для создания автономной функции.

plpgsql — это имя языка, на котором реализована функция. Здесь мы используем эту опцию для PostgreSQL, это может быть SQL, C, внутренний или имя пользовательского процедурного языка. Для обратной совместимости имя может быть заключено в одинарные кавычки.

пример

Следующий пример иллюстрирует создание и вызов отдельной функции. Эта функция возвращает общее количество записей в таблице COMPANY. Мы будем использовать таблицу 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 — агрегатная функция COGT в PostgreSQL используется для подсчета количества строк в таблице базы данных.

• Функция PostgreSQL MAX — Функция агрегации PostgreSQL MAX позволяет нам выбрать максимальное (максимальное) значение для определенного столбца.

• Функция PostgreSQL MIN — агрегатная функция PostgreSQL MIN позволяет нам выбрать самое низкое (минимальное) значение для определенного столбца.

• Функция PostgreSQL AVG — Функция агрегирования PostgreSQL AVG выбирает среднее значение для определенного столбца таблицы.

• Функция PostgreSQL SUM — агрегатная функция PostgreSQL SUM позволяет выбрать сумму для числового столбца.

• Функции ARGAY в PostgreSQL — Агрегирующая функция PostgreSQL ARRAY помещает входные значения, включая нули, в массив.

• Числовые функции PostgreSQL — Полный список функций PostgreSQL, необходимых для работы с числами в SQL.

• Строковые функции PostgreSQL — Полный список функций PostgreSQL, необходимых для работы со строками в PostgreSQL.

Функция COUNT в PostgreSQL — агрегатная функция COGT в PostgreSQL используется для подсчета количества строк в таблице базы данных.

Функция PostgreSQL MAX — Функция агрегации PostgreSQL MAX позволяет нам выбрать максимальное (максимальное) значение для определенного столбца.

Функция PostgreSQL MIN — агрегатная функция PostgreSQL MIN позволяет нам выбрать самое низкое (минимальное) значение для определенного столбца.

Функция PostgreSQL AVG — Функция агрегирования PostgreSQL AVG выбирает среднее значение для определенного столбца таблицы.

Функция PostgreSQL SUM — агрегатная функция PostgreSQL SUM позволяет выбрать сумму для числового столбца.

Функции ARGAY в PostgreSQL — Агрегирующая функция PostgreSQL ARRAY помещает входные значения, включая нули, в массив.

Числовые функции PostgreSQL — Полный список функций PostgreSQL, необходимых для работы с числами в SQL.

Строковые функции PostgreSQL — Полный список функций PostgreSQL, необходимых для работы со строками в PostgreSQL.

PostgreSQL — интерфейс C / C ++

В этом руководстве будет использоваться библиотека 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 :: соединение C (const std :: string & dbstring) Это typedef, который будет использоваться для подключения к базе данных. Здесь dbstring предоставляет обязательные параметры для подключения к базе данных, например, dbname = testdb user = postgres password = pass123 hostaddr = 127.0.0.1 port = 5432 . Если соединение установлено успешно, то создается C с объектом соединения, который предоставляет различные полезные функции public function.
2	C.is_open () Метод is_open () является открытым методом объекта соединения и возвращает логическое значение. Если соединение активно, то этот метод возвращает true, в противном случае он возвращает false.
3	C.disconnect () Этот метод используется для отключения открытого соединения с базой данных.
4	pqxx :: работа W (C) Это typedef, который будет использоваться для создания транзакционного объекта с использованием соединения C, который в конечном итоге будет использоваться для выполнения операторов SQL в транзакционном режиме. Если объект транзакции успешно создан, он присваивается переменной W, которая будет использоваться для доступа к открытым методам, связанным с объектом транзакции.
5	W.exec (const std :: string & sql) Этот открытый метод из транзакционного объекта будет использоваться для выполнения оператора SQL.
6	W.commit () Этот открытый метод из транзакционного объекта будет использоваться для фиксации транзакции.
7	W.abort () Этот открытый метод из транзакционного объекта будет использоваться для отката транзакции.
8	pqxx :: нетранзакция N (C) Это typedef, который будет использоваться для создания нетранзакционного объекта с использованием соединения C, который в конечном итоге будет использоваться для выполнения операторов SQL в нетранзакционном режиме. Если объект транзакции успешно создан, он присваивается переменной N, которая будет использоваться для доступа к открытым методам, связанным с нетранзакционным объектом.
9	N.exec (const std :: string & sql) Этот открытый метод из нетранзакционного объекта будет использоваться для выполнения оператора SQL и возвращает объект результата, который на самом деле является интегратором, содержащим все возвращенные записи.

pqxx :: соединение C (const std :: string & dbstring)

Это typedef, который будет использоваться для подключения к базе данных. Здесь dbstring предоставляет обязательные параметры для подключения к базе данных, например, dbname = testdb user = postgres password = pass123 hostaddr = 127.0.0.1 port = 5432 .

Если соединение установлено успешно, то создается C с объектом соединения, который предоставляет различные полезные функции public function.

C.is_open ()

Метод is_open () является открытым методом объекта соединения и возвращает логическое значение. Если соединение активно, то этот метод возвращает true, в противном случае он возвращает false.

C.disconnect ()

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

pqxx :: работа W (C)

Это typedef, который будет использоваться для создания транзакционного объекта с использованием соединения C, который в конечном итоге будет использоваться для выполнения операторов SQL в транзакционном режиме.

Если объект транзакции успешно создан, он присваивается переменной W, которая будет использоваться для доступа к открытым методам, связанным с объектом транзакции.

W.exec (const std :: string & sql)

Этот открытый метод из транзакционного объекта будет использоваться для выполнения оператора SQL.

W.commit ()

Этот открытый метод из транзакционного объекта будет использоваться для фиксации транзакции.

W.abort ()

Этот открытый метод из транзакционного объекта будет использоваться для отката транзакции.

pqxx :: нетранзакция N (C)

Это typedef, который будет использоваться для создания нетранзакционного объекта с использованием соединения C, который в конечном итоге будет использоваться для выполнения операторов SQL в нетранзакционном режиме.

Если объект транзакции успешно создан, он присваивается переменной N, которая будет использоваться для доступа к открытым методам, связанным с нетранзакционным объектом.

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 в наших программах на Java, мы должны убедиться, что на компьютере установлены PostgreSQL JDBC и Java. Вы можете проверить учебник Java для установки Java на вашем компьютере. Теперь давайте проверим, как настроить драйвер JDBC PostgreSQL.

• Загрузите последнюю версию postgresql- (VERSION) .jdbc.jar из репозитория postgresql-jdbc .

• Добавьте скачанный файл jar postgresql- (VERSION) .jdbc.jar в путь к классу или используйте его вместе с параметром -classpath, как описано ниже в примерах.

Загрузите последнюю версию 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 в качестве пароля для доступа к базе данных. Вы можете изменить это согласно конфигурации и настройке базы данных. Мы также предполагаем, что текущая версия драйвера JDBC postgresql-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

Монтаж

Расширение 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	ресурс pg_connect (строка $ connection_string [, int $ connect_type]) Это открывает соединение с базой данных PostgreSQL, указанной в параметре connection_string. Если PGSQL_CONNECT_FORCE_NEW передается как connect_type, то создается новое соединение в случае второго вызова pg_connect (), даже если строка_соединения идентична существующему соединению.
2	bool pg_connection_reset (ресурс $ соединение) Эта процедура сбрасывает соединение. Это полезно для восстановления после ошибок. Возвращает TRUE в случае успеха или FALSE в случае неудачи.
3	int pg_connection_status (ресурс $ connection) Эта процедура возвращает состояние указанного соединения. Возвращает PGSQL_CONNECTION_OK или PGSQL_CONNECTION_BAD.
4	строка pg_dbname ([ресурс $ соединение]) Эта подпрограмма возвращает имя базы данных, к которой относится данный ресурс соединения PostgreSQL.
5	ресурс pg_prepare ([ресурс $ соединение], строка $ stmtname, строка $ запрос) Это отправляет запрос на создание подготовленного оператора с заданными параметрами и ожидает завершения.
6	ресурс pg_execute ([ресурс $ connection], строка $ stmtname, массив $ params) Эта подпрограмма отправляет запрос на выполнение подготовленного оператора с заданными параметрами и ожидает результата.
7	ресурс pg_query ([ресурс $ соединение], строка $ запрос) Эта подпрограмма выполняет запрос для указанного соединения с базой данных.
8	массив pg_fetch_row (ресурс $ result [, int $ row]) Эта процедура извлекает одну строку данных из результата, связанного с указанным ресурсом результата.
9	массив pg_fetch_all (ресурс $ результат) Эта подпрограмма возвращает массив, содержащий все строки (записи) в результирующем ресурсе.
10	int pg_affered_rows (ресурс $ результат) Эта подпрограмма возвращает количество строк, затронутых запросами INSERT, UPDATE и DELETE.
11	int pg_num_rows (ресурс $ результат) Эта подпрограмма возвращает количество строк в результирующем ресурсе PostgreSQL, например, количество строк, возвращаемых оператором SELECT.
12	bool pg_close ([ресурс $ соединение]) Эта процедура закрывает непостоянное соединение с базой данных PostgreSQL, связанной с данным ресурсом соединения.
13	строка pg_last_error ([ресурс $ соединение]) Эта процедура возвращает последнее сообщение об ошибке для данного соединения.
14	строка pg_escape_literal ([ресурс $ соединение], строка $ данные) Эта подпрограмма экранирует литерал для вставки в текстовое поле.
15	строка pg_escape_string ([ресурс $ соединение], строка $ данные) Эта подпрограмма экранирует строку для запроса к базе данных.

ресурс pg_connect (строка $ connection_string [, int $ connect_type])

Это открывает соединение с базой данных PostgreSQL, указанной в параметре connection_string.

Если PGSQL_CONNECT_FORCE_NEW передается как connect_type, то создается новое соединение в случае второго вызова pg_connect (), даже если строка_соединения идентична существующему соединению.

bool pg_connection_reset (ресурс $ соединение)

Эта процедура сбрасывает соединение. Это полезно для восстановления после ошибок. Возвращает TRUE в случае успеха или FALSE в случае неудачи.

int pg_connection_status (ресурс $ connection)

Эта процедура возвращает состояние указанного соединения. Возвращает PGSQL_CONNECTION_OK или PGSQL_CONNECTION_BAD.

строка pg_dbname ([ресурс $ соединение])

Эта подпрограмма возвращает имя базы данных, к которой относится данный ресурс соединения PostgreSQL.

ресурс pg_prepare ([ресурс $ соединение], строка $ stmtname, строка $ запрос)

Это отправляет запрос на создание подготовленного оператора с заданными параметрами и ожидает завершения.

ресурс pg_execute ([ресурс $ connection], строка $ stmtname, массив $ params)

Эта подпрограмма отправляет запрос на выполнение подготовленного оператора с заданными параметрами и ожидает результата.

ресурс pg_query ([ресурс $ соединение], строка $ запрос)

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

массив pg_fetch_row (ресурс $ result [, int $ row])

Эта процедура извлекает одну строку данных из результата, связанного с указанным ресурсом результата.

массив pg_fetch_all (ресурс $ результат)

Эта подпрограмма возвращает массив, содержащий все строки (записи) в результирующем ресурсе.

int pg_affered_rows (ресурс $ результат)

Эта подпрограмма возвращает количество строк, затронутых запросами INSERT, UPDATE и DELETE.

int pg_num_rows (ресурс $ результат)

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

bool pg_close ([ресурс $ соединение])

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

строка pg_last_error ([ресурс $ соединение])

Эта процедура возвращает последнее сообщение об ошибке для данного соединения.

строка pg_escape_literal ([ресурс $ соединение], строка $ данные)

Эта подпрограмма экранирует литерал для вставки в текстовое поле.

строка pg_escape_string ([ресурс $ соединение], строка $ данные)

Эта подпрограмма экранирует строку для запроса к базе данных.

Подключение к базе данных

Следующий код 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 в вашей тестовой базе данных и отобразит следующие сообщения:

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);
?>

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

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

Монтаж

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	$ ДВГ → сделать ($ SQL) Эта подпрограмма подготавливает и выполняет один оператор SQL. Возвращает количество затронутых строк или undef при ошибке. Возвращаемое значение -1 означает, что количество строк неизвестно, неприменимо или недоступно. Здесь $ dbh — дескриптор, возвращаемый вызовом DBI → connect ().
3	$ ДВГ → подготовить ($ SQL) Эта подпрограмма подготавливает оператор для последующего выполнения ядром базы данных и возвращает ссылку на объект дескриптора оператора.
4	$ STH → Execute () Эта процедура выполняет любую обработку, необходимую для выполнения подготовленного оператора. Undef возвращается, если произошла ошибка. Успешное выполнение всегда возвращает true независимо от количества затронутых строк. Здесь $ sth — дескриптор оператора, возвращаемый вызовом $ dbh → prepare ($ sql).
5	$ STH → fetchrow_array () Эта процедура извлекает следующую строку данных и возвращает ее в виде списка, содержащего значения полей. Пустые поля возвращаются как неопределенные значения в списке.
6	$ DBI :: эээ Это эквивалентно $ h → err, где $ h — любой из типов дескрипторов, таких как $ dbh, $ sth или $ drh. Это возвращает собственный код ошибки ядра базы данных из последнего вызванного метода драйвера.
7	$ DBI :: ErrStr Это эквивалентно $ h → errstr, где $ h — любой из типов дескрипторов, таких как $ dbh, $ sth или $ drh. Это возвращает собственное сообщение об ошибке ядра СУБД из последнего вызванного метода DBI.
8	$ dbh-> разъединение () Эта процедура закрывает соединение с базой данных, ранее открытое с помощью вызова DBI → connect ().

DBI → connect ($ data_source, «userid», «password», \% attr)

Устанавливает соединение базы данных или сеанс с запрошенным $ data_source. Возвращает объект дескриптора базы данных, если соединение установлено успешно.

Источник данных имеет вид: DBI: Pg: dbname = $ database; host = 127.0.0.1; port = 5432 Pg — это имя драйвера PostgreSQL, а testdb — имя базы данных.

$ ДВГ → сделать ($ SQL)

Эта подпрограмма подготавливает и выполняет один оператор SQL. Возвращает количество затронутых строк или undef при ошибке. Возвращаемое значение -1 означает, что количество строк неизвестно, неприменимо или недоступно. Здесь $ dbh — дескриптор, возвращаемый вызовом DBI → connect ().

$ ДВГ → подготовить ($ SQL)

Эта подпрограмма подготавливает оператор для последующего выполнения ядром базы данных и возвращает ссылку на объект дескриптора оператора.

$ STH → Execute ()

Эта процедура выполняет любую обработку, необходимую для выполнения подготовленного оператора. Undef возвращается, если произошла ошибка. Успешное выполнение всегда возвращает true независимо от количества затронутых строк. Здесь $ sth — дескриптор оператора, возвращаемый вызовом $ dbh → prepare ($ sql).

$ STH → fetchrow_array ()

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

$ DBI :: эээ

Это эквивалентно $ h → err, где $ h — любой из типов дескрипторов, таких как $ dbh, $ sth или $ drh. Это возвращает собственный код ошибки ядра базы данных из последнего вызванного метода драйвера.

$ DBI :: ErrStr

Это эквивалентно $ h → errstr, где $ h — любой из типов дескрипторов, таких как $ dbh, $ sth или $ drh. Это возвращает собственное сообщение об ошибке ядра СУБД из последнего вызванного метода DBI.

$ dbh-> разъединение ()

Эта процедура закрывает соединение с базой данных, ранее открытое с помощью вызова 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();

Когда приведенная выше программа будет выполнена, она создаст таблицу COMPANY в вашей тестовой базе данных и отобразит следующие сообщения:

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();

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

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

Монтаж

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 () Эта подпрограмма создает курсор, который будет использоваться при программировании вашей базы данных на Python.
3	cursor.execute (sql [, необязательные параметры]) Эта подпрограмма выполняет инструкцию SQL. Оператор SQL может быть параметризован (т. Е. Заполнители вместо литералов SQL). Модуль psycopg2 поддерживает заполнитель с использованием знака% s Например: cursor.execute («вставить в людей значения (% s,% s)», (кто, возраст))
4	cursor.executemany (sql, seq_of_parameters) Эта подпрограмма выполняет команду SQL для всех последовательностей параметров или отображений, найденных в последовательности sql.
5	cursor.callproc (procname [, параметры]) Эта процедура выполняет хранимую процедуру базы данных с заданным именем. Последовательность параметров должна содержать одну запись для каждого аргумента, ожидаемого процедурой.
6	cursor.rowcount Этот атрибут только для чтения, который возвращает общее количество строк базы данных, которые были изменены, вставлены или удалены последним последним выполнением * ().
7	connection.commit () Этот метод фиксирует текущую транзакцию. Если вы не вызываете этот метод, все, что вы сделали после последнего вызова commit (), не будет видно из других соединений с базой данных.
8	connection.rollback () Этот метод откатывает любые изменения в базе данных с момента последнего вызова commit ().
9	connection.close () Этот метод закрывает соединение с базой данных. Обратите внимание, что это не вызывает автоматически commit (). Если вы просто закроете соединение с базой данных без предварительного вызова commit (), ваши изменения будут потеряны!
10	cursor.fetchone () Этот метод извлекает следующую строку из набора результатов запроса, возвращая одну последовательность или None, если больше нет данных.
11	cursor.fetchmany ([размер = cursor.arraysize]) Эта процедура извлекает следующий набор строк результата запроса, возвращая список. Пустой список возвращается, когда больше нет доступных строк. Метод пытается извлечь столько строк, сколько указано параметром size.
12	cursor.fetchall () Эта процедура извлекает все (оставшиеся) строки результата запроса, возвращая список. Пустой список возвращается, когда нет доступных строк.

psycopg2.connect (database = «testdb», user = «postgres», password = «cohondob», host = «127.0.0.1», port = «5432»)

Этот API-интерфейс открывает соединение с базой данных PostgreSQL. Если база данных открыта успешно, она возвращает объект подключения.

connection.cursor ()

Эта подпрограмма создает курсор, который будет использоваться при программировании вашей базы данных на Python.

cursor.execute (sql [, необязательные параметры])

Эта подпрограмма выполняет инструкцию SQL. Оператор SQL может быть параметризован (т. Е. Заполнители вместо литералов SQL). Модуль psycopg2 поддерживает заполнитель с использованием знака% s

Например: cursor.execute («вставить в людей значения (% s,% s)», (кто, возраст))

cursor.executemany (sql, seq_of_parameters)

Эта подпрограмма выполняет команду SQL для всех последовательностей параметров или отображений, найденных в последовательности sql.

cursor.callproc (procname [, параметры])

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

cursor.rowcount

Этот атрибут только для чтения, который возвращает общее количество строк базы данных, которые были изменены, вставлены или удалены последним последним выполнением * ().

connection.commit ()

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

connection.rollback ()

Этот метод откатывает любые изменения в базе данных с момента последнего вызова commit ().

connection.close ()

Этот метод закрывает соединение с базой данных. Обратите внимание, что это не вызывает автоматически commit (). Если вы просто закроете соединение с базой данных без предварительного вызова commit (), ваши изменения будут потеряны!

cursor.fetchone ()

Этот метод извлекает следующую строку из набора результатов запроса, возвращая одну последовательность или None, если больше нет данных.

cursor.fetchmany ([размер = cursor.arraysize])

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

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()

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

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()

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