Обсуждение: in PostgreSQL 9.0.2 / pgAdmin III - ERROR: relation "[table name]" already exists
in PostgreSQL 9.0.2 / pgAdmin III - ERROR: relation "[table name]" already exists
От
thewhitelantern
Дата:
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.
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
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.