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.