Using PostgreSQL Course: Error

Q&A DQ Courses
, ,
#1

Screen Link:
https://app.dataquest.io/m/114/using-postgresql/5/sql-transactions

My Code: 

import psycopg2
conn = psycopg2.connect("dbname=dq user=dq")
cur = conn.cursor()

query = "CREATE TABLE notes ( \
            id INTEGER PRIMARY KEY, \
            body TEXT, \
            title TEXT \
            );"

cur.execute(query)

conn.commit()

conn.close()

What I expected to happen:
I expect the code to run without any error

What actually happened:

ProgrammingErrorTraceback (most recent call last)
<ipython-input-1-9eb9ce41164c> in <module>()
      4 query = "CREATE TABLE notes (             id INTEGER PRIMARY KEY,             body TEXT,             title TEXT             )"
      5 
----> 6 cur.execute(query)
      7 
      8 conn.commit()

ProgrammingError: relation "notes" already exists
1 Like
#2

As the error indicates, it is likely that you ended up running your code more than once.

The first time you ran it, notes got created. The 2nd time your ran it, you get an error like above indicating that notes already exists.

If you want to be able to run the code again so that notes gets created again, then I think you will have to first drop that table -

conn = psycopg2.connect("dbname=dq user=dq")
cur = conn.cursor()
cur.execute("DROP TABLE notes")
conn.commit()
conn.close()
#3

But if I run the provided solution, it gives me the answer without refreshing or dropping any tables.

I got the same error here as well. This time, I followed the DataQuest format.

https://app.dataquest.io/m/114/using-postgresql/6/autocommitting

#4

This is what I tried for the initial link you shared, in order -

  1. Ran your code. It ran without issue. Ran the solution code. Got that error.
  2. Dropped that table.
  3. Ran your code. It ran without issue. Ran your code again. Got that error.
  4. Ran solution code. Got that error.
  5. Dropped that table.
  6. Ran solution code. Ran your code. Got that error.

The error is pretty straightforward. You can only get that error if you try to create a table if the table already exists. And for the Mission link you shared initially, that can only happen if you ran some code to create that table at least once.

Dropping the table is likely the only solution here. Anything else, I am not really sure what it could be given what all I tried above.

1 Like
#5

Okay. Thanks. Will drop the table when I experience this error again.

#6

I had the same problem and it was inconsistent. I dropped the table, tried again - got the same error, then dropped the table a second time and got an error that the table did not exist. Created the table and got the error that the table was already there. I refreshed the browser, dropped the table and then it worked.

#7

For these cases, it is always best to use DROP TABLE IF EXISTS like this:

conn = psycopg2.connect("dbname=dq user=dq")
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS notes")
cur.execute("CREATE TABLE notes(id integer PRIMARY KEY, body text, title text)")
conn.commit()
conn.close()

This way, you can run it any number of times without error.

Best,
Sahil

4 Likes
Not allowing me to 'Submit Answer'