Tag Archives: postgresql

Never use “reserved keyword” as column name

The title says it all. Never EVER use the reserved keyword of a database system for a column name. You will meet problems that cost lots of time (which isn’t worth at all!)

If someone tells you about this little trick:

    <class name="long.model.User" table="USER">
        <cache usage="read-write"/>
        <id name="id" column="ID">
            <generator class="sequence">
                <param name="sequence">user_seq</param>

        <property name="position" column=""POSITION""/>

Then just kick him in the ass! Why we should use something as dirty as “"”?

If you still not believe, take the example above, then try to UPDATE the position of a random user.

As you may guess:

UPDATE public.user set position = 'MANAGER'; // NOT WORK 
UPDATE public.user set 'position' = 'MANAGER'; // NOT WORK 
UPDATE public.user u set u.'position' = 'MANAGER'; // NOT WORK AGAIN 
UPDATE public.user u set u.POSITION = 'MANAGER'; // NOT WORK! 


Here’s what you MUST do if already get fallen into the trap

UPDATE public.user set "POSITION" = "MANAGER"; // WORK! Windows only 
UPDATE public.user set "POSITION" = 'MANAGER'; // WORK! only Linux 

Hence don’t try to trick the system. Curiosity is good, but you might need to pay for it by several hours playing with how Postgresql deal with case-sensitive name. Nice to find out, but either way, it isn’t a portable database script.