Как создать таблицу в postgresql

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

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

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

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

DROP DATABASE hexlet; CREATE DATABASE hexlet; 

В примере выше два SQL запроса:

• DROP DATABASE hexlet — удаляет базу данных с именем hexlet
• CREATE DATABASE hexlet — создает базу данных с таким же именем

Базовые правила построения запросов:

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

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

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

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

hexlet=> -- i am comment hexlet=> 

Нам удалось создать базу данных hexlet , поэтому можно переходить к созданию таблицы.

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

Таблица создается с помощью запроса CREATE TABLE :

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

Чтобы создать таблицу, необходимо указать ее имя, набор полей и их типы. В примере выше названия полей — это name , slug , lessons_count и body , а varchar(255) , integer и text — их типы.

Типы данных

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

-- Выполняем запрос на вставку передавая в lessons_count строку вместо числа ERROR: invalid input syntax for type integer: "wrong value" 

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

Строки

Для строк в базах данных в основном используются два типа:

• varchar — для строк с ограничением максимальной длины
• text — для строк без ограничения. Как правило, это полноценные тексты

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

Если размер данных не известен, то придется просматривать весь файл в поисках нужного значения. Чтобы избежать подобной ситуации, тип text хранится отдельно. Это тоже негативно влияет на скорость, но уже не так сильно. Если размер строки известен или он меньше какого-то значения, то предпочтительнее использовать varchar.

Имя	Описание
character varying(n), varchar(n)	строка ограниченной переменной длины
text	строка неограниченной переменной длины

• varchar. Полное название типа character varying (varchar может использоваться как псевдоним). Размер строки с таким типом указывается в скобках после названия типа, например, varchar(10). Это значит, что в поле с таким типом можно записать строку длиной до 10 символов.
• text. Не требует указания размера и может содержать текст произвольной длины

Пример создания таблицы с такими типами:

CREATE TABLE blog_posts ( name varchar(80), body text ); 

Числа

Для чисел в основном используются два типа данных: integer и bigint. Какой конкретно указывать тип, зависит от потенциального потолка значения. Ниже указаны диапазоны, допустимые в рамках этих типов:

Имя	Описание	Диапазон
integer	типичный выбор для целых чисел	-2147483648 .. +2147483647
bigint	целое в большом диапазоне	-9223372036854775808 .. 9223372036854775807

Пример создания таблицы с такими типами:

CREATE TABLE users ( id bigint, age integer ); 

Даты

Типы для хранения дат отличаются друг от друга очень сильно, в первую очередь по решаемой задаче. Нам надо хранить день без конкретного времени? Это тип date. Нужно конкретный момент времени, тогда timestamp. Просто время без даты? Тогда time.

Имя	Описание	Наименьшее значение	Наибольшее значение	Точность
timestamp	дата и время (без часового пояса)	4713 до н. э.	294276 н. э.	1 микросекунда
date	дата (без времени суток)	4713 до н. э.	5874897 н. э.	1 день
time	время суток (без даты)	00:00:00	24:00:00	1 микросекунда

Пример создания таблицы с такими типами:

CREATE TABLE events ( start_date date, -- имя поля может называться как тип данных time time, updated_at timestamp, created_at timestamp ); 

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

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

Пример	Описание
1999-01-08	ISO 8601 (рекомендуемый формат)
January 8, 1999

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

Содержит всего два значения: true и false . Этот тип используется для флагов:

Имя	Описание
boolean	true или false (истина или ложь)

Пример создания таблицы с такими типами:

CREATE TABLE blog_posts ( -- флаг: опубликован? published boolean ); 

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

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

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

Анализ структуры базы данных

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

SQL для анализа структуры базы данных не существует. Если вы хотите посмотреть список таблиц и их структуру в базе данных, то придется использовать команды самого psql :

Просмотр списка таблиц базы данных hexlet

hexlet=> \d List of relations Schema | Name | Type | Owner --------+------------+-------+--------- public | courses | table | vagrant public | events | table | vagrant public | blog_posts | table | vagrant 

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

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

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

hexlet=> \d courses # public - обозначает схему по умолчанию Table "public.courses" Column | Type | Modifiers ---------------+------------------------+----------- name | character varying(255) | slug | character varying(255) | lessons_count | integer | body | text | 

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

Кроме перечисленных полезными могут оказаться следующие команды:

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

Удаление таблиц

Чтобы удалить таблицу, выполняется запрос DROP :

DROP TABLE courses; 

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

Открыть доступ

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

• 130 курсов, 2000+ часов теории
• 1000 практических заданий в браузере
• 360 000 студентов

Наши выпускники работают в компаниях:

Как создать таблицу в postgresql

Для создания таблиц применяется команда CREATE TABLE , после которой указывается название таблицы. Также с этой командой можно использовать ряд операторов, которые определяют столбцы таблицы и их атрибуты. Общий синтаксис создания таблицы выглядит следующим образом:

CREATE TABLE название_таблицы (название_столбца1 тип_данных атрибуты_столбца1, название_столбца2 тип_данных атрибуты_столбца2, . название_столбцаN тип_данных атрибуты_столбцаN, атрибуты_таблицы );

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

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

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

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

CREATE TABLE customers ( Id SERIAL PRIMARY KEY, FirstName CHARACTER VARYING(30), LastName CHARACTER VARYING(30), Email CHARACTER VARYING(30), Age INTEGER );

В данном случае в таблице Customers определяются пять столбцов: Id, FirstName, LastName, Age, Email. Первый столбец — Id представляет идентификатор клиента, он служит первичным ключом и поэтому имеет тип SERIAL . Фактически данный столбец будет хранить числовое значение 1, 2, 3 и т.д., которое для каждой новой строки будет автоматически увеличиваться на единицу.

Следующие три столбца представляют имя, фамилию клиента и его электронный адрес и имеют тип CHARACTER VARYING(30) , то есть представляют строку длиной не более 30 символов.

Последний столбец — Age представляет возраст пользователя и имеет тип INTEGER , то есть хранит числа.

И после выполнения этой команды в выбранную базу данных будет добавлена таблица customers.

Удаление таблиц

Для удаления таблиц используется команда DROP TABLE , которая имеет следующий синтаксис:

DROP TABLE table1 [, table2, . ];

Например, удаление таблицы customers:

DROP TABLE customers;

Как создать таблицу в postgresql

CREATE TABLE AS — создать таблицу из результатов запроса

Синтаксис

CREATE [ [ GLOBAL | LOCAL ] < TEMPORARY | TEMP >| UNLOGGED ] TABLE [ IF NOT EXISTS ] имя_таблицы [ (имя_столбца [, . ] ) ] [ WITH ( параметр_хранения [= значение] [, . ] ) | WITH OIDS | WITHOUT OIDS ] [ ON COMMIT < PRESERVE ROWS | DELETE ROWS | DROP >] [ TABLESPACE табл_пространство ] AS запрос [ WITH [ NO ] DATA ]

Описание

CREATE TABLE AS создаёт таблицу и наполняет её данными, полученными в результате выполнения SELECT . Столбцы этой таблицы получают имена и типы данных в соответствии со столбцами результата SELECT (хотя имена столбцов можно переопределить, добавив явно список новых имён столбцов).

CREATE TABLE AS напоминает создание представления, но на самом деле есть значительная разница: эта команда создаёт новую таблицу и выполняет запрос только раз, чтобы наполнить таблицу начальными данными. Последующие изменения в исходных таблицах запроса в новой таблице отражаться не будут. С представлением, напротив, определяющая его команда SELECT выполняется при каждой выборке из него.

Параметры

GLOBAL или LOCAL

Для совместимости игнорируются. Использование этих ключевых слов считается устаревшим; за подробностями обратитесь к CREATE TABLE .

TEMPORARY или TEMP

Если указано, создаваемая таблица будет временной. За подробностями обратитесь к CREATE TABLE . UNLOGGED

Если указано, создаваемая таблица будет нежурналируемой. За подробностями обратитесь к CREATE TABLE . IF NOT EXISTS

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

Имя создаваемой таблицы (возможно, дополненное схемой). имя_столбца

Имя столбца в создаваемой таблице. Если имена столбцов не заданы явно, они определяются по именам столбцов результата запроса. WITH ( параметр_хранения [= значение ] [, . ] )

Это предложение определяет дополнительные параметры хранения для новой таблицы: за подробностями обратитесь к Параметры хранения. Предложение WITH может также включать указание OIDS=TRUE (или просто OIDS ), с которым строкам в новой таблице будут назначаться идентификаторы объектов (OID), либо указание OIDS=FALSE , с которым строки не будут содержать OID. За дополнительными сведениями обратитесь к CREATE TABLE . WITH OIDS
WITHOUT OIDS

Это устаревшее написание указаний WITH (OIDS) и WITH (OIDS=FALSE) , соответственно. Если требуется определить одновременно свойство OIDS и параметры хранения, необходимо использовать синтаксис WITH ( . ) ; см. ниже. ON COMMIT

Поведением временных таблиц в конце блока транзакции позволяет управлять предложение ON COMMIT , которое принимает три параметра:

PRESERVE ROWS

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

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

Эта временная таблица будет удаляться в конце текущего блока транзакции.

TABLESPACE табл_пространство

Здесь табл_пространство — имя табличного пространства, в котором будет создаваться новая таблица. Если оно не указано, выбирается default_tablespace или temp_tablespaces, если таблица временная. запрос

Команда SELECT , TABLE или VALUES , либо команда EXECUTE , выполняющая подготовленный запрос SELECT , TABLE или VALUES . WITH [ NO ] DATA

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

Замечания

Функциональность этой команды подобна SELECT INTO , но предпочтительнее использовать её, во избежание путаницы с другими применениями синтаксиса SELECT INTO . Кроме того, набор возможностей CREATE TABLE AS шире, чем у SELECT INTO .

Команда CREATE TABLE AS позволяет пользователю явно определить, добавлять ли OID в таблицу. Если присутствие OID не определено явно, оно определяется конфигурационной переменной default_with_oids.

Примеры

Создание таблицы films_recent , содержащей только последние записи из таблицы films :

CREATE TABLE films_recent AS SELECT * FROM films WHERE date_prod >= '2002-01-01';

Чтобы скопировать таблицу полностью, можно также использовать короткую форму команды TABLE :

CREATE TABLE films2 AS TABLE films;

Создание временной таблицы films_recent , содержащей только последние записи таблицы films , с применением подготовленного оператора. Новая таблица будет содержать OID и прекратит существование при фиксации транзакции:

PREPARE recentfilms(date) AS SELECT * FROM films WHERE date_prod > $1; CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS EXECUTE recentfilms('2002-01-01');

Совместимость

CREATE TABLE AS соответствует стандарту SQL . Нестандартные расширения перечислены ниже:

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

Стандарт требует наличия указания WITH [ NO ] DATA , в PostgreSQL оно необязательно.

PostgreSQL работает с временными таблицами не так, как описано в стандарте; за подробностями обратитесь к CREATE TABLE .

Предложение WITH является расширением PostgreSQL ; в стандарте ни параметры хранения, ни OID не оговариваются.

См. также

Пред.	Наверх	След.
CREATE TABLE	Начало	CREATE TABLESPACE

Как создать таблицу в postgresql

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

CREATE TABLE weather ( city varchar(80), temp_lo int, -- минимальная температура дня temp_hi int, -- максимальная температура дня prcp real, -- уровень осадков date date );

Весь этот текст можно ввести в psql вместе с символами перевода строк. psql понимает, что команда продолжается до точки с запятой.

В командах SQL можно свободно использовать пробельные символы (пробелы, табуляции и переводы строк). Это значит, что вы можете ввести команду, выровняв её по-другому или даже уместив в одной строке. Два минуса ( « — » ) обозначают начало комментария. Всё, что идёт за ними до конца строки, игнорируется. SQL не чувствителен к регистру в ключевых словах и идентификаторах, за исключением идентификаторов, взятых в кавычки (в данном случае это не так).

varchar(80) определяет тип данных, допускающий хранение произвольных символьных строк длиной до 80 символов. int — обычный целочисленный тип. real — тип для хранения чисел с плавающей точкой одинарной точности. date — тип даты. (Да, столбец типа date также называется date . Это может быть удобно или вводить в заблуждение — как посмотреть.)

Postgres Pro поддерживает стандартные типы SQL : int , smallint , real , double precision , char( N ) , varchar( N ) , date , time , timestamp и interval , а также другие универсальные типы и богатый набор геометрических типов. Кроме того, Postgres Pro можно расширять, создавая набор собственных типов данных. Как следствие, имена типов не являются ключевыми словами в данной записи, кроме тех случаев, когда это требуется для реализации особых конструкций стандарта SQL .

Во втором примере мы сохраним в таблице города и их географическое положение:

CREATE TABLE cities ( name varchar(80), location point );

Здесь point — пример специфического типа данных Postgres Pro .

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

DROP TABLE имя_таблицы;

Пред.	Наверх	След.
2.2. Основные понятия	Начало	2.4. Добавление строк в таблицу