先来看一下官网上对fkey_deferrable参数的解释

FKEY_DEFERRABLE

When exporting tables, Ora2Pg normally exports constraints as they are, if they are non-deferrable they are exported as non-deferrable. However, non-deferrable constraints will probably cause problems when attempting to import data to Pg. The FKEY_DEFERRABLE option set to 1 will cause all foreign key constraints to be exported as deferrable.

当导出表时，Ora2Pg通常按原样导出约束，如果它们不可延迟，则按不可延迟导出。然而，当试图将数据导入Pg时，不可延迟约束可能会导致问题。将fkey_deferable选项设置为1将导致所有外键约束都被导出为可延迟约束。

ora2pg导出外键，创建外键的方式如下

ALTER TABLE banksecuacctrelation ADD CONSTRAINT fk_banksecu_rel_reg FOREIGN KEY (exchid,regid) REFERENCES registration(exchid,regid) ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE NOT VALID;

ctsdb=# \d banksecuacctrelation
                  Table "xc_test.banksecuacctrelation"
    Column     |         Type          | Collation | Nullable | Default
---------------+-----------------------+-----------+----------+---------
 bankid        | character varying(4)  |           | not null |
 currencyid    | character varying(2)  |           | not null |
 bankacctid    | character varying(30) |           | not null |
 exchid        | character varying(1)  |           | not null |
 regid         | character varying(10) |           | not null |
 custid        | character varying(12) |           | not null |
 acctid        | character varying(12) |           | not null |
 customerfeeid | character varying(3)  |           | not null |
 linkstatus    | smallint              |           | not null |
 rightownflag  | smallint              |           | not null |
Indexes:
    "banksecuacctrelation_pkey" PRIMARY KEY, btree (bankid, exchid, regid)
    "banksecuacctrelation_custid_idx" btree (custid)
Foreign-key constraints:
    "fk_banksecu_rel_reg" FOREIGN KEY (exchid, regid) REFERENCES registration(exchid, regid) DEFERRABLE NOT VALID

经测试发现ALTER TABLE ADD CONSTRAINT的NOT VALID选项,它阻止了对现有数据的验证。NOT VALID选项主要是出于性能原因,以允许用户将验证推迟到以后。但它没有明确说明它不能用于保留旧(无效)数据。

postgres=# table client；
 client_id |  client_name   | client_profession | client_qualification | client_salary
-----------+----------------+-------------------+----------------------+---------------
         1 | Emma Hernandez | Web Designer      | BTech                |         25000
         2 | Mia Clark      | Software Engineer | BE                   |         20000
         3 | Noah Rodriguez | Bussinessman      | MBA                  |         50000
         4 | Martha Brown   | Doctor            | MBBS                 |         75000
         5 | James Luther   | HR                | MBA                  |         35000
         6 | Maria Garcia   | Astronaut         | Msc                  |        
         7 | Robert Smith   | Software Tester   | BTech                |         30000
(7 rows)

postgres=# \d client
                           Table "public.client"
        Column        |       Type        | Collation | Nullable | Default
----------------------+-------------------+-----------+----------+---------
 client_id            | integer           |           | not null |
 client_name          | character varying |           | not null |
 client_profession    | character varying |           | not null |
 client_qualification | character varying |           | not null |
 client_salary        | integer           |           |          |
Indexes:
    "client_pkey" PRIMARY KEY, btree (client_id)
Statistics objects:
    "public.statext_student" ON client_id, client_name, client_profession, client_qualification, client_salary FROM client
 

只有7条数据的时候可以执行非空约束

postgres=# \d client
                           Table "public.client"
        Column        |       Type        | Collation | Nullable | Default
----------------------+-------------------+-----------+----------+---------
 client_id            | integer           |           | not null |
 client_name          | character varying |           | not null |
 client_profession    | character varying |           | not null |
 client_qualification | character varying |           | not null |
 client_salary        | integer           |           |          |
Indexes:
    "client_pkey" PRIMARY KEY, btree (client_id)
Check constraints:
    "client_salary_valid" CHECK (client_salary IS NOT NULL) NOT VALID
Statistics objects:
    "public.statext_student" ON client_id, client_name, client_profession, client_qualification, client_salary FROM client

之后再插入数据，报错，提示client_salary列不能为null，也就是创建NOT VALID约束之后，对之后创建的不符合数据将报错

讯享网

postgres=# insert into client values (8,'Hernandez','Designer','BTec',null);
ERROR:  new row for relation "client" violates check constraint "client_salary_valid"
DETAIL:  Failing row contains (8, Hernandez, Designer, BTec, null).

删掉约束后可执行数据插入

postgres=# ALTER TABLE client drop CONSTRAINT client_salary_valid;
ALTER TABLE
postgres=# insert into client values (8,'Hernandez','Designer','BTec',null);
INSERT 0 1

当前数据如下：client_id为8的数据client_salary列为null，但是插入再次创建not valid约束不会对之前的数据进行约束检查了

postgres=# table client ;
 client_id |  client_name   | client_profession | client_qualification | client_salary
-----------+----------------+-------------------+----------------------+---------------
         1 | Emma Hernandez | Web Designer      | BTech                |         25000
         2 | Mia Clark      | Software Engineer | BE                   |         20000
         3 | Noah Rodriguez | Bussinessman      | MBA                  |         50000
         4 | Martha Brown   | Doctor            | MBBS                 |         75000
         5 | James Luther   | HR                | MBA                  |         35000
         6 | Maria Garcia   | Astronaut         | Msc                  |        
         7 | Robert Smith   | Software Tester   | BTech                |         30000
         8 | Hernandez      | Designer          | BTec                 |
(8 rows)

postgres=# alter table client ADD CONSTRAINT client_salary_valid check( client_salary is not null ) not valid;
ALTER TABLE
postgres=# \d client
                           Table "public.client"
        Column        |       Type        | Collation | Nullable | Default
----------------------+-------------------+-----------+----------+---------
 client_id            | integer           |           | not null |
 client_name          | character varying |           | not null |
 client_profession    | character varying |           | not null |
 client_qualification | character varying |           | not null |
 client_salary        | integer           |           |          |
Indexes:
    "client_pkey" PRIMARY KEY, btree (client_id)
Check constraints:
    "client_salary_valid" CHECK (client_salary IS NOT NULL) NOT VALID

接下来，您可以手动执行命令，告诉PostgreSQL验证约束，client_id为8的数据client_salary列为null再次违反约束。

结论：

1、执行not valid非空约束后，之后插入的数据不能为空，执行验证之后，之后再insert数据就会检查非空约束。

2、执行not valid非空约束后，如果之前存在违反约束的数据，不能验证约束，否则报错。

3、执行not valid非空约束后，不执行验证约束，对之前的数据无任何影响。（检查新行,但不保证现有数据）

使得PostgreSQL不会获得ACCESS EXCLUSIVE LOCK，而是SHARE UPDATE EXCLUSIVE，意味着读/写不受影响。