postgresql 外键 null 和 ''的坑

最帅dba工作笔记 2020-07-22

1040

postgresql null 和 ''

oracle的''和null 跟 postgresql的''和null是有区别的哦！！！！

问题描述：从oracle迁移到pg中后有两张表在pg中显示为这样：

BSUSER:

         栏位          |            类型             | Collation | Nullable |                                Default                                 
-----------------------+-----------------------------+-----------+----------+------------------------------------------------------------------------
code                  | character varying(50)       |           | not null |
name                  | character varying(30)       |           | not null |
password              | character varying(300)      |           | not null |
isactive              | numeric(1,0)                |           | not null | 1
reason                | character varying(12)       |           |          |
lsinputway            | numeric(10,0)               |           | not null | 1
iconindex             | numeric(10,0)               |           | not null | 0
isuserinputwb         | numeric(1,0)                |           |          | 0
isuserinputpy         | numeric(1,0)                |           |          | 0
isuserinputcode       | numeric(1,0)                |           |          | 0
isuserinputname       | numeric(1,0)                |           |          | 0
isuserinputstrokecode | numeric(1,0)                |           |          | 0
isuserinputengdesc    | numeric(1,0)                |           |          | 0
picturepath           | character varying(200)      |           |          | ' '::character varying
address               | character varying(40)       |           |          |
mobile                | character varying(30)       |           |          |
hospitalid            | numeric(10,0)               |           | not null | '0'::numeric
caregroupid1          | numeric(10,0)               |           |          |
certidno              | character varying(100)      |           |          |
pycode                | character varying(30)       |           |          |
wbcode                | character varying(30)       |           |          |
isuserinputf1         | numeric(1,0)                |           | not null |
isuserinputlist       | numeric(1,0)                |           | not null |
isuserwordpad         | numeric(1,0)                |           | not null |
isuserselectonly      | numeric(1,0)                |           | not null |
isgiveredenvelopes    | numeric(1,0)                |           |          | '1'::numeric
remark                | character varying(200)      |           |          |
isinternalstaff       | numeric(1,0)                |           |          | '1'::numeric
deduct                | numeric(10,2)               |           |          |
lsstatus              | numeric(3,0)                |           |          |
email                 | character varying(50)       |           |          |
birthdate             | timestamp(0) with time zone |           |          |
sex                   | character(1)                |           |          |
moditime              | timestamp(0) with time zone |           |          | LOCALTIMESTAMP
opertime              | timestamp(0) with time zone |           |          | LOCALTIMESTAMP
isintegralstaff       | numeric(1,0)                |           |          |
iscancancel           | numeric(10,0)               |           |          |
isvolunteer           | numeric(1,0)                |           |          |
isrecipe              | numeric(1,0)                |           |          |
guid                  | character varying(50)       |           | not null | md5((random() || to_char(now(), 'yyyy-mm-dd hh24:mi:ss:ms:us'::text)))
doclevid              | character varying(50)       |           |          |
caregroupid           | character varying(50)       |           |          |
levelid               | character varying(50)       |           |          |
departmentid          | character varying(50)       |           |          |
diagroomid            | character varying(50)       |           |          |
modioperid            | character varying(50)       |           |          |
roleid                | character varying(50)       |           |          |
locationid            | character varying(50)       |           |          |
isitanexpert          | numeric(1,0)                |           |          |
histype               | numeric(2,0)                |           | not null | '0'::numeric
introduce             | character varying(1000)     |           |          |
teamid                | character varying(50)       |           |          |
doclevname            | character varying(50)       |           |          |
departmentname        | character varying(50)       |           |          |
ruleid                | character varying(150)      |           |          |
ybcode                | character varying(50)       |           |          |
wechatid              | character varying(50)       |           |          |
school                | character varying(50)       |           |          |
major                 | character varying(50)       |           |          |
bankaccount           | character varying(100)      |           |          |
salary                | numeric(18,4)               |           |          |
qq                    | character varying(50)       |           |          |
wechat                | character varying(50)       |           |          |
native                | character varying(100)      |           |          |
linkmanname           | character varying(10)       |           |          |
familyground          | character varying(100)      |           |          |
doccertifycode        | character varying(100)      |           |          |
provinceid            | character varying(50)       |           |          |
索引：
    "pk_bsuser_guid" PRIMARY KEY, btree (guid)
    "ix_bsuser_caregroupid" btree (caregroupid)
    "ix_bsuser_code" btree (code)
    "ix_bsuser_doclevid" btree (doclevid)
    "ix_bsuser_levelid" btree (levelid)
    "ix_bsuser_name" btree (name)
    "ix_bsuser_wechatid" btree (wechatid)
外部键(FK)限制：
    "fk_bsuser_bscaregroup" FOREIGN KEY (caregroupid) REFERENCES bscaregroup(guid)
    "fk_bsuser_bsdoclevel" FOREIGN KEY (doclevid) REFERENCES bsdoclevel(guid)
    "fk_bsuser_bsedulevel" FOREIGN KEY (levelid) REFERENCES bsedulevel(guid)
    "fk_bsuser_wechatid" FOREIGN KEY (wechatid) REFERENCES weichat_user(guid)
由引用：
    TABLE "additionalmeal" CONSTRAINT "fk_additionalmeal_adduser" FOREIGN KEY (adduser) REFERENCES bsuser(guid)
    TABLE "additionalmeal" CONSTRAINT "fk_additionalmeal_auditor" FOREIGN KEY (auditor) REFERENCES bsuser(guid)
    TABLE "additionalmeal" CONSTRAINT "fk_additionalmeal_canceller" FOREIGN KEY (canceller) REFERENCES bsuser(guid)
    TABLE "additionalmeal" CONSTRAINT "fk_additionalmeal_updtuser" FOREIGN KEY (updtuser) REFERENCES bsuser(guid)
    TABLE "additionfee" CONSTRAINT "fk_additionfee_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "additionfee" CONSTRAINT "fk_additionfee_signoperid" FOREIGN KEY (signoperid) REFERENCES bsuser(guid)
    TABLE "bsdoctor" CONSTRAINT "fk_bsdoctor_userid" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "bseqmaintain" CONSTRAINT "fk_bseqmaintain_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "bshrdegree" CONSTRAINT "fk_bshrdegree_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "bsitem" CONSTRAINT "fk_bsitem_bsuser" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "bssigningdoctor_iywt" CONSTRAINT "fk_bssigningdoctor_doctorid" FOREIGN KEY (doctorid) REFERENCES bsuser(guid)
    TABLE "bsbed" CONSTRAINT "fk_bsuser_bsbed" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "bsitembpid" CONSTRAINT "fk_bsuser_bsitembpid" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "inhosinfo" CONSTRAINT "fk_bsuser_inhosinfo" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "inhosinfo" CONSTRAINT "fk_bsuser_inhosinfo2" FOREIGN KEY (endchgoperid) REFERENCES bsuser(guid)
    TABLE "ckitem" CONSTRAINT "fk_ckitem_bsuser" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "colinkmanchange" CONSTRAINT "fk_colinkmanchange_auditoperid" FOREIGN KEY (auditoperid) REFERENCES bsuser(guid)
    TABLE "colinkmanchange" CONSTRAINT "fk_colinkmanchange_mealoperid" FOREIGN KEY (mealoperid) REFERENCES bsuser(guid)
    TABLE "colinkmanchange" CONSTRAINT "fk_colinkmanchange_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "copatientfixedcost" CONSTRAINT "fk_copatientfixedcost_lastchar" FOREIGN KEY (lastchargeoperid) REFERENCES bsuser(guid)
    TABLE "copatientfixedcost" CONSTRAINT "fk_copatientfixedcost_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "copatientfixedcost" CONSTRAINT "fk_copatientfixedcost_stopchar" FOREIGN KEY (stopchargeoperid) REFERENCES bsuser(guid)
    TABLE "copatientprimecost" CONSTRAINT "fk_copatientprimecost_cancelid" FOREIGN KEY (cancelid) REFERENCES bsuser(guid)
    TABLE "copatientprimecost" CONSTRAINT "fk_copatientprimecost_chargeop" FOREIGN KEY (chargeoperid) REFERENCES bsuser(guid)
    TABLE "coretreatlive" CONSTRAINT "fk_coretreatlive_auditoperid" FOREIGN KEY (auditoperid) REFERENCES bsuser(guid)
    TABLE "coretreatlive" CONSTRAINT "fk_coretreatlive_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "eqequipment" CONSTRAINT "fk_eqequipment_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "eqmoneytoapply" CONSTRAINT "fk_eqmoneytoapply_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "eqspecialfund" CONSTRAINT "fk_eqspecialfund_operid" FOREIGN KEY (approvaloperid) REFERENCES bsuser(guid)
    TABLE "inemr" CONSTRAINT "fk_inemr_deloperid" FOREIGN KEY (deloperid) REFERENCES bsuser(guid)
    TABLE "inhosinforenew" CONSTRAINT "fk_inhosinforenew_operrenewid" FOREIGN KEY (operrenewid) REFERENCES bsuser(guid)
    TABLE "innurserrec" CONSTRAINT "fk_innurserrec_deloperid" FOREIGN KEY (deloperid) REFERENCES bsuser(guid)
    TABLE "instopmeal" CONSTRAINT "fk_instopmeal_auditoperid" FOREIGN KEY (auditoperid) REFERENCES bsuser(guid)
    TABLE "instopmeal" CONSTRAINT "fk_instopmeal_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oaadmin" CONSTRAINT "fk_oaadmin_userid" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "oacadreappoint" CONSTRAINT "fk_oacadreappoint_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oacadreappoint" CONSTRAINT "fk_oacadreappoint_userid" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "oacandidates" CONSTRAINT "fk_oacandidates_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oacandidates" CONSTRAINT "fk_oacandidates_refereesid" FOREIGN KEY (refereesid) REFERENCES bsuser(guid)
    TABLE "oacandidatesalter" CONSTRAINT "fk_oacandidatesalter_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oacare" CONSTRAINT "fk_oacare_userid" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "oacontract" CONSTRAINT "fk_oacontract_leader" FOREIGN KEY (leader) REFERENCES bsuser(guid)
    TABLE "oacontract" CONSTRAINT "fk_oacontract_salesman" FOREIGN KEY (salesman) REFERENCES bsuser(guid)
    TABLE "oacontractplan" CONSTRAINT "fk_oacontractplan_userid" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "oadisctitle" CONSTRAINT "fk_oadisctitle_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oadisctitleitem" CONSTRAINT "fk_oadisctitleitem_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oafile" CONSTRAINT "fk_oafile_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oafile" CONSTRAINT "fk_oafile_userid" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "oagooutside" CONSTRAINT "fk_oagooutside_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oaincometax" CONSTRAINT "fk_oaincometax_userid" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "oainterviewtest" CONSTRAINT "fk_oainterviewtest_createid" FOREIGN KEY (createoperid) REFERENCES bsuser(guid)
    TABLE "oainterviewtest" CONSTRAINT "fk_oainterviewtest_deloperid" FOREIGN KEY (deloperid) REFERENCES bsuser(guid)
    TABLE "oainterviewtest" CONSTRAINT "fk_oainterviewtest_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oainterviewtitle" CONSTRAINT "fk_oainterviewtitle_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oalaunnewtech" CONSTRAINT "fk_oalaunnewtech_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oalevelmote" CONSTRAINT "fk_oalevelmote_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oamanagepost" CONSTRAINT "fk_oamanagepost_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oamanagepost" CONSTRAINT "fk_oamanagepost_userid" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "oamedimage" CONSTRAINT "fk_oamedimage_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oamedsat" CONSTRAINT "fk_oamedsat_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oanodeauth" CONSTRAINT "fk_oanodeauth_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oapapermono" CONSTRAINT "fk_oapapermono_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oaproassoc" CONSTRAINT "fk_oaproassoc_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oapunrecord" CONSTRAINT "fk_oapunrecord_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oapunrecord" CONSTRAINT "fk_oapunrecord_userid" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "oaqualifica" CONSTRAINT "fk_oaqualifica_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oarelation" CONSTRAINT "fk_oarelation_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oarelation" CONSTRAINT "fk_oarelation_userid" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "oaresume" CONSTRAINT "fk_oaresume_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oaresume" CONSTRAINT "fk_oaresume_userid" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "oareward" CONSTRAINT "fk_oareward_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oareward" CONSTRAINT "fk_oareward_userid" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "oasalary" CONSTRAINT "fk_oasalary_auditid" FOREIGN KEY (auditoperid) REFERENCES bsuser(guid)
    TABLE "oasalary" CONSTRAINT "fk_oasalary_createid" FOREIGN KEY (createid) REFERENCES bsuser(guid)
    TABLE "oasalary" CONSTRAINT "fk_oasalary_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oasalary" CONSTRAINT "fk_oasalary_reviewid" FOREIGN KEY (reviewoperid) REFERENCES bsuser(guid)
    TABLE "oasalaryitem" CONSTRAINT "fk_oasalaryitem_userid" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "oasatisfiedinfo" CONSTRAINT "fk_oasatisfiedinfo_userid" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "oasatisfieditem" CONSTRAINT "fk_oasatisfieditem_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oascienresea" CONSTRAINT "fk_oascienresea_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oatalentpool" CONSTRAINT "fk_oatalentpool_interviewerid" FOREIGN KEY (interviewerid) REFERENCES bsuser(guid)
    TABLE "oatalentpool" CONSTRAINT "fk_oatalentpool_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oatitleitem" CONSTRAINT "fk_oatitleitem_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oatitlescore" CONSTRAINT "fk_oatitlescore_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oatrain" CONSTRAINT "fk_oatrain_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oatrain" CONSTRAINT "fk_oatrain_presenterid" FOREIGN KEY (presenterid) REFERENCES bsuser(guid)
    TABLE "oatrain" CONSTRAINT "fk_oatrain_userid" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "oausercare" CONSTRAINT "fk_oausercare_authoperid" FOREIGN KEY (authoperid) REFERENCES bsuser(guid)
    TABLE "oausercare" CONSTRAINT "fk_oausercare_directorid" FOREIGN KEY (directorid) REFERENCES bsuser(guid)
    TABLE "oausercare" CONSTRAINT "fk_oausercare_miantanid" FOREIGN KEY (miantanid) REFERENCES bsuser(guid)
    TABLE "oausercare" CONSTRAINT "fk_oausercare_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oausercare" CONSTRAINT "fk_oausercare_patientid" FOREIGN KEY (patientid) REFERENCES bsuser(guid)
    TABLE "oausercare" CONSTRAINT "fk_oausercare_planid" FOREIGN KEY (planid) REFERENCES bsuser(guid)
    TABLE "oausercare" CONSTRAINT "fk_oausercare_userid" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "oausercareansr" CONSTRAINT "fk_oausercareansr_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oausercaretitle" CONSTRAINT "fk_oausercaretitle_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oausercontract" CONSTRAINT "fk_oausercontract_userid" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "oauserdisc" CONSTRAINT "fk_oauserdisc_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oauserdisc" CONSTRAINT "fk_oauserdisc_userid" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "oauserdiscoption" CONSTRAINT "fk_oauserdiscoption_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oauserexam" CONSTRAINT "fk_oauserexam_userid" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "oauserfile" CONSTRAINT "fk_oauserfile_userid" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "oauserflow" CONSTRAINT "fk_oauserflow_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oauserform" CONSTRAINT "fk_oauserform_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oausersatisfied" CONSTRAINT "fk_oausersatisfied_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oausersatisfied" CONSTRAINT "fk_oausersatisfied_userid" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "oauserscore" CONSTRAINT "fk_oauserscore_userid" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "oausertrain" CONSTRAINT "fk_oausertrain_userid" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "oavalueneed" CONSTRAINT "fk_oavalueneed_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oaviolation" CONSTRAINT "fk_oaviolation_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oaworkauth" CONSTRAINT "fk_oaworkauth_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "oaworkexper" CONSTRAINT "fk_oaworkexper_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "ouemrnew" CONSTRAINT "fk_ouemrnew_authoperid" FOREIGN KEY (authoperid) REFERENCES bsuser(guid)
    TABLE "ouemrnew" CONSTRAINT "fk_ouemrnew_closedoperid" FOREIGN KEY (closedoperid) REFERENCES bsuser(guid)
    TABLE "ouemrnew" CONSTRAINT "fk_ouemrnew_modifyoperid" FOREIGN KEY (modifyoperid) REFERENCES bsuser(guid)
    TABLE "ouemrnew" CONSTRAINT "fk_ouemrnew_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "ouemrnew" CONSTRAINT "fk_ouemrnew_printoperid" FOREIGN KEY (printoperid) REFERENCES bsuser(guid)
    TABLE "ouemrnew" CONSTRAINT "fk_ouemrnew_realeaseoperid" FOREIGN KEY (realeaseoperid) REFERENCES bsuser(guid)
    TABLE "ourecipetemp" CONSTRAINT "fk_ourecipetemp_bsuser" FOREIGN KEY (issueoperid) REFERENCES bsuser(guid)
    TABLE "outreatmentrecord" CONSTRAINT "fk_outreatmentrecord_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "psapplyitem" CONSTRAINT "fk_paapplyitem_bsuser" FOREIGN KEY (canceloperid) REFERENCES bsuser(guid)
    TABLE "psdrugaction" CONSTRAINT "fk_psdrugaction_canceloperid" FOREIGN KEY (canceloperid) REFERENCES bsuser(guid)
    TABLE "psdrugaction" CONSTRAINT "fk_psdrugaction_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "pssuperlreservation" CONSTRAINT "fk_pssuperlreser_canceloperid" FOREIGN KEY (canceloperid) REFERENCES bsuser(guid)
    TABLE "pssuperlreservation" CONSTRAINT "fk_pssuperlreser_reportoperid" FOREIGN KEY (reportoperid) REFERENCES bsuser(guid)
    TABLE "pssuperlreservation" CONSTRAINT "fk_pssuperlreserva_authoperid" FOREIGN KEY (authoperid) REFERENCES bsuser(guid)
    TABLE "pssuperlreservation" CONSTRAINT "fk_pssuperlreserva_checkoperid" FOREIGN KEY (checkoperid) REFERENCES bsuser(guid)
    TABLE "pssuperlreservation" CONSTRAINT "fk_pssuperlreservation_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "rdinfectiousnew" CONSTRAINT "fk_rdinfectiousnew_canceloper" FOREIGN KEY (canceloperid) REFERENCES bsuser(guid)
    TABLE "rdinfectiousnew" CONSTRAINT "fk_rdinfectiousnew_chkoperid" FOREIGN KEY (chkoperid) REFERENCES bsuser(guid)
    TABLE "rdinfectiousnew" CONSTRAINT "fk_rdinfectiousnew_operid" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "rdinfectiousnew" CONSTRAINT "fk_rdinfectiousnew_recioperid" FOREIGN KEY (recioperid) REFERENCES bsuser(guid)
    TABLE "volunteerschedule" CONSTRAINT "fk_volunteerfiles_bsuser" FOREIGN KEY (userid) REFERENCES bsuser(guid)
    TABLE "volunteerschedule" CONSTRAINT "fk_volunteerfiles_bsuser1" FOREIGN KEY (operid) REFERENCES bsuser(guid)
    TABLE "volunteerschedule" CONSTRAINT "fk_volunteerfiles_bsuser2" FOREIGN KEY (auditoperid) REFERENCES bsuser(guid)
触发器：
    tr_bsuser_unequal BEFORE UPDATE ON bsuser FOR EACH ROW EXECUTE PROCEDURE tr_bsuser_unequal_function()

BSEDULEVEL:

引用表是BSUSER (levelid)

被引用表是BSEDULEVEL(guid)

应用在访问页面时无法显示，java后台报错

查看数据库日志发现报错sql如下

UPDATE BSUSER
   SET CODE                  = (('999999') ::text),
       NAME                  = (('超级管理员') ::text),
       ROLEID                = (('c1a05b4777874615ba7bba011904a255') ::text),
       PASSWORD              = (('c4ca4238a0b923820dcc509a6f75849b') ::text),
       ISACTIVE              = ((TRUE) ::bool),
       REASON                = (('') ::text),
       LSINPUTWAY            = ((1) ::int4),
       ICONINDEX             = ((0) ::int4),
       ISUSERINPUTWB         = ((TRUE) ::bool),
       ISUSERINPUTPY         = ((TRUE) ::bool),
       ISUSERINPUTCODE       = ((TRUE) ::bool),
       ISUSERINPUTNAME       = ((TRUE) ::bool),
       ISUSERINPUTSTROKECODE = ((TRUE) ::bool),
       ISUSERINPUTENGDESC    = ((TRUE) ::bool),
       INTRODUCE             = (('1') ::text),
       PICTUREPATH           = ((' ') ::text),
       ISITANEXPERT          = ((FALSE) ::bool),
       ADDRESS               = (('') ::text),
       MOBILE                = (('') ::text),
       LEVELID               = (('') ::text),
       DOCLEVID              = (('A4F9E6021CA7C9A9E05012AC6A0B26E7') ::text),
       HOSPITALID            = ((1149) ::int4),
       CAREGROUPID           = ((NULL) ::text),
       CAREGROUPID1          = ((0) ::int4),
       CERTIDNO              = (('') ::text),
       PYCODE                = (('') ::text),
       WBCODE                = (('') ::text),
       ISUSERINPUTF1         = ((FALSE) ::bool),
       ISUSERINPUTLIST       = ((TRUE) ::bool),
       ISUSERWORDPAD         = ((TRUE) ::bool),
       ISUSERSELECTONLY      = ((FALSE) ::bool),
       TEAMID                = ((NULL) ::text),
       LOCATIONID            = (('A4F9E601EE28C9A9E05012AC6A0B26E7') ::text),
       ISGIVEREDENVELOPES    = ((TRUE) ::bool),
       REMARK                = ((NULL) ::text),
       DEPARTMENTID          = ((NULL) ::text),
       ISINTERNALSTAFF       = ((TRUE) ::bool),
       DEDUCT                = ((0) ::float8),
       LSSTATUS              = ((1) ::int4),
       EMAIL                 = ((NULL) ::text),
       BIRTHDATE             = (('0001-01-01 08:00:00') ::timestamp),
       SEX                   = (('3') ::text),
       MODIOPERID            = ((NULL) ::text),
       MODITIME              = (('2020-05-06 19:16:49') ::timestamp),
       OPERTIME              = (('2020-05-06 19:16:49') ::timestamp),
       ISINTEGRALSTAFF       = ((FALSE) ::bool),
       ISCANCANCEL           = ((0) ::int4),
       ISVOLUNTEER           = ((FALSE) ::bool),
       DIAGROOMID            = ((NULL) ::text),
       ISRECIPE              = ((TRUE) ::bool),
       RULEID                = ((NULL) ::text),
       DEPARTMENTNAME        = ((NULL) ::text),
       DOCLEVNAME            = (('A4F9E6021CA7C9A9E05012AC6A0B26E7') ::text),
       HISTYPE               = ((1) ::int4),
       WECHATID              = ((NULL) ::text),
       SCHOOL                = (('') ::text),
       MAJOR                 = (('') ::text),
       BANKACCOUNT           = (('') ::text),
       SALARY                = ((0) ::float8),
       QQ                    = (('') ::text),
       WECHAT                = (('') ::text),
       NATIVE                = (('') ::text),
       LINKMANNAME           = (('') ::text),
       FAMILYGROUND          = (('') ::text),
       DOCCERTIFYCODE        = ((NULL) ::text),
       PROVINCEID            = ((NULL) ::text)
 WHERE (GUID = (('A4F9E601EF56C9A9E05012AC6A0B26E7') ::text))

这里要强调一点，这段代码执行会报错，因为没有boolean转numeric的转换

这里需要添加Cast转换：

create or replace function boolean_to_numeric(boolean) returns numeric as $$          
  select (case when $1='TRUE' then 1 when $1='FALSE' then 0 end)::numeric;
$$ language sql strict;




create cast (boolean as numeric) with function boolean_to_numeric(boolean) as implicit;

添加之后发现另外一个跟本章相关的报错：

问题就在于，当update bsuser set levelid=''的时候，这里就会出现一个问题，bsedulevel中的guid是主键，插入''的时候，那就违背了主键的非空特性。

在oracle当中进行测试：

oracle会将''空字符串指定成null；

在pg中测试：

''空字符串就是空字符串；

这样我们尝试在pg中将客户的sql语句中的levelid改成null：

UPDATE BSUSER
   SET CODE                  = (('999999') ::text),
       NAME                  = (('超级管理员') ::text),
       ROLEID                = (('c1a05b4777874615ba7bba011904a255') ::text),
       PASSWORD              = (('c4ca4238a0b923820dcc509a6f75849b') ::text),
       ISACTIVE              = ((TRUE) ::bool),
       REASON                = (('') ::text),
       LSINPUTWAY            = ((1) ::int4),
       ICONINDEX             = ((0) ::int4),
       ISUSERINPUTWB         = ((TRUE) ::bool),
       ISUSERINPUTPY         = ((TRUE) ::bool),
       ISUSERINPUTCODE       = ((TRUE) ::bool),
       ISUSERINPUTNAME       = ((TRUE) ::bool),
       ISUSERINPUTSTROKECODE = ((TRUE) ::bool),
       ISUSERINPUTENGDESC    = ((TRUE) ::bool),
       INTRODUCE             = (('1') ::text),
       PICTUREPATH           = ((' ') ::text),
       ISITANEXPERT          = ((FALSE) ::bool),
       ADDRESS               = (('') ::text),
       MOBILE                = (('') ::text),
       LEVELID               = ((null) ::text),
       DOCLEVID              = (('A4F9E6021CA7C9A9E05012AC6A0B26E7') ::text),
       HOSPITALID            = ((1149) ::int4),
       CAREGROUPID           = ((NULL) ::text),
       CAREGROUPID1          = ((0) ::int4),
       CERTIDNO              = (('') ::text),
       PYCODE                = (('') ::text),
       WBCODE                = (('') ::text),
       ISUSERINPUTF1         = ((FALSE) ::bool),
       ISUSERINPUTLIST       = ((TRUE) ::bool),
       ISUSERWORDPAD         = ((TRUE) ::bool),
       ISUSERSELECTONLY      = ((FALSE) ::bool),
       TEAMID                = ((NULL) ::text),
       LOCATIONID            = (('A4F9E601EE28C9A9E05012AC6A0B26E7') ::text),
       ISGIVEREDENVELOPES    = ((TRUE) ::bool),
       REMARK                = ((NULL) ::text),
       DEPARTMENTID          = ((NULL) ::text),
       ISINTERNALSTAFF       = ((TRUE) ::bool),
       DEDUCT                = ((0) ::float8),
       LSSTATUS              = ((1) ::int4),
       EMAIL                 = ((NULL) ::text),
       BIRTHDATE             = (('0001-01-01 08:00:00') ::timestamp),
       SEX                   = (('3') ::text),
       MODIOPERID            = ((NULL) ::text),
       MODITIME              = (('2020-05-06 19:16:49') ::timestamp),
       OPERTIME              = (('2020-05-06 19:16:49') ::timestamp),
       ISINTEGRALSTAFF       = ((FALSE) ::bool),
       ISCANCANCEL           = ((0) ::int4),
       ISVOLUNTEER           = ((FALSE) ::bool),
       DIAGROOMID            = ((NULL) ::text),
       ISRECIPE              = ((TRUE) ::bool),
       RULEID                = ((NULL) ::text),
       DEPARTMENTNAME        = ((NULL) ::text),
       DOCLEVNAME            = (('A4F9E6021CA7C9A9E05012AC6A0B26E7') ::text),
       HISTYPE               = ((1) ::int4),
       WECHATID              = ((NULL) ::text),
       SCHOOL                = (('') ::text),
       MAJOR                 = (('') ::text),
       BANKACCOUNT           = (('') ::text),
       SALARY                = ((0) ::float8),
       QQ                    = (('') ::text),
       WECHAT                = (('') ::text),
       NATIVE                = (('') ::text),
       LINKMANNAME           = (('') ::text),
       FAMILYGROUND          = (('') ::text),
       DOCCERTIFYCODE        = ((NULL) ::text),
       PROVINCEID            = ((NULL) ::text)
 WHERE (GUID = (('A4F9E601EF56C9A9E05012AC6A0B26E7') ::text))

这样就通过了

在网上发现，其实在数据库当中如果被引用表是主键的情况下，是可以将外键赋值成null的，但是不建议这样做。

THAT'S ALL

BY CUI PEACE！！！！

postgresql

文章转载自最帅dba工作笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

postgresql 外键 null 和 ''的坑

评论