Re: [SQL] ORDER BY collation order
Joe wrote: I guess that means the encoding of the respective template0 database is what determines the sort order (UTF8 on FreeBSD and LATIN1 on Debian), right? Unfortunately, I'm unable to change the FreeBSD cluster since it's shared with others at our hosting provider. Is there some way to override the cluster setting, or plans to allow for database-specific collation orders? The problem I see here is not just locale settings as Scott Marlowe pointed out but also the fact that the server runs on FreeBSD with UTF8 encoding. PostgreSQL relies on system (in this case libc) functions to handle locale stuff and FreeBSD does not support UTF8. This led to FreeBSD specific patch (which is in ports) that uses ICU library to handle UTF8 encoding. Sadly, it is not available for PostgreSQL 8.2 (original author made patches only up to 8.1 and I ported it to 8.3 later). Also that patch has to be enabled (which is not default iirc) when installing PostgreSQL package. So, since you say your cluster is hosted by some hosting company then your only options are either to convince them to upgrade to 8.3 (provided the system locale is ok for your purposes) or wait until per database collation is added (it was one of GSoC 08 projects and might get into 8.4) and then convince them to upgrade. -- Regards Petr Jelinek (PJMODOS) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] pg_dump in windows
HI all, I am facing a problem while taking a back up in windows. It is asking for a password while using pg_dump. Why it is asking for a password ?(it is not asking password in linux) Give some details about fe_sendauth in postgres . How i can avoid asking password in windows shile taking backup? Thanks and Regards, Ramasubramanian.G|Software Engineer - Delivery E-mail:[EMAIL PROTECTED] | Extn: 1607 Sobha Renaissance Information Technology (P) Ltd. An SEI-CMM, P-CMM & SSE-CMM Level 5 Company BS ISO/IEC 27001:2005 & ISO 9001:2000 Certified A Top 50 Fast Growing Technology Company (Deloitte, 2006 & 2007) | A Six Sigma Practice Company Phone: + 91 80 41951999 | Fax: + 91 80 41523300 A Top 50 Fast Growing Technology Company (Deloitte, 2006 & 2007) | A Six Sigma Practice Company Phone: + 91 80 41951999 | Fax: + 91 80 41523300 URL: www.renaissance-it.com | Video Conference: + 91 80 4125 ?
Re: [SQL] pg_dump in windows
Ramasubramanian wrote: > HI all, I am facing a problem while taking a back up in windows. It > is asking for a password while using pg_dump. Why it is asking for a > password ?(it is not asking password in linux) Give some details > about fe_sendauth in postgres . How i can avoid asking password in > windows shile taking backup? This is going to depend on how you have set up authentication on the server (the Linux box might not need any password) and how you provide a password from your client. See "The password file" in chapter 30 of the manuals for details on how to store a password in a (secured - make sure this is true!) file. -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ORDER BY collation order
Joe wrote: Thanks for the info. The hosting company already offers 8.3 databases. It's just that other things (we use tsearch2) have taken precedence over migrating to 8.3. How can I tell if the patch you mention is installed? As admin (or somebody with shell access) by running "pg_config --configure" should show "--with-icu" as one of configure parameters. As user you could try SELECTing upper() or lower() on some text with more exotic chars (anything non-ASCII should do) in database which has UTF8 encoding. If it works then ICU support is there. -- Regards Petr Jelinek (PJMODOS) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ORDER BY collation order
On Thu, Sep 18, 2008 at 10:35 PM, Joe <[EMAIL PROTECTED]> wrote:
> Hi Scott,
>
> Scott Marlowe wrote:
>>
>> no, not encoding, locale, such as en_US or C determine sort order.
>>
>
> OK, so I guess you're saying that whatever was in the LC_COLLATE environment
> variable at the time the template0 database was created determines the
> collation/sort order? Is that stored and visible somewhere?
It's set at the time of init.d and can't be changed without a
dump/initdb/restore cycle.
You can see by typing
show lc_collate ;
lc_collate
en_US
in psql.
>> You can use varchar_pattern_ops and ~*~ operator.
>> Search for those in the docs.
>
> What I found
> (http://www.postgresql.org/docs/8.2/static/indexes-opclass.html), talks
> about creating an index with varchar_pattern_ops but that presumably won't
> affect an ORDER BY result. I'm not quite sure where to find the "~*~"
> operator, although I did find similar ones in 9.7 Pattern Matching. In any
> case, I'm not sure how an operator helps in changing an ORDER BY result from
>
> "quoted"
> 123
> Abc
>
> to
>
> 123
> Abc
> "quoted"
Sorry, I mentioned the wrong operator before, it's ~>~ and ~<~ (asc
versus desc):
smarlowe=# create table col_test (a text);
CREATE TABLE
smarlowe=# insert into col_test (a) values ('"quoted"'),('Abc'),('123');
INSERT 0 3
smarlowe=# select * from col_test order by a;
a
--
123
Abc
"quoted"
(3 rows)
smarlowe=# select * from col_test order by a using ~<~;
a
--
"quoted"
123
Abc
(3 rows)
smarlowe=# select * from col_test order by a using ~>~;
a
--
Abc
123
"quoted"
(3 rows)
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
