Задать вопрос
Ответы пользователя по тегу PostgreSQL
  • Выгружает ли хэши паролей пользователей команда pg_dumpall?

    @galaxy
    Для будущих поколений ответ на заданный вопрос: да, выгружает.
    Ответ написан
    Комментировать
  • Как гарантируется порядок данных в столбцах для индекса BRIN?

    @galaxy
    Пункт 7.5 говорит о порядке строк в результатах конкретного запроса, ни к физическому порядку, ни к BRIN индексам это отношения не имеет.

    BRIN индекс вы всегда сможете применить (в смысле создать). Будет ли он использоваться, определяет планировщик, исходя из статистики данных и структуры запроса. Будет ли такой индекс эффективен, зависит действительно от данных.

    Наверно, лучше описать простыми словами, как работает BRIN, а вы уж сами думайте дальше.

    BRIN хранит небольшую выжимку об нескольких последовательных (в смысле размещения на диске) блоках данных таблицы. Поэтому он, как правило, очень эффективен по объему: по сравнению с B-tree и другими индексами он очень невелик.
    При запросе такой индекс отвечает на вопрос: совместима ли выжимка с условиями запроса, т.е. могут ли в блоках данных на диске быть подходящие под запрос строки. Например, для сортируемых типов данных индекс может хранить MIN и MAX значения колонки в пределах блоков, которые описывает выжимка.
    Индекс может выдавать ложноположительный ответ, но не может ложноотрицательный. Допустим, его спрашивают: "найди строки с x = 5", он видит у себя группу блоков с MIN=3 и MAX=20 и отвечает: "тут может быть строка с x = 5". А для группы с MIN=13 и MAX=88, например, он ничего не ответит, т.к. 5 там содержаться не может. СУБД, получив данные по такому индексу, обязана перепроверить строки на предмет ложноположительных результатов.

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

    Если данные не отсортированы, BRIN будет выдавать слишком много блоков-кандидатов, в которых будет, скорее всего, сравнительно немного подходящих строк.
    Ответ написан
    Комментировать
  • Как в EF Core (Npgsql) указать владельца базы данных и ее таблиц?

    @galaxy
    Есть такой вариант:
    REASSIGN OWNED — сменить владельца у всех объектов (в текущей БД) на другого.
    Ответ написан
    Комментировать
  • Важен порядок элементов в параметре типа список в SELECT чтобы избежать дедлока?

    @galaxy
    Тут ORDER BY code может помочь, причем сортировка должна быть в одном и том же порядке в разных сессиях.
    Ответ написан
    Комментировать
  • Как оптимизировать скорость обновления материализованного представления Postgres?

    @galaxy
    REFRESH в постгресе выполняет по сути пересоздание представления. Если у вас огромная таблица-источник, да еще куча данных упакована в JSON, тут матвью вряд ли в текущем виде спасет.

    Как часто меняются данные в JSON? Какая часть таблицы обновляется (или там вообще aphend-only)?
    Если данные меняются редко и/или лишь небольшая их часть (или вы готовы несколько пожертвовать производительностью таких UPDATE), можете триггером при обновлении/вставке JSON колонки распаковывать данные и записывать отдельную таблицу (которая раньше у вас была матвью).

    Если скорость операций обновления на исходной таблице критична, добавьте поле типа json_col_updated TIMESTAMP (можно сделать отдельную таблицу со связью 1-к-1, если исходную не хочется трогать) и обновляйте его триггером, когда меняется поле с JSON. Дальше с нужной периодичностью распаковывайте массово только обновленные данные (json_col_updated > last_unpack).
    Ответ написан
    Комментировать
  • 10к строк по базе размером 500кк?

    @galaxy
    SELECT * FROM base WHERE domain LIKE '%habr%' LIMIT 10000;


    проще всего так, наверно:

    CREATE EXTENSION pg_trgm;
    
    CREATE INDEX trgm_domain_base_idx ON base USING GIST (domain gist_trgm_ops);
    
    EXPLAIN ANALYZE SELECT * FROM base WHERE domain LIKE '%habr%' LIMIT 10000;


    https://www.postgresql.org/docs/current/pgtrgm.html
    Ответ написан
    Комментировать
  • Как написать sql-скрипт, который пройдет по всем таблицам всей БД и изменит поле row_num с varchar(10) на int4 и сделает его not null?

    @galaxy
    Запрос на изменение типа должен быть примерно такой (и обратите внимание на точку с запятой в конце запроса):
    alter table %I alter column row_num type int4 using row_num::int, alter column row_num set not null;

    (если где-то будут NULL'ы или row_num не влезет/не сконвертируется в int, будут проблемы).

    В скрипте DO $ $ BEGIN EXECUTE ( - лишнее, это же просто SELECT.
    Либо, если вы не хотите копировать вывод запроса и отдельно выполнять, можно таким скриптом:
    DO $$DECLARE q TEXT;
    BEGIN
        FOR q IN SELECT format(
                            'alter table %I alter column row_num type int4 using row_num::int, alter column row_num set not null',
                            tablename
                          )
                   FROM pg_tables WHERE schemaname = 'public'
        LOOP
            EXECUTE q;
        END LOOP;
    END$$;
    Ответ написан
    Комментировать
  • COPY в Postgresql?

    @galaxy
    Слейте все в один файл.
    Под *nix одной командой:
    tail -qn +2 files/*.txt > data.csv
    (если все файлы в одной папке files)

    Ну либо берете список файлов (из вывода dir / ls) и, допустим, в продвинутом текстовом редакторе дописываете к каждому названию куски команды COPY. Получится один большой скрипт
    Ответ написан
    1 комментарий
  • Как вернуть записи из каждой группы с минимальной датой создания?

    @galaxy
    возвращать одно рандомное из дублей, но тогда есть вероятность вывода неправильного результата

    а в чем неправильность такого результата?

    В любом случае, группировка и джойн тут избыточны:
    SELECT DISTINCT ON (project_id) *
      FROM tasks
     WHERE tasks.id = ANY (...)
     ORDER BY project_id, created
    Ответ написан
    2 комментария
  • Как сделать рекурсивный запрос с возвращением всех пройденных элементов?

    @galaxy
    with recursive q as (
        select * from tes where from_id = 1
         union all
        select tes.* from tes, q where tes.from_id = q.to_id
    )
    select * from q order by from_id, to_id

    https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/4445
    Ответ написан
  • Как разблокировать колонки в postgresql?

    @galaxy
    Нет первичного ключа (primary key), вот и блокирует. Это не Excel.

    Хотите как в экселе - ну и экспортируйте в него данные, потом обратно залейте
    Ответ написан
  • Найти поле по значению pgadmin?

    @galaxy
    Дурацкая задача какая-то...
    Ну допустим, если база небольшая, то pg_dump в текстовый формат + grep
    Ответ написан
    Комментировать
  • Как запретить ввод в таблицу пустого поля?

    @galaxy
    Где вы увидели пустой id?

    BIGSERIAL - равносильно полю типа BIGINT с DEFAULT значением из автоматически создаваемого сиквенса (сделайте \d+ myTable в psql для интереса), так что если вы не указываете значение для id в INSERT, оно берется из сиквенса.
    По этой же причине
    INSERT INTO myTable(lastName) VALUES('upsertedLastNameOnly orger') ON CONFLICT (id)
    не имеет смысла, т.к. конфликта по id тут быть не может.

    В общем, что конкретно вы хотите? Поле id у вас заполняется и не может быть пустым
    Ответ написан
    Комментировать
  • Создание таблицы в postgresql - TIME WITH TIME ZONE?

    @galaxy
    В вашем запросе вы потеряли закрывающую скобку.

    Тип TIME WITH TIME ZONE существует, но он слегка странный, т.к. без даты (а этот тип только про время внутри суток - от 00:00 до 23:59) зона несколько бессмысленна.

    Нужно хранить время (внутри суток) - TIME.
    Дату без времени - DATE.
    Временную метку - TIMESTAMP (with time zone / without time zone).
    https://www.postgresql.org/docs/current/datatype-d...

    Разница между with time zone / without time zone: TIMESTAMP WITH TIME ZONE хранится внутренне в UTC, при записи конвертируется из текущей временной зоны; TIMESTAMP WITHOUT TIME ZONE хранится как есть, без конвертаций.
    Что использовать - зависит от приложения и вообще организации работы с БД. Для одного приложения (типа вебсайта) в принципе пофигу
    Ответ написан
  • Как проверять процедуру с out-параметром через расширение plpgsql_check?

    @galaxy
    Ну там по идее сигнатура в стиле regprocedure передается. OUT-параметры на сигнатуру не влияют, так что просто:
    SELECT * from sys_addons.plpgsql_check_function('helpdesk.create_issue(json)');


    можно проверить сначала кастом к regprocedure:
    SELECT 'helpdesk.create_issue(json)'::regprocedure;
    Ответ написан
  • Как подключиться к psql на Ubuntu с использованием библиотеки aiogram?

    @galaxy
    # здесь указал пароль от учетной записи ubuntu с одноименным именем username

    Пароль учетной записи ubuntu не имеет отношения к паролю пользователя в postgres. Вы, похоже, вообще не создавали пароль там.
    Заходите в psql под username, там есть команда \password
    Ответ написан
  • Golang jetbrains не читает БД в sql?

    @galaxy
    Если я правильно понял вопрос (а это непросто), то потому что язык SQL - нечувствителен к регистру, т.е. следующие запросы эквивалентны:
    select * from Groups;
    select * from groups;
    select * from GROUPS;
    SELECT * FROM groups;
    SeLeCt * FrOm GrOuPs;


    Если вы берете название таблицы/колонки (или иного объекта БД) в кавычки, он становится (по крайней мере, согласно SQL стандарту) case-sensitive, т.е. "group"/"Groups"/"gRoUps" - будут разными идентификаторами (например, все три такие таблицы могут существовать в БД одновременно).
    Если кавычек нет, идентификаторы по стандарту сравниваются после приведения к upper-case. В Postgres это немного не так: там идентификаторы без кавычек приводятся к lower-case.

    Т.е. другими словами, запрос create table Groups ... создаст в Postgres таблицу с именем groups. Запрос select * from GrOupS; будет также искать таблицу с именем groups.
    При использовании кавычек запрос create table "Groups" ... создаст таблицу с именем Groups. Запрос select * from "GrOupS"; будет также искать таблицу с именем GrOupS.
    spoiler
    Речь выше только про Postgresql. Другие СУБД могут вести (и ведут) себя по-другому. Так, в mysql поведение идентификаторов зависит от ОС (точнее, от того, case sensitive/insensitive ее файловая система) и от конфигурации БД.


    Обычно это не вызывает проблем, пока вы не начинаете использовать закавыченные и незакавыченные идентификаторы одновременно. Например, что скорее всего имеет место в вашем случае, таблица создается запросом
    create table Groups ...
    -- реальное имя таблицы - groups

    Когда же вы делаете запрос select * from "Groups", обращение идет к несуществующей таблице Groups.

    В общем, разберитесь, какое название у таблицы в БД (в psql команда \dt, или смотрите свойства таблицы в своей среде). Если оно не lower-case, используйте кавычки с точным названием (например, select * from "GROUPS"), в противном случае лучше обходиться вообще без кавычек.
    Ответ написан
    Комментировать
  • Django не видит команды "createuser" для PostgreSQL?

    @galaxy
    В системных (или pycharm'овских) путях нет папки с бинарниками постгреса.

    Создать юзера можно и в pgAdmin (и в psql).
    Ответ написан
    2 комментария
  • SELECT если есть в стоблце?

    @galaxy
    Сделать то, что вам нужно, в Постгресе не проблема, можно даже несколькими способами. Например:
    SELECT * FROM table WHERE value = ANY(string_to_array(index, ', ')::int[])

    Можно и проиндексировать при необходимости.
    Ответ написан
    Комментировать
  • Как исправить кракозябры вместо кириллицы в дампе PostgreSQL?

    @galaxy
    Попробуйте сделать дамп так:
    pg_dump -U postgres -W -E UTF8 -d dbname -f dbname.sql

    т.к. возможно глючит перенаправление
    Ответ написан
    1 комментарий