From a Windows installation of 12.0
testing=> select version();
version
------------------------------------------------------------
PostgreSQL 12.0, compiled by Visual C++ build 1914, 64-bit
(1 row)
Time: 0.283 ms
testing=> create table foo (
testing(> a text,
testing(> b text collate "C",
testing(> c text collate "en-US-x-icu",
testing(> d text generated always as (a) stored,
testing(> e text generated always as (b) stored,
testing(> f text generated always as (c) stored,
testing(> g text collate "C" generated always as (c) stored,
testing(> h text generated always as (c collate "C") stored,
testing(> i text collate "C" generated always as (c collate "en-US-x-icu")
stored
testing(> );
CREATE TABLE
Time: 81.025 ms
testing=> \d foo
Table "public.foo"
Column | Type | Collation | Nullable | Default
--------+------+-------------+----------+------------------------------------------------------
a | text | | |
b | text | C | |
c | text | en-US-x-icu | |
d | text | | | generated always as (a) stored
e | text | | | generated always as (b) stored
f | text | | | generated always as (c) stored
g | text | C | | generated always as (c) stored
h | text | | | generated always as (c COLLATE "C")
stored
i | text | C | | generated always as (c COLLATE
"en-US-x-icu") stored
testing=> select attnum, attname, attgenerated, collname from pg_attribute left
outer join pg_collation on pg_attribute.attcollation = pg_collation.oid where
attrelid = 'foo'::regclass and attnum > 0 order by attnum;
attnum | attname | attgenerated | collname
--------+---------+--------------+-------------
1 | a | | default
2 | b | | C
3 | c | | en-US-x-icu
4 | d | s | default
5 | e | s | default
6 | f | s | default
7 | g | s | C
8 | h | s | default
9 | i | s | C
(9 rows)
Time: 0.590 ms
testing=>
-----Original Message-----
From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of
Richard Hipp
Sent: Thursday, January 30, 2020 12:21 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Generated columns and COLLATE in the AS parens
On 1/30/20, Markus Winand <markus.win...@winand.at> wrote:
>
> Unfortunately, the “what would PostgreSQL do” approach doesn’t provide
> guidance here.
Maybe it does. PostgreSQL doesn't allow typeless columns, but it does
allow columns with unspecified collating sequences, does it not? What
if you have a normal column X with some collating sequence C and then
a generated column Y that as just "AS(X)". If you do comparisons on
column Y, which collating sequence does it use - the default or C?
Can you run that experiment for us?
Or maybe you are thinking the collating sequence of the expression in
the AS clause should only be carried through into the generated column
if it is explicitly stated, and not implied?
What happens if there is a collating sequence specified in the AS
clause and also another collating sequence on the column definitions?
CREATE TABLE t1(x TEXT, y TEXT AS (x COLLATE nocase) COLLATE rtree);
Which collating sequence should be used for "SELECT * FROM t1 WHERE y='xyz';"?
Regarding WWPD: If you can convince Tom Lane and/or Bruce Momjian to
send me an email that says "An explicit collating sequence at the
top-level of a GENERATED ALWAYS AS clause should be carried through as
the default collating sequence of the generated column itself", then
I'll change it. :-)
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
