galaxy

Текущее время - now()
date_trunc() - округление даты

-- date('now', 'start of day')
date_trunc('day', now())

-- date('now', 'start of month')
date_trunc('month', now())

-- date('now', '-6 days')
now() - INTERVAL '6 days'

Есть проблема, что я не нашел блокировки строк, блокирующей на чтение

SELECT FOR UPDATE

Есть разные варианты.
Можно работать с JSONB по-человечески:
функция jsonb_set() меняет значение по пути внутри JSON. В вашем случае как-то так:

jsonb_set(ExternalUrls, '{0,Url}', '"https://example/browse/CP-12"')

- заменит ключ Url в элементе с индексом 0 массива из ExternalUrls. Она возвращает новый JSONB объект, обновление колонок таблиц в SQL в любом случае делается через UPDATE:

UPDATE ExternalWorkItems
   SET ExternalUrls = jsonb_set(ExternalUrls, '{0,Url}', '"https://example/browse/CP-12"');

Есть некоторая проблемка в том, что jsonb_set меняет одно значение, т.е. нельзя использовать "мульти-путь" типа {*,Url}, чтобы поменять сразу все элементы массива ExternalUrls (если у вас их там больше одного).
Проблема решаемая через серию подзапросов с применением функций jsonb_array_elements() -> jsonb_set() -> jsonb_agg(), но уж какая-то монструозная выходит конструкция.

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

UPDATE ExternalWorkItems
   SET ExternalUrls = jsonb_set(
          ExternalUrls, '{0,Url}',
          to_jsonb(replace(ExternalUrls #>> '{0,Url}', 'test', 'example'))
   );

(опять поменяет только первый элемент массива ExternalUrls)

Посему, предлагается небольшой костыль в виде конвертации JSONB в текст и простой замене (с некоторым риском заменить не то, что нужно... но если вы уверены в структуре, то это не проблема):

UPDATE ExternalWorkItems SET ExternalUrls = replace(ExternalUrls::text, 'https://test', 'https://example')::jsonb;

И ещё ошибка с from psycopg2 import OperationalError, он просто не находит OperationalError

находит:

>>> from psycopg2 import OperationalError
>>> OperationalError
<class 'psycopg2.OperationalError'>
>>> psycopg2.__version__
'2.8.6 (dt dec pq3 ext lo64)'

версия psycopg2 какая?

The error 'FATAL: password authentication failed for user "postgres"

Проверяйте свой pg_hba.conf. Там нужна запись для 127.0.0.1 или localhost

UPD:

Как этот пароль посмотреть или новый создать?

Инсталлятор может задавать какой-то пароль по умолчанию, а может и вообще не задавать (тогда юзер postgres не сможет входить по паролю). Вам нужно либо настроить в pg_hba.conf вход без пароля (метод trust), либо зайти через консоль/pgadmin и задать пароль:
ALTER ROLE postgres WITH PASSWORD '123'; или в psql \password

Зайти через консоль/pgadmin без пароля получится только, если в pg_hba.conf это настроено (обычно инсталлятор это делает, если нет - меняйте файл вручную и не забудьте рестартнуть постгрес).

Если group_recepients - JSON объект или массив, то через оператор "-" (в случае объекта убирает по ключу, в случае массива - по значению):

postgres=# select '{"email": "a@example.com", "telegram_token": "", "telegram_user_id":""}'::jsonb - 'email';
                    ?column?
------------------------------------------------
 {"telegram_token": "", "telegram_user_id": ""}
(1 row)

Как-то так

UPDATE group_settings 
SET group_recepients = jsonb_build_object('email', subquery.email, 'telegram_token', '', 'telegram_user_id', '')
FROM (SELECT id, email from users) AS subquery WHERE group_settings.user_id = subquery.id;

jsonb_build_object

CREATE USER user_name PASSWORD (SELECT random_string((SELECT random_int_between(5,15))));

Не думаю, что такое извращение возможно. По крайнер мере документация по CREATE ROLE явно допуская только PASSWORD NULL или PASSWORD 'qwerty', без выражений или тем более SQL запросов.

Далее

Non-optimizable SQL commands (also called utility commands) are not capable of accepting query parameters. So automatic substitution of PL/pgSQL variables does not work in such commands. To include non-constant text in a utility command executed from PL/pgSQL, you must build the utility command as a string and then EXECUTE it, as discussed in Section 43.5.4.

https://www.postgresql.org/docs/current/plpgsql-st...

Т.е. вы не можете в команду CREATE USER вставить переменную plpgsql. Ваша команда просто интерпетируется как будто вы хотите создать юзера с именем user_name. Аналогично ведет себя GRANT.
Пароль лучше сгенерить в переменную, а эти команды надо выполнять через EXECUTE, примерно так:

SELECT random_string(SELECT random_int_between(5,15)) INTO pass;
EXECUTE format('CREATE USER %I PASSWORD %L', user_name, pass);
EXECUTE format('GRANT %I TO %I', role_name, user_name);

select right(email, -strpos(email, '@')) as domain, count(*)
  from t
 group by 1
 order by 2 desc

Ну например:

# select array_agg(a) from jsonb_array_elements_text('[1, 5, "a", {}]'::jsonb) t (a);
  array_agg
--------------
 {1,5,a,"{}"}
(1 row)

Наверное, есть еще варианты, фукнций и операторов там полно.