Re: [GENERAL] Error in column constraint syntax

2016-12-27 Thread Rich Shepard 

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



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Error in column constraint syntax

2016-12-27 Thread David G. Johnston 
On Tue, Dec 27, 2016 at 9:39 AM, Rich Shepard 
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.

​

[GENERAL] Error in column constraint syntax

2016-12-27 Thread Rich Shepard 

  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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general