sort order

On Sunday, December 11, 2011 8:17:41 am Abraham, Danny wrote:

On PG 9.0.4, Windows, Encoding and Collate WIN1252 trying to get a
database to sort according to the ascii order. Example:
Select t from test order by t
Should be exactly like
Select t from test order by ascii(t).

Can it be done? How?

Not after a database has been created.

For more info see:
http://www.postgresql.org/docs/9.0/interactive/locale.html
In particular LC_COLLATE.

Thanks

Danny

--
Adrian Klaver
adrian.klaver@gmail.com

This is exactly what I am trying to do -
To find a CREATE DATABASE statement that will produce a database that has a binary sorting.

Thanks

Danny

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@gmail.com]
Sent: Monday, December 12, 2011 5:53 PM
To: pgsql-general@postgresql.org
Cc: Abraham, Danny
Subject: Re: [GENERAL] sort order

On Sunday, December 11, 2011 8:17:41 am Abraham, Danny wrote:

On PG 9.0.4, Windows, Encoding and Collate WIN1252 trying to get a
database to sort according to the ascii order. Example:
Select t from test order by t
Should be exactly like
Select t from test order by ascii(t).

Can it be done? How?

Not after a database has been created.

For more info see:
http://www.postgresql.org/docs/9.0/interactive/locale.html
In particular LC_COLLATE.

Thanks

Danny

--
Adrian Klaver
adrian.klaver@gmail.com

"Abraham, Danny" <danny_abraham@bmc.com> writes:

This is exactly what I am trying to do -
To find a CREATE DATABASE statement that will produce a database that has a binary sorting.

Setting its LC_COLLATE to "C" ought to do that.

BTW, as of 9.1 you can control this at finer granularity than database
level, see the COLLATE clause.

regards, tom lane

On Monday, December 12, 2011 7:55:37 am Abraham, Danny wrote:

This is exactly what I am trying to do -
To find a CREATE DATABASE statement that will produce a database that has a
binary sorting.

http://www.postgresql.org/docs/9.0/interactive/sql-createdatabase.html
CREATE DATABASE name
[ [ WITH ] [ OWNER [=] user_name ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ LC_COLLATE [=] lc_collate ]
[ LC_CTYPE [=] lc_ctype ]
[ TABLESPACE [=] tablespace ]
[ CONNECTION LIMIT [=] connlimit ] ]

Be sure and read the Notes section at the bottom for important caveats.

Thanks

--
Adrian Klaver
adrian.klaver@gmail.com

Thanks a lot.

Worked fine with .....

CREATE DATABASE danny
Template template0
LC_CTYPE = 'C'
LC_COLLATE = 'C';

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, December 12, 2011 6:07 PM
To: Abraham, Danny
Cc: Adrian Klaver; pgsql-general@postgresql.org
Subject: Re: [GENERAL] sort order

"Abraham, Danny" <danny_abraham@bmc.com> writes:

This is exactly what I am trying to do -
To find a CREATE DATABASE statement that will produce a database that has a binary sorting.

Setting its LC_COLLATE to "C" ought to do that.

BTW, as of 9.1 you can control this at finer granularity than database
level, see the COLLATE clause.

regards, tom lane