Пользователи (роли)
Вместо привычного понятия пользователей, PostgreSQL использует концепцию ролей. Несмотря на определенные отличия, в целом можно определять роли как пользователей базы данных.
В зависимости от настройки, роль можно рассматривать как пользователя базы данных или как группу пользователей. Роли могут владеть объектами базы данных (например, таблицами) и выдавать другим ролям разрешения на доступ к этим объектам. Также можно предоставить одной роли членство в другой роли (схоже с добавлением пользователя в группу), чтобы одна роль могла использовать привилегии другой роли.
Как отмечалось выше, во время установки была автоматически создана роль postgres. Вы можете работать с СУБД из-под нее. Для этого переключитесь на сессию данного пользователя:
sudo su - postgres
После чего запустите консоль Postgres:
psql
После завершения работы вы сможете выйти из консоли Postgres командой :
postgres=# \q
Так как для каждой созданной роли Postgres предполагает наличие базы данных с таким же именем и по умолчанию подключается именно к ней, имеет смысл создавать новую роль для каждой базы. Кроме того, если имя роли совпадает с именем пользователя, созданного в системе Linux, подключение к БД также упрощается.
Создание новой роли
Создать роль из консоли системы (не psql) можно с помощью команды:
createuser -P --interactive
Ключ позволит сразу задать пароль пользователю, а запустит интерактивный режим для указания дополнительных параметров.
Система поочередно запросит имя новой роли, ее пароль и повтор пароля, а также позволит указать привилегии: сделать ли роль суперпользователем, должны ли быть права на создание баз данных и создание других ролей. Нажимайте y / n и Enter для выбора.
Создать роль из консоли Postgres можно с помощью команды CREATE ROLE.
При использовании этой команды без дополнительных опций пользователь будет создан без каких-либо привилегий, поэтому желательно сразу при создании роли указывать нужные привилегии. Полный список доступных опций можно просмотреть, выполнив:
\h CREATE ROLE
Чтобы создать новую роль выполните:
CREATE ROLE имя_роли WITH LOGIN CREATEDB CREATEROLE;
Далее задайте новому пользователю пароль:
\password имя_роли
Просмотр существующих ролей
Чтобы просмотреть созданных пользователей и их привилегии, перейдите в консоль Postgres:
psql
И выполните команду:
\du
Пример вывода:
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
tmweb | Create role, Create DB | {}
tweb | Create role, Create DB | {}
Нажмите q, чтобы закрыть вывод, и \q, если нужно выйти из консоли Postgres.
dropuser имя_роли
Либо команду в консоли Postgres:
DROP ROLE имя_роли;
Смена пароля пользователя
Для смены пароля одной из ролей подключитесь к Postgres от суперпользователя (postgres или другой роли с такими привилегиями), после чего выполните:
ALTER USER имя_роли WITH PASSWORD 'новый_пароль';
Обратите внимание, что эта операция сохранится в файле вместе с паролем в открытом виде. Из соображений безопасности рекомендуется удалить соответствующую запись из файла
Как правило, файл размещается в директории . Проверить его расположение можно в терминале системы с помощью:
grep postgres /etc/passwd | cut -d ':' -f 6
Откройте файл, указав корректный путь к нему:
sudo nano /var/lib/postgresql/.psql_history
Удалите запись с паролем и сохраните изменения.
Как вставить значение из другой таблицы INSERT INTO … SELECT …
Допустим у нас есть еще одна таблица которая по структуре точно такая же как и первая. Нам в таблицу table2 нужно вставить все строки из table1.
Вставляем значения из table1 в таблицу table2:
INSERT INTO table2 (a, b, c) SELECT a, b, c FROM table1;
Вам следует позаботиться об уникальности ключей, если они есть в таблице, в которую мы вставляем. Например при дублировании PRIMARY KEY мы получим следующее сообщение об ошибке:
/* ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY' */
Если вы делаете не какую-то единичную вставку при переносе данных, а где-то сохраните этот запрос, например в вашем PHP скрипте, то всегда перечисляйте столбцы.
Как не рекомендуется делать (без перечисления столбцов):
INSERT INTO table2 SELECT * FROM table1;
Если у вас со временем изменится количество столбцов в таблице, то запрос перестанет работать. При выполнении запроса MySQL в лучшем случае просто будет возвращать ошибку:
/* Ошибка SQL (1136): Column count doesn't match value count at row 1 */
Либо еще хуже: значения вставятся не в те столбцы.
Листинг баз данных
Вы можете подключиться к серверу PostgreSQL, используя psqlкоманду как любой системный пользователь. В зависимости от конфигурации сервера пользователю может потребоваться ввести свой пароль для подключения к терминалу psql. Чтобы получить доступ к терминалу psql от имени пользователя, в который вы вошли, просто введите psql.
Когда пакет PostgreSQL установлен, создается административный пользователь с именем «postgres». По умолчанию этот пользователь может подключиться к локальному серверу PostgreSQL без пароля.
Чтобы получить доступ к psqlтерминалу как пользователь «postgres», запустите:
Из терминала psql выполните мета-команду l или list, чтобы вывести список всех баз данных:
Вывод будет включать количество баз данных, имя каждой базы данных, ее владельца, кодирование и привилегии доступа:
Сервер PostgreSQL имеет три базы данных, созданные по умолчанию: template0, template1 и postgres. Первые два – это шаблоны, которые используются при создании новых баз данных.
Если вы хотите получить информацию о размерах баз данных, табличных пространствах по умолчанию и описаниях, используйте l+ или list+. Размер базы данных будет показан, только если текущий пользователь может подключиться к нему.
Чтобы получить список всех баз данных без доступа к оболочке psql, используйте переключатель -c, как показано ниже:
Другой способ составить список баз данных – использовать следующий оператор SQL:
В отличие от мета-команды l, приведенный выше запрос покажет только имена баз данных:
Настройка psql
psql отлично настроена и из коробки, однако я бы хотел остановиться на 2х вещах, которые использую каждый день и которые делают psql более удобным инструментом
Первая по важности — получение более удобочитаемового вывода. По умолчанию psql старается представить данные как таблицу:
Пока ширина таблицы меньше ширины экрана всё нормально, но потом строки начнут переноситься и вывод станет просто отвратительным:
Чтобы избежать этого воспользуемся командой активации “расширенного дисплея” (expanded display):
Теперь запрос будет выводиться в две колонки: в первой имя поля, во второй значение.
Ещё одна хитрость: можно включать такой режим только когда он действительно нужен (). В таком случае если таблица помещается по ширине, то будет табличный вывод, в противном случае — построчный.
Вторая возможность — указать как будет выводиться значение NULL. По умолчанию оно неотличимо от пустой строки.
Это можно исправить задав в psql символ для визуализации: . Теперь можно быть уверенным, что если в строке пусто, то это пустая строка, а не NULL.
Хотел бы рассказать ещё об одной фиче psql. Если вам, как и мне, нравятся SQL выражения в ALL-CAPS, то это можно настроить в автодополнении командой . Теперь, когда будете набирать SQL-запрос, последовательность будет автоматически преобразовываться в .
Конечно, прописывать все эти команды каждый раз при запуске psql будет слишком утомительным, так что я предлагаю прописать их один раз в ~/.psqlrc, который выполняется при каждой новой сессии psql.
Это лишь самая малость команд для настройки psql. Гораздо больше вы можете найти в th и psql’s doc.
Возможные ошибки
Input file appears to be a text format dump. please use psql.
Причина: дамп сделан в текстовом формате, поэтому нельзя использовать утилиту pg_restore.
Решение: восстановить данные можно командой psql <имя базы> < <файл с дампом> или выполнив SQL, открыв файл, скопировав его содержимое и вставив в SQL-редактор.
No matching tables were found
Причина: Таблица, для которой создается дамп не существует. Утилита pg_dump чувствительна к лишним пробелам, порядку ключей и регистру.
Решение: проверьте, что правильно написано название таблицы и нет лишних пробелов.
Причина: Утилита pg_dump чувствительна к лишним пробелам.
Решение: проверьте, что нет лишних пробелов.
Aborting because of server version mismatch
Причина: несовместимая версия сервера и утилиты pg_dump. Может возникнуть после обновления или при выполнении резервного копирования с удаленной консоли.
Решение: нужная версия утилиты хранится в каталоге /usr/lib/postgresql/<version>/bin/. Необходимо найти нужный каталог, если их несколько и запускать нужную версию. При отсутствии последней, установить.
No password supplied
Причина: нет системной переменной PGPASSWORD или она пустая.
Решение: либо настройте сервер для предоставление доступа без пароля в файле pg_hba.conf либо экспортируйте переменную PGPASSWORD (export PGPASSWORD или set PGPASSWORD).
Неверная команда \
Причина: при выполнении восстановления возникла ошибка, которую СУБД не показывает при стандартных параметрах восстановления.
Решение: запускаем восстановление с опцией -v ON_ERROR_STOP=1, например:
psql -v ON_ERROR_STOP=1 users < /tmp/users.dump
Теперь, когда возникнет ошибка, система прекратит выполнять операцию и выведет сообщение на экран.
10 ответов
Лучший ответ
Просто обновление, потому что я пробовал его в Windows 10, вам нужно указать следующий путь:
Вы можете сделать это либо через CMD, используя или из моего
Затем ищите путь.
Важно: не заменяйте уже имеющиеся ПУТЬ, просто добавьте один рядом с ними следующим образом
Обратите внимание : в Windows 10, если вы выполните следующее: компьютер => свойства => дополнительные системные настройки => переменные среды => системные переменные> выберите ПУТЬ, вы фактически получите возможность добавить новую строку. Нажмите Edit, добавьте расположение папок / bin и / lib и сохраните изменения
Затем закройте командную строку, если она открыта, а затем запустите ее снова. попробуйте psql —version Если он дает вам ответ, то все в порядке, если нет, попробуйте и посмотрите, был ли добавлен заданный вами путь или нет, и правильно ли он добавлен.
103
Hossain Mahmood Tuhin
11 Окт 2018 в 07:59
Всегда лучше установить предыдущую версию или при установке убедитесь, что вы указали ‘/ data’ в отдельной папке каталога «C: \ data»
Tshiamo Motshabi
6 Окт 2020 в 14:14
Простое решение, которое не упоминалось в этом вопросе: перезагрузите компьютер после объявления переменной пути.
Мне всегда приходится перезагружать — путь никогда не обновляется, пока я не сделаю это. И когда я перезапускаю, путь всегда обновляется.
Michael Jay
18 Апр 2020 в 19:23
Найдите свой двоичный файл, в котором он сохранен. получить путь в конечной шахте
Затем найдите свой локальный путь к данным пользователя, он в основном
Теперь все, что нам нужно, это ввести следующую команду в пути двоичного терминала:
Сделано!
1
Anupam Maurya
13 Май 2020 в 23:10
Введите этот путь в системную переменную среды .
В этом случае я использую версию 10. Если вы проверите папку postgres, вы увидите свои текущие версии.
В моем случае я использовал следующие строки в отдельных строках:
2
Divine Hycenth
16 Янв 2020 в 13:34
Даже если уже немного поздно, я решил проблему PATH, удалив все пробелы.
У меня работает сейчас.
2
CertainPerformance
17 Авг 2018 в 08:41
Убедитесь, что путь действительно ведет к исполняемым файлам. Я использую версию 11, и она не работала, пока она не была указана в качестве пути:
Возможно, так устроена версия 11, или я как-то испортил установку, но с тех пор у меня не было проблем.
5
Nate
13 Фев 2019 в 04:38
Если вы попробовали все ответы, но голова все еще кружится, не забудьте сменить версию на ту, которую вы скачали.
Например, не копируйте просто
Более ясно,
Я кружил головой. Надеюсь это поможет.
5
creamCheeseCoder
13 Дек 2018 в 04:49
У меня была ваша проблема, и она снова заработала (в Windows 7).
Моя установка сначала действительно работала. Я , а затем настроил системные переменные PATH с помощью `X0 `. Ключевое слово psql в командной строке не дало ошибок.
Я удалил указанные выше переменные PATH по одной, чтобы проверить, действительно ли они нужны. Psql продолжал работать после того, как я удалил путь к lib, но перестал работать после того, как я удалил путь к bin. Когда я вернул bin, он все еще не работал, то же самое и с lib. Я закрыл и снова открыл командную строку между попытками и проверил путь. Проблема сохранялась, хотя путь был идентичен тому, каким он был при работе. Я его перепрошил.
Я удалил и переустановил postgres. Проблема не исчезла. Это, наконец, сработало после того, как я удалил пробелы между «; C: …» в путях и повторно сохранил.
Не уверен, действительно ли виноваты были пробелы. Возможно, переменные среды просто нужно было изменить и обновить после установки.
Я также все еще не уверен, нужны ли пути как lib, так и bin, поскольку, похоже, для старых конфигураций пути существует какая-то устаревшая память. Однако я не хочу проверять это снова.
5
voluntier
27 Ноя 2016 в 09:18
Возможно, ваш сервер не запускается автоматически в Windows 10, и вам нужно запустить его самостоятельно после установки пути Postgresql, используя следующую команду в cmd:
EX :
7
Abdel-Raouf
18 Июл 2019 в 18:17
Работа с PostgreSQL из командной строки:
- -c (или –command) – запуск команды SQL без выхода в интерактивный режим
- -f file.sql — выполнение команд из файла file.sql
- -l (или –list) – выводит список доступных баз данных
- -U (или –username) – указываем имя пользователя (например postgres)
- -W (или –password) – приглашение на ввод пароля
- -d dbname — подключение к БД dbname
- -h – имя хоста (сервера)
- -s – пошаговый режим, то есть, нужно будет подтверждать все команды
- –S – однострочный режим, то есть, переход на новую строку будет выполнять запрос (избавляет от ; в конце конструкции SQL)
- -V – версия PostgreSQL без входа в интерактивный режим
Примеры:
psql -U postgres -d dbname -c «CREATE TABLE my(some_id serial PRIMARY KEY, some_text text);» — выполнение команды в базе dbname.
psql -d dbname -H -c «SELECT * FROM my» -o my.html — вывод результата запроса в html-файл.
Псевдо роль public
Псевдо роль public не видна, но про неё следует знать. Это групповая роль, в которую включены все остальные роли. Это означает, что все роли по умолчанию будут иметь привилегии наследуемые от public. Поэтому иногда у public отбирают некоторые привилегии, чтобы отнять их у всех пользователей.
Роль public по умолчанию имеет следующие привилегии:
-
для всех баз данных:
- CONNECT – это означает что любая созданная роль сможет подключаться к базам данных, но не путайте с привилегией LOGIN;
- TEMPORARY – любая созданная роль сможет создавать временные объекты во всех база данных и объекты эти могут быть любого размера;
-
для схемы public:
- CREATE (создание объектов) – любая роль может создавать объекты в этой схеме;
- USAGE (доступ к объектам) – любая роль может использовать объекты в этой схеме;
-
для схемы pg_catalog и information_schema
USAGE (доступ к объектам) – любая роль может обращаться к таблицам системного каталога;
:
-
для всех функций
EXECUTE (выполнение) – любая роль может выполнять любую функцию. Ещё нужны ещё права USAGE на ту схему, в которой функция находится, и права к объектам к которым обращается функция.
:
-
![Psql интерактивный терминал postgresql [айти бубен]](https://fuzeservers.ru/wp-content/uploads/8/0/e/80e1e27016fca006e9a6dc4d803eabd5.png)
Это сделано для удобства, но снижает безопасность сервера баз данных.
Получение данных. Команда Select
Последнее обновление: 20.03.2018
Для извлечения данных из БД применяется команда SELECT. В упрощенном виде она имеет следующий синтаксис:
SELECT список_столбцов FROM имя_таблицы
Например, пусть ранее была создана таблица Products, и в нее добавлены некоторые начальные данные:
CREATE TABLE Products ( Id SERIAL PRIMARY KEY, ProductName VARCHAR(30) NOT NULL, Manufacturer VARCHAR(20) NOT NULL, ProductCount INTEGER DEFAULT 0, Price NUMERIC ); INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price) VALUES ('iPhone X', 'Apple', 3, 36000), ('iPhone 8', 'Apple', 2, 41000), ('Galaxy S9', 'Samsung', 2, 46000), ('Galaxy S8 Plus', 'Samsung', 1, 56000), ('Desire 12', 'HTC', 5, 28000);
Получим все объекты из этой таблицы:
SELECT * FROM Products;
Символ звездочка * указывает, что нам надо получить все столбцы.
Однако использование символа звездочки * считается не очень хорошей практикой, так как, как правило, не все столбцы бывают нужны. И более
оптимальный подход заключается в указании всех необходимых столбцов после слова SELECT. Исключение составляет тот случай, когда надо получить данные по абсолютно всем столбцам таблицы.
Также использование символа * может быть предпочтительно в таких ситуациях, когда в точности не известны названия столбцов.
Если нам надо получить данные не по всем, а по каким-то конкретным столбцам, то тогда все эти спецификации столбцов перечисляются через запятую после SELECT:
SELECT ProductName, Price FROM Products;
Спецификация столбца необязательно должна представлять его название. Это может быть любое выражение, например, результат арифметической операции.
Так, выполним следующий запрос:
SELECT ProductCount, Manufacturer, Price * ProductCount FROM Products;
Здесь при выборке будут создаваться три столбца. Причем третий столбец представляет значение столбца Price, умноженное на значение столбца
ProductCount, то есть совокупную стоимость товара.
С помощью оператора AS можно изменить название выходного столбца или определить его псевдоним:
SELECT ProductCount AS Title, Manufacturer, Price * ProductCount AS TotalSum FROM Products;
В данном случае результатом выборки являются данные по 3-м столбцам. Для первого столбца определяется псевдоним Title, хотя в реальности
он будет представлять столбец ProductName. Второй столбец сохраняет свое название — Manufacturer. Третий столбец TotalSum хранит произведение столбцов
ProductCount и Price.
НазадВперед
Резервные копии (экспорт и импорт дампа)
При установке PostgreSQL на сервер устанавливаются утилиты и , с помощью которых вы сможете из консоли Linux создавать резервные копии базы данных (pg_dump) и восстанавливать данные из них ().
Создание резервной копии
Чтобы создать резервную копию базы и сохранить ее на сервере, необходимо выполнить команду:
pg_dump -h хост -U имя_роли -F формат_дампа -f путь_к_дампу имя_базы
Параметры:
- — сервер, на котором располагается база; может быть указан localhost, IP-адрес или домен;
- — имя пользователя PostgreSQL, под которым вы подключаетесь к базе;
- — формат, в котором будет сохранен дамп; указывается буквами c, t или p: ‘с’ (custom — архив .tar.gz), ‘t’ (tar — архив .tar), ‘p’ (plain — текстовый файл без сжатия, как правило, .sql);
- — путь сохранения для файла дампа и имя файла;
- — имя базы данных, для которой создается резервная копия.
Например:
pg_dump -h localhost -U tmweb -F c -f /home/user/backups/dump.tar.gz tmweb
После выполнения команды будет запрошен пароль пользователя Postgres, указанного в команде (в примере — tmweb).
Восстановление из дампа
Импорт дампов, сохраненных в форматах .tar.gz и .tar, выполняется с помощью :
pg_restore -h хост -U имя_роли -F формат_дампа -d имя_базы путь_к_дампу
Параметры:
- — сервер, на котором располагается база; может быть указан localhost, IP-адрес или домен;
- — имя пользователя PostgreSQL, под которым вы подключаетесь к базе;
- — формат, в котором был сохранен дамп; необходимо указать ‘с’ для архива .tar.gz и ‘t’ для архива .tar;
- — имя базы данных, в которую импортируется дамп;
- — путь к файлу дампа и имя файла.
Например:
pg_restore -h localhost -U tmweb -F c -d new_db /home/user/backups/dump.tar.gz
Для импорта дампов в формате .sql используется cat:
cat путь_к_дампу | psql -h хост -U имя_роли имя_базы
Параметры:
- — путь к файлу дампа и имя файла;
- — сервер, на котором располагается база; может быть указан localhost, IP-адрес или домен;
- — имя пользователя PostgreSQL, под которым вы подключаетесь к базе;
- — имя базы данных, в которую импортируется дамп.
Например:
cat /home/user/backups/dump.sql | psql -h localhost -U tmweb new_db
Автоматический выбор хоста-мастера
С библиотекой libpq
Чтобы гарантированно подключиться к хосту-мастеру, укажите FQDN всех хостов кластера в аргументе host и передайте параметр target_session_attrs=read-write . Этот параметр поддерживается библиотекой libpq начиная с версии 10:
Чтобы обновить версию библиотеки, которую использует утилита psql :
Для дистрибутивов Linux на основе Debian — установите пакет postgresql-client-10 (например, через apt-репозиторий).
Для ОС, использующих RPM-пакеты, дистрибутив PostgreSQL доступен в yum-репозитории.
Адреса всех хостов в кластере БД можно найти на странице нужного кластера в консоли управления.
С драйвером, поддерживающим только один хост
Если ваш драйвер для подключения к базе данных не позволяет передавать несколько хостов в строке подключения (например, pgx в Go), вы можете подключаться на специальный хост вида c- .rw.mdb.yandexcloud.net .
Это доменное имя всегда указывает на текущий мастер в кластере. Например, для кластера с идентификатором c9qash3nb1v9ulc8j9nm к мастеру можно подключиться так:
После установки
После установки проверьте версию установленного PostgreSQL
postgres -V
postgres (PostgreSQL) 9.2.24
Расположение файлов с настройками, например,
postgresql.conf
можно получить выполнив
-bash-4.2$ su — postgres -c «psql -c ‘SHOW config_file;'»
Password:
config_file
————————————-
/var/lib/pgsql/data/postgresql.conf
(1 row)
В этом примере директория, которая содержит настройки это
Полезно изучить её содержание
ll /var/lib/pgsql/data/
total 48
drwx——. 7 postgres postgres 67 Jun 9 22:54 base
drwx——. 2 postgres postgres 4096 Jun 9 23:19 global
drwx——. 2 postgres postgres 18 Jun 9 13:54 pg_clog
-rw——-. 1 postgres postgres 4371 Jun 10 01:23 pg_hba.conf
-rw——-. 1 postgres postgres 1636 Jun 9 13:54 pg_ident.conf
drwx——. 2 postgres postgres 58 Jun 10 00:00 pg_log
drwx——. 4 postgres postgres 36 Jun 9 13:54 pg_multixact
drwx——. 2 postgres postgres 18 Jun 9 14:14 pg_notify
drwx——. 2 postgres postgres 6 Jun 9 13:54 pg_serial
drwx——. 2 postgres postgres 6 Jun 9 13:54 pg_snapshots
drwx——. 2 postgres postgres 25 Jun 10 02:06 pg_stat_tmp
drwx——. 2 postgres postgres 18 Jun 9 13:54 pg_subtrans
drwx——. 2 postgres postgres 6 Jun 9 13:54 pg_tblspc
drwx——. 2 postgres postgres 6 Jun 9 13:54 pg_twophase
-rw——-. 1 postgres postgres 4 Jun 9 13:54 PG_VERSION
drwx——. 3 postgres postgres 60 Jun 9 13:54 pg_xlog
-rw——-. 1 postgres postgres 19889 Jun 10 01:43 postgresql.conf
-rw——-. 1 postgres postgres 45 Jun 9 14:14 postmaster.opts
-rw——-. 1 postgres postgres 92 Jun 9 14:14 postmaster.pid
Изменение таблиц
Последнее обновление: 19.03.2018
Нередко возникает изменить уже имеющуюся таблицу, в частности, добавить или удалить столбцы, изменить тип столбцов и т.д..
То есть потребуется изменить определение таблицы. Для этого применяется выражение ALTER TABLE,
которое имеет следующий формальный синтаксис:
ALTER TABLE название_таблицы
{ ADD название_столбца тип_данных_столбца |
DROP COLUMN название_столбца |
ALTER COLUMN название_столбца параметры_столбца |
ADD определение_ограничения |
DROP имя_ограничения}
Рассмотрим некоторые возможности по изменению таблицы.
Добавление нового столбца
Добавим в таблицу Customers новый столбец Phone:
ALTER TABLE Customers ADD Phone CHARACTER VARYING(20) NULL;
Здесь столбец Phone имеет тип , и для него определен атрибут , то есть
столбец допускает отсутствие значения. Но что если нам надо добавить столбец, который не должен принимать значения NULL? Если в таблице есть
данные, то следующая команда не будет выполнена:
ALTER TABLE Customers ADD Address CHARACTER VARYING(30) NOT NULL;
Поэтому в данном случае решение состоит в установке значения по умолчанию через атрибут :
ALTER TABLE Customers ADD Address CHARACTER VARYING(30) NOT NULL DEFAULT 'Неизвестно';
Удалим столбец Address из таблицы Customers:
ALTER TABLE Customers DROP COLUMN Address;
Изменение типа столбца
Для изменения типа применяется ключевое слово TYPE. Изменим в таблице Customers тип данных у столбца FirstName на (он же ):
ALTER TABLE Customers ALTER COLUMN FirstName TYPE VARCHAR(50);
Изменение ограничений столбца
Для добавления ограничения применяется оператор SET, после которого указывается ограничение.
Например, установим для столбца FirstName ограничение :
ALTER TABLE Customers ALTER COLUMN FirstName SET NOT NULL;
Для удаления ограничения применяется оператор DROP, после которого указывается ограничение.
Например, удалим выше установленное ограничение:
ALTER TABLE Customers ALTER COLUMN FirstName DROP NOT NULL;
Изменение ограничений таблицы
Добавление ограничения CHECK:
ALTER TABLE Customers ADD CHECK (Age > 0);
Добавление первичного ключа :
ALTER TABLE Customers ADD PRIMARY KEY (Id);
В данном случае предполагается, что в таблице уже есть столбец Id, который не имеет ограничения PRIMARY KEY. А с помощью вышеуказанного скрипта устанавливается ограничение PRIMARY KEY.
ALTER TABLE Customers ADD UNIQUE (Email);
При добавлении ограничения каждому из них дается определенное имя. Например, выше добавленное ограничение для CHECK будет называться
. Имена ограничений можно посмотреть в таблице через pgAdmin.
Также мы можем явным образом назначить ограничению при добавлении имя с помощью оператора CONSTRAINT.
ALTER TABLE Customers ADD CONSTRAINT phone_unique UNIQUE (Phone);
В данном случае ограничение будет называться «phone_unique».
Чтобы удалить ограничение, надо знать его имя, которое указывается после выражения . Например, удалим выше добавленное ограничение:
ALTER TABLE Customers DROP CONSTRAINT phone_unique;
Переименование столбца и таблицы
Переименуем столбец Address в City:
ALTER TABLE Customers RENAME COLUMN Address TO City;
Переименуем таблицу Customers в Users:
ALTER TABLE Customers RENAME TO Users;
НазадВперед
Изменить несколько столбцов в таблице
Синтаксис
Синтаксис для изменения нескольких столбцов в таблице в PostgreSQL (используя ALTER TABLE):
ALTER TABLE table_name
ALTER COLUMN column_name TYPE column_definition,
ALTER COLUMN column_name TYPE column_definition,
…
;
- table_name
- Имя таблицы для изменения.
- column_name
- Имя столбца, который нужно изменить в таблице.
- column_definition
- Измененный тип данных столбца.
Пример
Рассмотрим пример, который показывает, как изменить несколько столбцов в таблице PostgreSQL с помощью оператора ALTER TABLE.
Например:
PgSQL
ALTER TABLE order_details
ALTER COLUMN notes TYPE varchar(500),
ALTER COLUMN quantity TYPE numeric;
|
1 |
ALTERTABLEorder_details ALTERCOLUMNnotesTYPEvarchar(500), ALTERCOLUMNquantityTYPEnumeric; |
В этом примере ALTER TABLE будут изменены два столбца таблицы order_details — notes и quantity.
Поле notes будет изменен на тип данных varchar(500), а столбец quantity будет изменен на тип данных numeric.
Мета-команды PostgreSQL
Теперь, когда ты все настроил и готов приступить к работе с базой данных, осталось разобрать несколько мета-команд.
Это не SQL запросы, а команды специфичные для PostgreSQL.
В других системах управления базами данных есть их аналоги, но их синтаксис немного отличается.
Всем мета-командам предшествует обратная косая черта , за которой следует фактическая команда.
Список всех баз данных
Чтобы получить список всех баз данных на сервере, ты можешь использовать команду .
Ввод этой мета-команды в оболочке Postgres выведет:
Это список всех имеющихся баз данных и служебная информация, такая как владелец базы данных, кодировка и права доступа.
На данный момент мы пока ничего не создали, а базы данных которые ты видишь на экране — создаются по умолчанию при установке Postgres.
- postgres — это просто пустая база данных.
- «template0» и «template1» — это служебные базы данных, которые служат шаблоном для создания новых баз.
Тебе пока не стоит беспокоиться о них. Если хочешь изучить все детали, то проверь официальную документацию.
Подключаемся к базе данных PostgreSQL
Некоторые команды SQL требуют, чтобы ты сначала вошел в базу данных (например, для создания новой таблицы).
Ты можешь выбрать, в какую базу данных входить, при запуске SQL Shell.
Когда ты находишься внутри оболочки (shell), то можешь использовать команду (или ), за которой следует имя
базы данных. Если бы у тебя была другая база данных под названием , то подключиться к ней можно было бы так:
Полностью в терминале у тебя получится что-то такое:
Обрати внимание, что приглашение оболочки изменилось с на. Это значит, что теперь ты
подключен к базе данных , а не
Получить список всех таблиц в базе данных
Как и в случае со списком существующих баз данных, ты можешь получить список таблиц внутри конкретной базы данных
с помощью команды .
Перед выполнением этой команды вам необходимо войти в базу данных.
Предположим, ты уже находишься внутри базы , и в ней есть таблица с именем. Набрав , ты
получишь следующее:
Ты можешь увидеть имя таблицы и некоторую другую информацию, такую как схема (мы обсудим схемы в более сложных
руководствах) и владельца.
Владелец (owner) — это пользователь, который создал таблицу.
Если ты создаешь других пользователей и используешь их для создания таблиц, то в последнем столбце будут именно они.
Список пользователей и ролей
Как ты уже знаешь, при установке Postgres создается суперпользователь с именем .
Список всех пользователей базы данных можно вывести на экран используя команду .
Обрати внимание, что первый столбец называется — роль (role name).
И весь вывод на экран называется “список ролей” (List of roles), а не список пользователей. В PostgreSQL пользователи и роли практически
одинаковы
В PostgreSQL пользователи и роли практически
одинаковы.
У ролей есть атрибуты, которые определяют их разрешения, такие как создание баз данных или даже создание
других новых ролей.
Любая роль с атрибутом LOGIN может рассматриваться, как пользователь.
Здесь мы видим только одну роль, суперпользователя по умолчанию.
В реальном мире все будет иначе, потому что использовать только суперпользователя все время опасно.
Вместо этого создают другие роли с меньшими привилегиями.
Это гарантирует, что никто не совершит нежелательных действий по ошибке.
Если у одной из ролей есть доступ только на чтение данных, то с помощью этой роли будет невозможно удалить таблицу или поле.
