Postgres
Введение
PostgreSQL (произносится «Пост-Грэс-Кью-Эл»)
— свободная объектно-реляционная система управления базами данных (СУБД).
Существует в реализациях для множества UNIX-подобных платформ, включая AIX,
различные BSD-системы, HP-UX, IRIX,
Linux
, macOS, Solaris/OpenSolaris,
Tru64, QNX, а также для
Microsoft Windows
.
Установка
Создать базу данных
Список уже существующих БД можно получить командой
\l
Name | Owner | Encoding | Collate | Ctype | Access privileges --------------+----------+----------+----------------------------+----------------------------+----------------------- urn.su | postgres | UTF8 | English_United States.1252 | English_United States.1252 | topbicyle.ru | postgres | UTF8 | English_United States.1252 | English_United States.1252 | postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres test | postgres | UTF8 | English_United States.1252 | English_United States.1252 | (6 rows)
Обратите внимание на столбцы Encoding, Collate и Ctype. Знание кодировок может Вам пригодиться в будущем.
Создадим базу данных heihei_ru_db с кодировкой utf8
CREATE DATABASE "heihei_ru_db" WITH OWNER "postgres" ENCODING 'UTF8';
CREATE DATABASE
Создадим базу данных heihei с кодировкой utf8 и укажем значения для Collate, Ctype и Template
CREATE DATABASE "heihei" WITH OWNER "postgres" ENCODING
'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C' TEMPLATE = template0;
CREATE DATABASE
Проверим, что получилось
\l
Name | Owner | Encoding | Collate | Ctype | Access privileges --------------+----------+----------+----------------------------+----------------------------+----------------------- heihei | postgres | UTF8 | C | C | urn.su | postgres | UTF8 | English_United States.1252 | English_United States.1252 | topbicyle.ru | postgres | UTF8 | English_United States.1252 | English_United States.1252 | postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres test | postgres | UTF8 | English_United States.1252 | English_United States.1252 | (7 rows)
Если я в bash сделаю
locale -a
То получу сразу четыре доступные кодировки
C
C.UTF-8
en_US.utf8
POSIX
Но создать БД, например, с en_US.utf8 у меня не получается
CREATE DATABASE "heihei" WITH OWNER "postgres" ENCODING 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8' TEMPLATE = template0;
Приводит к ошибке
ERROR: invalid locale name: "en_US.UTF-8"
Если Вы знаете как решить эту проблему - отпишитесь в комментариях к статье.
Удалить базу данных
Чтобы удалить базу данных воспользуйтесь командой
DROP DATABASE db_name;
DROP DATABASE
Проверить кодировки
Проверить кодировку сервера можно командой
SHOW SERVER_ENCODING;
server_encoding ----------------- UTF8 (1 row)
Проверить кодировку клиента
SHOW CLIENT_ENCODING;
client_encoding ----------------- WIN1252 (1 row)
Подключение к БД
Чтобы получить доступ к базам данных можно воспользоваться стандартной консолью psql.
Если Вы хотите подключиться из
bash в Windows
не забудьте добавить местоположение
psql.exe
(у меня это C:\Program Files\PostgreSQL\12\bin) в PATH
Как это делается описано в статье
PATH
psql.exe -h localhost -p 5433 -U postgres
Показать адрес текущей директории
\!
У этого способа есть побочный эффект в виде выкидывания из Postgres
Чтобы начать работу с базой данных нужно знать её имя, например
пусть Вам нужна база даныых, которую назвали просто HeiHei_ru_DB
Воспользуйтесь командой
\c HeiHei_ru_DB
You are now connected to database "HeiHei_ru_DB" as user "postgres".
Если вы работаете в Linux и база данных находится на том же хосте можно выполнить
psql -h 127.0.0.1 -d DATABASENAME -U DATABASEUSERNAME
Чтобы посмотреть список таблиц введите
\dt
List of relations
Schema | Name | Type | Owner
--------+--------+-------+----------
public | person | table | postgres
(1 row)
Чтобы посмотреть всю таблицу person уже можно использовать стандартный
SELECT * FROM person;
Запуск скрипта из файла
Сперва проверим, что с переменными окружения всё впорядке. Для этого введём
в консоль psql.exe нажмём Enter и проверим что bash не жалуется на неизвестную команду.
Если жалуется - прочитайте мои советы в статье
Системная переменная PATH
Пишем скрипт
script.sql
CREATE TABLE person (
id int,
first_name VARCHAR(50),
last_name VARCHAR(50),
gender VARCHAR(5),
date_of_birth DATE
)
Применим этот скрипт к базе данных
HeiHei_ru_DB
У меня postgres запущен локально на порту 5433. У Вас может быть
на 5432 - проверьте.
cat script.sql | psql.exe -h localhost -p5433 -U postgres HeiHei_ru_DB
Password for user postgres:
CREATE TABLE
Саме время сделать что-то более близкое к реальному скрипту
На поля таблицы нужно ввести некоторые ограничения и добавить им свойств.
CREATE TABLE booking_sites (
id BIGSERIAL NOT NULL PRIMARY KEY,
company_name VARCHAR(50) NOT NULL,
origin_country VARCHAR(50) NOT NULL,
age VARCHAR(3) NOT NULL,
date_of_birth DATE NOT NULL,
website_url VARCHAR(50)
);
Теперь запустим этот скрпит уже не в тестовую а в рабочую базу данных heihei (которая совпадает с названием сайта HeiHei.ru, но если написать .ru будет синтаксическая ошибка ERROR: syntax error at or near ".")
cat booking_sites.sql | psql.exe -h localhost -p5433 -U postgres heihei
Password for user postgres:
CREATE TABLE
Содержание базы данных
В предыдущем параграфе мы создали в базе данных heihei таблицу booking_sites
Убедимся в том, что скрипт отработал удачно
Перейдём в базу данных heihei и проверим создалась ли таблица
\c heihei
You are now connected to database "heihei" as user "postgres".
heihei=# \dt
List of relations Schema | Name | Type | Owner --------+---------------+-------+---------- public | booking_sites | table | postgres (1 row)
Описание таблицы
Если таблица создана какое-то время назад. Вы могли уже забыть, какие конкретно столбцы она содержит.
Для описания таблицы используется команда \d
\d booking_sites
Table "public.booking_sites" Column | Type | Collation | Nullable | Default ----------------+-----------------------+-----------+----------+------------------------------------------- id | bigint | | not null | nextval('booking_sites_id_seq'::regclass) company_name | character varying(50) | | not null | origin_country | character varying(50) | | not null | age | character varying(3) | | not null | date_of_birth | date | | not null | website_url | character varying(50) | | | Indexes: "booking_sites_pkey" PRIMARY KEY, btree (id)
Изменение таблицы
Когда нужно обновить название столбца таблицы используем команду ALTER
Предположим полю website_url не хватает 50 символов. Увеличим длину до 60.
ALTER TABLE booking_sites ALTER column website_url TYPE VARCHAR(60);
Проверим изменилась ли таблица
\d booking_sites
Table "public.booking_sites" Column | Type | Collation | Nullable | Default ----------------+-----------------------+-----------+----------+------------------------------------------- id | bigint | | not null | nextval('booking_sites_id_seq'::regclass) company_name | character varying(50) | | not null | origin_country | character varying(50) | | not null | age | character varying(3) | | not null | date_of_birth | date | | not null | website_url | character varying(60) | | | Indexes: "booking_sites_pkey" PRIMARY KEY, btree (id)
Импорт из .csv файла
Чтобы файл открылся нужно положить его в папку C:\Users\Public\
Иначе будет ошибка
ERROR: could not open file "C:\Users\andreyolegovich_ru\postgres_scripts\osnovnoi_products_20191104_161848-UTF8.csv" for reading: Permission denied HINT: COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy.
Выйти из консоли
\q
Узнать текущую схему
SELECT current_schema();
current_schema ---------------- public (1 row)
PostgreSQL скрипт из Bash
Чтобы выполнить Bash скрипт с командой PostgreSQL достаточно создать файл script.sh подобного содержания
#!/bin/bash
PGPASSWORD=ПАРОЛЬ psql -h ХОСТ -U ИМЯ_ПОЛЬЗОВАТЕЛЯ -d ИМЯ_БД -с "ВАША КОМАНДА"
Например
#!/bin/bash
PGPASSWORD=QWERTY psql -h 127.0.0.1 -U andrei -d urnsu -с "SELECT * FROM news"
И вызвать его
. script.sh
Если SQL скрипт большой и содержит сложный синтаксис, его не так просто подружить с синтаксисом bash.
В такой ситуации может быть полезно создать отдельный файл
sql_script.sql
и файл с bash скриптом
bash_script.sh
Из
bash_script.sh
можно вызвать
sql_script.sql
следующим образом
#!/bin/bash
cat sql_script.sql | PGPASSWORD=QWERTY psql -h 127.0.0.1 -U andrei -d urnsu
. bash_script.sh