Re: in PostgreSQL 9.0.2 / pgAdmin III - ERROR: relation "[table name]" already exists

thewhitelantern wrote:
>
> I'm a super noob, and based on the advice of friends, I chose the PostgreSQL
> platform to install on my Mac 10.6.5 and I just bought a copy of Simply SQL.
>
> The problem is, I've created a database in the graphical interface other
> than the 'postgres' default database (for some reason, I was unable to do
> this same thing in the command line interface for psql console, I think I've
> followed the instructions, but neither here nor there)
>
> I click on the "SQL" button at the top and it brings up the Query tool.
>
> In chapter two of SIMPLY SQL, i'm just cutting and pasting / manually typing
> in this code
>
> _________________________________________________
> /*  Chapter 2: SELECT Overview  */
>
> /*  Title and Category of Entries  */
>
> CREATE TABLE entries
> (
>    id         INTEGER      NOT NULL  PRIMARY KEY
> , title      VARCHAR(99)  NOT NULL
> /*  Note: use DATETIME if your database system does not support TIMESTAMP
> */
> , created    TIMESTAMP     NOT NULL
> , updated    TIMESTAMP       NULL
> , category   VARCHAR(37)    NULL
> , content    TEXT           NULL
> );
>
> INSERT INTO entries
>    ( id , title , created , updated , category )
> VALUES
>    (423 , 'What If I Get Sick and Die?' , '2008-12-30' , '2009-03-11' ,
> 'angst')
> ;
> INSERT INTO entries
>    ( id , title , created , updated , category )
> VALUES
>    (524 , 'Uncle Karl and the Gasoline' , '2009-02-28' , NULL , 'humor')
> ;
> INSERT INTO entries
>    ( id , title , created , updated , category )
> VALUES
>    (537 , 'Be Nice to Everybody' , '2009-03-02' , NULL , 'advice')
> ;
> INSERT INTO entries
>    ( id , title , created , updated , category )
> VALUES
>    (573 , 'Hello Statue' , '2009-03-17' , NULL , 'humor')
> ;
> INSERT INTO entries
>    ( id , title , created , updated , category )
> VALUES
>    (598 , 'The Size of Our Galaxy' , '2009-04-03' , NULL , 'science')
> ;
>
> SELECT
>    title
> , category
> FROM
>    entries
> ;
> /* Results should look like this --
>     title                        category
>     What If I Get Sick and Die?  angst
>     Uncle Karl and the Gasoline  humor
>     Be Nice to Everybody         advice
>     Hello Statue                 humor
>     The Size of Our Galaxy       science
> */
> _____________________________________
>
> THe first time I execute it, it does it's table, just like in the book.
>
> However, I'm called to put a WHERE filter on the code at the end
>
> WHERE
>    id = 524
> ;
>
> THen when I try to execute it again, it gives this error message
>
> ERROR:  relation "entries" already exists
>
> ********** Error **********
>
> ERROR: relation "entries" already exists
> SQL state: 42P07
>
> Even when I try to Clear the History, delete the current document an make a
> new one, or even try to do a DROP staement, I still get this same message.
> The only way I can eliminate this message is if I delete the database in the
> pgAdmin Object browser, recreate a new database and do it again, at which
> point, it stops giving the error message, but only once.
>
> So does this mean that I have to delete the database EVERY TIME I CHANGE THE
> CODE?!?

Absolutely not.  It means do not run the CREATE TABLE every time you change
the SELECT.  Apparently you're running the entire script each time, not just
the SELECT.

--
Lew
Ceci n'est pas une pipe.