PostgreSQL – Different Syntax to Add/Subtract Interval

datatypesoperatorpostgresqltimestamp

Till now I used the following syntax to add an interval to a timestamp:

select now() + '5 year';

This worked fine till I tried to subtract the interval which results in a syntax error.

invalid input syntax for type timestamp with time zone: "5 year"
LINE 1: select now() - '5 year'

In the documentation I learned that the syntax actually is:

select now() - interval '5 year'

So my questions are:
Why does select now() + '5 year' work at all?
Does it work only by accident and it might break in a future Postgresql release?

Best Answer

Like @a_horse explained, there are two operators available for the expression now() - '5 year':

now() returns timestamp with time zone (timestamptz).
'5 year' is an untyped string literal.

SELECT oprleft::regtype, oprname, oprright::regtype
FROM   pg_operator
WHERE  oprname = '-'
AND    oprleft = 'timestamptz'::regtype;

         oprleft          | oprname |         oprright         
--------------------------+---------+--------------------------
 timestamp with time zone | -       | timestamp with time zone
 timestamp with time zone | -       | interval
(2 rows)

The exact reason for the choice can be found in the manual in the chapter Operator Type Resolution:

[...]

2. Check for an operator accepting exactly the input argument types. If one exists (there can be only one exact match in the set of operators considered), use it. [...]

a. If one argument of a binary operator invocation is of the unknown type, then assume it is the same type as the other argument for this check. [...]

[...]

Bold emphasis mine. Read the whole chapter to understand the process fully.

The same type is preferred if one argument type is unknown and a matching operator is available. There is an operator for timestamptz - timestamptz, bingo. The operator is resolved here. Fortunately, '5 years' is illegal input for timestamptz, else this might result in confusion!

The operator resolves to timestamptz - interval after adding an explicit type cast:

now() - interval '5 year'  -- always the way to go

LC_COLLATE

LC_COLLATE affects comparisons between strings. In practice, the most visible effect is the sort order. LC_COLLATE='C' (or POSIX which is a synonym) means that it's the byte order that drives comparisons, whereas a locale in the language_REGION form means that cultural rules will drive the comparisons.

An example with french names, executed from inside an UTF-8 database:

select firstname from (values ('bernard'), ('bérénice'), ('béatrice'), ('boris'))
 AS l(firstname)
order by firstname collate "fr_FR";

Result:

 firstname 
-----------
 béatrice
 bérénice
 bernard
 boris

béatrice comes before boris, because the accented E compares against O as if it was non-accented. It's a cultural rule.

This differs from what happens with a C locale:

select firstname from (values ('bernard'), ('bérénice'), ('béatrice'), ('boris')) 
 AS l(firstname)
order by firstname collate "C";

Result:

 firstname 
-----------
 bernard
 boris
 béatrice
 bérénice

Now the names with accented E are pushed at the end of the list. The byte representation of é in UTF-8 is the hexadecimal C3 A9 and for o it's 6f. c3 is greater than 6f so under the C locale, 'béatrice' > 'boris'.

It's not just accents. There a more complex rules with hyphenation, punctuation, and weird characters like œ. Weird cultural rules are to be expected in every locale.

Now if the strings to compare happen to mix different languages, as when having a firstname column for people from all other the world, it might be that any particular locale should not dominate, anyway, because different alphabets for different languages have not been designed to be sorted against each other.

In this case C is a rational choice, and it has the advantage of being faster, because nothing can beat pure byte comparisons.

LC_CTYPE

Having LC_CTYPE set to 'C' implies that C functions like isupper(c) or tolower(c) give expected results only for characters in the US-ASCII range (that is, up to codepoint 0x7F in Unicode).

Because SQL functions like upper(), lower() or initcap are implemented in Postgres on top of these libc functions, they're affected by this as soon as there are non US-ASCII characters in strings.

Example:

test=> show lc_ctype;
  lc_ctype   
-------------
 fr_FR.UTF-8
(1 row)

-- Good result
test=> select initcap('élysée');
 initcap 
---------
 Élysée
(1 row)

-- Wrong result
-- collate "C" is the same as if the db has been created with lc_ctype='C'
test=> select initcap('élysée' collate "C");
 initcap 
---------
 éLyséE
(1 row)

For the C locale, é is treated as an uncategorizable character.

Similarly wrong results are also obtained with regular expressions:

test=> select 'élysée' ~ '^\w+$';
 ?column? 
----------
 t
(1 row)

test=> select 'élysée' COLLATE "C" ~ '^\w+$';
 ?column? 
----------
 f
(1 row)

Best Answer

Related Solutions

Postgresql – create index – invalid memory alloc request size

PostgreSQL – Impact of LC_CTYPE on Database

LC_COLLATE

LC_CTYPE

Related Question