Postgres Drop Table Syntax Error

Postgres drop table syntax error

User is a reserved keyword in Postgres. You'll have to put it in quotes if you want to refer to an actual table named user:

DROP TABLE "user";

Probably best to stay away from using reserved keywords as table names if you can help it. It usually ends up creating weird problems down the road. Users might be a better name for a table.

PostgreSQL: Unable to drop a specific table named user

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 (").

Sql syntax error on DROP TABLE

• USE should be followed by a database name.
• There is extra DROP Table.
• Drop the table if exists DROP TABLE IF EXISTS users first.
• Then create the table.

Like this:

CREATE DATABASE IF NOT EXISTS myusers; 
USE myusers;
DROP TABLE IF EXISTS `users`;

CREATE TABLE `myusers`.`users` 
    ( 
    `username`  VARCHAR(45) NOT NULL, 
    `password`  VARCHAR(45) NULL, 
    `firstname` VARCHAR(45) NOT NULL, 
    `lastname`  VARCHAR(45) NULL,
    `phone`     INT NULL, 
    PRIMARY KEY (`username`) 
);

Can't drop a table in postgres

The problem is that your Users table is mixed case (and object names in Postgres are case sensitive). Without quotes around the table name Postgres will case fold the supplied name to "users"-- which doesn't exist. Your solution of quoting the table name works, not because users is a reserved name but because, by quoting it, you are telling Postgres to drop the "Users" table rather than the "users" table.

using execute in postgres returns syntax error

%I is replaced as a whole identifier. If you want to concatenate things, you need to do it before replacement.

You can test/debug this for yourself by inspecting the result of the format() function:

select format('DROP TABLE IF EXISTS report.products_%I',42);

returns DROP TABLE IF EXISTS report.products_"42"

you need to use:

select format('DROP TABLE IF EXISTS report.%I',concat('products_', 42));

which correctly returns DROP TABLE IF EXISTS report.products_42

(obviously you need to replace 42 with your variable.

Drop database which has a minus in its name in Postgres

You could try renaming it first perhaps? ALTER DATABASE "a-b" RENAME TO adashb; Then follow up with a DROP DATABASE adashb;

postgresql ERROR: syntax error at or near PRIMARY

Should all primary keys auto-increment in PostgreSQL?

No, not necessarily.

In the first statement you are not specifying a data type for the column, if you don't want it to be an auto increment, simply use integer:

 songplay_id integer PRIMARY KEY,

---

Note that serial is not a "constraint", it's a data type definition which is a shortcut for an integer column that takes its default value from a sequence.

---

Related Topics