10 заметок с тегом

PostgreSQL

Найти все дублированные строки в PostgreSQL

Допустим задача: найти дубли не просто по одному полю, а сразу по нескольким полям. Например, в таблице есть поля user_id и link

id name user_id link
1 Первая запись 226 яндекс.ру
2 Вторая запись 67 гугл.ру
3 Третья запись 315 рамблер.ру
... ... ... ...
3116 Последняя запись 226 яндекс.ру

и необходимо найти все дубли, чтобы совпадали поля user_id и link. В нашем случае, это строки под id=1 и id=3116.
Можно применить следующий sql:

SELECT user_id, link, string_agg(CAST(id AS TEXT), ', ') AS ids, count(user_id) AS count
FROM our_table
GROUP BY user_id, link
HAVING count(user_id) > 1
ORDER BY user_id DESC;

Строка string_agg(CAST(id AS TEXT), ', ') AS ids необходима для того, чтобы конкатенировать id дублированных строк

12 сентября   double row   PostgreSQL

Как сортировать в PostgreSQL согласно списку конструкции WHERE

Если необходимо осуществить поиск по нескольким ID, например (4, 6, 3, 12). Видно, что список имеет неупорядоченную сортировку, и вывод результата в PostgreSQL необходимо построить согласно этому списку.
Что-то вроде того:

SELECT *
FROM "table"
WHERE id IN (4, 6, 3, 12)
ORDER BY (4, 6, 3, 12)

Т. е. чтобы конечный результат сохранил сортировку id

В данном случае нам поможет следующая конструкция:

SELECT *
FROM "table"
JOIN unnest('{4, 6, 3, 12}'::int[]) WITH ORDINALITY t(id, sort) USING (id)
ORDER  BY t.sort;
10 сентября   order by   PostgreSQL   WITH ORDINALITY

Быстрый update всех строк для поля JSONB

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

update "tableName" set columnName = jsonb_set("columnName",'{"ip"}','"192.168.0.1"');
6 сентября   jsonb   jsonb_set   PostgreSQL

Поиск по массиву в jsonb поле в PostgreSQL

Например, если в jsonb храним массив ['Berlin', 'Moscow', 'London'], то осуществить поиск по таблице можно следующей командой:

SELECT * FROM "table_name" WHERE column_title ?& array['London'];
2018   jsonb   PostgreSQL

Сброс значения auto_increment в PostgreSQL

Если вы получили подобную ошибку:

Unique violation: 7 ERROR:  duplicate key value violates unique constraint "my_table_pkey"
DETAIL:  Key (id)=(12) already exists

это означает, что primary key — поле, то, которое автоинкриментится при добавлении новых строк в БД «сломалось». И не может определить следующий id.
Для данной таблицы можно перезапустить sequence:

ALTER SEQUENCE my_table_id_seq RESTART WITH 13;

Где 13 — это число, с которого должен пойти новый отсчет SERIAL колонки, той, что автоинкриментится.

2018   PostgreSQL   sequence   Unique violation

Сделать UPDATE с JOIN в PostgreSQL

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

UPDATE TableA AS a
SET param_from_table_a=FALSE
FROM TableB AS b
WHERE b.id=a.param_id AND a.amount <> 0; 
2017   PostgreSQL   sql   sql update   update & join

Преобразовать timestamp в формат date

Когда нужно преобразовать имеющийся timestamp в date да и с часовым поясом.

Вот так:

SELECT id, to_timestamp(create_at) at time zone 'Europe/Moscow' FROM "table" WHERE ...
2017   date   PostgreSQL   sql   Timestamp

Конвертируем поле json в jsonb в PostgreSQL

На чистом SQL это можно сделать так:

ALTER TABLE table_name ALTER COLUMN column_with_json SET DATA TYPE jsonb USING column_with_json::jsonb

Если делать миграцию на Yii2, то можно сделать так:

public function safeUp()
{
    $this->execute('ALTER TABLE table_name ALTER COLUMN column_with_json SET DATA TYPE jsonb USING column_with_json::jsonb');
}
2017   alter column   alter table   json   jsonb   migrate   php   PostgreSQL   sql

PostgreSQL: группируем данные и оборачиваем в массив

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

id author book_ids
1 1 [1, 4]
2 2 3
3 3 [2, 5, 7]

Ну давайте посмотрим, что можно с тим сделать в PostgreSQL.

Вот представим есть 3 таблицы:

  • Книги, `books`
  • Авторы, `authors`
  • Связанная таблица Книга — Автор, `book_author`.

Опишем данные:

Табл `books`:

serial id
string name, Название книги
timestamp create_date, Дата выпуска

id name create_date
1 Преступление и наказание ...
2 Анна Каренина ...
3 Мёртвые души ...
4 Братья Карамазовы ...

Табл `authors`:

serial id
string name, Имя автора
string lastname, Фамилия
string middlename, Отчество (необязательное)
datetime birth_date, День рождения автора

id name lastname middlename birth_date
1 Федор Достоевский Михайлович ...
2 Николай Гоголь ...
3 Лев Толстой Николаевич ...

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

Табл `book_author`:

serial id
int book_id
int author_id

id book_id author_id
1 1 1
2 2 3
3 3 2
4 4 1

Задача: получить сгруппированный список Авторов и написанных ими книг в виде массива, т.е:

id author book_ids
1 1 [1, 4]
2 2 3
3 3 2

SQL выражение может быть таким:

SELECT array_to_json(array_agg(concat(book_id))) as book_ids, author_id
FROM "book_author"
GROUP BY author_id;

Если же составлять такой запрос в Yii2, то выглядить это может вот так:

$query = BookAuthor::find()
    ->select([
        new Expression("array_to_json(array_agg(concat(".BookAuthor::tableName().".book_id))) as book_ids"),
        BookAuthor::tableName().'.author_id',
    ])
    ->joinWith('author')
    ->groupBy([BookAuthor::tableName().'.author_id'])
    ->indexBy('author_id');

Либо можно передать в провайдер:

$dataProvider = new ActiveDataProvider([
    'query' => $query->all(),
]);

Стоит обратить внимание, что я добавил жадной загрузки в виде joinWith('author'). Эти связанные данные лучше одним запросом достать, если планируется делать вывод во представлении всего списка.

2017   ActiveDataProvider   array_agg   array_to_json   concat   joinWith   PostgreSQL   sql   yii2

PostgreSQL: преобразование типа поля VARCHAR в INTEGER

Стандартными средствами sql это можно сделать так:

ALTER TABLE table_name ALTER COLUMN column_name TYPE integer USING (column_name::integer);

А миграцией в Yii2 можно сделать так:

public function safeUp()
{
    $this->execute('alter table {{%table_name}} alter column column_name TYPE integer USING (column_name::integer);');
}

В итоге мы получим преобразованное поле Integer.

UPD, в некоторых случаях необходимо сделать:

update "table_name" set column_name=null;
2017   alter table   PostgreSQL   sql   yii2