Are Postgresql Column Names Case-Sensitive

Are PostgreSQL column names case-sensitive?

Identifiers (including column names) that are not double-quoted are folded to lowercase in PostgreSQL. Column names that were created with double-quotes and thereby retained uppercase letters (and/or other syntax violations) have to be double-quoted for the rest of their life:

"first_Name"

Values (string literals / constants) are enclosed in single quotes:

'xyz'

So, yes, PostgreSQL column names are case-sensitive (when double-quoted):

SELECT * FROM persons WHERE "first_Name" = 'xyz';

Read the manual on identifiers here.

My standing advice is to use legal, lower-case names exclusively so double-quoting is not needed.

Why PostgreSQL does not like UPPERCASE table names?

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.

Postgres Case Sensitivity

In PostgreSQL unquoted names are case-insensitive. Thus SELECT * FROM hello and SELECT * FROM HELLO are equivalent.

However, quoted names are case-sensitive. SELECT * FROM "hello" is not equivalent to SELECT * FROM "HELLO".

To make a "bridge" between quoted names and unquoted names, unquoted names are implicitly lowercased, thus hello, HELLO and HeLLo are equivalent to "hello", but not to "HELLO" or "HeLLo" (OOPS!).

Thus, when creating entities (tables, views, procedures, etc) in PostgreSQL, you should specify them either unquoted, or quoted-but-lowercased.

---

To convert existing tables/views/etc you can use something like ALTER TABLE "FOO" RENAME TO "foo".

Or, try to modify dump from MSSQL to make it "PostgreSQL-compatible" (so that it will contain foos or "foo"s but not "FOO"s).

• Either by explicitly editing dump file. (If you're using Linux, you can do sed -r 's/"[^"]+"/\L\0/g' dumpfile — however be warned that this command may also modify text in string literals.)
• Or by specifying some options when getting dump from MSSQL. (I'm not sure if there are such options in MSSQL, never used it, but probably such options should exist.)

Select uppercase table name on postgreSQL is not working

PostgresSQL column and table names are case insensitive, unless you surround them with quotes (like you do, "SELECT * FROM \"" + name + "\" ;").

See this answer: https://stackoverflow.com/a/21798517/1453822

How can I convert all columns in my database to case insensitive

Along the same lines as the original, then, you should be able to do the following. This renames all columns that are not already in lower case, by extracting them from the information_schema, generating SQL for the changes, storing it to a file then executing the SQL again.

\t on
select 'ALTER TABLE '||'"'||table_name||'"'||' RENAME COLUMN '||'"'||column_name||'"'||' TO ' || lower(column_name)||';' 
from information_schema.columns 
where table_schema = 'public' and lower(column_name) != column_name
\g /tmp/go_to_lower
\i /tmp/go_to_lower

---

Related Topics