Postgres Pro
Downloads
Home   >   mailing lists

Thread: failed SQL CREATE TABLE

failed SQL CREATE TABLE

From
Tim Wilson
Date:
Hi everyone,

I'm trying to create a database and I'm getting some errors when I try
to create some of the tables. I'm going to post the SQL and the errors
and I'd appreciate it if there's a guru on the list who'd have a look
and let me know where I'm messing up. Everything looks OK to me, but
doesn't it always in situations like this. :-)

Here's the SQL:

CREATE TABLE location (
  location_id SERIAL PRIMARY KEY,
  room VARCHAR(15),
  location_name VARCHAR(30),
  address VARCHAR(30),
  city VARCHAR(20),
  state CHAR(2),
  zip VARCHAR(10)
);
CREATE TABLE submitter (
  username CHAR(10) PRIMARY KEY,
  first_name VARCHAR(20),
  last_name VARCHAR(30),
  email VARCHAR(40)
);
CREATE TABLE participant (
  participant_id SERIAL PRIMARY KEY,
  org_name VARCHAR(40),
  activity_name VARCHAR(40)
);
CREATE TABLE event (
  event_id SERIAL PRIMARY KEY,
  event_name VARCHAR(30),
  location_id INTEGER,
  start TIMESTAMP,
  end TIMESTAMP,
  description TEXT,
  username CHAR(10),
  CONSTRAINT location_exists FOREIGN KEY (location_id)
    REFERENCES location
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT username_exists FOREIGN KEY (username)
    REFERENCES submitter
    ON UPDATE CASCADE
    ON DELETE SET NULL
);
CREATE TABLE game (
  our_score INTEGER,
  their_score INTEGER)
  INHERITS (event);
CREATE TABLE tournament (
  finish VARCHAR(15))
  INHERITS (event);
CREATE TABLE meeting (
  agenda_url TEXT,
  minutes_url TEXT)
  INHERITS (event);
CREATE TABLE event_participant (
  event_id INTEGER PRIMARY KEY,
  participant_id INTEGER PRIMARY KEY,
  CONSTRAINT event_exists FOREIGN KEY (event_id)
    REFERENCES event
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT participant_exists FOREIGN KEY (participant_id)
    REFERENCES participant
    ON UPDATE CASCADE
    ON DELETE SET NULL
);

And the errors I get:

eventdb=# \i /home/wilson/make_eventdb.sql
psql:/home/wilson/make_eventdb.sql:9: NOTICE:  CREATE TABLE will create
implicit sequence 'location_location_id_seq' for SERIAL column
'location.location_id'
psql:/home/wilson/make_eventdb.sql:9: NOTICE:  CREATE TABLE/PRIMARY KEY
will create implicit index 'location_pkey' for table 'location'
CREATE
psql:/home/wilson/make_eventdb.sql:15: NOTICE:  CREATE TABLE/PRIMARY KEY
will create implicit index 'submitter_pkey' for table 'submitter'
CREATE
psql:/home/wilson/make_eventdb.sql:20: NOTICE:  CREATE TABLE will create
implicit sequence 'participant_participant_id_seq' for SERIAL column
'participant.participant_id'
psql:/home/wilson/make_eventdb.sql:20: NOTICE:  CREATE TABLE/PRIMARY KEY
will create implicit index 'participant_pkey' for table 'participant'
CREATE
psql:/home/wilson/make_eventdb.sql:37: ERROR:  parser: parse error at or
near "end"
psql:/home/wilson/make_eventdb.sql:41: ERROR:  Relation 'event' does not
exist
psql:/home/wilson/make_eventdb.sql:44: ERROR:  Relation 'event' does not
exist
psql:/home/wilson/make_eventdb.sql:48: ERROR:  Relation 'event' does not
exist
psql:/home/wilson/make_eventdb.sql:60: ERROR:  CREATE TABLE/PRIMARY KEY
multiple primary keys for table 'event_participant' are not allowed

Thanks again for any help anyone can offer.

-Tim

--
Tim Wilson      |   Visit Sibley online:   | Check out:
Henry Sibley HS |  http://www.isd197.org   | http://www.zope.com
W. St. Paul, MN |                          | http://slashdot.org
wilson@visi.com |  <dtml-var pithy_quote>  | http://linux.com

Re: failed SQL CREATE TABLE

From
Chris
Date:
Hi Tim,

The initial thing that springs to mind is that 'end' is a reserved keyword
(for use with transactions), so if you want to use it in a table you have
to quote it ("end").

Either that, or, rename the field to for example end_time.

>I'm trying to create a database and I'm getting some errors when I try
>to create some of the tables. I'm going to post the SQL and the errors
>and I'd appreciate it if there's a guru on the list who'd have a look
>and let me know where I'm messing up. Everything looks OK to me, but
>doesn't it always in situations like this. :-)
>
>Here's the SQL:

<snip>

>CREATE TABLE event (
>   event_id SERIAL PRIMARY KEY,
>   event_name VARCHAR(30),
>   location_id INTEGER,
>   start TIMESTAMP,
>   end TIMESTAMP,
>   description TEXT,
>   username CHAR(10),
>   CONSTRAINT location_exists FOREIGN KEY (location_id)
>     REFERENCES location
>     ON UPDATE CASCADE
>     ON DELETE SET NULL,
>   CONSTRAINT username_exists FOREIGN KEY (username)
>     REFERENCES submitter
>     ON UPDATE CASCADE
>     ON DELETE SET NULL
>);

<snip>

>psql:/home/wilson/make_eventdb.sql:37: ERROR:  parser: parse error at or
>near "end"


-----------------
      Chris Smith
http://www.squiz.net/

Re: failed SQL CREATE TABLE

From
Tom Lane
Date:
Tim Wilson <wilson@isis.visi.com> writes:
> ...
>   start TIMESTAMP,
>   end TIMESTAMP,
> ...
> psql:/home/wilson/make_eventdb.sql:37: ERROR:  parser: parse error at or
> near "end"

"end" is a reserved word.

            regards, tom lane

Re: failed SQL CREATE TABLE

From
"Joshua b. Jore"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'd also question whether you can designate two columns as primary keys
separately. I rewrote the primary key to be a separate constraint. It's
more clear (to me anyway) that both keys are primary keys when used
together. And are you on TCLUG? Your name looks familiar.

Josh

CREATE TABLE event_participant (
   event_id INTEGER,
   participant_id INTEGER,
   CONSTRAINT event_pkey PRIMARY KEY(event_id,participant_id),
   CONSTRAINT event_exists FOREIGN KEY (event_id)
      REFERENCES event
      ON UPDATE CASCADE
      ON DELETE SET NULL,
   CONSTRAINT participant_exists FOREIGN KEY (participant_id)
      REFERENCES participant
      ON UPDATE CASCADE
      ON DELETE SET NULL
);


Joshua b. Jore
http://www.greentechnologist.org

On Mon, 8 Apr 2002, Tim Wilson wrote:

> Hi everyone,
>
> I'm trying to create a database and I'm getting some errors when I try
> to create some of the tables. I'm going to post the SQL and the errors
> and I'd appreciate it if there's a guru on the list who'd have a look
> and let me know where I'm messing up. Everything looks OK to me, but
> doesn't it always in situations like this. :-)
>
> Here's the SQL:
>
> CREATE TABLE location (
>   location_id SERIAL PRIMARY KEY,
>   room VARCHAR(15),
>   location_name VARCHAR(30),
>   address VARCHAR(30),
>   city VARCHAR(20),
>   state CHAR(2),
>   zip VARCHAR(10)
> );
> CREATE TABLE submitter (
>   username CHAR(10) PRIMARY KEY,
>   first_name VARCHAR(20),
>   last_name VARCHAR(30),
>   email VARCHAR(40)
> );
> CREATE TABLE participant (
>   participant_id SERIAL PRIMARY KEY,
>   org_name VARCHAR(40),
>   activity_name VARCHAR(40)
> );
> CREATE TABLE event (
>   event_id SERIAL PRIMARY KEY,
>   event_name VARCHAR(30),
>   location_id INTEGER,
>   start TIMESTAMP,
>   end TIMESTAMP,
>   description TEXT,
>   username CHAR(10),
>   CONSTRAINT location_exists FOREIGN KEY (location_id)
>     REFERENCES location
>     ON UPDATE CASCADE
>     ON DELETE SET NULL,
>   CONSTRAINT username_exists FOREIGN KEY (username)
>     REFERENCES submitter
>     ON UPDATE CASCADE
>     ON DELETE SET NULL
> );
> CREATE TABLE game (
>   our_score INTEGER,
>   their_score INTEGER)
>   INHERITS (event);
> CREATE TABLE tournament (
>   finish VARCHAR(15))
>   INHERITS (event);
> CREATE TABLE meeting (
>   agenda_url TEXT,
>   minutes_url TEXT)
>   INHERITS (event);
> And the errors I get:
>
> eventdb=# \i /home/wilson/make_eventdb.sql
> psql:/home/wilson/make_eventdb.sql:9: NOTICE:  CREATE TABLE will create
> implicit sequence 'location_location_id_seq' for SERIAL column
> 'location.location_id'
> psql:/home/wilson/make_eventdb.sql:9: NOTICE:  CREATE TABLE/PRIMARY KEY
> will create implicit index 'location_pkey' for table 'location'
> CREATE
> psql:/home/wilson/make_eventdb.sql:15: NOTICE:  CREATE TABLE/PRIMARY KEY
> will create implicit index 'submitter_pkey' for table 'submitter'
> CREATE
> psql:/home/wilson/make_eventdb.sql:20: NOTICE:  CREATE TABLE will create
> implicit sequence 'participant_participant_id_seq' for SERIAL column
> 'participant.participant_id'
> psql:/home/wilson/make_eventdb.sql:20: NOTICE:  CREATE TABLE/PRIMARY KEY
> will create implicit index 'participant_pkey' for table 'participant'
> CREATE
> psql:/home/wilson/make_eventdb.sql:37: ERROR:  parser: parse error at or
> near "end"
> psql:/home/wilson/make_eventdb.sql:41: ERROR:  Relation 'event' does not
> exist
> psql:/home/wilson/make_eventdb.sql:44: ERROR:  Relation 'event' does not
> exist
> psql:/home/wilson/make_eventdb.sql:48: ERROR:  Relation 'event' does not
> exist
> psql:/home/wilson/make_eventdb.sql:60: ERROR:  CREATE TABLE/PRIMARY KEY
> multiple primary keys for table 'event_participant' are not allowed
>
> Thanks again for any help anyone can offer.
>
> -Tim
>
> --
> Tim Wilson      |   Visit Sibley online:   | Check out:
> Henry Sibley HS |  http://www.isd197.org   | http://www.zope.com
> W. St. Paul, MN |                          | http://slashdot.org
> wilson@visi.com |  <dtml-var pithy_quote>  | http://linux.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (OpenBSD)
Comment: For info see http://www.gnupg.org

iD8DBQE8sna+fexLsowstzcRAo4CAJoDPmlRS+rinWHnFaLbntQiG0MsBACcCrgJ
kxZ0MCfBOBZV8fTszGqfd7Q=
=0kBS
-----END PGP SIGNATURE-----