galaxy

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

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

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

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

Запрос на изменение типа должен быть примерно такой (и обратите внимание на точку с запятой в конце запроса):

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$$;

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

Хотите как в экселе - ну и экспортируйте в него данные, потом обратно залейте

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

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

Тип 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 хранится как есть, без конвертаций.
Что использовать - зависит от приложения и вообще организации работы с БД. Для одного приложения (типа вебсайта) в принципе пофигу

Если я правильно понял вопрос (а это непросто), то потому что язык 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.


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

create table Groups ...
-- реальное имя таблицы - groups

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

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

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

Создать юзера можно и в pgAdmin (и в psql).

Сделать то, что вам нужно, в Постгресе не проблема, можно даже несколькими способами. Например:

SELECT * FROM table WHERE value = ANY(string_to_array(index, ', ')::int[])

Можно и проиндексировать при необходимости.

Если вас интересуют функциональные отличия - https://postgrespro.ru/docs/postgrespro/14/intro-p...

Ну и Postgres Pro - не бесплатная:
https://postgrespro.ru/products/postgrespro/certified
https://www.syssoft.ru/postgres-professional/postg...

Конструкция with кажется предполагает закрытие соединения с БД

не предполагает:

Cursors can be used as context managers: leaving the context will close the cursor.

Cоединение закрывает только метод close() объекта connection (или del на connection).

Делать commit() в вашем случае необходимо, т.к. по умолчанию psycopg открывает транзакцию перед выполнением первого запроса. Другой вариант - работать в режиме autocommit.

Вообще, на будущее, - конструкции с with делают в разных драйверах СУБД разные вещи и не регламентируются DB-API. Так что надо либо внимательно курить документацию, либо их не использовать.