Why PostgreSQL does not like UPPERCASE table names?

Question

I have recently tried to create some tables in PostgreSQL all in uppercase names. However in order to query them I need to put the table name inside the quotation "TABLE_NAME". Is there any way to avoid this and tell the postgres to work with uppercase name as normal ?

UPDATE

this query create a table with lowercase table_name

create table TABLE_NAME 
(
id integer,
name varchar(255)
)

However, this query creates a table with uppercase name "TABLE_NAME"

create table "TABLE_NAME"
(
id integer,
name varchar(255)
)

the problem is the quotations are part of the name now!! in my case I do not create the tables manually, another Application creates the table and the names are in capital letters. this cause problems when I want to use CQL filters via Geoserver.

Shaquana

Please show us your CREATE TABLE statement. – Tuinenga

postgresql.org/docs/current/static/… – Rooftop

There's a very answer with explanation at #6311596 HTH – Bushcraft

Answer 1

put table name into double quotes if you want postgres to preserve case for relation names.

Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.)

from docs (emphasis mine)

example with quoting:

t=# create table "UC_TNAME" (i int);
CREATE TABLE
t=# \dt+ UC

t=# \dt+ "UC_TNAME"
                      List of relations
 Schema |   Name   | Type  |  Owner   |  Size   | Description
--------+----------+-------+----------+---------+-------------
 public | UC_TNAME | table | postgres | 0 bytes |
(1 row)

example without quoting:

t=# create table UC_TNAME (i int);
CREATE TABLE
t=# \dt+ UC_TNAME
                      List of relations
 Schema |   Name   | Type  |  Owner   |  Size   | Description
--------+----------+-------+----------+---------+-------------
 public | uc_tname | table | postgres | 0 bytes |
(1 row)

So if you created table with quotes, you should not skip quotes querying it. But if you skipped quotes creating object, the name was folded to lowercase and so will be with uppercase name in query - this way you "won't notice" it.

Galilean

You didn't fully explain it. So then when he created using uppercase, it stuck, but then when he queried using uppercase, it got lowercased and didn't work. Is that right? – Tuinenga

I quoted docs - they say "you are advised to always quote a particular name or never quote it." which I believe means "when you create table without quotes no matter case you can select it without quotes. if your created with quotes, use quotes when querying". Or you telling something different? – Galilean

But then shouldn't the OP be able to access his table using upper or lowercase? I think he must have created the table with quotes, no? – Tuinenga

with quotes - he should. And I believe he can access, but does not like the fact that he needs to put quotes arounf table name and asks if he can skip quotes, just using uppercase. – Galilean

Gosh I love this site. I could finally sleep after this lol. Thank you! – Doublehung

Interestingly you can use uppercase umlauts like Ü, Ä, Ö without quotes. – Tipple

Answer 2

The question implies that double quotes, when used to force PostgreSQL to recognize casing for an identifier name, actually become part of the identifier name. That's not correct. What does happen is that if you use double quotes to force casing, then you must always use double quotes to reference that identifier.

Background:

In PostgreSQL, names of identifiers are always folded to lowercase unless you surround the identifier name with double quotes. This can lead to confusion.

Consider what happens if you run these two statements in sequence:

CREATE TABLE my_table (
    t_id serial,
    some_value text
);

That creates a table named my_table.

Now, try to run this:

CREATE TABLE My_Table (
    t_id serial,
    some_value text
);

PostgreSQL ignores the uppercasing (because the table name is not surrounded by quotes) and tries to make another table called my_table. When that happens, it throws an error:

ERROR:  relation "my_table" already exists

To make a table with uppercase letters, you'd have to run:

CREATE TABLE "My_Table" (
    t_id serial,
    some_value text
);

Now you have two tables in your database:

 Schema |           Name            | Type  |  Owner   
--------+---------------------------+-------+----------
 public | My_Table                  | table | postgres
 public | my_table                  | table | postgres

The only way to ever access My_Table is to then surround the identifier name with double quotes, as in:

SELECT * FROM "My_Table"

If you leave the identifier unquoted, then PostgreSQL would fold it to lowercase and query my_table.

Bhayani

Answer 3

In simple words, Postgres treats the data in (double-quotes) "" as case-sensitive. And remaining as lowercase.

Example: we can create 2-columns with names DETAILS and details and while querying:

select "DETAILS" 

return DETAILS column data and

select details/DETAILS/Details/"details"

returns details column data.

Moya

Which basically means all the queries in postgres are by default converted to lowercase letters. But, If you want to create a column with Upper case letters then you must specify it inside the "". And get the data of in uppercase Column(DETAILS), you need to specify the column name in (double-quotes)"". So that postgres treats the data in quotes as Case-sensitive and gives the expected column(DETAILS) values. – Moya