CODEWITHSUNDEEP
sqlpostgresqlidentifiercase-sensitivecase-insensitive
5122014009
5122014009

Reputation: 4102

Are PostgreSQL column names case-sensitive?

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?

Upvotes: 273

Views: 246409

Answers (5)

randomness
randomness

Reputation: 1457

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.

Example:

create table mytable (a int, "B" int);

select a from mytable; -- works
select "a" from mytable; -- also works

select "B" from mytable; -- works
select b from mytable; -- ERROR: column "b" does not exist
select "b" from mytable; -- ERROR: column "b" does not exist

As you can see, if the column contains an upper-case character, it must always be quoted when referencing it.

Tested with PostgreSQL 17.

Upvotes: 20

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658897

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.

System catalogs like pg_class store names in case-sensitive fashion - as provided when double-quoted (without enclosing quotes, obviously), or lower-cased if not.

Upvotes: 504

Tugay ÜNER
Tugay ÜNER

Reputation: 205

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))";

Upvotes: 1

Carlos Ernesto Lizarazo Sierra
Carlos Ernesto Lizarazo Sierra

Reputation: 51

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;

Upvotes: 5

Eugene Yarmash
Eugene Yarmash

Reputation: 150128

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.

Upvotes: 29

Related Questions