Postgresql create database windows

The first test to see whether you can access the database server is to try to create a database. A running PostgreSQL server can manage many databases. Typically, a separate database is used for each project or for each user.

Possibly, your site administrator has already created a database for your use. In that case you can omit this step and skip ahead to the next section.

To create a new database, in this example named mydb, you use the following command:

$ createdb mydb

If this produces no response then this step was successful and you can skip over the remainder of this section.

If you see a message similar to:

createdb: command not found

then PostgreSQL was not installed properly. Either it was not installed at all or your shell’s search path was not set to include it. Try calling the command with an absolute path instead:

$ /usr/local/pgsql/bin/createdb mydb

The path at your site might be different. Contact your site administrator or check the installation instructions to correct the situation.

Another response could be this:

createdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?

This means that the server was not started, or it is not listening where createdb expects to contact it. Again, check the installation instructions or consult the administrator.

Another response could be this:

createdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  role "joe" does not exist

where your own login name is mentioned. This will happen if the administrator has not created a PostgreSQL user account for you. (PostgreSQL user accounts are distinct from operating system user accounts.) If you are the administrator, see Chapter 21 for help creating accounts. You will need to become the operating system user under which PostgreSQL was installed (usually postgres) to create the first user account. It could also be that you were assigned a PostgreSQL user name that is different from your operating system user name; in that case you need to use the -U switch or set the PGUSER environment variable to specify your PostgreSQL user name.

If you have a user account but it does not have the privileges required to create a database, you will see the following:

createdb: error: database creation failed: ERROR:  permission denied to create database

Not every user has authorization to create new databases. If PostgreSQL refuses to create databases for you then the site administrator needs to grant you permission to create databases. Consult your site administrator if this occurs. If you installed PostgreSQL yourself then you should log in for the purposes of this tutorial under the user account that you started the server as. ^[1]

You can also create databases with other names. PostgreSQL allows you to create any number of databases at a given site. Database names must have an alphabetic first character and are limited to 63 bytes in length. A convenient choice is to create a database with the same name as your current user name. Many tools assume that database name as the default, so it can save you some typing. To create that database, simply type:

$ createdb

If you do not want to use your database anymore you can remove it. For example, if you are the owner (creator) of the database mydb, you can destroy it using the following command:

$ dropdb mydb

(For this command, the database name does not default to the user account name. You always need to specify it.) This action physically removes all files associated with the database and cannot be undone, so this should only be done with a great deal of forethought.

More about createdb and dropdb can be found in createdb and dropdb respectively.

Введение

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

Подготовка инфраструктуры

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

Подробнее, что такое облачные базы данных и в чем отличие от аренды целого сервера, рассказываем ниже в разделе «PostgreSQL на инфраструктуре Selectel».

Как создать БД в панели управления Selectel

Чтобы создать новый кластер БД Selectel, выполните следующее.

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

Когда кластер будет создан, он отобразится в той же вкладке Базы данных в статуте Active.

Картинка с сайта: selectel.ru

Для организации БД в новом кластере нужно сначала создать нового пользователя. 

В своем кластере БД перейдите на вкладку Пользователи и нажмите кнопку Создать пользователя.

Картинка с сайта: selectel.ru

Заполните имя и пароль нового пользователя и нажмите Создать.

Перейдите на вкладку Базы данных.

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

Картинка с сайта: selectel.ru

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

Кому подходит СУБД PostgreSQL на инфраструктуре Selectel

DBaaS (Database as a Service) — это облачное решение, которое позволяет пользователям управлять базами данных, не беспокоясь о настройке и обслуживании серверной инфраструктуры. Оно походит тем, кто хочет сосредоточиться на развитии продукта, а не на управлении инфраструктурой.

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

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

• Полностью управляемое решение. Selectel берет на себя все задачи по управлению инфраструктурой. Пользователю не нужно думать о настройке, резервировании и безопасности — все автоматизировано.
• Высокая отказоустойчивость. PostgreSQL на платформе Selectel разворачивается в надежных дата-центрах с обеспечением высокой доступности. Если один из узлов откажет, система продолжит работу без простоев, а резервные копии позволят быстро восстановить данные.
• Безопасность. Облачные базы данных соответствуют российским и международным стандартам: закону 152-ФЗ, приказу ФСТЭК № 21, PCI DSS, ISO 27001, 27017, 27018.
• Master Discovery. В услуге представлена встроенная система поиска актуального мастера на базе DNS. При аварийной ситуации и смене мастера система в реальном времени перенаправит запросы клиентского приложения к новому IP-адресу.
• Гибкость и масштабируемость. Вы можете гибкого изменять ресурсы базы данных в зависимости от потребностей приложения. При росте нагрузки просто поменяйте конфигурацию облачного сервера и количество реплик. Доступно как горизонтальное, так и вертикальное масштабирование.
• Интеграция с другими сервисами Selectel. DBaaS легко интегрируется с другими решениями, такими как выделенные серверы, DAVM, Kubernetes, сети и сервисы безопасности. Это позволяет создать полноценную и безопасную экосистему для работы с данными.
• Поддержка и документация. Selectel предоставляет детальную документацию и круглосуточную техническую поддержку, что упрощает процесс использования DBaaS и помогает быстро решать возникающие проблемы.

Как создать БД в графическом инструменте pgAdmin

Для работы с СУБД PostgreSQL через графический интерфейс можно использовать pgAdmin — популярный инструмент для администрирования баз данных. 

Установка

Для начала скачайте установочный файл: перейдите на официальный сайт pgAdmin и выберите подходящую версию операционной системы (Windows, macOS или Linux).

После загрузки следуйте инструкции для вашей системы.

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

macOS. Откройте загруженный файл и перенесите pgAdmin в папку Applications. После этого pgAdmin можно запустить из папки приложений.

Linux. Для установки выполните несколько команд в терминале. Например, для Ubuntu:

• Установите открытый ключ для репозитория.

    curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg

• Создайте файл конфигурации репозитория.

    sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

• Установите pgAdmin.

    sudo apt install pgadmin4

Запуск и подключение

После успешной установки запустите pgAdmin. При первом запуске вас попросят ввести пароль для администрирования интерфейса pgAdmin. Он будет использоваться для доступа к pgAdmin при каждом запуске.

Теперь, когда pgAdmin запущен, необходимо подключиться к серверу PostgreSQL.

В главном окне pgAdmin нажмите правой кнопкой мыши на Servers и выберите Register → Server...

Картинка с сайта: selectel.ru

В открывшемся окне создания нового подключения укажите:

• Name — имя для вашего сервера. Например, Selectel.

Картинка с сайта: selectel.ru

Перейдите на вкладку Connection и заполните следующие поля.

• Host name/address — адрес сервера PostgreSQL. Если вы работаете локально, используйте localhost.

• Port — порт для подключения к серверу. Мы советуем использовать 5433 – порт для подключения через пулер соединений, который позволяет снизить нагрузку на PostgreSQL.
• Maintenance Database — имя базы данных, которое вы задали в панели управления.
• Username — имя пользователя из панели управления.
• Password — пароль пользователя PostgreSQL.

Картинка с сайта: selectel.ru

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

Если при попытке добавления нового сервера вы получаете ошибку вида:

    Unable to connect to server: connection failed: connection to server at "127.0.0.1" port 5433 failed: FATAL: password authentication failed for user "postgres" connection to server at "127.0.0.1", port 5433 failed: FATAL: password authentication failed for user "postgres"

… подключитесь к PostgreSQL через терминал и сбросьте пароль для указанного пользователя:

    sudo -i -u postgres 

    psql

    \password postgres

    [Указать новый пароль]

    [Повторить ввод нового пароля]

Создание БД

После подключения к серверу PostgreSQL через pgAdmin для создания новой базы данных выполните следующие шаги:

В левом меню pgAdmin найдите подключенный сервер, нажмите на него правой кнопкой мыши и выберите Create → Database…

В открывшемся окне заполните поля:

• Database. Введите название для новой базы данных, например, database_from_pgadmin.
• Owner. Выберите пользователя, который будет владельцем базы данных. По умолчанию это postgres, но можно выбрать любого другого пользователя.

Нажмите Save, чтобы создать базу данных.

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

Теперь ваша база данных PostgreSQL отображается в списке pgAdmin. Создадим таблицу в одной из схем новой БД.

Разверните список Servers, выберите нужный сервер и базу данных, в которой хотите создать таблицу. Например, Databases → selectel_db → Schemas → public → Tables.

Картинка с сайта: selectel.ru

Нажмите правой кнопкой мыши на Tables в дереве объектов и выберите Create → Table…

Заполните следующие поля.

• Name — название таблицы. Например, employees.
• Owner — пользователь, который будет владельцем таблицы.
• Schema — обычно используется схема public, но если у вас есть другие схемы, можете выбрать нужную.

Перейдите на вкладку Columns для определения столбцов таблицы. Нажмите на кнопку +, чтобы добавить новый столбец, и укажите следующие параметры.

• Name — имя столбца, например, id, first_name, last_name, email.
• Data type — тип данных столбца. Например, serial, varchar, integer, и т.д.
• Primary Key? — если это ключевой столбец, например, id, поставьте галочку напротив чекбокса.
• Not NULL? — если столбец должен быть обязательным, отметьте этот параметр.

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

Как создать БД в командной строке

Для тех, кто предпочитает работу через терминал, PostgreSQL предоставляет утилиту командной строки — psql.

Для установки psql на Linux используйте консольную команду:

    sudo apt install postgresql postgresql-contrib

Подключение к БД

1. Откройте терминал. Подключитесь к серверу PostgreSQL:

    psql -h <IP_server> -p <port> -U <username> -d <db_name>

Базовые настройки через psql

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

Параметры конфигурации PostgreSQL

PostgreSQL позволяет настраивать различные параметры конфигурации: кодировку данных, язык сообщений, уровень логирования и многое другое. Для этого используются команды языка SQL. Чтобы изменить текущие настройки сеанса, используйте команду SET. Например, для изменения языка сообщений на русский:

    SET lc_messages = 'ru_RU.UTF-8';

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

    SET client_encoding = 'UTF8';

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

    SHOW lc_messages;

Управление пользователями и ролями

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

Чтобы добавить нового пользователя в PostgreSQL, введите команду CREATE ROLE или CREATE USER. Например:

    CREATE USER myuser WITH PASSWORD 'mypassword';

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

    ALTER USER myuser CREATEDB;

Получение справки и помощи в psql

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

Команда \help в psql выводит справку по командам SQL. Например, чтобы узнать больше о команде CREATE DATABASE, выполните:

    \help CREATE DATABASE

Помимо SQL-команд, psql поддерживает множество встроенных команд, которые начинаются с обратного слэша (\). Вот некоторые из наиболее полезных:

• \l — выводит список всех баз данных;
• \dt — отображает список всех таблиц в текущей базе данных;
• \du — выводит список всех пользователей и ролей;
• \c <db_name> — подключение к конкретной базе данных;
• \dn — выводит список всех схем в базе данных.

Чтобы узнать больше о встроенных командах, используйте \? в терминале psql. Например, чтобы увидеть список всех доступных команд psql:

    \?

Создание базы данных: CREATE DATABASE

Теперь перейдем к созданию новой базы данных. В PostgreSQL для этого используется команда CREATE DATABASE:

    CREATE DATABASE <db_name>;

После создания базы данных для дальнейшей работы следует подключиться к ней с помощью команды:

    \c <db_name>

Работа с таблицами 

Создание таблицы: CREATE TABLE

Теперь, когда вы подключены к БД, можно приступить к созданию таблицы. В PostgreSQL таблицы создаются с помощью команды CREATE TABLE.

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

    CREATE TABLE IF NOT EXISTS employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    hire_date DATE
);

В этом примере создается таблица employees с пятью столбцами:

• employee_id — уникальный идентификатор сотрудника, создается автоматически при помощи типа данных SERIAL;
• first_name и last_name — текстовые поля для хранения имени и фамилии;
• department — строка, обозначающая отдел, к которому относится сотрудник;
• hire_date — дата приема на работу.

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

    \d employees

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

Удаление таблицы: DROP TABLE

Для удаления таблицы из базы данных PostgreSQL используется SQL-запрос:

    DROP TABLE <db_name>;

Создание резервной копии БД: pg_dump, pg_restore

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

Создание

    pg_dump -U <username> -h <host> -p <port> -d <database_name> -F c -f /path/to/backup_file.backup

• -U <username> — имя пользователя PostgreSQL;
• -h <host> — адрес сервера базы данных, например, localhost;
• -p <port> — порт для подключения к серверу;
• -d <db_name> — имя базы данных, которую нужно сохранить;
• -F c — формат архива, в данном случае c = custom;
• -f /path/to/backup_file.backup — путь и имя файла для сохранения резервной копии.

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

    pg_dump -U postgres -d employees -F c -f ~/backups/mydb_backup.backup

Восстановление

Чтобы восстановить базу данных из резервной копии, созданной с помощью pg_dump, можно воспользоваться утилитой pg_restore. Она позволяет восстановить данные из резервной копии, выполненной в формате архива.

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

    createdb -U <username> -h <host> -p <port> <new_db_name>

Затем восстановите данные из резервной копии. В этом поможет команда pg_restore:

    pg_restore -U <username> -h <host> -p <port> -d <new_db_name> /path/to/backup_file.backup

И в конце убедитесь, что данные восстановлены корректно, проверив таблицы в новой БД.

Заключение

В статье подробно рассказали, как создать базу данных в PostgreSQL различными способами: в панели управления Selectel, с помощью графического интерфейса pgAdmin и через командную строку утилитой psql.

Узнать больше о работе с PostgreSQL можно в одном из наших курсов в Академии Selectel.

PostgreSQL — одна из самых популярных и мощных систем управления реляционными базами данных (СУБД). У неё открытый исходный код, плюс она абсолютно бесплатная. Система помогает создавать и хранить базы данных, а также работать с ними на языке SQL (Structured Query Language). Без СУБД это будет невозможно, поскольку технически БД — это просто файлы в определённом формате. 

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

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

В прошлом году PostgreSQL заняла четвёртую строчку в рейтинге самых популярных СУБД, уступив только Oracle, MySQL и Microsoft SQL Server. Вот несколько её преимуществ: 

• Опенсорс. PostgreSQL — это СУБД с открытым исходным кодом. Это значит, что она доступна для использования, модификации и распространения без каких-либо лицензионных сборов. 
• Надёжность. Эта система предоставляет несколько уровней защиты данных и механизмов восстановления после сбоев, что гарантирует минимальные потери данных и стабильную работу системы. Среди механизмов надежности — журналирование транзакций (Write-Ahead Logging, WAL) и поддержка транзакции ACID.
• Полная поддержка SQL-стандартов. PostgreSQL позволяет выполнять сложные запросы SQL, включая вложенные и агрегированные, поддерживает различные типы данных от базовых числовых и строковых типов до сложных JSON и XML, а также предлагает расширенные функции: оконные функции, общие табличные выражения (CTE) и множество встроенных функций для работы с датами, строками и географическими данными.
• Объектно-реляционная модель. СУБД поддерживает одновременно реляционный подход, то есть данные хранятся в виде записей, которые связаны друг с другом с помощью relations (отношения), и объектный подход, то есть данные представлены в виде объектов с атрибутами и методами, как в объектно-ориентированном программировании (ООП). 
• Поддержка JSON и NoSQL. PostgreSQL предлагает средства для работы с JSON-документами, что позволяет использовать его как гибридную СУБД, объединяющую реляционные и NoSQL подходы. Она поддерживает два формата хранения JSON: JSON (текстовый) и JSONB (бинарный). JSONB оптимизирован для быстрого поиска и индексирования.
• Мощные средства индексирования. Среди типов индексов в этой системе управления БД — B-Tree, Hash, GIN и GiST, BRIN.  
• Возможность использовать другие языки программирования. Postgre позволяет писать собственные функции и поддерживает больше языков программирования, чем остальные СУБД. Среди них — C, C++, Python, Java, PHP, Ruby, Lua и даже JavaScript (есть поддержка V8). 

Кстати, на основе PostgreSQL создана отечественная СУБД Postgres Pro — в качестве замены Oracle. В рамках импортозамещения российским аналогом пользуются многие крупные компании и организации, например, Сбер, ФНС, Минфин, «Газпромнефть» и другие.

Как установить и запустить PostgreSQL

Перед тем как приступить к установке PostgreSQL, нужно определить, какая версия вам нужна. Каждая версия PostgreSQL включает обновления, улучшения и новые функции — выбор зависит от совместимости с вашими приложениями. Мы будем рассматривать установку и запуск на трёх операционных системах на примере последней 16 версии. 

Для работы с PostgreSQL на Windows и macOS может понадобится графический интерфейс pgAdmin. В нем есть Query Tool, с помощью которого пишутся и выводятся SQL-запросы. 

Установка и запуск на Windows

1. Скачайте установочный файл с официального сайта PostgreSQL.
2. Запустите установку. В появившемся мастере установки следуйте этим шагам:

• Выберите директорию установки и отметьте галочкой эти компоненты:

Картинка с сайта: tproger.ru

Компоненты для установки PostgreSQL

• Укажите данные и задайте пароль для суперпользователя (обычно это postgres): 

Картинка с сайта: tproger.ru

Меню для ввода пароля

• Настройте порт для сервера (по умолчанию это 5432).
• Определите каталог для хранения данных.

3. После установки откройте pgAdmin и подключитесь к вашему новому серверу. Уже в этом интерфейсе вы будете создавать базы данных и таблицы и писать SQL-запросы. 

Картинка с сайта: tproger.ru

Интерфейс pgAdmin

Установка на macOS

Установить СУБД на macOS можно тремя способами. 

1. С помощью интерактивного установщика от EDB. Его нужно скачать с официального сайта. Утилита уже включает в себя сервер PostgreSQL, pgAdmin и StackBuilder — менеджер пакетов, который можно использовать для загрузки и установки дополнительных инструментов и драйверов.  
2. Postgreapp. Это полнофункциональная программа для установки PostgreSQL, которая работает как стандартное приложение для Mac. Она включает в себя все необходимое для начала работы, даже популярное расширение PostGIS для геоданных.
3. Через Homebrew. Homebrew — популярный пакетный менеджер для macOS. Убедитесь, что он установлен, и выполните команду:

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

			initdb /usr/local/var/postgres
pg_ctl -D /usr/local/var/postgres start

		

Убедитесь, что сервер работает с помощью команды psql:

Установка на Linux

• Для установки на Ubuntu/Debian используйте команду в консоли:

			sudo apt install -y postgresql-common
		

			sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
		

Для CentOS/RHEL:

			sudo yum install postgresql-server postgresql-contrib
		

На Ubuntu и Debian PostgreSQL автоматически инициализируется и запускается. 

• На CentOS и RHEL выполните:

			sudo postgresql-setup initdb
sudo systemctl start postgresql

		

На Linux тоже есть pgAdmin — его нужно скачать отдельно, однако он открывается не в приложении, а окне браузера. Однако большинство разработчиков, использующих эту операционную систему, работают с PostgreSQL в основном в консоли с помощью psql.  

Основные команды SQL

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

CREATE — и все её вариации: CREATE DATABASE, CREATE TABLE, CREATE DOMAIN, CREATE USER и другие. 

ALTER | DROP — изменить/удалить данные СУБД.

INSERT | UPDATE | DELETE/TRUNCATE — вставить/изменить/удалить данные в таблице.

SELECT, TABLE, WITH — получить строки из таблицы или представления.

Типы данных PostgreSQL

Прежде чем создавать базы данных и таблицы, нужно разобраться в основных типах данных этой СУБД. В PostgreSQL есть базовые и расширенные типы данных. Ниже рассмотрим основные (подробнее можно почитать в документации на русском языке). 

Числовые типы

Числовые типы включают 2-, 4- и 8-байтные целые, 4- и 8-байтные числа с плавающей точкой, а также десятичные числа с задаваемой точностью. 

Картинка с сайта: tproger.ru

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

Денежные типы

Здесь все проще, тип всего один. 

Картинка с сайта: tproger.ru

Денежные типы данных

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

В SQL есть два основных символьных типа: character varying(n) и character(n), где n — положительное число. Они могут хранить текстовые строки длиной до n символов.

Картинка с сайта: tproger.ru

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

Типы даты/времени

Дата и время в PostgreSQL хранятся в целочисленном виде и считаются по Григорианскому календарю. 

Картинка с сайта: tproger.ru

Типы времени/даты

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

В СУБД есть стандартный SQL-тип boolean, который может иметь состояния «true», «false» и «unknown» (оно представляется SQL-значением NULL).

Картинка с сайта: tproger.ru

Логический тип данных 

Создаем первую таблицу в PostgreSQL

Для начала нужно завести базу данных: 

			CREATE DATABASE mydatabase
		

Это создаст новую базу данных с именем mydatabase. Затем подключаемся к нашей БД: 

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

			CREATE TABLE table_name (
    column1_name data_type [column_constraint],
    column2_name data_type [column_constraint],
    ...
    table_constraint
);

		

где 

table_name: название новой таблицы.

column1_name, column2_name: названия столбцов в таблице.

data_type: тип данных столбца (например, INTEGER, VARCHAR(100), DATE).

column_constraint: опциональные ограничения на уровне столбца (например, NOT NULL, UNIQUE).

table_constraint: опциональные ограничения на уровне таблицы (например, PRIMARY KEY, FOREIGN KEY).

Рассмотрим пример создания таблицы employees с различными типами данных и ограничениями:

			CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE NOT NULL,
    salary NUMERIC(10, 2),
    department_id INTEGER REFERENCES departments(id)
);

		

После выполнения этого SQL-запроса в вашей БД появится таблица employees с указанными столбцами и ограничениями (PRIMARY KEY, UNIQUE, NOT NULL и так далее).

Чтобы удалить базу данных или таблицу, понадобится команда drop:

			drop database <database_name>
drop table <table_name>
		

Модификация таблиц

Если вы допустили ошибку при оформлении таблицы, PostgreSQL предоставляет мощный набор команд для изменения ее структуры без необходимости удаления. 

Добавление столбца

			ALTER TABLE employees ADD COLUMN bio TEXT;

		

Эта команда добавляет новый столбец bio типа TEXT в таблицу employees.

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

			ALTER TABLE employees DROP COLUMN bio;

		

Удаляет столбец bio из таблицы employees.

Изменение типа данных столбца

			ALTER TABLE employees ALTER COLUMN email TYPE VARCHAR(150);

		

Изменяет тип данных столбца email на VARCHAR(150).

Получение информации о таблице

Команда выводит информацию о структуре таблицы employees, включая список столбцов, их типы данных и ограничения.

Изменение данных в таблице

PostgreSQL также имеет много функций для модификации строк и колонок в таблице. Ниже несколько примеров. 

Добавление новой записи

			INSERT INTO employees (last_name, email) VALUES ('Ivanov', 'ivanivanov@example.com');
		

Вставляет новую запись в таблицу employees с указанными значениями для last_name и email.

Изменение существующих данных

			UPDATE employees 
SET email = 'ivan_ivanov@example.com' 
WHERE last_name = 'ivanov';

		

Обновляет адрес электронной почты пользователя с фамилией ivanov.

Удаление записей

			DELETE FROM employees 
WHERE last_name = 'ivanov';

		

Удаляет записи из таблицы employees, где last_name равен ivanov.

Запросы с командой SELECT

Выбор всех записей из таблицы: 

Фильтрация данных: 

			SELECT * FROM employees 
WHERE email LIKE '%@example.com';

		

Сортировка данных:

			SELECT * FROM employees 
ORDER BY hire_date 
DESC;
		

Команда сортирует записи по столбцу hire_date в порядке убывания.

Группировка данных:

			SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

		

Запрос вернет список департаментов и количество сотрудников в каждом из них.

Это наиболее простые запросы в PostgreSQL. Сложные будут включать, например, объединение данных через оператор JOIN (INNER JOIN, LEFT JOIN, RIGHT JOIN и другие).  

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

С помощью индексов

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

Создание индекса:

			CREATE INDEX idx_username ON employees(last_name);

		

Этот индекс ускоряет поиск записей по last_name.

Использование уникальных индексов:

			CREATE UNIQUE INDEX idx_unique_email ON employees(email);
		

Он обеспечивает уникальность значений в столбце email.

С помощью транзакций и блокировок

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

Начало и завершение транзакции: 

			BEGIN;
UPDATE employees 
SET email = 'ivan_updated@example.com' 
WHERE last_name = 'ivanov';
COMMIT;

		

Откат транзакции: 

			BEGIN; 
UPDATE employees 
SET email = 'ivan_failed@example.com' 
WHERE last_name = 'ivanov'; 
ROLLBACK;

		

PostgreSQL — мощная и многофункциональная СУБД. В этом руководстве мы рассмотрели основные аспекты установки, создания баз данных и выполнения простых SQL-запросов. На самом деле PostgreSQL не такая страшная, как кажется на первый взгляд. Главное — знать азы.