Обсуждение: CREATE TABLE ... CONSTRAINT

Hi all

  I have been using postgreSQL for a while now and it is doing the job
I want it to.  Thank you to all pgsql-hackers for their good job.

  I        am   running    a       vanilla 6.3.2     installed    from
postgresql-{,clients-,devel-,data-}6.3.2-4.rpm.   Some of the problems
of taht distribution  stem  from the   rpm specfile  (for instance,  a
world-writable  and world-readable pg_pwd !!)  and are not interesting
for this list.

  However, there is one thing which I find annoying:

testdata=> CREATE TABLE test ( number int check ( number > 3 ) );

works fine, but the table is dumped with a different syntax:

CREATE TABLE test (number int4) CONSTRAINT test_number CHECK number > 3;

which is not accepted back:

testdata=> CREATE TABLE test (number int4) CONSTRAINT test_number CHECK number > 3;
ERROR:  parser: parse error at or near "constraint"

  Now, the  second  syntax  is standard  SQL   and the  parser  should
recognize  it, but in  any case at least  pg_dump compliance should be
aimed at.

Bye
Dario

--
######################################################################
# Dario Besseghini, system manager,
#                  Department of  Computer Science, University of Pisa
# http://www.di.unipi.it/~besseghi

Hello Dario,

lunedì, 17 agosto 98, you wrote:


DB> Hi all

DB>   I have been using postgreSQL for a while now and it is doing the job
DB> I want it to.  Thank you to all pgsql-hackers for their good job.

DB>   I        am   running    a       vanilla 6.3.2     installed    from
DB> postgresql-{,clients-,devel-,data-}6.3.2-4.rpm.   Some of the problems
DB> of taht distribution  stem  from the   rpm specfile  (for instance,  a
DB> world-writable  and world-readable pg_pwd !!)  and are not interesting
DB> for this list.

DB>   However, there is one thing which I find annoying:

testdata=>> CREATE TABLE test ( number int check ( number > 3 ) );

DB> works fine, but the table is dumped with a different syntax:

DB> CREATE TABLE test (number int4) CONSTRAINT test_number CHECK number > 3;

DB> which is not accepted back:

testdata=>> CREATE TABLE test (number int4) CONSTRAINT test_number CHECK number > 3;
DB> ERROR:  parser: parse error at or near "constraint"

DB>   Now, the  second  syntax  is standard  SQL   and the  parser  should
DB> recognize  it, but in  any case at least  pg_dump compliance should be
DB> aimed at.

DB> Bye
DB> Dario

DB> --
DB> ######################################################################
DB> # Dario Besseghini, system manager,
DB> #                  Department of  Computer Science, University of Pisa
DB> # http://www.di.unipi.it/~besseghi

Ciao Dario,

Seems that your syntax (CREATE TABLE test (number int4) CONSTRAINT
test_number CHECK number > 3;)
isn't SQL standard.

-----------------------
There are two kinds of Integrity CONSTRAINTs;
    - the Column constraint:
      CREATE TABLE test ( number int check ( number > 3 ) );
and
    - the Table constraint:
      CREATE TABLE test ( number int4 CONSTRAINT test_number CHECK (number > 3));

This one has the right syntax and it works.

Best regards,
  Jose'                            mailto:sferac@bo.nettuno.it

>
> Ciao Dario,
>
> Seems that your syntax (CREATE TABLE test (number int4) CONSTRAINT
> test_number CHECK number > 3;)
> isn't SQL standard.
>
> -----------------------
> There are two kinds of Integrity CONSTRAINTs;
>     - the Column constraint:
>       CREATE TABLE test ( number int check ( number > 3 ) );
> and
>     - the Table constraint:
>       CREATE TABLE test ( number int4 CONSTRAINT test_number CHECK (number > 3));
>
> This one has the right syntax and it works.
>
> Best regards,
>   Jose'                            mailto:sferac@bo.nettuno.it
>


Ciao Jose'.

  I know that the two forms you mention do work. pg_dump, however, not
I, uses the third form, which doesn't work.

$ pg_dump -t test testdata
\connect - besseghi
CREATE TABLE test (number int4) CONSTRAINT test_number CHECK number > 3;
COPY test FROM stdin;
\.

  The point of my post was  that I think  the postgresql parser should
accept pg_dump's output. Both solutions are legitimate: either pg_dump
or the  parser might be changed. In  the former  case, the docs should
also be  changed,  because both  `\h create table'  in  psql  and `man
create_table' put CONSTRAINT outside the parentheses.

Bye,
Dario

>
> Hi all
>
>   I have been using postgreSQL for a while now and it is doing the job
> I want it to.  Thank you to all pgsql-hackers for their good job.
>
>   I        am   running    a       vanilla 6.3.2     installed    from
> postgresql-{,clients-,devel-,data-}6.3.2-4.rpm.   Some of the problems
> of taht distribution  stem  from the   rpm specfile  (for instance,  a
> world-writable  and world-readable pg_pwd !!)  and are not interesting
> for this list.
>
>   However, there is one thing which I find annoying:
>
> testdata=> CREATE TABLE test ( number int check ( number > 3 ) );
>
> works fine, but the table is dumped with a different syntax:
>
> CREATE TABLE test (number int4) CONSTRAINT test_number CHECK number > 3;
>
> which is not accepted back:
>
> testdata=> CREATE TABLE test (number int4) CONSTRAINT test_number CHECK number > 3;
> ERROR:  parser: parse error at or near "constraint"
>
>   Now, the  second  syntax  is standard  SQL   and the  parser  should
> recognize  it, but in  any case at least  pg_dump compliance should be
> aimed at.

This is fixed.  6.4 beta is September 1. [ I ought to make this text a
macro.]


--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)