Ответы пользователя по тегу SQL
  • Как оптимизировать update postgresql?

    Melkij
    @Melkij
    PostgreSQL DBA
    UPDATE orders_new
    SET client_code=access.title
    FROM orders_new O

    Ну классика жанра. Если не видите self-join - то посмотрите ещё раз. Очевидно ошибочный запрос.

    https://www.postgresql.org/docs/current/sql-update.html
    Do not repeat the target table as a from_item unless you intend a self-join (in which case it must appear with an alias in the from_item).
    Ответ написан
  • Почему SELECT CASE WHEN возвращает только первое значение?

    Melkij
    @Melkij
    PostgreSQL DBA
    А вот если заглянуть в документацию...
    Day full capitalized day name (blank-padded to 9 chars)

    Действительно непонятно, почему же так срабатывает сравнение двух разных строк
    'monday' = 'monday '

    Что попросили у to_char - то вполне ожидаемо и получили. При том, в чём вообще смысл получать название дня недели, а не его номер? А вдруг Day окажется locale-specific штукой?
    Ответ написан
    1 комментарий
  • Как в PosgreSQL, в JSON добавить key:value?

    Melkij
    @Melkij
    PostgreSQL DBA
    array_to_json(array_agg(row_to_json (r)))
    не первый раз вижу такой конструкт, но не понимать зачем оно такое надо в таком переусложненном виде. Если нашли array_agg - значит могли заметить и json_agg там же, что приводит к простейшему select json_agg(r) from r

    Для json оператора конкатенации нет, возьмите jsonb:
    select jsonb_agg(r) || jsonb_build_object('city', 'SanAndreas') from tablename r;
    Ответ написан
    Комментировать
  • Как оптимизировать запрос sqlite datetime для postgresSQL date?

    Melkij
    @Melkij
    PostgreSQL DBA
    select current_date,
    current_date - interval '6 day',
    date_trunc('month', now()),
    now();

    Просто другой набор функций, выберите подходящую.
    Ответ написан
    4 комментария
  • Почему возникает ошибка "Final statement returns bigint instead of integer" в SQL функции?

    Melkij
    @Melkij
    PostgreSQL DBA
    Не соответствуют типы данных как они объявлены в выходных параметрах функции и тем, что вы пытаетесь делать в return. Должны соответствовать друг другу.

    sum у нас в pg13 существуют такие:
    melkij=> \da pg_catalog.sum
                                                  Список агрегатных функций
       Схема    | Имя | Тип данных результата | Типы данных аргументов |                     Описание                     
    ------------+-----+-----------------------+------------------------+--------------------------------------------------
     pg_catalog | sum | numeric               | bigint                 | sum as numeric across all bigint input values
     pg_catalog | sum | double precision      | double precision       | sum as float8 across all float8 input values
     pg_catalog | sum | bigint                | integer                | sum as bigint across all integer input values
     pg_catalog | sum | interval              | interval               | sum as interval across all interval input values
     pg_catalog | sum | money                 | money                  | sum as money across all money input values
     pg_catalog | sum | numeric               | numeric                | sum as numeric across all numeric input values
     pg_catalog | sum | real                  | real                   | sum as float4 across all float4 input values
     pg_catalog | sum | bigint                | smallint               | sum as bigint across all smallint input values

    Как видно, нет ни одного варианта, который бы возвращал int4

    Final statement returns too many columns действительно надо пояснять? Или прямым английским языком всё и так уже сказано?
    Ответ написан
    3 комментария
  • Указать типы для hardcoded select?

    Melkij
    @Melkij
    PostgreSQL DBA
    Не надо заниматься обфускацией запросов и пытаться сбить с толку планировщик. Он умный, но он тупой.
    Ответ написан
    Комментировать
  • Почему Barman не находит pg_receivexlog?

    Melkij
    @Melkij
    PostgreSQL DBA
    https://www.pgbarman.org/barman-2-3-released/
    5 September 2017: 2ndQuadrant is proud to announce the release of Barman version 2.3, a Backup and Recovery Manager for PostgreSQL.

    This minor release introduces support for PostgreSQL 10.
    Ответ написан
  • Как оптимизировать SQL Join + Expresssion?

    Melkij
    @Melkij
    PostgreSQL DBA
    Order by в запросе вычисляет rating для всей таблицы, а потом делает сортировку.

    А как вы ещё предлагаете считать topN? Откуда знать, что где-то в середине множества данных не найдётся новой top1 величины? Конечно надо найти все величины и затем из них выбрать топ.

    coalesce, джойны, сортировки - всё фигня на фоне count. Это для вас одна маловажная циферка (вот реально ведь критично, 157832 лайков там или 157784?) а базе идти и вычитывать всю таблицу, проверять, видна ли каждая конкретная строка именно вашей текущей транзакции, группировать и считать. Да не будет это никогда нормально работать в OLTP нагрузке, конечно.

    А задача простая и банальная. "как делать count в OLTP" описано много где - не делать count.
    Как получить бесполезную циферку быстро - зависит от потребностей.
    - спросить грубую оценку в explain и не считать вообще
    - материализовать в отдельную таблицу post_id, likes_count, dislikes_count и обновлять триггерами
    - материализовать в табличку и обновлять по таймеру, а сами счётчики считать где-то во внешнем мире - redis тот же хорошо умеет increment
    - просто кэшировать и обновлять рейтинг в фоне (как самое простое для вашей view - заменить её на materialized view + периодический refresh concurrently и повесить индекс на рейтинг)
    - ещё что угодно подходящее под вашу задачу и ваши ограничения, но по-прежнему исключающее count большого числа строк из oltp нагрузки
    Ответ написан
    1 комментарий
  • Как использовать WHERE для конкретного столбца в DO UPDATE?

    Melkij
    @Melkij
    PostgreSQL DBA
    Вас ваше выравнивание запроса путает.
    UPDATE tablename
        SET
            name = ?,
            domain = ?,
            in_chats = usersdata_mirror.in_chats || ARRAY[?]::int[]
                WHERE
                    not(usersdata_mirror.in_chats @> ARRAY[?]::int[]) and user_id = ?

    Что будет делать? Обновлять name при каждом вызове? Нет, конечно.
    А почему делающий именно это on conflict должен себя вести иначе? where относится не к полю, а ко всему do update.

    Если хотите отдельную логику для поля - то в выражении обновления конкретного поля её и напишите.
    in_chats = case when ... then ... else usersdata_mirror.in_chats end
    Ответ написан
    1 комментарий
  • Как открыть таблицу в базе данных?

    Melkij
    @Melkij
    PostgreSQL DBA
    Очевидно psql на скрине, соответственно postgresql.

    Приглашение командной строки -# говорит о том, что это уже не начало запроса, а вы что-то напечатали уже ранее. И psql считает, что вы пишете запрос далее, затем отправляет его весь целиком. Команды самого psql при этом обрабатываются так же.
    О том же самом говорит LINE 2.

    По таблицам - обратите внимание на регистр. from couriers и from Сouriers - это обращение к таблице couriers. А регистрозависимое имя синтаксически отличается по стандарту.
    Ответ написан
    5 комментариев
  • Как добавить поле в высоконагруженную базу в таблицу на продакшене где 100 миллионов записей?

    Melkij
    @Melkij
    PostgreSQL DBA
    Могу ответить про postgresql.

    без ее блокировки

    Именно в такой постановке вопроса - никак.

    С короткой блокировкой (доли секунд) - другое дело. Принципиально зависит от конкретного alter table и версии базы
    Простой alter table foo add column col; - ставите statement_timeout to '100ms' и вносите как есть.
    alter table foo add column col default const - после pg11 см. выше, до - интересное приключение
    Если default не константа - сначала внесите как default null, затем обновляете частями. как в процессе до pg11.
    Ответ написан
    Комментировать
  • Как вытащить все уникальные записи из бд?

    Melkij
    @Melkij
    PostgreSQL DBA
    distinct on требует использовать order by по этим же полям и каким-то ещё критериям определения "первой" строки.
    Если вам нужна в результате всего запроса какая-то ещё сортировка не совпадающая с этим - заверните запрос в подзапрос и пересортируйте как нужно ещё раз.
    select ... from (...) subquery order by ...
    Ответ написан
    Комментировать
  • Как в хранимой процедуре изменить текст в дефолтной ошибке на читабельный?

    Melkij
    @Melkij
    PostgreSQL DBA
    У вас нет функции add_product_4 (a INT, b TIMESTAMP, c VARCHAR(20), d VARCHAR(50), e INT, f text), но поскольку процедура вызывается как 'df') - то попробуем имеющуюся, а для этого требуется привести 6 аргумент к числу. Что невозможно.

    Итог: не имеет значения что вы напишете в процедуре, она вовсе не вызывается.

    Добавьте вариант процедуры который принимает text и проверьте в процедуре число ли в этом тексте.
    Ответ написан
  • Как присвоить результаты SQL-запроса в другую колонку?

    Melkij
    @Melkij
    PostgreSQL DBA
    Переписать в коррелированный запрос:
    update my_table set all_child_qty = (select sum(child.qty) from my_table child where child.my_path LIKE (my_table.my_path || '%'));


    или update my_table set ... from (подзапрос) where ...
    Ответ написан
    3 комментария
  • Как посмотреть существует ли столбец хоть в какой-то таблице текущей схемы?

    Melkij
    @Melkij
    PostgreSQL DBA
    select table_name from information_schema.columns where column_name = ? and table_schema = ?;


    Или то же самое через родной pg_attribute.
    Ответ написан
    Комментировать
  • Запрос в MySQL. Есть идея?

    Melkij
    @Melkij
    PostgreSQL DBA
    Для поиска самих link_id
    select link_id from tablename
    group by link_id
    having count(*) = sum(flag  = 1)

    Либо
    select id from links
    where exists(select null from tablename where flag  = 1 and link_id = links.id) -- возможно это условие вам по задаче не нужно
    and not exists(select null from tablename where flag != 1 and link_id = links.id)


    Вычитывать сами строки можно попробовать через
    select ...
    from tablename t
    where t.flag = 1
    and not exists(select null from tablename sq where sq.link_id = t.link_id and sq.flag != 1)


    При том, какой из вариантов эффективнее - критично зависит от распределения данных. При большой числе различающихся link_id с малым числом строк на каждый link_id и малым ожидаемом числе подходящих строк под задачу может быть эффективнее именно
    select ... from tablename t where t.link_id in (
    select q.link_id from tablename q
    group by q.link_id
    having count(*) = sum(flag  = 1)
    )


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

    Третий запрос по ожиданию лучше отзовётся на случай малого числа flag = 1 строк в таблице.

    Самый быстрый на чтение вариант, впрочем, всё равно не выполнять обработку этого фильтра налету, а каким-то образом его материализовать и хранить предварительно рассчитанным.
    Ответ написан
    7 комментариев
  • Как составить сложный SQL запрос?

    Melkij
    @Melkij
    PostgreSQL DBA
    Каждая проверка - целостный срез данных? То есть может быть такое, что для фразы 5 последняя проверка была в 2020-07-05, а для фразы 6 надо отматывать в 2020-07-03?

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

    select count(*) filter(where r1.position < r2.position) as pos_down,
    count(*) filter(where r1.position = r2.position) as pos_same,
    count(*) filter(where r1.position > r2.position) as pos_up from ...


    Укус второй: надо откуда-то получить r1 и r2 соответственно строки таблицы за сравниваемые срезы. Это
    tablename r1 full join tablename r2 on r1.phrase_id = r2.phrase_id where r1.... and r2....


    Доедаем слона:
    нужно дописать условие фильтрации и достать для r1 данные одного среза, для r2 - другого. Тут у вас не вполне конкретизировано что есть "предыдущая проверка". Например, достанем из таблицы tasks предыдущий task_id:
    where r2.task_id = 123 and r1.task_id in (select id from tasks where id < 123 order by id desc limit 1)
    Ответ написан
  • Как сделать запрос к 3 таблицам с условием where?

    Melkij
    @Melkij
    PostgreSQL DBA
    По одному полю с таблицы проще подзапросом.
    select 
      (select deal_id from table_1 where item_id = 9041) as deal_id_1
      (select deal_id from table_2 where item_id = 9041) as deal_id_2
      (select deal_id from table_3 where item_id = 9041) as deal_id_3
    Ответ написан
    Комментировать