Уроки postgresql: от полного нуля до полного просветления

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

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

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

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

• serial: представляет автоинкрементирующееся числовое значение, которое занимает 4 байта и может хранить
  числа от 1 до 2147483647. Значение данного типа образуется путем автоинкремента значения предыдущей строки. Поэтому, как правило, данный тип используется для определения идентификаторов строки.

• smallserial: представляет автоинкрементирующееся числовое значение, которое занимает 2 байта и может хранить
  числа от 1 до 32767. Аналог типа serial для небольших чисел.

• bigserial: представляет автоинкрементирующееся числовое значение, которое занимает 8 байт и может хранить
  числа от 1 до 9223372036854775807. Аналог типа serial для больших чисел.




• smallint: хранит числа от -32768 до +32767. Занимает 2 байта. Имеет псевдоним int2.

• integer: хранит числа от -2147483648 до +2147483647. Занимает 4 байта. Имеет псевдонимы int и int4.

• bigint: хранит числа от -9223372036854775808 до +9223372036854775807. Занимает 8 байт. Имеет псевдоним int8.




• numeric: хранит числа с фиксированной точностью, которые могут иметь до 131072 знаков в целой части и до 16383 знаков после запятой.

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

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

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

  Например, для числа 23.5141 precision равно 6, а scale — 4.

• decimal: хранит числа с фиксированной точностью, которые могут иметь до 131072 знаков в целой части и до 16383 знаков
  в дробной части. То же самое, что и numeric.

• real: хранит числа с плавающей точкой из диапазона от 1E-37 до 1E+37. Занимает 4 байта. Имеет псевдоним
  float4.

• double precision: хранит числа с плавающей точкой из диапазона от 1E-307 до 1E+308. Занимает 8 байт. Имеет псевдоним
  float8.

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

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

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

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

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

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

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

• text: представляет текст произвольной длины.

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

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

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

• timestamp: хранит дату и время. Занимает 8 байт. Для дат самое нижнее значение — 4713 г до н.э., самое верхнее значение —
  294276 г н.э.

• timestamp with time zone: то же самое, что и timestamp, только добавляет данные о часовом поясе.

• date: представляет дату от 4713 г. до н.э. до 5874897 г н.э. Занимает 4 байта.

• time: хранит время с точностью до 1 микросекунды без указания часового пояса. Принимает значения от 00:00:00 до 24:00:00. Занимает 8 байт.

• time with time zone: хранит время с точностью до 1 микросекунды с указанием часового пояса. Принимает значения от 00:00:00+1459 до 24:00:00-1459. Занимает 12 байт.

• interval: представляет временной интервал. Занимает 16 байт.

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

• yyyy-mm-dd — 1999-01-08

• Month dd, yyyy — January 8, 1999

• mm/dd/yyyy — 1/8/1999

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

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

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

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

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

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

• cidr: интернет-адрес в формате IPv4 и IPv6. Например, 192.168.0.1. Занимает от 7 до 19 байт.

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

• macaddr: хранит MAC-адрес. Занимает 6 байт.

• macaddr8: хранит MAC-адрес в формате EUI-64. Занимает 8 байт.

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

• point: представляет точку на плоскости в формате (x,y). Занимает 16 байт.

• line: представляет линию неопределенной длины в формате {A,B,C}. Занимает 32 байта.

• lseg: представляет отрезок в формате ((x1,y1),(x2,y2)). Занимает 32 байта.

• box: представляет прямоугольник в формате ((x1,y1),(x2,y2)). Занимает 32 байта.

• path: представляет набор содиненных точек. В формате ((x1,y1),...) путь является закрытым (первая и последняя точка соединяются линией)
  и фактически представляет многоугольник. В формате [(x1,y1),...] путь является открытым Занимает 16+16n байт.

• polygon: представляет многоугольник в формате ((x1,y1),...). Занимает 40+16n байт.

• circle: представляет окружность в формате <(x,y),r>. Занимает 24 байта.

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

• json: хранит данные json в текстовом виде.

• jsonb: хранит данные json в бинарном формате.

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

• xml: хранит даные в формате XML.

НазадСодержаниеВперед

Уроки по теме «postgresql» для начинающих

В каталоге хекслета найдено 53 урока по теме «postgresql». Уроки по теме «postgresql» есть в курсах: PHP PDO: Работа с базой данных, Основы реляционных баз данных, Основы SQL, Основы аналитики на SQL.

Урок «Формирование подготовленных запросов»

В курсе
«PHP PDO: Работа с базой данных»

Изучить механизм формирования запроса к базе данных и подготовку плана запроса с помощью prepared statement.

Урок «Like»

В курсе
«PHP PDO: Работа с базой данных»

Научиться делать выборку из базы с помощью оператора like.

Урок «Реализация in»

В курсе
«PHP PDO: Работа с базой данных»

Рассмотреть ручную обработку, соответствующую работе оператора in.

Урок «Соединение с базой данных»

В курсе
«PHP PDO: Работа с базой данных»

Познакомиться с PDO и научиться создавать соединение с базой данных.

Урок «Безопасность при работе с внешними данными»

В курсе
«PHP PDO: Работа с базой данных»

Узнать про sql инъекции и понять как их предотвращать.

Урок «Результат запроса в базу данных»

В курсе
«PHP PDO: Работа с базой данных»

Изучить PDO Statement — объект, возвращаемый после запроса в базу данных, рассмотреть способы работы с ним.

Урок «Функции»

В курсе
«Основы реляционных баз данных»

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

Урок «Лимит (LIMIT)»

В курсе
«Основы реляционных баз данных»

Научиться ограничивать выборку

Урок «Ограничения»

В курсе
«Основы реляционных баз данных»

Научиться использовать UNIQUE и NOT NULL

Урок «Выборка данных»

В курсе
«Основы реляционных баз данных»

Познакомиться с SELECT и научиться делать простые SQL запросы

Урок «Структура реляционной базы данных»

В курсе
«Основы реляционных баз данных»

Познакомиться с табличной организацией данных

Урок «Условия (WHERE)»

В курсе
«Основы реляционных баз данных»

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

Урок «Изменение структуры таблицы (ALTER)»

В курсе
«Основы реляционных баз данных»

Научиться изменять структуру существующей таблицы

Урок «Автоинкремент»

В курсе
«Основы реляционных баз данных»

Научиться генерировать ключи автоматически

Урок «Онтология»

В курсе
«Основы реляционных баз данных»

Познакомиться с сущностями, связями и ERD

Урок «DISTINCT»

В курсе
«Основы реляционных баз данных»

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

Урок «Производительность»

В курсе
«Основы реляционных баз данных»

Познакомиться со способами обеспечения скорости выполнения запросов (индексы, Explain)

Урок «Соединения (JOINS)»

В курсе
«Основы реляционных баз данных»

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

Урок «Архитектура СУБД»

В курсе
«Основы реляционных баз данных»

Узнать, как устроены СУБД

Урок «Подготовка к работе»

В курсе
«Основы реляционных баз данных»

Создать пользователя и базу данных

Урок «Транзакционность»

В курсе
«Основы реляционных баз данных»

Научиться выполнять запросы внутри транзакции, разобраться с ACID

Урок «Вставка и модификация данных»

В курсе
«Основы реляционных баз данных»

Научиться наполнять базу данных и изменять ее содержимое

Урок «Введение»

В курсе
«Основы реляционных баз данных»

Познакомиться с курсом

Урок «Первая нормальная форма»

В курсе
«Основы реляционных баз данных»

Разобраться с первой нормальной формой

Урок «Сортировка (ORDER)»

В курсе
«Основы реляционных баз данных»

Научиться сортировать данные в прямом и обратном порядке

Урок «Вторая нормальная форма»

В курсе
«Основы реляционных баз данных»

Разобраться со второй нормальной формой

Нашли 4 курса
по тегу «postgresql»

PHP PDO: Работа с базой данных

query builder

подготовленные запросы

квотирование

8 часов

Посмотреть

Основы реляционных баз данных

postgresql

транзакции

нормальные формы

27 часов

Посмотреть

Основы SQL

select

выборка

11 часов

Посмотреть

Основы аналитики на SQL

postgresql

запросы

базы данных

12 часов

Посмотреть

Вам могут быть интересны темы:
PDO
query builder
подготовленные запросы
квотирование
базы данных
транзакции
нормальные формы
СУБД
SQL
select
запросы
выборка

PostgreSQL: Документация: 15: Часть I.

Учебное пособие

PostgreSQL: Документация: 15: Часть I. Учебное пособие

9 февраля 2023 г.:
Выпущены версии PostgreSQL 15.2, 14.7, 13.10, 12.14 и 11.19!

Документация → PostgreSQL 15

Поддерживаемые версии:

Текущий
(15)

/

14

/

13

/

12

/

11

Разрабатываемые версии:

devel

Неподдерживаемые версии:

10

/
9,6

/
9,5

/
9. 4

/
9.3

/
9.2

/
9.1

/
9,0

/
8.4

/
8.3

/
8.2

/
8.1

/
8,0

/
7.4

/
7.3

/
7.2

/
7.1

Добро пожаловать в учебник по PostgreSQL. Следующие несколько глав предназначены для того, чтобы дать простое введение в PostgreSQL, концепции реляционных баз данных и язык SQL для тех, кто плохо знаком с любым из этих аспектов. Мы только предполагаем некоторые общие знания о том, как использовать компьютеры. Никакого особого опыта Unix или программирования не требуется. Эта часть в основном предназначена для того, чтобы дать вам некоторый практический опыт работы с важными аспектами системы PostgreSQL. Он не пытается быть полным или тщательным рассмотрением тем, которые он охватывает.

Поработав с этим учебным пособием, вы можете перейти к чтению части II, чтобы получить более формальное знание языка SQL, или части IV, чтобы получить информацию о разработке приложений для PostgreSQL. Тем, кто настраивает и управляет собственным сервером, также следует прочитать часть III.

Содержание

1. Начало работы

1.1. Установка

1.2. Архитектурные основы

1.3. Создание базы данных

1.4. Доступ к базе данных

2. Язык SQL

2. 1. Введение

2.2. Концепты

2.3. Создание новой таблицы

2.4. Заполнение таблицы строками

2.5. Запрос таблицы

2.6. Соединения между таблицами

2.7. Агрегатные функции

2.8. Обновления

2.9. Удаления

3. Дополнительные функции

3.1. Введение

3.2. просмотров

3.3. Внешние ключи

3.4. Транзакции

3.5. Оконные функции

3.6. Наследство

3.7. Вывод

Учебник по PostgreSQL — Изучайте PostgreSQL с нуля

Добро пожаловать на сайт PostgreSQLTutorial.com! Это руководство PostgreSQL поможет вам быстро понять PostgreSQL. Вы очень быстро освоите PostgreSQL на множестве практических примеров и примените полученные знания при разработке приложений с использованием PostgreSQL.

Если вы…

• Хотите быстро изучить PostgreSQL.
• Разработка приложений с использованием PostgreSQL в качестве серверной системы управления базами данных.
• Миграция с других систем управления базами данных, таких как MySQL, Oracle и Microsoft SQL Server, на PostgreSQL.

На этом веб-сайте вы найдете все, что вам нужно знать, чтобы быстро и эффективно начать работу с PostgreSQL.

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

Начало работы с PostgreSQL

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

Базовое руководство по PostgreSQL

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

Раздел 1. Запрос данных

• Выбрать – показать, как запрашивать данные из одной таблицы.
• Псевдонимы столбцов — узнайте, как назначать временные имена столбцам или выражениям в запросе.
• Упорядочить по — руководство по сортировке набора результатов, возвращаемого запросом.
• Select Distinct  – предоставить вам предложение, которое удаляет повторяющиеся строки в наборе результатов.

Раздел 2. Фильтрация данных

• Где – фильтрация строк по заданному условию.
• Ограничить – получить подмножество строк, сгенерированных запросом.
• Выборка — ограничение количества строк, возвращаемых запросом.
• В – выберите данные, соответствующие любому значению в списке значений.
• Между – выберите данные, представляющие собой диапазон значений.
• Нравится – фильтрация данных на основе сопоставления с образцом.
• Is Null — проверить, является ли значение нулевым или нет.

Раздел 3. Объединение нескольких таблиц

• Соединения — краткий обзор объединений в PostgreSQL.
• Псевдонимы таблиц — описывает, как использовать псевдонимы таблиц в запросе.
• Внутреннее соединение — выбор строк из одной таблицы, которым соответствуют строки в других таблицах.
• Левое соединение — выберите строки из одной таблицы, которые могут иметь или не иметь соответствующие строки в других таблицах.
• Самостоятельное соединение — соединение таблицы с самой собой путем сравнения таблицы с самой собой.
• Полное внешнее соединение — используйте полное соединение, чтобы найти строку в таблице, которой нет соответствующей строки в другой таблице.
• Cross Join – производит декартово произведение строк в двух или более таблицах.
• Естественное соединение — объединение двух или более таблиц с использованием неявных условий соединения на основе общих имен столбцов в соединяемых таблицах.

Раздел 4. Группировка данных

• Группировать по – разделить строки на группы и применить к каждой агрегатную функцию.
• Наличие — применить условия к группам.

Раздел 5. Операции над множествами

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

Раздел 6. Наборы группировок, кубы и сводки

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

Раздел 7. Подзапрос

• Подзапрос – написать запрос, вложенный в другой запрос.
• ЛЮБОЙ — получение данных путем сравнения значения с набором значений, возвращаемых подзапросом.
• ALL – запрос данных путем сравнения значения со списком значений, возвращаемых подзапросом.
• EXISTS — проверка существования строк, возвращаемых подзапросом.

Раздел 8. Общие табличные выражения

• PostgreSQL CTE — познакомит вас с общими табличными выражениями PostgreSQL или CTE.
• Рекурсивный запрос с использованием CTE — обсудите рекурсивный запрос и узнайте, как применять его в различных контекстах.

Раздел 9. Изменение данных

В этом разделе вы узнаете, как вставлять данные в таблицу с помощью оператора INSERT , изменять существующие данные с помощью оператора UPDATE и удалять данные с помощью оператора DELETE . . Кроме того, вы узнаете, как использовать оператор upsert для слияния данных.

• Вставка — подскажет, как вставить одну строку в таблицу.
• Вставить несколько строк — показать, как вставить несколько строк в таблицу.
• Обновить — обновить существующие данные в таблице.
• Обновить соединение — обновить значения в таблице на основе значений в другой таблице.
• Удалить – удалить данные в таблице.
• Upsert — вставка или обновление данных, если новая строка уже существует в таблице.

Раздел 10. Сделки

• Транзакции PostgreSQL — покажет вам, как обрабатывать транзакции в PostgreSQL с помощью операторов BEGIN, COMMIT и ROLLBACK.

Раздел 11. Импорт и экспорт данных

Вы узнаете, как импортировать и экспортировать данные PostgreSQL из файла формата CSV и в него с помощью команды копирования.

• Импорт файла CSV в таблицу — покажет вам, как импортировать файл CSV в таблицу.
• Экспорт таблицы PostgreSQL в файл CSV — покажет вам, как экспортировать таблицы в файл CSV.

Раздел 12. Управление таблицами

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

• Типы данных — охватывают наиболее часто используемые типы данных PostgreSQL.
• Создать таблицу — руководство по созданию новой таблицы в базе данных.
• Выберите «В» и «Создать таблицу как» — показано, как создать новую таблицу из набора результатов запроса.
• Автоинкрементный столбец с SERIAL — использует SERIAL для добавления автоинкрементного столбца в таблицу.
• Последовательности — познакомить вас с последовательностями и описать, как использовать последовательность для создания последовательности чисел.
• Столбец идентификаторов — показывает, как использовать столбец идентификаторов.
• Изменить таблицу – изменить структуру существующей таблицы.
• Переименовать таблицу – изменить имя таблицы на новое.
• Добавить столбец – показать, как добавить один или несколько столбцов в существующую таблицу.
• Удаление столбца — продемонстрируйте, как удалить столбец таблицы.
• Изменить тип данных столбца — показать вам, как изменить данные столбца.
• Переименовать столбец — показано, как переименовать один или несколько столбцов таблицы.
• Удалить таблицу — удалить существующую таблицу и все зависимые от нее объекты.
• Усечение таблицы — быстрое и эффективное удаление всех данных в большой таблице.
• Временная таблица — показать вам, как использовать временную таблицу.
• Копировать таблицу — показано, как скопировать таблицу в новую.

Раздел 13. Понимание ограничений PostgreSQL

• Первичный ключ — показано, как определить первичный ключ при создании таблицы или добавлении первичного ключа в существующую таблицу.
• Внешний ключ — покажет вам, как определить ограничения внешнего ключа при создании новой таблицы или добавить ограничения внешнего ключа для существующих таблиц.
• Ограничение CHECK — добавить логику для проверки значения на основе логического выражения.
• Ограничение UNIQUE — убедитесь, что значения в столбце или группе столбцов уникальны в таблице.
• Ограничение NOT NULL — убедитесь, что значения в столбце не равны NULL .

Раздел 14. Подробное описание типов данных PostgreSQL

• Boolean — храните значения TRUE и FALSE с типом данных Boolean.
• CHAR, VARCHAR и TEXT – узнайте, как использовать различные типы символов, включая CHAR , VARCHAR и TEXT .
• NUMERIC – покажет вам, как использовать тип NUMERIC для хранения значений, требующих точности.
• Integer — познакомит вас с различными целочисленными типами в PostgreSQL, включая SMALLINT , INT и BIGINT .
• DATE — введите тип данных DATE для хранения значений даты.
• Отметка времени – быстрое понимание типов данных меток времени.
• Интервал — показывает, как использовать интервальный тип данных для эффективной обработки периода времени.
• TIME — используйте тип данных TIME для управления значениями времени суток.
• UUID — расскажет, как использовать тип данных UUID и как генерировать значения UUID с помощью поставляемых модулей.
• Массив — показывает, как работать с массивом, и знакомит с некоторыми удобными функциями для работы с массивами.
• hstore — познакомьте вас с типом данных, который представляет собой набор пар ключ/значение, хранящихся в одном значении в PostgreSQL.
• JSON — иллюстрирует, как работать с типом данных JSON, и показывает, как использовать некоторые из наиболее важных операторов и функций JSON.
• Пользовательские типы данных — показано, как использовать операторы CREATE DOMAIN и CREATE TYPE для создания пользовательских типов данных.

Раздел 15. Условные выражения и операторы.

Раздел 16. Утилиты PostgreSQL

• Команды psql — показывают наиболее распространенные команды psql, которые помогают быстрее и эффективнее взаимодействовать с psql.

Раздел 17. Рецепты PostgreSQL

• Как сравнить две таблицы — опишите, как сравнивать данные в двух таблицах в базе данных.
• Как удалить повторяющиеся строки в PostgreSQL — показать вам различные способы удаления повторяющихся строк из таблицы.
• Как сгенерировать случайное число в диапазоне – проиллюстрируйте, как сгенерировать случайное число в определенном диапазоне. Оператор
• EXPLAIN — руководство по использованию оператора EXPLAIN для возврата плана выполнения запроса.
• PostgreSQL и MySQL — сравните PostgreSQL и MySQL с точки зрения функциональных возможностей.

Расширенное руководство по PostgreSQL

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

PostgreSQL PL/pgSQL

В этом разделе хранимых процедур PostgreSQL показано, как шаг за шагом разрабатывать пользовательские функции PostgreSQL с использованием процедурного языка PL/pgSQL.

Триггеры PostgreSQL

В этом разделе представлена ​​концепция триггеров PostgreSQL и показано, как управлять триггерами в PostgreSQL.

Представления PostgreSQL

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