PostgreSQL - добавление столбца в таблицу и обновление его значением из другой таблицы в одном операторе не обновляет столбец

ProgramBox

PostgreSQL — добавление столбца в таблицу и обновление его значением из другой таблицы в одном операторе не обновляет столбец

Post author:admin
Запись опубликована:19 марта, 2023
Post category:Вопросы по программированию

#postgresql

Вопрос:

Я пытаюсь создать ddl, чтобы добавить столбец в одну таблицу и заполнить его значениями из другой вновь созданной таблицы в зависимости от логического значения из обновленной таблицы. Поэтому я сначала создаю новую таблицу, заполняю ее необходимыми значениями, затем добавляю столбец в существующую таблицу. Затем я обновляю этот столбец, но это обновление не выполняется, когда я пытаюсь выполнить все в одной транзакции.

Если я выполняю оператор update отдельно после создания таблицы и нового столбца, он работает нормально, поэтому я знаю, что в моем операторе update нет ничего плохого.

Я не понимаю, почему он не выполняется, когда я запускаю все операторы в одной транзакции. Кто-нибудь может помочь?

 -- Create the table for the sub types
CREATE SEQUENCE IF NOT EXISTS framework."UserGroupSubType_S";
CREATE TABLE IF NOT EXISTS
    framework."UserGroupSubTypes"
(
    "UserGroupSubTypeId" INTEGER NOT NULL,
    "UserGroupSubType" CHARACTER VARYING(50) NOT NULL,
    "Updated" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    CONSTRAINT
        "UserGroupSubTypeId_P"
        PRIMARY KEY ("UserGroupSubTypeId"),
    CONSTRAINT
        "UserGroupSubTypes_UserGroupSubType"
        UNIQUE ("UserGroupSubType")
)
WITH
    (
    OIDS = FALSE
);

-- Fill the table with the sub types
INSERT INTO framework."UserGroupSubTypes"
VALUES 
    (NEXTVAL('"UserGroupSubType_S"'), 'CustomerDefined', NOW()),
    (NEXTVAL('"UserGroupSubType_S"'), 'Mandatory', NOW()),
    (NEXTVAL('"UserGroupSubType_S"'), 'GeneralAdmin', NOW()),
    (NEXTVAL('"UserGroupSubType_S"'), 'Buyer', NOW())
ON CONFLICT DO NOTHING;

-- Add the column to the table "UserGroups"
ALTER TABLE framework."UserGroups" ADD COLUMN IF NOT EXISTS "UserGroupSubTypeId" INTEGER;

-- Update the column with the correct value
UPDATE framework."UserGroups"
SET "UserGroupSubTypeId" = (CASE
    WHEN "IsMandatory" = TRUE
        THEN (SELECT "UserGroupSubTypeId" FROM framework."UserGroupSubTypes" WHERE "UserGroupSubType" = 'Mandatory')
    WHEN "IsMandatory" = FALSE
        THEN (SELECT "UserGroupSubTypeId" FROM framework."UserGroupSubTypes" WHERE "UserGroupSubType" = 'CustomerDefined')
    END);

Я пробовал другие способы обновления, например, устанавливал значение по умолчанию равным 1, а затем обновлял только те, для которых требуется другое значение, но, похоже, ничего не работает.
Столбец не обновляется никогда.

1. Во-первых, почему бы и нет "UserGroupSubTypeId" SERIAL PRIMARY KEY , то же самое для UNIQUE on "UserGroupSubType" . Во-вторых, я не вижу BEGIN/COMMIT указаний на то, что это все одна транзакция? В-третьих, если это действительно в транзакции, просматриваете ли вы обновленную таблицу из другого сеанса до COMMIT ? Если это так, вы не увидите изменений. В-четвертых, отображаются ли в журналах Postgres какие-либо ошибки, когда это происходит?

2. Что касается первичного ключа и ограничения уникальности, это просто способ, которым он настроен в компании, поэтому я не могу это изменить. Начало фиксации обрабатывается в другой части кода, поэтому я уверен, что все эти операторы обрабатываются в одних и тех же транзакциях. Я также пробовал это в pgAdmin, с тем же результатом. Что касается вашего третьего пункта, если бы это было так, я бы не увидел никаких изменений? Я вижу новую таблицу, я вижу новый столбец, но значения столбцов по-прежнему равны НУЛЮ

3. Не имеет отношения к вашей проблеме, но: вам действительно следует избегать этих страшных идентификаторов в кавычках. Они доставляют гораздо больше хлопот, чем того стоят. wiki.postgresql.org/wiki /…

4. Я знаю, но иногда вы должны следовать правилам компании: p

Ответ №1:

По-видимому, pgAdmin не обрабатывает группу выбранных операторов как одну транзакцию. Благодаря комментарию Adrain я попытался добавить

 BEGIN;
---
---
COMMIT;

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

Спасибо, Адриан, за вашу помощь

1. Рад, что это сработало. В pgAdmin я предполагаю, что вы используете инструмент запроса. Если это так, рядом со значком выполнения должен быть значок с пометкой / . Нажмите на это и посмотрите, установлены ли флажки автоматической фиксации и / или автоматического отката. Похоже, автоматическая фиксация была / не проверена.

2. Я первым делом проверю в понедельник, спасибо за совет