Как создавать, удалять и управлять таблицами в PostgreSQL на облачном сервере

Что такое PostgreSQL?

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

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

Как установить и войти в PostgreSQL на Ubuntu

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

Введите следующие команды для установки:

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

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

sudo adduser postgres_user

Войдите под пользователем PostgreSQL по умолчанию (называемым «postgres»), чтобы создать базу данных и назначить ее новому пользователю:

sudo su - postgres
psql

Вы попадете в командную строку PostgreSQL.

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

CREATE USER postgres_user WITH PASSWORD 'password';
CREATE DATABASE my_postgres_db OWNER postgres_user;

Выйдите из интерфейса с помощью следующей команды:

\q

Выйдите из учетной записи пользователя «postgres» по умолчанию и войдите в систему пользователя, которого вы создали, с помощью следующих команд:

exit
sudo su - postgres_user

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

psql my_postgres_db

Теперь мы готовы узнать об управлении таблицами.

Синтаксис создания таблиц в PostgreSQL

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

\d

No relations found.

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

CREATE TABLE new_table_name (
	table_column_title TYPE_OF_DATA column_constraints,
	next_column_title TYPE_OF_DATA column_constraints,
	table_constraint
	table_constraint
) INHERITS existing_table_to_inherit_from;

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

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

Определения столбцов и таблиц PostgreSQL

Определения столбцов следуют этому шаблону синтаксиса:

column_name data_type (optional_data_length_restriction) column_constraints

Имя столбца должно быть самоочевидным.

Типы данных Postgres Pro

Тип данных может быть любым из следующих:

• boolean: используйте \boolean\ или \bool\, чтобы объявить истинное или ложное значение.
• символьные значения
  • char: содержит один символ
  • char (#): содержит # количество символов. Пробелы будут вставлены, чтобы заполнить любую дополнительную комнату.
  • varchar (#): содержит не более # символов. Может содержать меньше.
  • smallint: целое число от -32768 до 32767.
  • int: целое число от -214783648 до 214783647.
  • серийный номер: Целое число, заполняемое автоматически.
  • float (#): число с плавающей запятой с точностью не менее # точек.
  • real: 8-байтовое число с плавающей запятой
  • числовой (#,after_dec): действительное число с # количеством цифр и after_dec цифрами после запятой
  • date: сохраняет значение даты
  • время: сохраняет значение времени
  • отметка времени: сохраняет значение даты и времени
  • timestamptz: хранит метку времени, включающую данные о часовом поясе
  • interval: сохраняет разницу между двумя значениями отметки времени
  • точка: хранит пару координат, определяющих точку
  • line: хранит набор точек, отображающих линию
  • lseg: хранит данные, определяющие сегмент линии
  • box: хранит данные, определяющие прямоугольник
  • полигон: хранит данные, определяющие любое замкнутое пространство
  • inet: хранит IP-адрес
  • macaddr: сохраняет MAC-адрес устройства

  Ограничения столбцов и таблиц PostreSQL

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

  • NOT NULL: столбец не может иметь нулевое значение
  • УНИКАЛЬНО: значение столбца не должно совпадать ни для одной записи. Null всегда считается уникальным значением.
  • ПЕРВИЧНЫЙ КЛЮЧ: комбинация двух указанных выше ограничений. Можно использовать только один раз за стол
  • ПРОВЕРКА: убедитесь, что условие истинно для значений в столбце
  • ССЫЛКИ: значение должно существовать в столбце другой таблицы

  После определения столбцов можно объявить ограничения для всей таблицы. Ограничения для всей таблицы могут быть либо UNIQUE, PRIMARY KEY, CHECK, либо REFERENCES.

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

  Давайте создадим тестовую таблицу для практики. Мы создадим таблицу под названием \pg_equipment\, которая определяет различные части оборудования для игровых площадок. Введите следующее определение таблицы:

  CREATE TABLE pg_equipment (
  	equip_id serial PRIMARY KEY,
  	type varchar (50) NOT NULL,
  	color varchar (25) NOT NULL,
  	location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
  	install_date date
  	);

  NOTICE:  CREATE TABLE will create implicit sequence "pg_equipment_equip_id_seq" for serial column "pg_equipment.equip_id"
  NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pg_equipment_pkey" for table "pg_equipment"
  CREATE TABLE

  Мы можем увидеть нашу новую таблицу, набрав \\d\ в командной строке:

  \d

                         List of relations
   Schema |           Name            |   Type   |     Owner     
  --------+---------------------------+----------+---------------
   public | pg_equipment              | table    | postgres_user
   public | pg_equipment_equip_id_seq | sequence | postgres_user
  (2 rows)

  Перечислена таблица, а также последовательность, созданная объявлением последовательного типа данных \equip_id\.

  Как изменить данные таблицы в PostgreSQL

  Мы можем изменить определение наших таблиц с помощью следующего общего синтаксиса:

  ALTER TABLE table_name Action_TO_Take;

  Например, мы можем добавить столбец в нашу таблицу \pg_equipment\, введя эту команду:

  ALTER TABLE pg_equipment ADD COLUMN functioning bool;

  ALTER TABLE

  Мы можем увидеть дополнительный столбец, набрав:

  \d pg_equipment

      Column    |         Type          |                            Modifiers                            
  --------------+-----------------------+-----------------------------------------------------------------
   equip_id     | integer               | not null default nextval('pg_equipment_equip_id_seq'::regclass)
   type         | character varying(50) | not null
   color        | character varying(25) | not null
   location     | character varying(25) | 
   install_date | date                  | 
   functioning  | boolean               |
   . . .

  Чтобы добавить значение по умолчанию, указывающее, что «оборудование следует считать работающим, если не указано иное», введите следующую команду:

  ALTER TABLE pg_equipment ALTER COLUMN functioning SET DEFAULT 'true';

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

  ALTER TABLE pg_equipment ALTER COLUMN functioning SET NOT NULL;

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

  ALTER TABLE pg_equipment RENAME COLUMN functioning TO working_order;

  Чтобы удалить только что созданный столбец, введите следующую команду:

  ALTER TABLE pg_equipment DROP COLUMN working_order;

  Мы можем переименовать всю таблицу с помощью этой команды:

  ALTER TABLE pg_equipment RENAME TO playground_equip;

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

  Мы можем удалить созданную нами таблицу, набрав:

  DROP TABLE playground_equip;

  DROP TABLE

  Если мы дадим эту команду несуществующей таблице, мы получим следующую ошибку:

  ERROR: table "playground_equip" does not exist

  Чтобы избежать этой ошибки, мы можем указать postgreSQL удалить таблицу, если она существует, и успешно вернуться в любом случае. Мы делаем это, введя следующую команду:

  DROP TABLE IF EXISTS playground_equip;

  NOTICE:  table "playground_equip" does not exist, skipping
  DROP TABLE

  На этот раз он сообщает нам, что таблица не найдена, но продолжает работу, а не выдает ошибку.

  Заключение

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