Меню

Postgresql дата создания таблиц

SQL(postgresql) запрос на создание таблицы с полем даты (YYYY-MM-DD-HH)

подскажите пожалуйста команду для создания таблицы где будет поле с датой и временем типа YYYY-MM-DD-HH. Не очень понимаю какой тип данных использовать.

Например для таблицы «Test» где будут только поля «id» и «date», запрос будет выглядеть как

create table Test( ‘id’ int not null, ‘date’ . not null );

что ставить на место «. » ?

2 ответа 2

PostgreSQL для хранения времени предоставляет типы данных:

  • timestamp с точностью до микросекунд
  • date с точностью в день
  • time для времени без даты

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

timestamp

Использовать timestamp как есть. Несложно добавить небольшое ограничение, чтобы случайно не писать значения не укладывающиеся в заданную точность:

Добавленный check constraint эффективно запретит попытки писать в поле что-то вроде 2019-04-06 21:56:53 , но разрешит 2019-01-01 23:00:00

Два поля

Очевидное решение — сделать поле типа даты и отдельное поле для хранения часа

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

Собственный тип данных

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

Источник



15 полезных команд PostgreSQL

Обложка: 15 полезных команд PostgreSQL

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

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

Получение информации о базе данных

Размер базы данных

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

Результат будет представлен как число вида 41809016 .

current_database() — функция, которая возвращает имя текущей базы данных. Вместо неё можно ввести имя текстом:

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

В результате получим информацию вида 40 Mb .

Перечень таблиц

Иногда требуется получить перечень таблиц базы данных. Для этого используем следующий запрос:

information_schema — стандартная схема базы данных, которая содержит коллекции представлений (views), таких как таблицы, поля и т.д. Представления таблиц содержат информацию обо всех таблицах баз данных.

Запрос, описанный ниже, выберет все таблицы из указанной схемы текущей базы данных:

В последнем условии IN можно указать имя определенной схемы.

Размер таблицы

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

Функция pg_relation_size возвращает объём, который занимает на диске указанный слой заданной таблицы или индекса.

Имя самой большой таблицы

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

Для того, чтобы вывести информацию о самой большой таблице, ограничим запрос с помощью LIMIT :

relname — имя таблицы, индекса, представления и т.п.
relpages — размер представления этой таблицы на диске в количествах страниц (по умолчанию одна страницы равна 8 Кб).
pg_class — системная таблица, которая содержит информацию о связях таблиц базы данных.

Перечень подключенных пользователей

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

Активность пользователя

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

Работа с данными и полями таблиц

Удаление одинаковых строк

Если так получилось, что в таблице нет первичного ключа (primary key), то наверняка среди записей найдутся дубликаты. Если для такой таблицы, особенно большого размера, необходимо поставить ограничения (constraint) для проверки целостности, то удалим следующие элементы:

  • дублирующиеся строки,
  • ситуации, когда одна или более колонок дублируются (если эти колонки предполагается использовать в качестве первичного ключа).

Рассмотрим таблицу с данными покупателей, где задублирована целая строка (вторая по счёту).

Удалить все дубликаты поможет следующий запрос:

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

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

Читайте также:  Соляной бунт причины поражения таблица

Usetech , Удалённо , По итогам собеседования

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

Если допустимо удаление дубликатов без сохранения всех данных, выполним такой запрос:

Если данные важны, то сначала нужно найти записи с дубликатами:

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

Общая форма запроса на удаление описанных выше записей выглядит следующим образом:

Безопасное изменение типа поля

Может возникнуть вопрос о включении в этот список такой задачи. Ведь в PostgreSQL изменить тип поля очень просто с помощью команды ALTER . Давайте для примера снова рассмотрим таблицу с покупателями.

Для поля customer_id используется строковый тип данных varchar . Это ошибка, так как в этом поле предполагается хранить идентификаторы покупателей, которые имеют целочисленный формат integer . Использование varchar неоправданно. Попробуем исправить это недоразумение с помощью команды ALTER :

Но в результате выполнения получим ошибку:

ERROR: column “customer_id” cannot be cast automatically to type integer
SQL state: 42804
Hint: Specify a USING expression to perform the conversion.

Это значит, что нельзя просто так взять и изменить тип поля при наличии данных в таблице. Так как использовался тип varchar , СУБД не может определить принадлежность значения к integer . Хотя данные соответствуют именно этому типу. Для того, чтобы уточнить этот момент, в сообщении об ошибке предлагается использовать выражение USING , чтобы корректно преобразовать наши данные в integer :

В результате всё прошло без ошибок:

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

Например, преобразуем поле customer_id обратно в varchar , но с преобразованием формата данных:

В результате таблица примет следующий вид:

Поиск «потерянных» значений

Будьте внимательны при использовании последовательностей (sequence) в качестве первичного ключа (primary key): при назначении некоторые элементы последовательности случайно пропускаются, в результате работы с таблицей некоторые записи удаляются. Такие значения можно использовать снова, но найти их в больших таблицах сложно.

Рассмотрим два варианта поиска.

Первый способ
Выполним следующий запрос, чтобы найти начало интервала с «потерянным» значением:

В результате получим значения: 5 , 9 и 11 .

Если нужно найти не только первое вхождение, а все пропущенные значения, используем следующий (ресурсоёмкий!) запрос:

В результате видим следующий результат: 5 , 9 и 6 .

Второй способ
Получаем имя последовательности, связанной с customer_id :

И находим все пропущенные идентификаторы:

Подсчёт количества строк в таблице

Количество строк вычисляется стандартной функцией count , но её можно использовать с дополнительными условиями.

Общее количество строк в таблице:

Количество строк при условии, что указанное поле не содержит NULL :

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

Использование транзакций

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

Начнём транзакцию с помощью команды BEGIN .

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

А чтобы применить — команду COMMIT .

Просмотр и завершение исполняемых запросов

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

Для того, чтобы остановить конкретный запрос, выполним следующую команду, с указанием id процесса (pid):

Для того, чтобы прекратить работу запроса, выполним:

Работа с конфигурацией

Поиск и изменение расположения экземпляра кластера

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

Изменим расположение на другое с помощью команды:

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

Получение перечня доступных типов данных

Получим перечень доступных типов данных с помощью команды:

typname — имя типа данных.
typlen — размер типа данных.

Изменение настроек СУБД без перезагрузки

Настройки PostgreSQL находятся в специальных файлах вроде postgresql.conf и pg_hba.conf . После изменения этих файлов нужно, чтобы СУБД снова получила настройки. Для этого производится перезагрузка сервера баз данных. Понятно, что приходится это делать, но на продакшн-версии проекта, которым пользуются тысячи пользователей, это очень нежелательно. Поэтому в PostgreSQL есть функция, с помощью которой можно применить изменения без перезагрузки сервера:

Читайте также:  Таблица график посещения работы

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

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

Источник

Установка и основы работы с PostgreSQL

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

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

Установка PostgreSQL

Примечание: В данном разделе описана установка PostgreSQL в Ubuntu.

Чтобы запустить установку, введите:

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;

Закройте сессию пользователя postgres и войдите как новый пользователь:

exit
sudo su — postgres_user

Войдите в базу данных:

Синтаксис таблиц 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

Примечание: Имя столбца должно быть описательным.

Типы данных PostgreSQL

  • boolean: (или bool) объявляет значения true и false.

Символьные значения

  • char: хранит один символ
  • char (#): содержит # количество символов; свободные места будут заполнены пробелами.
  • varchar (#): хранит данные переменной длины (не в Юникоде); параметр # определяет длину строки.

Целые значения

  • smallint: целое число между -32768 и 32767.
  • int: целое число между -214783648 и 214783647.
  • serial: целое число с автоувеличением.

Числа с плавающей точкой

  • float (#): число с плавающей точкой, где # – количество битов.
  • real: 8-битное число с плавающей точкой.
  • numeric (#,after_dec): число с # количеством цифр, где after_dec – количество цифр после десятичного знака

Дата и время

  • date: дата
  • time: время
  • timestamp: дата и время
  • timestamptz: дата, время и часовой пояс
  • interval: разница между двумя значениями timestamp

Геометрические типы

  • point: хранит пару координат определённой точки.
  • line: набор точек, определяющих линию.
  • lseg: набор данных, определяющий сегмент линии.
  • box: набор данных, который определяет прямоугольник.
  • polygon: набор данных, определяющий любое закрытое пространство

Сетевые адреса

  • inet: IP-адрес
  • macaddr: MAC адреса.

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

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

  • NOT NULL: столбец не может иметь значение 0.
  • UNIQUE: значение столбца должно быть разным для каждой записи. Нуль всегда считается уникальным значением.
  • PRIMARY KEY: комбинирует первые два ограничения. Можно использовать только раз на всю таблицу.
  • CHECK: проверяет, истинно ли условие для значений в столбце.
  • REFERENCES: значение должно существовать в столбце в другой таблице.

После определения ограничений столбцов можно объявить ограничения для всей таблицы. Среди них 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)

Эта команда выведет на экран таблицу и создаст последовательность согласно типу данных serial.

Изменение таблиц в 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

Чтобы удалить таблицу PostgreSQL, введите:

DROP TABLE playground_equip;
DROP TABLE

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

ERROR: table «playground_equip» does not exist

Чтобы избежать этой ошибки, можно добавить в команду IF EXISTS; тогда таблица будет удалена, если она существует. В любом случае команда будет выполнена успешно.

DROP TABLE IF EXISTS playground_equip;
NOTICE: table «playground_equip» does not exist, skipping
DROP TABLE

В этот раз команда сообщает, что заданная таблица не найдена, но не возвращает ошибки.

Заключение

Данное руководство охватывает основы работы с таблицами PostgreSQL. Эти навыки помогут управлять данными PostgreSQL при помощи стороннего приложения или командной строки.

Источник

PostgreSQL создание, удаление, изменение таблиц

database_postgres

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

Значение по умолчанию при создании таблиц:

Значением по умолчанию может быть выражение, которое будет оцениваться при вставке значения по умолчанию (а не при создании таблицы). Типичным примером является то, что timestamp столбец имеет значение по умолчанию CURRENT_TIMESTAMP , поэтому для него устанавливается время вставки строки.

Или может потребоваться генерация серийного номера, например так:

NOT NULL — проверка на заполенность:

Проверка на уникальность:

Уникальные и заполненные

Можно записать так:

Ссылки на другие таблицы

Ссылки на другие таблицы, отношение многие ко многим

Запрещение удаления если есть ссылки и каскадное удаление

RESTRICT — запрещает удаление, если есть ссылки

CASCADE — каскадное удаление, и ссылающихся

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

Также можно использовать DROP TABLE IF EXISTS — для того чтобы не выбрасывалась ошибка, если базы не существует.

Изменение таблиц

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

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

Однако если на столбец есть ссылки, тогда он не будет удален, можно использовать CASCADE и для удаления всех ссылок

Добавление ограничений столбцу

Удаление ограничений у столбца

Чтобы удалить не нулевое ограничение:

Изменение значения по умолчанию у столбца

Для удаления значения по умолчанию у столбца:

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

Переименование столбца

Переименование таблицы

Пример создания таблицы users из Node.js

Информационная схема

В базе PostgreSQL есть информационная схема и в ней несколько таблиц.

Ниже опишу некоторые из них самые важные:

tables — позволяет получить список таблиц

например такой запрос:

Вернет все добавленные таблицы, без служебных таблиц posgres

Источник

Adblock
detector