To Understand A Sentence About The Case In Sql/ddl -queries

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 th

Solution 1:

In PostgreSQL, an unquoted identifier is converted into lowercase:

CREATETABLE "MYTABLE" (id INTNOTNULL);

CREATETABLE "mytable" (id INTNOTNULL);

INSERTINTO    "MYTABLE"
VALUES  (1);

INSERTINTO    "mytable"
VALUES  (2);

SELECT*FROM    mytable;

---2SELECT*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.

Solution 2:

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

Solution 3:

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?