It seems that libc's behaviour regarding LC_COLLATE in Linux/FreeBSD are two different stories, hence the seen results. (Switching LC_COLLATE between en_US.UTF-8 and POSIX, in FreeBSD it does not have any impact on orderring while in Linux does) I think i have resolved the issue, so the solution boils down to setting LC_COLLATE=C in our linux central production postgresql server. What is the shortest way to change the default locale on a production installation running PostgreSQL-8.3.9? Is there anything less painful than dump, initdb,restore? I know 8.4 allows for per-db locale settings, i am just asking if there is a way to solve the issue now without waiting till the migration to 8.4 Thanx! Στις Tuesday 02 February 2010 14:26:44 ο/η Achilleas Mantzios έγραψε: > Hi, i have the following problem, text ordering seems to behave incosistently across various lc_collate values, OS'es, PostgreSQL versions. > Some behaviour might be expected, some not, thats why i am asking to see where i stand with this. > Test Data > postgres@dynacom=# SELECT * from test_sort_order; > fooname > ------------------------- > Cylinder head cover No1 > Cylinder Liner No1 > Cylinder head No1 > (3 rows) > > Now the query > # SELECT * from test_sort_order order by fooname; > in PostgreSQL 8.3.7 on x86_64-unknown-linux-gnu (lc_collate=en_US.UTF-8) gives > fooname > ------------------------- > Cylinder head cover No1 > Cylinder head No1 > Cylinder Liner No1 > > while in all of > PostgreSQL 8.3.3 on i686-pc-linux-gnu (lc_collate=C) > PostgreSQL 8.3.7 (lc_collate=el_GR.UTF-8) on i386-unknown-freebsd6.3 > PostgreSQL 8.4.1 on x86_64-unknown-freebsd8.0 (lc_collate=en_US.UTF-8), gives > fooname > ------------------------- > Cylinder Liner No1 > Cylinder head No1 > Cylinder head cover No1 > > Database encoding is SQL_ASCII in all four cases. > > -- > Achilleas Mantzios > -- Achilleas Mantzios -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
