Subject: alter table add x wrong error position

From: jian he <[email-masked]>
Date: Mon, 8 Jan 2024 13:59:54 +0800

hi.
Maybe this is a small printout err_position bug.

create table atacc2 ( test int, a int, b int) ;
success tests:
alter table atacc2 add CONSTRAINT x PRIMARY KEY (id, b );
alter table atacc2 add CONSTRAINT x PRIMARY KEY (id, b a);
alter table atacc2 add CONSTRAINT x PRIMARY KEYa (id, b);

tests have problem:
alter table atacc2 add constraints x unique (test, a, b);
ERROR:  syntax error at or near "("
LINE 1: alter table atacc2 add constraints x unique (test, a, b);

          ^
ADD either following with the optional keyword "COLUMN" or
"CONSTRAINT"  as the doc.
so I should expect the '^' point at "constraints"?

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 7 Jan 2024 23:17:38 -0700

> so I should expect the '^' point at "constraints"?

It’s finding “… add column_name data_type column_constraint” then dies at
the parenthesis.  So indeed the care t should be pointing where it probably
is, at the parenthesis that the error is referring to.

David J.

---

From: Alvaro Herrera <[email-masked]>
Date: Fri, 12 Jan 2024 10:58:21 +0100

> so I should expect the '^' point at "constraints"?

Here you're saying to add a column called constraints, of
type x; then UNIQUE is parsed by columnDef as ColQualList, which goes to
the ColConstraintElem production starting with the UNIQUE keyword: 

			| UNIQUE opt_unique_null_treatment opt_definition OptConsTableSpace

so the next thing could be opt_unique_null_treatment or opt_definition
or OptConsTableSpace or going back to ColQualList, but none of those
start with a '(' parens.  So the ( doesn't have a match and you get the
syntax error.

If you don't misspell CONSTRAINT as "constraints", there's no issue.

I don't see any way to improve this.  You can't forbid misspellings of
the keyword CONSTRAINT, because they can be column names.

alter table atacc2 add cnstrnt x unique (test, a, b);
ERROR:  error de sintaxis en o cerca de «(»
LÍNEA 1: alter table atacc2 add cnstrnt x unique (test, a, b);
                                                 ^

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/