Re: BUG #15473: Incorrect error when executing a certain create tablestatement through psql - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      15473
Logged by:          Erki Eessaar
Email address:      erki.eessaar@taltech.ee
PostgreSQL version: 11.0
Operating system:   CentOS release 6.10 (Final)
Description:       

I use psql.

I execute the following statements without a problem.

CREATE TABLE riik (
    riik_kood character varying(3) NOT NULL,
    nimetus character varying(150) NOT NULL,
    CONSTRAINT pk_riik PRIMARY KEY (riik_kood),
    CONSTRAINT ak_riik_nimetus UNIQUE (nimetus),
    CONSTRAINT chk_riik_nimetus_pole_tyhikutest_ega_tyhi CHECK
(nimetus::text !~ '^[[:space:]]*$'::text),
    CONSTRAINT chk_riik_riik_kood_koosneb_kolmest_suurest_tahest CHECK
(riik_kood::text ~ '^[A-Z]{3}$'::text)
);              


CREATE TABLE isiku_seisundi_liik(
    isiku_seisundi_liik_kood smallint NOT NULL,
    nimetus character varying(100) NOT NULL,
    CONSTRAINT pk_isiku_seisundi_liik PRIMARY KEY
(isiku_seisundi_liik_kood),
    CONSTRAINT ak_isiku_seisundi_liik_nimetus UNIQUE (nimetus),
    CONSTRAINT chk_isiku_seisundi_liik_nimetus_pole_tyhikutest_ega_tyhi
CHECK (nimetus::text !~ '^[[:space:]]*$'::text),
    CONSTRAINT
chk_isiku_seisundi_liik_isiku_seisundi_liik_kood_positiivne_arv CHECK
(isiku_seisundi_liik_kood > 0)
);

Now, I want to execute a CREATE TABLE statement to create table Isik
(Person) that references by using foreign keys to the tables Riik (Country)
and Isiku_seisundi_liik (Person_state_type).

CREATE TABLE Isik
(
        isik_id serial NOT NULL ,
        isikukoodi_riik varchar(3)       NOT NULL,
        isiku_seisundi_liik_kood smallint NOT NULL DEFAULT 1,
        isikukood varchar(50)    NOT NULL,
        e_meil varchar(254)      NOT NULL,
        parool varchar(60)       NOT NULL,
        synni_kp date NOT NULL,
        reg_aeg timestamp NOT NULL DEFAULT localtimestamp(0),
        elukoht varchar(3000)    NOT NULL,
        eesnimi varchar(3000)   ,
        perenimi varchar(3000)  ,
        CONSTRAINT PK_Isik PRIMARY KEY (isik_id),
        CONSTRAINT AK_Isik_e_meil UNIQUE (e_meil),
        CONSTRAINT AK_Isik_isukukood_riik_kood UNIQUE
(isikukood,isikukoodi_riik),
        CONSTRAINT chk_Isik_eesnimi_perenimi_voi_molemad CHECK ((eesnimi Is Not
Null) Or (perenimi Is Not Null)),
        CONSTRAINT chk_Isik_isikukood_pole_tyhikutest_ega_tyhi CHECK
(isikukood!~'^[[:space:]]*$'),
        CONSTRAINT chk_Isik_e_meil_sisaldab_uhte_at_marki CHECK
(e_meil~'^[a-zA-Z0-9]+@([a-zA-Z0-9]+\.)+[a-zA-Z0-9]{2,4}$'),
        CONSTRAINT chk_Isik_eesnimi_pole_tyhikutest_ega_tyhi CHECK
(eesnimi!~'^[[:space:]]*$'),
        CONSTRAINT chk_Isik_perenimi_pole_tyhikutest_ega_tyhi CHECK
(perenimi!~'^[[:space:]]*$'),
        CONSTRAINT chk_Isik_synni_kp_vaartuste_vahemik CHECK
(synni_kp>='1900-01-01' AND synni_kp<'2101-01-01'),
        CONSTRAINT chk_Isik_reg_aeg_vaartuste_vahemik CHECK (reg_aeg>='2010-01-01'
AND reg_aeg<'2101-01-01'),
        CONSTRAINT chk_Isik_elukoht_pole_tyhikutest_ega_tyhi CHECK
(elukoht!~'^[[:space:]]*$'),
        CONSTRAINT chk_Isik_elukoht_ei_koosne_ainult_numbritest CHECK
(elukoht!~'^[[:digit:]]*$'),
        CONSTRAINT chk_Isik_reg_aeg_suurem_kui_synni_kp CHECK
(reg_aeg>=synni_kp),
        CONSTRAINT chk_Isik_isikukoodis_lubatud_symbolid CHECK
(isikukood~*'^[a-z0-9 \/-]+$'),
        CONSTRAINT FK_Isik_Isiku_seisundi_liik FOREIGN KEY
(isiku_seisundi_liik_kood) REFERENCES Isiku_seisundi_liik
(isiku_seisundi_liik_kood) ON DELETE No Action ON UPDATE Cascade,
        CONSTRAINT FK_Isik_Riik FOREIGN KEY (isikukoodi_riik) REFERENCES Riik
(riik_kood) ON DELETE No Action ON UPDATE Cascade
)
;

I expect the statement to succeed. However, I get an error message
instead:

ERROR:  syntax error at or near "CONSTRAINT"
LINE 29: SET CONSTRAINT FK_Isik_Riik FOREIGN KEY (isikukoodi_riik) RE...

If I try to execute the same statement through pgAdmin (ver 4.3) query tool,
then it is completed without a problem and the table is created.

If I remove one or both foreign key declarations from the CREATE TABLE Isik
... statement, then the execution of the CREATE TABLE Isik ... statement
succeeds in psql.

The result of SELECT Version();
PostgreSQL 11.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313
(Red Hat 4.4.7-23), 64-bit


Sorry for having table names not in English (in Estonian). I tried to
replicate the bug with artificially created statements.

CREATE TABLE A(a_id INTEGER PRIMARY KEY);
CREATE TABLE B(b_id INTEGER PRIMARY KEY);

CREATE TABLE C(c_id INTEGER PRIMARY KEY,
a_id INTEGER NOT NULL,
b_id INTEGER NOT NULL,
CONSTRAINT ak_c UNIQUE (a_id, b_id),
CONSTRAINT fk_c_a FOREIGN KEY (a_id) REFERENCES A(a_id) ON DELETE No Action
ON UPDATE Cascade,
CONSTRAINT fk_c_b FOREIGN KEY (b_id) REFERENCES B(b_id) ON DELETE No Action
ON UPDATE Cascade);

However, executing these through psql succeeds without a problem.


The same problem appears in a machine with Ubuntu 18.04 and PostgreSQL
10.5.

The problem DOES NOT APPEAR (i.e., CREATE TABLE Isik ... succeeds in psql)
in a machine with CentOS release 5.7 (Final)
and PostgreSQL  8.4.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
4.1.2 2
0080704 (Red Hat 4.1.2-44), 64-bit