Ответы пользователя по тегу PostgreSQL
  • Как сгруппировать ответ?

    @Akina
    Сетевой и системный админ, SQL-программист.
    WITH
    cte1 AS (
      SELECT jsonb_build_object('name',   users.name, 
                               'value',  comments.value,
                               'rating', comments.rating) single_user,
             comments.order_id,
             'comment' || ROW_NUMBER() OVER (PARTITION BY comments.order_id ORDER BY comments.id) num
      FROM users
      JOIN comments ON users.id = comments.user_id
    ),
    cte2 AS (
      SELECT jsonb_build_object('order_id', order_id) ||
             jsonb_object_agg(num, single_user) single_order
      FROM cte1
      GROUP BY order_id
    )
    SELECT jsonb_agg(single_order) final_data
    FROM cte2;

    DEMO fiddle
    Ответ написан
    Комментировать
  • Почему возникает ошибка при использовании оконной функции?

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

    Правильно:

    SELECT "Items"."itemId", sum(price) / sum( sum(price) ) OVER ()
    FROM "Items"
    INNER JOIN "Purchases" USING("itemId")
    GROUP BY "Items"."itemId"


    Т.е. оконная функция применяется не к исходному полю, а к агрегатной функции.
    Ответ написан
    2 комментария
  • Как убрать кавычки из ответа sql запроса с json?

    @Akina
    Сетевой и системный админ, SQL-программист.
    SELECT DISTINCT value::jsonb->>'name_level_1'
    ...
    Ответ написан
    Комментировать
  • Как заменить значение в поле postgresql?

    @Akina
    Сетевой и системный админ, SQL-программист.
    UPDATE table 
    SET value_column = REGEXP_REPLACE(value_column, '777\n?', '888');
    Ответ написан
    Комментировать
  • Как удалить таблицу postgresql по условию?

    @Akina
    Сетевой и системный админ, SQL-программист.
    with cte (cnt) as (
      select count(*)
      from test_sender 
      where statuse='crash'
      )
    delete 
      from test_sender 
      using cte
      where cte.cnt = 0;
    Ответ написан
  • Как правильно сделать запрос с поиском текста?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Видимый выход - парсить запрос на токены, запрашивать по каждому токену, отбирать записи с хотя бы одним вхождением, сортировать по количеству вхождений (или, например, по суммарной длине найденных токенов).
    Ответ написан
    Комментировать
  • Sequelize, как начать поиск элемента в таблице с конца?

    @Akina
    Сетевой и системный админ, SQL-программист.
    начать поиск элемента в таблице в БД с конца

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

    как получить id последнего элемента в таблице?

    Последний (при какой-то сортировке) - это первый при обратной сортировке.
    Так что надо просто указать сортировку (обратную той, которую ты считаешь "прямой") и взять только первую запись.
    Ответ написан
    1 комментарий
  • Триггер UPDATE с условием?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Пример
    CREATE FUNCTION fn_test() 
    RETURNS TRIGGER
    AS $fn_test$
    BEGIN
        IF OLD.val1 <> NEW.val1 THEN   -- задать условие
            NEW.id := NEW.id * 10;     -- выполнить действие
        END IF;
        RETURN NEW;
    END;
    $fn_test$ LANGUAGE plpgsql;

    CREATE TRIGGER tr_test
    BEFORE UPDATE ON test
    FOR EACH ROW
    EXECUTE FUNCTION fn_test();

    DEMO

    Или так:
    CREATE FUNCTION fn_test() 
    RETURNS TRIGGER
    AS $fn_test$
    BEGIN
        NEW.id := NEW.id * 10;     -- выполнить действие
        RETURN NEW;
    END;
    $fn_test$ LANGUAGE plpgsql;

    CREATE TRIGGER tr_test
    BEFORE UPDATE ON test
    FOR EACH ROW
    WHEN (OLD.val1 <> NEW.val1)   -- задать условие
    EXECUTE FUNCTION fn_test();

    DEMO
    Ответ написан
    2 комментария
  • Как создать тригер для дефолтного значения?

    @Akina
    Сетевой и системный админ, SQL-программист.
    CREATE TABLE trs (
        wallet_id INT, 
        amount DECIMAL(12, 2), 
        hash BYTEA GENERATED ALWAYS AS (sha256((wallet_id + amount) :: TEXT :: BYTEA)) STORED
    )

    из конкатенированный строки wallet_id + hash

    Вообще-то оба поля чисельные - какая в пень конкатенация-то? Но если их надо преобразовать в строку и конкатенировать, то
    CREATE TABLE trs (
        wallet_id INT, 
        amount DECIMAL(12, 2), 
        hash BYTEA GENERATED ALWAYS AS (sha256((wallet_id :: TEXT || amount :: TEXT) :: BYTEA)) STORED
    )
    Ответ написан
    1 комментарий
  • Rак записать id в Postgres?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Использовать надо INSERT .. SELECT
    WITH cte_id  AS (
        INSERT INTO words (name_word, discription_word) 
        VALUES ('cool', 'круто') 
        RETURNING Id
    )
    INSERT INTO users_words (users_id, words_id, status_learn_word)
    SELECT 649651821, id , 0
    FROM cte_id;
    Ответ написан
    Комментировать
  • Можно ли в 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 остальные две таблицы.
    Ответ написан
    1 комментарий
  • Как в PosgreSQL, в JSON добавить key:value?

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

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

    В более сложных случаях - jsonb_set(), jsonb_insert()... в общем, посмотри сам.
    Ответ написан
    Комментировать