CODEWITHSUNDEEP
pythonpostgresqlcharacter-encodingpsycopg2
00__00__00
00__00__00

Reputation: 5327

drop postgreSQL table with Quotation Marks in name using psycopg2

I have a table in a schema.

my_schema.my_table

In the same schema, I also have a table named the same but with quotation marks

my_schema."my_table"

How can I drop my_schema."my_table" using psycopg2? (without risking to drop my_schema.my_table)

I have tried:

postgresConnection = psycopg2.connect(...)
from psycopg2 import sql 

cursor                = postgresConnection.cursor()
name_Table            = 'my_schema."my_table"'
cursor                = postgresConnection.cursor()
dropTableStmt   = "drop TABLE %s;"%name_Table;
cursor.execute(dropTableStmt)
postgresConnection.commit()
cursor.close();

But I get

SyntaxError: zero-length delimited identifier at or near """"
LINE 1: drop TABLE my_schema.""my_table"";

I have also tried:

from psycopg2 import sql 

cursor                = postgresConnection.cursor()
name_Table            = 'my_schema.""my_table""'
cur = postgresConnection.cursor()
cur.execute(sql.SQL("DROP table {table}").format(table=sql.Identifier(name_Table)))
postgresConnection.commit()
cursor.close();

But then I get:

UndefinedTable: table "my_schema.""my_table""" does not exist

Upvotes: 0

Views: 516

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246308

To escape double quotes in an object name, double the double quotes:

DROP TABLE my_schema."""my_table""";

Upvotes: 2

Related Questions