Question

是否有办法? 现有<代码>DOMAIN第13.4号指示中的限制细节?

我正在尝试并检查这套书,并怀疑答案是:“第DROP制约因素(或领域和制约因素)和重建?

由于I ve got油田和已经使用限制的功能,因此,I d系列的比照号是/code>和CREATE。我可以这样做,但这是一个牵涉的领域。

举例来说,我仅举一个简单的领域清单:

DROP DOMAIN IF EXISTS domains.user_name;

CREATE DOMAIN domains.user_name AS
    citext
    NOT NULL
    CONSTRAINT user_name_legal_values
        CHECK(
            VALUE IN (
                 postgres ,
                 dbadmin ,
                 user_bender ,
                 user_cleanup ,
                 user_domo_pull 
            )
     );

COMMENT ON DOMAIN domains.user_name IS
     Valid user_name role names. ;

我愿修改<代码>。 <代码>清单 CHECK, 插入一个名称:user_analytics 。

如果不放弃并重建领域、制约因素或两者,这是否可行?

如果是,我可以自行删除和重建,今后的数字是: DOMAIN 这是此类事情的适当工具。我可以总是用一个小盘看表。我只想像“条形状”——DOMAIN

Answer 1

Use 。 ALTER DOMAIN。克服旧的制约因素,增加一个新的制约因素。你可以在单一指挥系统(如):

ALTER DOMAIN user_name DROP CONSTRAINT user_name_legal_values;
ALTER DOMAIN user_name  ADD CONSTRAINT user_name_legal_values CHECK(
            VALUE IN (
                 postgres ,
                 dbadmin ,
                 user_analytics ,
                 user_bender ,
                 user_cleanup ,
                 user_domo_pull 
            ));

The manual:

ADD area_constraint> [NOT VALID]

This form adds a new constraint to a domain using the same syntax as CREATE DOMAIN. When a new constraint is added to a domain, all columns using that domain will be checked against the newly added constraint. [...]

由于你只允许增加价值,现有的一栏不可能发生冲突。

Answer 2

今天,我需要改变这个领域,进行搜索......,并在几年后打过这个问题。以下是几个说明的最新例子。 Might help others, or may help me Future Me in another years...

ALTER DOMAIN domains.user_name
        DROP CONSTRAINT IF EXISTS user_name_legal_values; -- Clear the old definition.

ALTER DOMAIN domains.user_name 
         ADD CONSTRAINT user_name_legal_values-- Set the updated definition
                 CHECK(
                        VALUE IN (
                             rds_super ,
                             rdsadmin ,
                             user_admin ,
                             user_analytics ,
                             user_backup ,
                             user_bender ,
                             user_change_structure ,
                             user_cleanup ,
                             user_dba_task ,
                             user_domo_pull ,
                             user_duck ,
                             user_iceberg_remote ,
                             user_iceberg ,
                             user_leviathan ,
                             user_listener ,
                             user_push ,
                             user_quick ,
                             user_reporting ,
                             user_rest ,
                             user_saws ,
                             user_sonar )
                ) NOT VALID; 
/* 
                  NOT VALID goes down here, after the constraint definition.
          
NOT VALID requires a heavy lock, which *may* lead to a wait: 
https://dba.stackexchange.com/questions/268301/why-is-add-constraint-not-valid-taking-a-long-time
As of PG 16.1, NOT VALID is needed here to avoid an automatic error from PG, based 
on a field dependency check.
*/


-- Shouldn t need to validate the rule now, as Erwin pointed out originally.
-- Still, here s the syntax, and the outcome that I get:
ALTER DOMAIN domains.user_name
     VALIDATE CONSTRAINT user_name_legal_values; -- If run, rechecks *entire* table.

-- ERROR:  cannot alter type "user_name" because column "test_case.run_as" uses it. 0.000 seconds. (Line 43).

友情链接