This seems a tad unfriendly: db1=# create table foo (f1 text collate "default"); ERROR: collation "default" for current database encoding "UTF8" does not exist LINE 1: create table foo (f1 text collate "default"); ^
Not being able to explicitly specify the default behavior is a no-no according to most people who have thought about language design for more than a moment. The reason it's failing is that "default" is entered into pg_collation with collencoding = 0 (SQL_ASCII), and the lookup code is designed to ignore all entries with collencoding different from the current database's encoding. So, in fact, the above command *will* work if you're in a SQL_ASCII database. Just not elsewhere. What I'm inclined to do about this is set "default"'s collencoding to -1, with the semantics of "works for any encoding", and fix the lookup routines to try -1 if they don't get a match with the database encoding. Having done that, we could also use -1 for "C" and "POSIX", thus avoiding having to make a bunch of duplicate entries for them. BTW, I would like to eventually have "C" and "POSIX" in there all the time (ie created by pg_collation.h), so that they can be used even in machines that don't have locale_t support. I haven't yet gotten around to reading the parts of the collation patch that might need to change to support this, so I'm not sure how much work it'd be. But I'd say that being able to do COLLATE "C" in an otherwise non-C database would cover a very large fraction of the user requests I've read about this, so being able to handle that case even without locale_t support would be really useful IMO. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers