Are PostgreSQL column names case-sensitive?

Question

I have a db table say, persons in Postgres handed down by another team that has a column name say, "first_Name". Now am trying to use PG commander to query this table on this column-name.

select * from persons where first_Name="xyz";

And it just returns

ERROR: column "first_Name" does not exist

Not sure if I am doing something silly or is there a workaround to this problem that I am missing?

Aboveground

Answer 1

Identifiers (including column names) that are not double-quoted are folded to lower case in PostgreSQL. Identifiers created with double quotes retain upper case letters (and/or syntax violations) and have to be double-quoted for the rest of their life:

"first_Name"                 -- upper-case "N" preserved
"1st_Name"                   -- leading digit preserved
"AND"                        -- reserved word preserved

But (without double-quotes):

first_Name   → first_name    -- upper-case "N" folded to lower-case "n"
1st_Name     → Syntax error! -- leading digit
AND          → Syntax error! -- reserved word

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';

The manual on identifiers.

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

Weisbrodt

@Erwin Are upper case table names illegal according to an SQL standard or is this something PostGresSQL specific? – Bari

@ArtB: The SQL standard defines case insensitive identifiers, just like Postgres implements it. The only deviation: unquoted identifiers are folded to upper case in the standard, but pg lower-cases everything that isn't double-quoted. (Only relevant in rare corner cases.) Details in the manual here. – Weisbrodt

@ErwinBrandstetter: Can you please explain what you meant by 'The SQL standard defines case insensitive identifiers, just like Postgres implements it'? When we say case insensitive, does it not mean that we can name identifiers with any upper and lower case combination and still be able to retrieve them with any combination of upper and lower cases as long as we get the identifier name right? In this can we say Postgres implements the identifier case insensitivity standard defined for SQL? – Byebye

@adfs: I don't think I can explain it any better than I already did. For more, follow the link to the manual I provided repeatedly. – Weisbrodt

@adfs: In SQL, foobar, FOOBAR and FooBar are the same identifier. However "foobar", "FooBar" and "FOOBAR" are different identifiers – Stay

@a_horse_with_no_name yes, but under SQL foobar and FOOBAR are the same as "FOOBAR", under potgresql FOOBAR and foobar etc are the same as "foobar". – Leveller

@ErwinBrandstetter hey sorry to bother you am using Uppercase Table name and am using jbdcimpl whatever that not too important i have sql like this "select pseudo,Password,enabled from UTILISATEUR` where Pseudo = ? "` my problem i can't double-quotes and even with those it doesn't work you seem like sql expert guy please help – Zr

@KamelMili: I suggest to ask your question as question, providing all necessary information. Comments are not the place. You can always link to this answer for context. And you can leave a comment with the link to your related question here (to also get my attention). – Weisbrodt

here and thank you #36218661 – Zr

Good advice. Especially the word legal, so don't use reserved words in tables or columns. You might have to add double quotes and after that, wherever you reference that table it's going to be pain in the neck. – Sudoriferous

Even if you pass word like ' ABC1234' in create table command in postgresql , it will be converted to 'abc1234' in Postgre DB and next time when you search table with ' ABC1234', you will run into all sorts of issues.So its better to follow a lowercase naming convention while creating table in postgresql . – Es

When specifying a table name, use this syntax: select table."FOOBAR" from table; – Bodgie

Stupid pgsql SQL parser will forced to converted your identifiers to lower-case, so they are not found in pgsql database (case-sensitive). But most databases SQL parsers will do these, so the are intelligent. – Mayhem

@Erwin: "The SQL standard defines case insensitive identifiers," No! The exact sentence in ISO SQL "foundation" document is : "An <SQL language identifier> is equivalent to an <SQL language identifier> in which every letter that is a lower-case letter is replaced by the corresponding upper-case letter or letters." Thta does not means that the name is transformed before to be writed into system tables... but only that SQL identifiers are case insensitive that PostGreSQL does not respect... – Isocracy

Answer 2

To quote the documentation:

Key words and unquoted identifiers are case insensitive. Therefore:

UPDATE MY_TABLE SET A = 5;

can equivalently be written as:

uPDaTE my_TabLE SeT a = 5;

You could also write it using quoted identifiers:

UPDATE "my_table" SET "a" = 5;

Quoting an identifier makes it case-sensitive, whereas unquoted names are always folded to lower case (unlike the SQL standard where unquoted names are folded to upper 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.

If you want to write portable applications you are advised to always quote a particular name or never quote it.

Pattani

So basically the docs encourage spongebob case – Forehead

Answer 3

The column names which are mixed case or uppercase have to be double quoted in PostgresQL. So best convention will be to follow all small case with underscore.

Huesman

This is incorrect as per the explanation given by @erwin-brandstetter – Bregenz

How is this incorrect? If you have column names that are mixed case or upper case, in order to refer to them you need to put the identifier in double quotes. – Pharyngo

No you don't. If you have names with upper-case letters, Postgres will lowercase them. And when you query with upper-case letters, Postgres will also lowercase that. So you only need to use double quotes if you used them in the CREATE statement. It's nit-picky, but hey we are programmers. You have to get it exactly right or it's just wrong. – Roybal

Answer 4

if use JPA I recommend change to lowercase schema, table and column names, you can use next intructions for help you:

select
    psat.schemaname,
    psat.relname,
    pa.attname,
    psat.relid
from
    pg_catalog.pg_stat_all_tables psat,
    pg_catalog.pg_attribute pa
where
    psat.relid = pa.attrelid

change schema name:

ALTER SCHEMA "XXXXX" RENAME TO xxxxx;

change table names:

ALTER TABLE xxxxx."AAAAA" RENAME TO aaaaa;

change column names:

ALTER TABLE xxxxx.aaaaa RENAME COLUMN "CCCCC" TO ccccc;

Lentiginous

Answer 5

You can try this example for table and column naming in capital letters. (postgresql)

//Sql;
      create table "Test"
        (
        "ID" integer,
        "NAME" varchar(255)
        )



//C#
  string sqlCommand = $@"create table ""TestTable"" (
                                ""ID"" integer GENERATED BY DEFAULT AS IDENTITY primary key, 
                                ""ExampleProperty"" boolean,
                                ""ColumnName"" varchar(255))";

Maxon

Stupid pgsql SQL parser will not explain identifiers as case insensitive in sql query, but most RDMS DB will do. I found it will be better in pgAdmin SQL tool. – Mayhem