pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
changes to table creation syntax in 7.1.2?
4+ messages / 3 participants
[nested] [flat]

* changes to table creation syntax in 7.1.2?
@ 2001-08-22 04:23 Jayson Callaway <jayson.callaway@iona.com>
  2001-08-29 18:24 ` Re: changes to table creation syntax in 7.1.2? Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-08-29 20:34 ` Re: changes to table creation syntax in 7.1.2? Jason Earl <jdearl@yahoo.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Jayson Callaway @ 2001-08-22 04:23 UTC (permalink / raw)
  To: pgsql-sql

In postgres 7.0.x I had some working code that lookes something like:

CREATE TABLE category
(
    uid int4 PRIMARY KEY,
    description text NOT NULL,
    parent int4 NULL REFERENCES category(uid)
)

After upgrading to postgres 7.1.2 however this syntax is not accepted
anymore. I receive an error that says it can not create the reference
because the table category does not exist.

How do I setup this type of reference structure in 7.1.2? Did the syntax
change?

I am running under Linux.
--
Jayson Callaway





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: changes to table creation syntax in 7.1.2?
  2001-08-22 04:23 changes to table creation syntax in 7.1.2? Jayson Callaway <jayson.callaway@iona.com>
@ 2001-08-29 18:24 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Stephan Szabo @ 2001-08-29 18:24 UTC (permalink / raw)
  To: Jayson Callaway <jayson.callaway@iona.com>; +Cc: pgsql-sql

On Wed, 22 Aug 2001, Jayson Callaway wrote:

> In postgres 7.0.x I had some working code that lookes something like:
> 
> CREATE TABLE category
> (
>     uid int4 PRIMARY KEY,
>     description text NOT NULL,
>     parent int4 NULL REFERENCES category(uid)
> )
> 
> After upgrading to postgres 7.1.2 however this syntax is not accepted
> anymore. I receive an error that says it can not create the reference
> because the table category does not exist.
> 
> How do I setup this type of reference structure in 7.1.2? Did the syntax
> change?

Hmm, that's odd, that should have still worked (and does work on
reasonably current sources).  I'd have thought we'd have seen reports
before now if this syntax got broken, but maybe not.  Only other thing I
could think of would be if the table name was a double quoted name of
mixed case and not such in the references constraint.

As an interim solution you can use alter table to add the references
constraint after the table creation.




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: changes to table creation syntax in 7.1.2?
  2001-08-22 04:23 changes to table creation syntax in 7.1.2? Jayson Callaway <jayson.callaway@iona.com>
@ 2001-08-29 20:34 ` Jason Earl <jdearl@yahoo.com>
  2001-08-29 21:42   ` RE: changes to table creation syntax in 7.1.2? Jayson Callaway <jayson.callaway@iona.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Jason Earl @ 2001-08-29 20:34 UTC (permalink / raw)
  To: Jayson Callaway <jayson.callaway@iona.com>; pgsql-sql

It looks like it works here :(.  Do you have an error
message?

processdata=# select version();
                            version                   
        
---------------------------------------------------------------
 PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by
GCC 2.95.4
(1 row)

processdata=# CREATE TABLE category
processdata-# (
processdata(#     uid int4 PRIMARY KEY,
processdata(#     description text NOT NULL,
processdata(#     parent int4 NULL REFERENCES
category(uid)
processdata(# )
processdata-# 
processdata-# ;
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit
index 'category_pkey' for table 'category'
NOTICE:  CREATE TABLE will create implicit trigger(s)
for FOREIGN KEY check(s)
CREATE

--- Jayson Callaway <jayson.callaway@iona.com> wrote:
> In postgres 7.0.x I had some working code that
> lookes something like:
> 
> CREATE TABLE category
> (
>     uid int4 PRIMARY KEY,
>     description text NOT NULL,
>     parent int4 NULL REFERENCES category(uid)
> )
> 
> After upgrading to postgres 7.1.2 however this
> syntax is not accepted
> anymore. I receive an error that says it can not
> create the reference
> because the table category does not exist.
> 
> How do I setup this type of reference structure in
> 7.1.2? Did the syntax
> change?
> 
> I am running under Linux.
> --
> Jayson Callaway
> 
> 
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org


__________________________________________________
Do You Yahoo!?
Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger
http://im.yahoo.com



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* RE: changes to table creation syntax in 7.1.2?
  2001-08-22 04:23 changes to table creation syntax in 7.1.2? Jayson Callaway <jayson.callaway@iona.com>
  2001-08-29 20:34 ` Re: changes to table creation syntax in 7.1.2? Jason Earl <jdearl@yahoo.com>
@ 2001-08-29 21:42   ` Jayson Callaway <jayson.callaway@iona.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Jayson Callaway @ 2001-08-29 21:42 UTC (permalink / raw)
  To: Jason Earl <jdearl@yahoo.com>; +Cc: pgsql-sql

Jason,

First of all, thanks for your response. I had given up that anyone was going
to respond. After seeing your response I went back and found what was making
the difference. The failing syntax below worked on 7.0.3 but not on the
7.1.2 version. Seeing your working example(unlike another response) gave me
the answer.

Jayson

This file of commands fail...

[jayson@grendal db]$ cat test
CREATE TABLE "test_cat" (
        "uid" int4 NOT NULL,
        "name" text NOT NULL,
        "description" text NOT NULL,
        "parent_category_uid" int4 NULL REFERENCES test_cat(uid)
);
CREATE UNIQUE INDEX test_cat_pkey on test_cat (uid);
[jayson@grendal db]$ psql -e -f test -d wr -U wr_web
CREATE TABLE "test_cat" (
        "uid" int4 NOT NULL,
        "name" text NOT NULL,
        "description" text NOT NULL,
        "parent_category_uid" int4 NULL REFERENCES test_cat(uid)
);
CREATE UNIQUE INDEX test_cat_pkey on test_cat (uid);
psql:test:6: NOTICE:  CREATE TABLE will create implicit trigger(s) for
FOREIGN KEY check(s)
psql:test:6: ERROR:  UNIQUE constraint matching given keys for referenced
table "test_cat" not found
[jayson@grendal db]$

While this set of commands is successsfull...

[jayson@grendal db]$ cat test
CREATE TABLE "test_cat" (
        "uid" int4 PRIMARY KEY,
        "name" text NOT NULL,
        "description" text NOT NULL,
        "parent_category_uid" int4 NULL REFERENCES test_cat(uid)
);
[jayson@grendal db]$ psql -e -f test -d wr -U wr_web
CREATE TABLE "test_cat" (
        "uid" int4 PRIMARY KEY,
        "name" text NOT NULL,
        "description" text NOT NULL,
        "parent_category_uid" int4 NULL REFERENCES test_cat(uid)
);
psql:test:6: NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index
'test_cat_pkey' for table 'test_cat'
psql:test:6: NOTICE:  CREATE TABLE will create implicit trigger(s) for
FOREIGN KEY check(s)
CREATE
[jayson@grendal db]$

> -----Original Message-----
> From: Jason Earl [mailto:jdearl@yahoo.com]
> Sent: Wednesday, August 29, 2001 4:35 PM
> To: Jayson Callaway; pgsql-sql@postgresql.org
> Subject: Re: [SQL] changes to table creation syntax in 7.1.2?
>
>
> It looks like it works here :(.  Do you have an error
> message?
>
> processdata=# select version();
>                             version
>
> ---------------------------------------------------------------
>  PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by
> GCC 2.95.4
> (1 row)
>
> processdata=# CREATE TABLE category
> processdata-# (
> processdata(#     uid int4 PRIMARY KEY,
> processdata(#     description text NOT NULL,
> processdata(#     parent int4 NULL REFERENCES
> category(uid)
> processdata(# )
> processdata-#
> processdata-# ;
> NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit
> index 'category_pkey' for table 'category'
> NOTICE:  CREATE TABLE will create implicit trigger(s)
> for FOREIGN KEY check(s)
> CREATE
>
> --- Jayson Callaway <jayson.callaway@iona.com> wrote:
> > In postgres 7.0.x I had some working code that
> > lookes something like:
> >
> > CREATE TABLE category
> > (
> >     uid int4 PRIMARY KEY,
> >     description text NOT NULL,
> >     parent int4 NULL REFERENCES category(uid)
> > )
> >
> > After upgrading to postgres 7.1.2 however this
> > syntax is not accepted
> > anymore. I receive an error that says it can not
> > create the reference
> > because the table category does not exist.
> >
> > How do I setup this type of reference structure in
> > 7.1.2? Did the syntax
> > change?
> >
> > I am running under Linux.
> > --
> > Jayson Callaway
> >
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
>
> __________________________________________________
> Do You Yahoo!?
> Get email alerts & NEW webcam video instant messaging with Yahoo!
> Messenger
> http://im.yahoo.com
>




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2001-08-29 21:42 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-08-22 04:23 changes to table creation syntax in 7.1.2? Jayson Callaway <jayson.callaway@iona.com>
2001-08-29 18:24 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-08-29 20:34 ` Jason Earl <jdearl@yahoo.com>
2001-08-29 21:42   ` Jayson Callaway <jayson.callaway@iona.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox