create table in postgresql using double quotes

Question

Whenever we are creating a table, just we are specify as, CREATE TABLE table_name in any of the database system. When we are creating a table in postgre then we use, CREATE TABLE "tabl_name". Is there any difference when we are using the double quotes " " for creation of table in postgreSql.

Answer 1

If you use double quotes round the table name then you are making the name case sensitive. By default tables names (and identifiers in general) that are not quoted are case insensitive.

So for example using the following create statement without quotes: CREATE TABLE TestTable...

means that all of the following can be used to reference the table: TESTTABLE, testtable, TesTTabLe

whereas if you used quotes in the create statement: CREATE TABLE "TestTable"...

then only "TestTable" can be used to reference this table, the others would not work.

The manual contains a section on exactly this topic that explains it in more detail: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

Answer 2

There's no difference if you only use lower case (contrary to SQL standard which says it should be uppercase).

If you use any uppercase letter without quotes it will be converted to lowercase.

If you use any uppercase letter with quotes it wan't be converted and you'd have to use it all the time to access this table (or column or any other identifier).

The best is to not use quotes in identifiers.