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.
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.
