Identifier Case Sensitivity in PostgreSQL

Skip to first unread message

Peter Hancox

May 12, 2013, 11:32:42 PM5/12/13
Having an issue with identifier case sensitivity in PostgreSQL.  Most databases fold unquoted identifiers to uppercase 
whereas PostgreSQL folds to lowercase.  As my code needs to be portable across multiple databases I specify the
identifier names in uppercase.

Running unit tests against H2 is working.  The schema is created with uppercase identifiers and the SQL
generated by MapperDao is either quoted uppercase, unquoted uppercase, or (in the case of table names) 
unquoted camelcase.  All of which are either uppercase or folded to uppercase by the database.

PostgreSQL is currently failing on insert.  The SQL generated by MapperDao is

insert into Suburb(NAME,POSTCODE,STATE,COUNTRY) values('Longueville','2066','NSW','Australia') RETURNING "ID"

which is being aborted because column "ID" doesn't exist.  The "id" column exists but "ID" doesn't.  Because "ID" 
is quoted in the SQL it is failing.  If it was unquoted as for all the other identifiers in the generated SQL then it
should work.

Suggestions as to how best to fix or work around this?

May 13, 2013, 6:28:00 AM5/13/13
I have a mysql solution that might be relevant to you.
Define your db to be utf8_general_ci (case insensitive) collation.
Then it doesn't matter if you call the column ID, Id, iD or id.
I never encountered a usecase that needs the db col names to be case sensitive...

Peter Hancox

May 13, 2013, 6:44:47 AM5/13/13
Thanks for the suggestion.  PostgreSQL has a number of options that can be set but I'd like to leave
pursuing that approach as a last recourse.  As an aside, I thought that setting collation options would 
most likely affect column data rather than the schema identifiers.  Though at the end of the day, the 
schema identifiers are typically data in the columns of a system table anyway.

What I'm hoping for as a solution is that the RETURNING "ID" clause can be unquoted as per the rest
of the query; allowing the databases default case folding to apply.

Kostas Kougios

May 13, 2013, 6:51:41 AM5/13/13
Hi, the "returning id" part of the sql is not controlled by mapperdao. Mapperdao does a batch insert informing the driver that it requires the autogenerated keys. Probably postgres adds that bit.

How do you map the key? Key("ID")? Can you share your mapping?


Sent from my self, sorry for any typos
You received this message because you are subscribed to the Google Groups "mapperdao" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
For more options, visit

Tim Pigden

May 13, 2013, 6:57:33 AM5/13/13
Peter - are you using upper case in the DDL when you create the tables?
I had to use all lower case in postgres after which I think I didn't have to worry about the case elsewhere.
Tim Pigden
Optrak Distribution Software Limited
+44 (0)1992 517100
Optrak Distribution Software Ltd is a limited company registered in England and Wales.
Company Registration No. 2327613 Registered Offices: Orland House, Mead Lane, Hertford, SG13 7AT England 
This email and any attachments to it may be confidential and are intended solely for the use of the individual to whom it is addressed. Any views or opinions expressed are solely those of the author and do not necessarily represent those of Optrak Distribution Software Ltd. If you are not the intended recipient of this email, you must neither take any action based upon its contents, nor copy or show it to anyone. Please contact the sender if you believe you have received this email in error.

Peter Hancox

May 13, 2013, 6:58:35 AM5/13/13
object SuburbEntity extends Entity[Int, SurrogateIntId, Suburb] {
  val id = key("ID") autogenerated (
  val name = column("NAME") to (
  val postcode = column("POSTCODE") to (_.postcode)
  val state = column("STATE") to (_.state)
  val country = column("COUNTRY") to (
  def constructor(implicit m: ValuesMap) =
    new Suburb(name, postcode, state, country) with SurrogateIntId {
      val id: Int =

Can you point me at the class that interfaces to the driver?

Seems odd that some identifiers are quoted and others aren't?


Peter Hancox

May 13, 2013, 7:05:08 AM5/13/13
I'm using Slick to generate the DDL.  As input to Slick I use uppercase names for compatibility with 
most databases.  Then I override the Slick PostgreSQL driver to force identifiers to lowercase.

So in PostgreSQL all my identifiers are lowercase.

The problem is that the insert query generated from MapperDao is putting the return id in quotes as "ID".
When PostgreSQL sees the quotes it doesn't fold the identifier name to lowercase.  So in the example
Suburb -> suburb
NAME -> name
"ID" -> ID (thus my problem since PostgreSQL has it as "id" not "ID"

Konstantinos Kougios

May 13, 2013, 7:11:54 AM5/13/13
When working with multiple databases (i.e. the test cases of mapperdao) the approach I used is to assume case insensitive and create all tables and all mappings with a consistent naming, i.e. create table Product(id int, name varchar) and then use key("id"). I recommend this highly.

MapperDao does:

connection.prepareStatement(sql, autogenerated=true)

and then

insert into Suburb(NAME,POSTCODE,STATE,COUNTRY) values(?,?,?,?)

I believe the "RETURNING "ID"" part is only part of the postgresql exception msg and not part of mapperdao debug logging.

As a workaround you can do val id=key(database match {
    case "postgresql"=>"id"
    case _=>"ID"


Peter Hancox

May 13, 2013, 7:28:49 AM5/13/13
The case I'm using is consistent for each database.  I use the native folding so that when working
with the database directly using other tools I won't have to be quoting everything all the time.

The problem I have is that quoting "ID" on the insert breaks this one particular case.  Your suggested 
work-around would solve the problem but I would have to implement it in 99% of my entity classes and
though not technically a problem would look a bit strange having just one quoted field in different case
to all other identifiers.

Curious that you say MapperDao just does 

connection.prepareStatement(sql, autogenerated=true)

and then

insert into Suburb(NAME,POSTCODE,STATE,COUNTRY) values(?,?,?,?)

I doubt PostgreSQL is adding the RETURNING "ID" as PostgreSQL knows nothing 
about an "ID" column; it knows about a "id" column which is the primary key.  So if
PostgreSQL was adding that clause then it would have added RETURNING "id"

I'd be interested to know the content of the "sql" variable that is passed to prepareStatement ?

Peter Hancox

May 13, 2013, 9:53:10 AM5/13/13
The "sql" variable contains the insert statement as you said.  However, MapperDao also passes "ID" 
as the name of the column to return.  This is correct based on what I've specified.  However, the following
code in the PostgreSQL driver is what is causing my grief:

        sb.append(" RETURNING ");
        for (int i=0; i<columns.length; i++) {
            if (i != 0)
                sb.append(", ");
            // If given user provided column names, quote and escape them.
            // This isn't likely to be popular as it enforces case sensitivity,
            // but it does match up with our handling of things like
            // DatabaseMetaData.getColumns and is necessary for the same
            // reasons.

So unless I can find a way to override the PostgreSQL driver code in some way then I'll have to
do something similar to the work around you proposed.  Unlikely overriding the PostgreSQL code
will be workable, particularly if I want to use a pooled datasource or something similar in production.

If other database drivers don't quote the column identifiers specified for returning auto-generated
columns then I might be able to get away with using lowercase for the id for all databases.

Konstantinos Kougios

May 13, 2013, 3:37:54 PM5/13/13
Hi Peter,

    yes, mapperdao passes the ID column to the jdbc driver, informing the driver that it is autogenerated. But mapperdao as you found out doesn't create the "RETURNING "ID"". It's the jdbc driver who adds that bit along with the double quotes.

What tools require this case-insensitive naming?

I know mysql is case sensitive, postgres+sql server + oracle isn't but maybe you get into more trouble further down the line.


Peter Hancox

May 13, 2013, 5:07:16 PM5/13/13
Not so much specific tools that require a case-insensitive mapping; mostly just when I'm 
working with a command line interface.

e.g., I don't want to be typing select "COL_A", "COL_B" from "TABLE_A" with quotes all 
over the place when I'm writing ad-hoc queries in PostgreSQL.

Using lowercase when specifying the autogenerated ID column works for H2 and PostgreSQL.
PostgreSQL driver is now being passed id which it quotes as "id" matching the name in its
schema.  H2 is being passed id which presumably is being used unquoted by the H2 driver
and thus folded to uppercase which matches the name in its schema.

I don't currently use MySQL but if its case sensitive then I'd be back with a similar problem
that I have for PostgreSQL!  In which case the only uniformly workable solution would be to
pass identifiers of different case to MapperDao (and any other library) based on the taret

Perhaps a class along the lines of the Scala s"somestring" string implementation; I'll have to 
look at how this is done.  Good chance to improve my knowledge of Scala internals.

Or alternately, is there somewhere I can hook in to / override where MapperDao passes the
ID column to the driver so that I can force the column names specified by the application
code to lowercase if the database is PostgreSQL?  This would allow me to put that logic in
one place rather than in each entity class definition.  Would be a more MapperDao only
solution but completely hidden in the framework rather than relying on application code
to use the "special string" class for identifier names.

Konstantinos Kougios

May 15, 2013, 6:20:51 PM5/15/13
Hi Peter, I am curious how this works for all databases including mysql. I might then be able to provide a generic solution. What's your status, what databases did you integrate so far?

In the meantime I suppose you impl a solution to lowercase the id for postgres, right?

Peter Hancox

May 15, 2013, 7:05:43 PM5/15/13
It wouldn't work for any database that folded to uppercase and where the driver quotes the 
column name for auto generated columns.  I believe MySQL is case-sensitive with no
folding of identifier names (???) in which case my implementation would work but the
auto-generated column names would be lowercase and other columns would be uppercase.
Bit messy and personally I wouldn't go with it.

The more generic approach I'm playing with is something along the lines of:

  implicit class DbIdHelper(val s: String) extends AnyVal {
    def asDbId(implicit dbms: String) = {
      dbms match {
        case "postgresql" => s.toLowerCase()
        case _ => s.toUpperCase()

Then in the application code:

object SuburbEntity extends Entity[Int, SurrogateIntId, Suburb] {
  implicit val dbms = "postgresql"
  val id = key("ID".asDbId) autogenerated (
  val name = column("NAME".asDbId) to (

Still messing with it but looking for something like implicit classes and values to
hide a lot of the boiler plate.  The basic idea being to have a function that when applied
to a string, alters the case based on the target database.  Same approach may well
be useful in other situations.

Wouldn't use implicit string variable in the final solution due to likely collision with
another string implicit.  Probably some enum / case class instead.  I'm still very
much a Scala newby unfortunately ;-(


Peter Hancox

May 16, 2013, 7:36:31 AM5/16/13
Can you tell me how I can find the dbms (e.g., h2, postgresql) from within the SuburbEntity?  Currently 
hardcoded as "postgresql" in the example.

In an earlier post I saw

object ProductEntity extends Entity[Int, SurrogateIntId, Product]
        override val databaseSchema = Some(Schema("test"))

        val id = key("id") sequence (Setup.database match {
            case "oracle" => Some("ProductSeq")
            case _ => None
        }) autogenerated (
but "Setup.database" doesn't appear to resolve anymore?

Konstantinos Kougios

May 16, 2013, 11:47:24 AM5/16/13
Entity isn't aware of the database (i.e. same entity can be used in 2 different databases) . Overall if you notice, mapperdao doesn't fix down singletons. You can have as many instances of MapperDao/QueryDao as you require and connect to different databases and configure some entities for database A and some others for B

Pretty much it comes down to your application to keep track of the configuration. If you connect to one database at a time you could i.e. have a similar to the "Setup" class I use for test cases:

object Config {
    val database = System.getProperty("database")

Looking also at your DbIdHelper, you might just want to subclass Entity and add the functionality you want reg. the id's and the database.


Peter Hancox

May 16, 2013, 7:26:24 PM5/16/13
Rather than subclass Entity, my current approach is to parameterise the SuburbEntity class

class SuburbEntity(implicit dbms: String) extends Entity[Int, SurrogateIntId, Suburb] with Logging {
  val id = key("ID".asDbId) autogenerated (
  val name = column("NAME".asDbId) to (

I'm guessing (limited Scala knowledge) that if I need to add more generic framework code that
I'd put it into a trait that I could reuse across all my entities.  Will see as the complexity of the
proof of concept grows.

Although current applications only connect to a single database and restart if the database changes,
I won't be limiting the new framework and will want to support connection to multiple databases of
different types at the same time.

Just about to start playing with one2many relations so no doubt will have some questions
on that.


Feb 18, 2016, 4:06:14 PM2/18/16
to mapperdao
Nice Article !

Really this will help to people of PostgreSQL Community. 
I have also prepared small note on this, Why identifiers are case sensitive in PostgreSQL. 
Reply all
Reply to author
0 new messages