user3635123 user3635123 - 3 months ago 10
SQL Question

PostgreSQL: Unable to drop a specific table named "user"

I'm unable to delete a specific table in my PostgreSQL database. That table is called "user". When I try to run the snippet of code below,

import psycopg2
conn = psycopg2.connect("dbname='mydatabase' user='postgres' host='localhost' password='mypassword'")
cur = conn.cursor()
cur.execute("DROP TABLE user;")
conn.commit()
conn.close()


It spits out the following error

Traceback (most recent call last):
File "dev_psycog.py", line 20, in <module>
cur.execute("DROP TABLE user;")
psycopg2.ProgrammingError: syntax error at or near "user"
LINE 1: DROP TABLE user;


I can delete any other table in my database just fine, but I can't seem to delete my table called "user". Is it because "user" is a reserved keyword?

Answer

Quote "user" as below

import psycopg2
conn = psycopg2.connect("dbname='mydatabase' user='postgres' host='localhost' password='mypassword'")
cur = conn.cursor()
cur.execute('DROP TABLE "user";')  
conn.commit()
conn.close()

See here.

There is a second kind of identifier: the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes (").