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

User · Accepted Answer

Identifiers  including column names  that are not double-quoted are folded to lower case in PostgreSQL  Column names that were created with double-quotes and thereby retained upper-case letters  and or other syntax violations  have to be double-quoted for the rest of their life   quot first Name quot   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  quot first Name quot     xyz    Read the manual on identifiers here  My standing advice is to use legal  lower-case names exclusively so double-quoting is not needed

User · Answer

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

User · Answer

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  quot XXXXX quot  RENAME TO xxxxx   change table names  ALTER TABLE xxxxx  quot AAAAA quot  RENAME TO aaaaa   change column names  ALTER TABLE xxxxx aaaaa RENAME COLUMN  quot CCCCC quot  TO ccccc

User · Answer

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

[sql] Are PostgreSQL column names case-sensitive?

Examples related to sql

Examples related to postgresql

Examples related to identifier

Examples related to case-sensitive

Examples related to case-insensitive