Как вывести список баз данных и таблиц postgresql с помощью psql

Посмотреть и удалить активные запросы

Если запрос запущен из интерфейса pgsql, то завершение работы сервера не поможет — запрос все равно продолжит свое выполнение, необходимо вызывать функцию pg_cancel_backend.

select * from pg_stat_activity; # посмотреть все запросы
select * from pg_stat_activity WHERE current_query like 'SELECT%'; # посмотреть все SELECT запросы
select * from pg_stat_activity WHERE current_query like 'INSERT%';

# снять все активные select запросы
SELECT pg_cancel_backend(procpid) as x FROM pg_stat_activity WHERE current_query like 'SELECT%';

# снять запрос VACUUM
SELECT pg_cancel_backend(procpid) as x FROM  pg_stat_activity WHERE current_query like 'VACUUM%';

SELECT запросы можно снимать из ОС командой kill

# ps auxww | grep ^postgres
...
postgres 15724 97.7 11.3 2332996 1871476 ?     Rs   07:50   1:53 postgres: postgres mybd 127.0.0.1(53624) SELECT
...
# kill 15724

procpid содержит PID процесса, которому можно сделать kill при необходимости. Например PID можно узнать запросом(отсортируем по длительности выполнения)

select datname,procpid,now()-query_start as duration,current_query from pg_stat_activity order by duration DESC;

Транзакции в PostgreSQL

В PostgreSQL Транзакция — это список команд SQL, которые находятся внутри блока, начинающегося командой BEGIN и заканчивающегося командой COMMIT.

PostgreSQL фактически считает каждый оператор SQL запущенным в транзакции. Если вы не указываете команду BEGIN, то каждый отдельный оператор имеет неявную команду BEGIN перед оператором и (при успешной отработке оператора) команду COMMIT после оператора. Группа операторов заключаемая в блок между BEGIN и COMMIT иногда называется транзакционным блоком.

Пример запуска транзакции из файла delprices.sql, которая удаляет в БД test777 из таблиц prices и ratesheets строки с id=2

# nano delprices.sql
BEGIN;
DELETE FROM prices WHERE ratesheet_id=2;
DELETE FROM ratesheets WHERE id=2;
COMMIT;

Выполним транзакцию для test777:

# sudo -u postgres psql -l
# sudo -u postgres psql test777 < delprices.sql

Настройка psql

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

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

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

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

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

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

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

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

Хотел бы рассказать ещё об одной фиче psql. Если вам, как и мне, нравятся SQL выражения в ALL-CAPS, то это можно настроить в автодополнении командой . Теперь, когда будете набирать SQL-запрос, последовательность будет автоматически преобразовываться в .

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

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

Уровни блокировок таблиц

Команда LOCK TABLE предназначена для блокировки таблиц на время транзакции. Блокировкой называется временное ограничение доступа к таблице (в зависимости от выбранного режима). Сеанс, заблокировавший таблицу, пользуется нормальным доступом; последствия блокировки распространяются только на других пользователей, пытающихся получить доступ к заблокированной таблице.

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

Некоторые команды SQL автоматически устанавливают блокировку для выполнения своих функций; в таких случаях PostgreSQL всегда выбирает минимально необходимый уровень блокировки. После завершения транзакции блокировка немедленно снимается.

Команда LOCK TABLE без параметра устанавливает максимально жесткий режим блокировки (ACCESS EXCLUSIVE). Чтобы ограничения были менее жесткими, следует явно задать нужный режим.

Блокировка таблиц возможна только в транзакциях. Выполнение команды LOCK TABLE вне транзакционного блока не приводит к ошибке, но установленная блокировка немедленно снимается. Транзакция создается командой BEGIN; команда COMMIT фиксирует изменения в базе данных и снимает блокировку.

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

• ACCESS SHARE MODE. Устанавливается автоматически командой SELECT для таблиц, из которых производится выборка данных. В заблокированных таблицах запрещается выполнение команд ALTER TABLE, DROP TABLE и VACUUM. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровня ACCESS EXCLUSIVE MODE.
• ROW SHARE MODE. Устанавливается автоматически командами SELECT, содержащими секцию FOR UPDATE или FOR SHARE. В заблокированных таблицах запрещается выполнение команд ALTER TABLE, DROP TABLE и VACUUM. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровней EXCLUSIVE MODE и ACCESS EXCLUSIVE MODE.
• ROW EXCLUSIVE MODE. Устанавливается автоматически командами UPDATE, INSERT и DELETE. В заблокированных таблицах запрещается выполнение команд ALTER TABLE, DROP TABLE и CREATE INDEX. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровней SHARE MODE, SHARE ROW EXCLUSIVE MODE, EXCLUSIVE MODE и ACCESS EXCLUSIVE MODE.
• SHARE UPDATE EXCLUSIVE MODE . Устанавливается автоматически командами VACUUM (без FULL), ANALYZE и CREATE INDEX CONCURRENTLY.
• SHARE MODE. Устанавливается автоматически командами CREATE INDEX (без CONCURRENTLY). В заблокированных таблицах запрещается выполнение команд INSERT, UPDATE, DELETE, ALTER TABLE, DROP TABLE и VACUUM. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровней ROW EXCLUSIVE MODE, SHARE ROW EXCLUSIVE MODE, EXCLUSIVE MODE и ACCESS EXCLUSIVE MODE.
• SHARE ROW EXCLUSIVE MOOE. Специальный режим блокировки, практически идентичный режиму EXCLUSIVE MODE, но допускающий установку параллельных блокировок уровня ROW SHARE MODE.
• EXCLUSIVE MODE. Запрещает выполнение команд INSERT, UPDATE, DELETE, CREATE INDEX, ALTER TABLE, DROP TABLE и VACUUM, а также команд SELECT с секцией FOR UPDATE. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровней ROW SHARE MODE, ROW EXCLUSIVE MODE, SHARE MODE, SHARE ROW EXCLUSIVE MODE и ACCESS EXCLUSIVE MODE.
• ACCESS EXCLUSIVE MODE. Устанавливается автоматически командами ALTER TABLE, DROP TABLE и VACUUM. В этом режиме для заблокированных таблиц запрещаются любые команды или параллельные блокировки любого уровня.

Description

psql is a character-based
front-end to Postgres. It
enables you to type in queries interactively, issue them to
Postgres, and see the query
results.

psql is a Postgres client application. Hence, a
postmaster process must be
running on the database server host before psql is executed. In addition, the correct
parameters to identify the database server, such as the
postmaster host name, may need
to be specified as described below.

When psql starts, it reads
SQL commands from /etc/psqlrc and then
from $(HOME)/.psqlrc This allows SQL commands like
SET which can be used to set the date
style to be run at the start of every session.

Connecting To A Database

psql attempts to make a
connection to the database at the hostname and port number
specified on the command line. If the connection could not be
made for any reason (e.g. insufficient privileges, postmaster
is not running on the server, etc) .IR psql will return an error that says

     Connection to database failed.
    

The reason for the connection failure is not provided.

Entering Queries

In normal operation, psql
provides a prompt with the name of the database that
psql is current connected to
followed by the string «=>». For example,

$ psql testdb
Welcome to the POSTGRESQL interactive sql monitor:
  Please read the file COPYRIGHT for copyright terms of POSTGRESQL


   type \? for help on slash commands
   type \q to quit
   type \g or terminate with semicolon to execute query
 You are currently connected to the database: testdb
          
testdb=>
    

At the prompt, the user may type in SQL queries. Unless the -S option is set,
input lines are sent to the backend when a query-terminating
semicolon is reached.

Whenever a query is executed, psql also polls for asynchronous
notification events generated by LISTEN
and NOTIFY.

psql can be used in a pipe
sequence, and automatically detects when it is not listening or
talking to a real tty.

Description

CREATE TABLE will create a new,
initially empty table in the current database. The table will be
owned by the user issuing the command.

If a schema name is given (for example, CREATE TABLE myschema.mytable ...) then the table
is created in the specified schema. Otherwise it is created in
the current schema. Temporary tables exist in a special schema,
so a schema name cannot be given when creating a temporary table.
The name of the table must be distinct from the name of any other
table, sequence, index, view, or foreign table in the same
schema.

CREATE TABLE also automatically
creates a data type that represents the composite type
corresponding to one row of the table. Therefore, tables cannot
have the same name as any existing data type in the same
schema.

The optional constraint clauses specify constraints (tests)
that new or updated rows must satisfy for an insert or update
operation to succeed. A constraint is an SQL object that helps
define the set of valid values in the table in various ways.

There are two ways to define constraints: table constraints
and column constraints. A column constraint is defined as part of
a column definition. A table constraint definition is not tied to
a particular column, and it can encompass more than one column.
Every column constraint can also be written as a table
constraint; a column constraint is only a notational convenience
for use when the constraint only affects one column.

Утилиты (программы) PosgreSQL:

• createdb и dropdb – создание и удаление базы данных (соответственно)
• createuser и dropuser – создание и пользователя (соответственно)
• pg_ctl – программа предназначенная для решения общих задач управления (запуск, останов, настройка параметров и т.д.)
• postmaster – многопользовательский серверный модуль PostgreSQL (настройка уровней отладки, портов, каталогов данных)
• initdb – создание новых кластеров PostgreSQL
• initlocation – программа для создания каталогов для вторичного хранения баз данных
• vacuumdb – физическое и аналитическое сопровождение БД
• pg_dump – архивация и восстановление данных
• pg_dumpall – резервное копирование всего кластера PostgreSQL
• pg_restore – восстановление БД из архивов (.tar, .tar.gz)

Примеры создания резервных копий:

Создание бекапа базы mydb, в сжатом виде

pg_dump -h localhost -p 5440 -U someuser -F c -b -v -f mydb.backup mydb

Создание бекапа базы mydb, в виде обычного текстового файла, включая команду для создания БД

pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb

Создание бекапа базы mydb, в сжатом виде, с таблицами которые содержат в имени payments

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *payments* -f payment_tables.backup mydb

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

pg_dump -a -t table_name -f file_name database_name

Создание резервной копии с сжатием в gz

pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c > mydb.gz

Список наиболее часто используемых опций:

• -h host — хост, если не указан то используется localhost или значение из переменной окружения PGHOST.
• -p port — порт, если не указан то используется 5432 или значение из переменной окружения PGPORT.
• -u — пользователь, если не указан то используется текущий пользователь, также значение можно указать в переменной окружения PGUSER.
• -a, —data-only — дамп только данных, по-умолчанию сохраняются данные и схема.
• -b — включать в дамп большие объекты (blog’и).
• -s, —schema-only — дамп только схемы.
• -C, —create — добавляет команду для создания БД.
• -c — добавляет команды для удаления (drop) объектов (таблиц, видов и т.д.).
• -O — не добавлять команды для установки владельца объекта (таблиц, видов и т.д.).
• -F, —format {c|t|p} — выходной формат дампа, custom, tar, или plain text.
• -t, —table=TABLE — указываем определенную таблицу для дампа.
• -v, —verbose — вывод подробной информации.
• -D, —attribute-inserts — дамп используя команду INSERT с списком имен свойств.

Бекап всех баз данных используя команду pg_dumpall.

pg_dumpall > all.sql

Восстановление таблиц из резервных копий (бэкапов):

psql — восстановление бекапов, которые хранятся в обычном текстовом файле (plain text);
pg_restore — восстановление сжатых бекапов (tar);

Восстановление всего бекапа с игнорированием ошибок

psql -h localhost -U someuser -d dbname -f mydb.sql

Восстановление всего бекапа с остановкой на первой ошибке

psql -h localhost -U someuser —set ON_ERROR_STOP=on -f mydb.sql

Для восстановления из tar-арихива нам понадобиться сначала создать базу с помощью CREATE DATABASE mydb; (если при создании бекапа не была указана опция -C) и восстановить

pg_restore —dbname=mydb —jobs=4 —verbose mydb.backup

Восстановление резервной копии БД, сжатой gz

gunzip mydb.gz
psql -U postgres -d mydb -f mydb

Open Source-лицензированная база данных

В начале XXI века многие компьютерные системы создаются на основе свободно распространяемых программ с открытыми исходными кодами. К их числу относится и PostgreSQL. Что же это означает в действительности?

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

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

Если с программным обеспечением Open Source возникают проблемы, пользователь может или исправить ошибки сам (поскольку у него есть исходные тексты), или же передать код кому-то другому для исправления. Сейчас многие коммерческие компании предлагают поддержку продуктов Open Source, поэтому, приобретая такой продукт, не стоит чувствовать себя «забытым».

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

Наиболее либеральной является лицензия Berkeley Software Distribution (BSD), разрешающая делать с программным обеспечением все что угодно, не предоставляя при этом никаких гарантий. Лицензия на использование PostgreSQL по сути своей аналогична лицензии BSD, она представляет собой заявление об авторских правах, в котором говорится: «Настоящим предоставляется право на использование, копирование, модификацию и распространение данного программного продукта и относящейся к нему документации в любых целях, без оплаты и без подписания соответствующих соглашений при условии, что во всех копиях будет присутствовать уведомление об авторских правах, указанное выше, данный абзац и два последующих». Два следующих абзаца посвящены отказу от каких бы то ни было обязательств и гарантий.

Ресурсы по обучению PostgreSQL

1. Информация о базах данных в целом и о PostgreSQL в частности может быть получена из множества источников, как печатных, так и доступных через Интернет.
2. Тем, кого интересует тема свободного распространения и открытости исходных кодов в отношении программного обеспечения (Open Source продукты), советуем посетить два следующих сайта:

Вас заинтересует / Intresting for you:

Redis или Memcached, что лучше… 6312 просмотров Андрей Васенин Tue, 21 Nov 2017, 13:17:28

Реляционная модель и проектиро… 1887 просмотров Ирина Светлова Tue, 21 Nov 2017, 13:27:29

Проектирование таблиц для базы… 5819 просмотров Дэйзи ак-Макарова Sat, 24 Mar 2018, 16:23:55

Джеймс Форгн (James Forgy) — с… 4105 просмотров Antoni Sun, 05 Aug 2018, 16:21:01

Author: Ирина Светлова

Другие статьи автора:

Клонирование базы данных

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

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

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

Для восстановления удалим существующую и склонируем обратно.

Compatibility

The CREATE TABLE command conforms to
the SQL standard, with
exceptions listed below.

Temporary Tables

Although the syntax of CREATE TEMPORARY
TABLE resembles that of the SQL standard, the effect is
not the same. In the standard, temporary tables are defined
just once and automatically exist (starting with empty
contents) in every session that needs them. PostgreSQL instead requires each session
to issue its own CREATE TEMPORARY
TABLE command for each temporary table to be used. This
allows different sessions to use the same temporary table name
for different purposes, whereas the standard’s approach
constrains all instances of a given temporary table name to
have the same table structure.

The standard’s definition of the behavior of temporary
tables is widely ignored. PostgreSQL’s behavior on this point is
similar to that of several other SQL databases.

The standard’s distinction between global and local
temporary tables is not in PostgreSQL, since that distinction depends
on the concept of modules, which PostgreSQL does not have. For
compatibility’s sake, PostgreSQL will accept the GLOBAL and LOCAL
keywords in a temporary table declaration, but they have no
effect.

The ON COMMIT clause for temporary
tables also resembles the SQL standard, but has some
differences. If the ON COMMIT clause
is omitted, SQL specifies that the default behavior is
ON COMMIT DELETE ROWS. However, the
default behavior in PostgreSQL
is ON COMMIT PRESERVE ROWS. The
ON COMMIT DROP option does not exist
in SQL.

Non-deferred Uniqueness Constraints

When a UNIQUE or PRIMARY KEY constraint is not deferrable,
PostgreSQL checks for
uniqueness immediately whenever a row is inserted or modified.
The SQL standard says that uniqueness should be enforced only
at the end of the statement; this makes a difference when, for
example, a single command updates multiple key values. To
obtain standard-compliant behavior, declare the constraint as
DEFERRABLE but not deferred (i.e.,
INITIALLY IMMEDIATE). Be aware that
this can be significantly slower than immediate uniqueness
checking.

Column Check Constraints

The SQL standard says that CHECK
column constraints can only refer to the column they apply to;
only CHECK table constraints can refer
to multiple columns. PostgreSQL does not enforce this
restriction; it treats column and table check constraints
alike.

NULL «Constraint»

The NULL «constraint» (actually a non-constraint) is a
PostgreSQL extension to the
SQL standard that is included for compatibility with some other
database systems (and for symmetry with the NOT NULL constraint). Since it is the default
for any column, its presence is simply noise.

Inheritance

Multiple inheritance via the INHERITS clause is a PostgreSQL language extension. SQL:1999
and later define single inheritance using a different syntax
and different semantics. SQL:1999-style inheritance is not yet
supported by PostgreSQL.

Zero-column Tables

PostgreSQL allows a table
of no columns to be created (for example, CREATE TABLE foo();). This is an extension from
the SQL standard, which does not allow zero-column tables.
Zero-column tables are not in themselves very useful, but
disallowing them creates odd special cases for ALTER TABLE DROP COLUMN, so it seems cleaner to
ignore this spec restriction.

LIKE Clause

While a LIKE clause exists in the
SQL standard, many of the options that PostgreSQL accepts for it are not in the
standard, and some of the standard’s options are not
implemented by PostgreSQL.

Tablespaces

The PostgreSQL concept of
tablespaces is not part of the standard. Hence, the clauses
TABLESPACE and USING INDEX TABLESPACE are extensions.

Основные команды PostgreSQL в интерактивном режиме:

• \connect db_name – подключиться к базе с именем db_name
• \du – список пользователей
• \dp (или \z) – список таблиц, представлений, последовательностей, прав доступа к ним
• \di – индексы
• \ds – последовательности
• \dt – список таблиц
• \dt+ — список всех таблиц с описанием
• \dt *s* — список всех таблиц, содержащих s в имени
• \dv – представления
• \dS – системные таблицы
• \d+ – описание таблицы
• \o – пересылка результатов запроса в файл
• \l – список баз данных
• \i – читать входящие данные из файла
• \e – открывает текущее содержимое буфера запроса в редакторе (если иное не указано в окружении переменной EDITOR, то будет использоваться по умолчанию vi)
• \d “table_name” – описание таблицы
• \i запуск команды из внешнего файла, например \i /my/directory/my.sql
• \pset – команда настройки параметров форматирования
• \echo – выводит сообщение
• \set – устанавливает значение переменной среды. Без параметров выводит список текущих переменных (\unset – удаляет).
• \? – справочник psql
• \help – справочник SQL
• \q (или Ctrl+D) – выход с программы

Добавление данных. Команда Insert

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

Для добавления данных применяется команда INSERT, которая имеет следующий формальный синтаксис:

INSERT INTO имя_таблицы (столбец1, столбец2, ... столбецN) 
VALUES (значение1, значение2, ... значениеN)

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

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

CREATE TABLE Products
(
	Id SERIAL PRIMARY KEY,
	ProductName VARCHAR(30) NOT NULL,
	Manufacturer VARCHAR(20) NOT NULL,
	ProductCount INTEGER DEFAULT 0,
	Price NUMERIC
);

Добавим в нее одну строку с помощью команды INSERT:

INSERT INTO Products VALUES (1, 'Galaxy S9', 'Samsung', 4, 63000)

После удачного выполнения в pgAdmin в поле сообщений должно появиться сообщение «INSERT 0 1»:

Стоит учитывать, что значения для столбцов в скобках после ключевого слова VALUES передаются по порядку их объявления. Например, в выражении
CREATE TABLE выше можно увидеть, что первым столбцом идет Id, поэтому этому столбцу передаетсячисло 1.
Второй столбец называется ProductName, поэтому второе значение — строка «Galaxy S9» будет передано именно этому столбцу и так далее.
То есть значения передаются столбцам следующим образом:

• Id: 1

• ProductName: ‘Galaxy S9’

• Manufacturer: ‘Samsung’

• ProductCount: 4

• Price: 63000

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

INSERT INTO Products (ProductName, Price, Manufacturer) 
VALUES ('iPhone X', 71000, 'Apple');

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

• ProductName: ‘iPhone X’

• Manufacturer: ‘Apple’

• Price: 71000

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

Для остальных столбцов будет добавляться значение по умолчанию, если задан атрибут DEFAULT (например, для столбца ProductCount),
значение NULL. При этом неуказанные столбцы (за исключением тех, которые имеют тип Serial) должны допускать значение NULL или иметь атрибут DEFAULT.

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

Также мы можем добавить сразу несколько строк:

INSERT INTO Products  (ProductName, Manufacturer, Price, ProductCount)
VALUES 
('iPhone 6', 'Apple', 3, 36000),
('Galaxy S8', 'Samsung', 2, 46000),
('Galaxy S8 Plus', 'Samsung', 1, 56000)

В данном случае в таблицу будут добавлены три строки.

Возвращение значений

Если мы добавляем значения только для части столбцов, то мы можем не знать, какие значения будут у других столбцов. Например, какое значени
получит столбец Id у товара. С помощью оператора RETURNING мы можем получить это значение:

INSERT INTO Products 
(ProductName, Manufacturer, Price, ProductCount) 
VALUES('Desire 12', 'HTC', 8, 21000) RETURNING id;

НазадВперед

История

PostgreSQL создана на основе некоммерческой СУБД Postgres, разработанной как open-source проект в Калифорнийском университете в Беркли. К разработке Postgres, начавшейся в 1986 году, имел непосредственное отношение Майкл Стоунбрейкер, руководитель более раннего проекта Ingres, на тот момент уже приобретённого компанией Computer Associates. Название расшифровывалось как «Post Ingres», и при создании Postgres были применены многие ранние наработки.

Стоунбрейкер и его студенты разрабатывали новую СУБД в течение восьми лет с по 1994 год. За этот период в синтаксис были введены процедуры, правила, пользовательские типы и другие компоненты. В 1995 году разработка снова разделилась: Стоунбрейкер использовал полученный опыт в создании коммерческой СУБД Illustra, продвигаемой его собственной одноимённой компанией (приобретённой впоследствии компанией Informix), а его студенты разработали новую версию Postgres — Postgres95, в которой язык запросов POSTQUEL — наследие Ingres — был заменен на SQL.

Разработка Postgres95 была выведена за пределы университета и передана команде энтузиастов. Новая СУБД получила имя, под которым она известна и развивается в текущий момент — PostgreSQL.

PostgreSQL CREATE TABLE examples

We will create a new table called that has the following columns:

• user_id – primary key
• username – unique and not null
• password – not null
• email – unique and not null
• created_on – not null
• last_login – null

The following statement creates the table:

The following statement creates the   table that consists of two columns: and :

The following statement creates the table that has three columns: , and .

The primary key of the  table consists of two columns: and , therefore, we have to define the primary key constraint as a table constraint.

Because the column references to the column in the table, we need to define a foreign key constraint for the column:

The column references the column in the table, we also need to define a foreign key constraint for the column.

The following shows the relationship between the , , and tables:

Example

Let’s look at a PostgreSQL CREATE TABLE example.

CREATE TABLE order_details
( order_detail_id integer CONSTRAINT order_details_pk PRIMARY KEY,
  order_id integer NOT NULL,
  order_date date,
  quantity integer,
  notes varchar(200)
);

This PostgreSQL CREATE TABLE example creates a table called order_details which has 5 columns and one primary key:

• The first column is called order_detail_id which is created as an integer datatype and can not contain NULL values, since it is the primary key for the table.
• The second column is called order_id which is an integer datatype and can not contain NULL values.
• The third column is called order_date which is a date datatype and can contain NULL values.
• The fourth column is called quantity which is an integer datatype and can contain NULL values.
• The fifth column is called notes which is a varchar datatype (maximum 200 characters in length) and can contain NULL values.
• The primary key is called order_details_pk and is set to the order_detail_id column.

You could alternatively have written the CREATE TABLE statement as follows:

CREATE TABLE order_details
( order_detail_id integer NOT NULL,
  order_id integer NOT NULL,
  order_date date,
  quantity integer,
  notes varchar(200),
  CONSTRAINT order_details_pk PRIMARY KEY (order_detail_id)
);

The difference between the two CREATE TABLE statements is how the PRIMARY KEY is defined. Both methods are acceptable in PostgreSQL.

Next, let’s create a table that has a DEFAULT VALUE.

CREATE TABLE order_details
( order_detail_id integer NOT NULL,
  order_id integer NOT NULL,
  order_date date,
  quantity integer,
  notes varchar(200) NOT NULL DEFAULT 'Standard shipping',
  CONSTRAINT order_details_pk PRIMARY KEY (order_detail_id)
);

This PostgreSQL CREATE TABLE example creates a table called order_details which has 5 columns and one primary key:

• The first column is called order_detail_id which is created as an integer datatype and can not contain NULL value.
• The second column is called order_id which is an integer datatype and can not contain NULL values.
• The third column is called order_date which is a date datatype and can contain NULL values.
• The fourth column is called quantity which is an integer datatype and can contain NULL values.
• The fifth column is called notes which is a varchar datatype (maximum 200 characters in length) and can not contain NULL values. If no value is provided for this column, the DEFAULT VALUE will be ‘Standard shipping’.
• The primary key is called order_details_pk and is set to the order_detail_id column.

Прочие возможности

• Соблюдение принципов ACID
• Соответствие стандартам ANSI SQL-92, SQL-99, SQL:2003, SQL:2011
• Поддержка запросов с , , , , и подзапросов
• Последовательности
• Контроль целостности
• Репликация
• Общие табличные выражения и рекурсивные запросы
• Аналитические функции
• Поддержка Юникода (UTF-8)
• Поддержка регулярных выражений в стиле Perl
• Встроенная поддержка SSL, SELinux и Kerberos
• Протокол разделяемых блокировок
• Подгружаемые расширения, поддерживающие SHA1, MD5, XML
• Расширения для написания сложных выборок, отчётов и т. д. (API открыт)
• Средства для генерации совместимого с другими системами SQL-кода и импорта из других систем
• Автономные блоки на доступных языках, а не только SQL