Melanie Plageman <melanieplageman@gmail.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