Postgres Pro
Downloads
Home   >   mailing lists

Thread: [GENERAL] Error in column constraint syntax

[GENERAL] Error in column constraint syntax

From
Rich Shepard
Date:
   I'm encountering DDL syntax errors using postgres-9.6.1 that I've not
before seen, and I'm having a difficult time finding the cause when reading
the appropriate sections of the manual; Section 5.3.1 in this case:

CREATE TABLE Agencies (
   org_name VARCHAR(48) PRIMARY KEY,
   acronym VARCHAR(8) DEFAULT ' ',
   org_lvl VARCHAR(8) DEFAULT 'State'
      CONSTRAINT invalid_agency_level
      CHECK org_lvl IN ('Federal', 'State', 'County', 'City', 'Local', 'Regional'),
   website VARCHAR(64) DEFAULT ' ',
   created_at TIMESTAMP,
   created_by TEXT NOT NULL,
   updated_at TIMESTAMP,
   updated_by TEXT NOT NULL,
   comment TEXT
);

   psql reports:

:86: ERROR:  syntax error at or near "org_lvl"
LINE 6:      CHECK org_lvl IN ('Federal', 'State', 'County', 'City',...
                    ^
   Having a default value or a named constraint makes no difference, and the
maximum size of the org_lvl column is that of the longest string so I am not
seeing the source of my error.

   Where, other than Section 5.3.1 of the manual can I learn why there's an
error?

   I also have several errors of 'table not found' yet to be understood and resolved
but this one is at the top of the file.

TIA,

Rich

Re: [GENERAL] Error in column constraint syntax

From
"David G. Johnston"
Date:
On Tue, Dec 27, 2016 at 9:39 AM, Rich Shepard <rshepard@appl-ecosys.com> wrote:
  I'm encountering DDL syntax errors using postgres-9.6.1 that I've not
before seen, and I'm having a difficult time finding the cause when reading
the appropriate sections of the manual; Section 5.3.1 in this case:

​5.3.1 is instructional.  The SQL Command chapter is the authoritative source for syntax.


CREATE TABLE Agencies (
  org_name VARCHAR(48) PRIMARY KEY,
  acronym VARCHAR(8) DEFAULT ' ',
  org_lvl VARCHAR(8) DEFAULT 'State'
     CONSTRAINT invalid_agency_level
     CHECK org_lvl IN ('Federal', 'State', 'County', 'City', 'Local', 'Regional'),
  website VARCHAR(64) DEFAULT ' ',
  created_at TIMESTAMP,
  created_by TEXT NOT NULL,
  updated_at TIMESTAMP,
  updated_by TEXT NOT NULL,
  comment TEXT
);

  psql reports:

:86: ERROR:  syntax error at or near "org_lvl"
LINE 6:      CHECK org_lvl IN ('Federal', 'State', 'County', 'City',...
                   ^
  Having a default value or a named constraint makes no difference, and the
maximum size of the org_lvl column is that of the longest string so I am not
seeing the source of my error.

​https://www.postgresql.org/docs/9.6/static/sql-createtable.html

CHECK ( expression ) [ NO INHERIT ] |   -- you are missing the mandatory ( ) surrounding your expression
​
All of the examples in 5.3.1 have a "(" following the word CHECK as well...


  Where, other than Section 5.3.1 of the manual can I learn why there's an
error?
​

If the error message in question doesn't provide a sufficient level of detail I'm doubting that anything in the documentation is going to help with diagnosis.

If you just mean where can you learn what is correct/expected then the SQL Commands chapter is the authoritative source for syntax and usage.

David J.

​

 

Re: [GENERAL] Error in column constraint syntax

From
Rich Shepard
Date:
On Tue, 27 Dec 2016, David G. Johnston wrote:

> ​5.3.1 is instructional.  The SQL Command chapter is the
> authoritative source for syntax.

David,

   I'll read that, too.

> CHECK ( expression ) [ NO INHERIT ] |   -- you are missing the mandatory (
> ) surrounding your expression
> All of the examples in 5.3.1 have a "(" following the word CHECK as well...

   Yes, I see now that I misplaced the opening parenthesis and missed the
second closing parenthesis at the end of the IN list.

   I thought I had tested this syntax; obviously I did not.

Thanks,

Rich