Avoid clashes between table name and keywords psql

I need to create a table in postgreSQL like the following but “USER” is a keyword and i am getting an error.

Btw shouldn’t it work anyways since I am writing “User” instead of “USER”?

CREATE TABLE User(

);

Answer

First, don’t use reserved words for identifiers. It is simple enough to change this to something like Users.

Second, Postgres by default insists that identifiers are case-insensitive by lower-casing them. So, User and USER and user are the same thing — and they all conflict with the keyword/reserved word.

All the rules about identifiers are pretty well explained in the documentation.

You can get around this by insisting on case-sensitivity by escaping the name:

 CREATE TABLE "User" ( . . .

But I strongly, strongly discourage this. There are zillions of ways of constructing an identifier without having to reuse the few hundred reserved words. Just be a little more creative in your naming.