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

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?!?

--
View this message in context: http://postgresql.1045698.n5.nabble.com/in-PostgreSQL-9-0-2-pgAdmin-III-ERROR-relation-table-name-already-exists-tp3318303p3318303.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

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.

Hi.

On Saturday 25 Dec 2010 at 15:10 thewhitelantern wrote:

[snip]

> 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

I suspect that you are running ALL of the code above again, with the "WHERE"
clause appended. What you actually need to do is ONLY run the SELECT
instruction again. The error is because you are trying to create the table
(called a "relation" in this context) again.

Instead of running the whole lot again the second time, just run the query:

SELECT
title
, category
FROM
entries
WHERE
id = 524
;

All the stuff before that only has to be done once.

> So does this mean that I have to delete the database EVERY TIME I CHANGE
> THE CODE?!?

No :)

Cheers,

Mark