Ответы пользователя по тегу PostgreSQL
  • Можно ли в postgres выполнить условную сортировку?

    @Akina
    Сетевой и системный админ, SQL-программист.
    select * 
    from products 
    order by type ASC,
             case WHEN type = 'books' then id      ELSE 0 END desc,
             case WHEN type = "food"  then name    ELSE 0 END asc,
             case WHEN type = "food"  then price   ELSE 0 END desc,
             case WHEN type = "relax" THEN popular ELSE 0 END asc,
             case WHEN type = "relax" THEN id      ELSE 0 END desc
    Ответ написан
    Комментировать
  • Как реализовать алгоритм экспайринга элементов в базе данных?

    @Akina
    Сетевой и системный админ, SQL-программист.
    По-моему, ты накрутил сверх меры. Всё решается куда проще.

    Структура таблицы, максимально упрощённая:
    CREATE TABLE tasks (
        id PRIMARY KEY,
        definition,
        performer_id REFERENCES performer (id)
        expired_at DATETIME
    );

    Взятие (параметры - id обработчика и id задачи):
    UPDATE tasks
    SET performer_id = @performer_idб
        expired_at  = NOW() + INTERVAL 'performing time'
    WHERE ( expired_at IS NULL or expired_at < NOW() )
      AND ( id = @task_id )

    То есть, задачу можно взять, если её ещё никто не брал, или если время ожидания ответа на задачу истекло. И в качестве бонуса - видно, что либо задачу никто не брал, либо кто-то брал (только последний, если таких было несколько) и прогавал сроки.

    performing time может либо поставляться снаружи как параметр, либо быть свойством задачи (с соотв. полем в структуре таблицы).
    Ответ написан
    8 комментариев
  • Как разбить Date и Time в PostgreSQL запросе?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Ну что за детский сад?

    SELECT '2022-04-01 09:23:45'::DATE, '2022-04-01 09:23:45'::TIME
    Ответ написан
    Комментировать
  • Postgresql как преобразовать пустую строку в "Не отправлено"?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Что такое "пустая строка", строка нулевой длины или NULL? Впрочем, оба случая накрываются конструкцией
    COALESCE(NULLIF(status, ''), 'Не отправлено')
    Если в status может быть горсть пробелов - предварительно тримануть.
    Ответ написан
    Комментировать
  • Как сделать правильный комплексный индекс?

    @Akina
    Сетевой и системный админ, SQL-программист.
    n_flag может быть 0..5
    num_status 0..20
    ...
    запись, где n_flag=0 и num_status>=10.

    Указанным условиям при равномерном распределении значений соответствует ~9% записей, т.е. 18 млн. Причём отбор по n_flag более селективен.
    Чисто теоретически оптимальным без использования фич Постгресса будет индекс (n_flag, update_date, num_status).

    DEMO fiddle. Правда, 200 млн. записей я генерить как-то не решился... но для 1 млн. записей запрос показывает 40-50 мс - по-моему, вменяемо.

    PS. Индекс называется на "комплексный", а композитный. Кроме того, этот индекс - покрывающий, т.е. для обработки запроса серверу не требуется обращаться к таблице.
    Ответ написан
  • Как составить аналогичный запрос, если поле text, а не json?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Можно ли написать такой же запрос, если поле raw не jsonb, а text?

    Ну офигеть проблема - заменить tp."raw"->>'errorCode' на tp."raw"::JSONB->>'errorCode'

    Кстати, это применимо к полю обоих типов. Просто для JSON - избыточно.
    Ответ написан
    1 комментарий
  • Как добавить свойство в каждый элемент массива в postgresql /jsonb при помощи sql?

    @Akina
    Сетевой и системный админ, SQL-программист.
    WITH cte AS (
        SELECT 'Bob' AS name, 25 AS age UNION ALL
        SELECT 'Mark'       , 30        UNION ALL
        SELECT 'Joe'        , 35
    )
    SELECT test.id,
           jsonb_build_object('items', jsonb_agg(jae.value_1 || jsonb_build_object('age', cte.age)))
    FROM test
    CROSS JOIN jsonb_array_elements(test.value->'items') AS jae (value_1)
    LEFT JOIN cte ON cte.name = jae.value_1->>'name'
    GROUP BY test.id

    DEMO
    Ответ написан
    2 комментария
  • Как объединить 2 запроса в 1 в Postgresql или как изменить запрос ниже?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Чисто технически, без понимания сути происходящего:
    WITH cte AS (
        SELECT PIP.purchase_id, SUM(PIP.product_count * PR.new_price) AS all_sum
        FROM product_in_purchase AS PIP
        RIGHT OUTER JOIN price_register AS PR ON PIP.product_id = PR.product_id
        GROUP BY PIP.purchase_id
    )
    ( SELECT * FROM cte ORDER BY all_sum ASC LIMIT 1 )
    UNION 
    ( SELECT * FROM cte ORDER BY all_sum DESC LIMIT 1 )
    Ответ написан
    4 комментария
  • Где может быть ошибка в SQL запросе?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Причина очень проста. Формально comma-style join - это алиас CROSS JOIN. Но есть подвох - приоритет comma-style join ниже приоритета explicit join. И выражение источника данных, если расставить скобки в соответствии с приоритетом, получится такое:

    from
    table1 , ( table2
               left join table1 h on table1.id = h.id
               left join table2 s on table2.id = s.id )

    Вот теперь прекрасно видно, что внутри скобки о существовании где-то там снаружи table1 ничего не известно.

    Правильно - забыть НАВСЕГДА о возможности использовать запятую. И писать вот так:
    FROM table1 
    CROSS JOIN table2
    LEFT JOIN table1 h ON table1.id = h.id
    LEFT JOIN table2 s ON table2.id = s.id


    PS. Если всё же без comma-style жизнь не мила, можно поступить так, как обычно делают для явного задания приоритета. То есть добавить задающие приоритет скобки:
    FROM (table1 , table2)
    LEFT JOIN table1 h ON table1.id = h.id
    LEFT JOIN table2 s ON table2.id = s.id

    Теперь всё в порядке - сначала выполнится запятая, а потом к результату будут LEFT JOIN остальные две таблицы.
    Ответ написан
    Комментировать
  • Как в PosgreSQL, в JSON добавить key:value?

    @Akina
    Сетевой и системный админ, SQL-программист.
    https://www.postgresql.org/docs/current/functions-...

    Оператор конкатенации JSONB - ||

    В более сложных случаях - jsonb_set(), jsonb_insert()... в общем, посмотри сам.
    Ответ написан
    Комментировать
  • Как выбрать дату последней транзакции (чтоб эта транзакция была 7 дней назад)?

    @Akina
    Сетевой и системный админ, SQL-программист.
    SELECT id AS servie_id,
           name AS servie_name,
           MAX(TO_TIMESTAMP(created_at))::DATE AS last_trans_date
    FROM services
    GROUP BY 1,2
    HAVING last_trans_date <= CURRENT_DATE - INTERVAL '7 day'
    Ответ написан
    Комментировать
  • Где ошибка в EXECUTE FORMAT?

    @Akina
    Сетевой и системный админ, SQL-программист.
    CREATE OR REPLACE FUNCTION _Foo2(st TIMESTAMP, fin TIMESTAMP)
    RETURNS TABLE (out_key INTEGER, out_tst timestamptz, out_val FLOAT) AS $$
    DECLARE  
      
    BEGIN
    
      RETURN QUERY EXECUTE FORMAT('
      SELECT %I, %I, %I
      FROM %I 
      WHERE %I BETWEEN ''%s'' AND ''%s'';
      ', 
      'in_key', 'in_tst', 'in_val', 'in_table', 'in_tst', st, fin);
    
    END;
    
    $$ LANGUAGE plpgsql;


    DEMO fiddle

    Ошибки найдёте самостоятельно...
    Ответ написан
    1 комментарий
  • Как заставить работать COUNT с GROUP BY?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Надо подсчитать количество строк в ответе запроса вида

    Вариантов несколько.

    Первый, наиболее правильный - получить это значение отдельным запросом.
    SELECT COUNT(*)  AS cnt
    FROM (
        SELECT 1
        FROM "order" AS o
             LEFT JOIN product p ON (p.order_id = o.id)
        GROUP BY o.id
        HAVING sum(p.price) >= 10
    ) x


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

    Третий - добавить соотв. поле в каждую запись выходного набора, используя оконную функцию, и взять значение из любой записи:
    SELECT o.id, COUNT(o.id) OVER () AS cnt
    FROM "order" AS o
             LEFT JOIN product p ON (p.order_id = o.id)
    GROUP BY o.id
    HAVING sum(p.price) >= 10
    Ответ написан
  • PostgreSQL как выбрать всех у кого день рожденья 16 февраля?

    @Akina
    Сетевой и системный админ, SQL-программист.
    SELECT *
    FROM table
    WHERE to_char(dob, 'MMDD') = '0216'
    Ответ написан
  • Как получить 100 записей, после записи с определённым uuid?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Использовать 2 копии таблицы. Одну для получения даты по заданному UUID, вторую для выборки записей.
    SELECT t1.*
    FROM table t1
    JOIN table t2 ON t2.uuid = @uuid1 
                 AND t1.createdAt > t2.createdAt
    ORDER BY createdAt ASC LIMIT 100
    Ответ написан
    Комментировать
  • Как задать валидатор для поля json/jsonb?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Конвертируешь в строку (jsonb -> text of json) и проверяешь регуляркой.
    select jsonb_value,
           jsonb_value::text ~ '^{"en": "[^"]+", "ru": "[^"]+"}$' 
    from test

    https://dbfiddle.uk/?rdbms=postgres_12&fiddle=afc3...

    Если поле не JSONB, а JSON, то дополнительно конвертируешь: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=2bd6...
    Ответ написан
    5 комментариев
  • Как защититься от двойного списания в многопоточном приложении?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Трата состоит из трех этапов. Чтение баланса пользователя, проверка хватает ли средств и собственно запись нового баланса.

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

    баланс юзера расчитывается на лету и не хранится в юзере. Расчитывсется на основе истории его пополнений/расходов.

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

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

    Как раз уровень изоляции. При правильно выбранном уровне хрен чего второй прочитает, пока первый не завершит свою транзакцию и не отпустит ресурсы.
    Ответ написан
    1 комментарий
  • Как вывести значение поля до выполнения UPDATE, вместе со значением после UPDATE?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Добавь в источник данных копию изменяемой таблицы, связанную по первичному ключу или иному уникальному полю/выражению, и оттуда возьми исходное значение.

    DEMO
    Ответ написан
    Комментировать
  • Postgre Sql DISTINCT массив с ORDER BY?

    @Akina
    Сетевой и системный админ, SQL-программист.
    DISTINCT - это фактически частная форма GROUP BY. А потому к ней относятся все ограничения для GROUP BY в части использования полей источника данных в оконных функциях, выражениях пост-отбора и сортировки.

    Посему используйте поле выходного набора для сортировки.
    SELECT DISTINCT (regexp_split_to_array(commend, E'\\s+'))[:3] AS first_3_words
    FROM orders 
    ORDER BY first_3_words[2];
    Ответ написан