On 07/10/2017 11:07 PM, Tom Lane wrote:
rihad <rihad@xxxxxxx> writes:
On 07/10/2017 08:42 PM, Tom Lane wrote:
No, your indexes on text/char/varchar columns will be corrupted
(because their sort order will now be wrong). If you can reindex
them before doing anything more with the database, you'd be ok
... I think. Testing on a scratch copy of the database would be
a good idea, if this is valuable data.
Thank you, Tom. But can I still do it for the template1 database?
update pg_database set datcollate='en_US.UTF-8', datctype='en_US.UTF-8'
where datname='template1';
It'd be safe to do it on template0, and also on template1 as long as that
has only the original contents ...
It's empty, only hosting a few extensions.
... which that isn't. I'd suggest checking for indexes that might need
to be rebuilt with this query borrowed from the regression tests:
SELECT indexrelid::regclass, indrelid::regclass, iclass, icoll
FROM (SELECT indexrelid, indrelid,
unnest(indclass) as iclass, unnest(indcollation) as icoll
FROM pg_index) ss
WHERE icoll != 0 AND iclass !=
(SELECT oid FROM pg_opclass
WHERE opcname = 'text_pattern_ops' AND opcmethod =
(SELECT oid FROM pg_am WHERE amname = 'btree'));
I ran the query on our production database. Zero results. Do I have the
green light to
set datcollate='en_US.UTF-8', datctype='en_US.UTF-8'
for all our working databases? :) Or for template0 & template1 only?
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
