To understand a sentence about the case in SQL/DDL -queries

Question

This question is based on this answer.

What does the following sentence mean?

Finally, don't use mixed case identifiers. Do everything lowercase, so you don't have to quote them.

Does it mean that I should change the commands such as CREATE TABLE, USER_ID and NOT NULL to lowercase? - It cannot because it would be against common naming conventions.

Answer 1

No, I think the gentleman referred to using all lowercase identifiers, e.g. table, column etc. names. This should have no impact on the commands like CREATE TABLE etc. that you use.

Marc

Answer 2

I have no idea why he said that. In SQL Server, at least, the case of an identifier doesn't matter. Maybe it does in other DBMS systems?

Answer 3

In PostgreSQL, an unquoted identifier is converted into lowercase:

CREATE TABLE "MYTABLE" (id INT NOT NULL);

CREATE TABLE "mytable" (id INT NOT NULL);

INSERT
INTO    "MYTABLE"
VALUES  (1);

INSERT
INTO    "mytable"
VALUES  (2);

SELECT  *
FROM    mytable;

---
  2

SELECT  *
FROM    MYTABLE;

---
  2

Both queries will return 2, since they are issued against "mytable", not "MYTABLE".

To return 1 (i. e. issue a query against "MYTABLE", not "mytable"), you need to quote it:

SELECT  *
FROM    "MYTABLE";

---
  1

CREATE TABLE etc. are not identifiers, they are reserved words.

You can use them in any case you like.