Re: Bug in either collation docs or code

Melanie Plageman <melanieplageman(at)gmail(dot)com> writes:
> On postgres built off of master on my mac (sierra), the following is the
> output:

[ scratches head ... ] I get the same results on either Mac or Linux:

regression=# create database u8 encoding utf8 template template0;
CREATE DATABASE
regression=# \c u8
You are now connected to database "u8" as user "tgl".
u8=# CREATE TABLE test_col_mac (
u8(# a text COLLATE "de_DE",
u8(# b text COLLATE "es_ES"
u8(# );
CREATE TABLE
u8=# SELECT a < (SELECT 'foo'::TEXT COLLATE "fr_FR") FROM test_col_mac;
?column?
----------
(0 rows)

u8=# INSERT INTO test_col_mac VALUES('A','A');
INSERT 0 1
u8=# SELECT a < (SELECT 'foo'::TEXT COLLATE "fr_FR") FROM test_col_mac;
ERROR: could not determine which collation to use for string comparison
HINT: Use the COLLATE clause to set the collation explicitly.

Now, without the sub-select, it works:

u8=# SELECT a < ('foo'::TEXT COLLATE "fr_FR") FROM test_col_mac;
?column?
----------
t
(1 row)

because the explicit COLLATE is considered to determine the
collation of the comparison operator.

I wonder whether you're using stock Postgres, or something that
flattens scalar sub-selects?

regards, tom lane