Создание таблиц

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

Если вы ещё не создали базу данных geeks, то самое время это сделать. Сейчас попробуем выполнить эту задачу, используя SQL. Подключитесь к СУБД через psql (не указывая базу данных, тогда произойдет подключение к базе данных по умолчанию) и выполните следующие запросы:

DROP DATABASE geeks;
CREATE DATABASE geeks;

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

В примере выше два SQL запроса: один из них удаляет базу данных с именем geeks, а другой создаёт базу данных с таким же именем. Рассмотрим базовые правила построения запросов:

• Каждый запрос должен заканчиваться точкой с запятой. Иначе psql будет думать, что вы продолжаете вводить команды.
• Регистр не важен. Можно было написать drop databasegeeksgeeks;. По сложившейся традиции принято использовать верхний регистр для ключевых слов самого SQL. Это позволяет визуально разделять структуру запроса от данных внутри него: в примере выше это имя базы данных, которое может быть произвольным.

Те самые команды createdb и createuser — не что иное как обёртки, выполняющие SQL запросы внутри СУБД. Их сделали только ради удобства первоначальной настройки и для использования в скриптах автоматизации.

Кстати, SQL поддерживает комментарии. Любая строчка, начинающаяся с двух дефисов — комментарий. Комментарии игнорируются СУБД при построении запросов.

geeks=# -- i am comment
geeks=#

Следующая задача — создать таблицу:

-- Это один запрос, хоть и многострочный. Описание запроса заканчивается символом ;
CREATE TABLE courses (
    name          varchar(255),
    slug          varchar(255),
    lessons_count integer,
    body          text
);

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

Этот запрос уже значительно сложнее предыдущего. Для создания таблицы обязательно указывается её имя и перечисляется набор полей с указанием их типов данных. В примере выше создаётся таблица courses с четырьмя полями: name, slug, lessons_count и body.

В отличие от языков программирования, где строка может иметь произвольный размер, в базах данных чаще всего используется специальный тип varchar (в pg его настоящее имя character varying, а varchar это псевдоним), который имеет фиксированный размер. Размер конкретного поля, имеющего такой тип, указывается в скобках после названия типа varchar(10). А вот тип text не требует указания размера и может содержать текст произвольной длины. Возникает резонный вопрос, зачем нужен varchar, когда можно использовать text и не думать о размерах. К сожалению, так не получится. Динамический размер типа text не даётся бесплатно. Если все строки делать текстом, то у приложения очень быстро начнутся проблемы с производительностью. Подробнее вопросы производительности рассмотрим позже.

Типы данных

Каждое поле в PostgreSQL имеет определённый тип, который задаётся на этапе создания таблицы. Это значит, что значением этого поля могут быть только определённые данные. Если поле имеет числовой тип, то в него невозможно вставить строку, и наоборот. База данных выдаст ошибку при попытке выполнить подобный запрос. PostgreSQL поддерживает большое количество встроенных типов и позволяет создавать свои (пользовательские). Вот основные:

Числа

Даты

Значения даты и времени принимаются практически в любом разумном формате, включая ISO 8601, SQL-совместимый и другие. Вот лишь несколько примеров того, как можно задавать дату:

Примеры создания даты

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

Состояние «true» может задаваться следующими значениями:

TRUE
't'
'true'
'y'
'yes'
'on'
'1'

Для состояния «false» можно использовать следующие варианты:

FALSE
'f'
'false'
'n'
'no'
'off'
'0'

NULL

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

Строки

Просмотр списка таблиц

geeks=# \d
           List of relations
 Schema |  Name   | Type  |   Owner    
--------+---------+-------+------------
 public | courses | table | pythoneast
(1 row)

Просмотр структуры таблицы

geeks=# \d courses
                         Table "public.courses"
    Column     |          Type          | Collation | Nullable | Default 
---------------+------------------------+-----------+----------+---------
 name          | character varying(255) |           |          | 
 slug          | character varying(255) |           |          | 
 lessons_count | integer                |           |          | 
 body          | text                   |           |          |

Полезные команды

• \l - список всех баз данных
• \dt - список всех таблиц

Удаление таблицы выполняется запросом DROP:

DROP TABLE courses;

Будьте внимательны. Удаление таблицы приводит к безвозвратной потере данных.

Дополнительные материалы

• Онлайн песочница для выполнения SQL запросов
• Типы данных
• CREATE TABLE
• интерактивный туториал по SQL