Thread: Case sensitive order by
Hi, I have a problem with a case sensitive 'order by'. On my development platform, the 'order by' clauses are insensitive But on my production platform, they are case sensitive. I did the same installation on both platforms, from RPMs I downloaded (Postgresql 7.1 on Redhat 7.0). I tried to set LC_COLLATE=C before initdb, but nothing changed. Is there a configuration for that ? Is it a specific parameter on my system ? Any help will be appreciated Thank's a lot.
=?iso-8859-1?Q?Micha=EBl_Fiey?= <m.fiey@futuresoundtech.com> writes:
> I have a problem with a case sensitive 'order by'.
> On my development platform, the 'order by' clauses are insensitive
Apparently you've discovered a feature that the developers didn't know
existed ;-). Seriously, I don't think I believe that. Could we see
some evidence? Also, what does the pg_controldata contrib program
(I think this is included in the RPMs) show is in your pg_control file?
regards, tom lane
On Fri, May 04, 2001 at 09:57:02AM -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > =?iso-8859-1?Q?Micha=EBl_Fiey?= <m.fiey@futuresoundtech.com> writes: > > I have a problem with a case sensitive 'order by'. > > On my development platform, the 'order by' clauses are insensitive Could this be a locale issue?
You're right ! LC_ALL=C explain the difference. But I haven't found pg_controldata on my platform. Is it installed with Postgresql 7.1 RPM for Redhat 7.0 ? Thank's for your help regards ----- Original Message ----- From: Tom Lane <tgl@sss.pgh.pa.us> To: Michaël Fiey <m.fiey@futuresoundtech.com> Sent: Wednesday, May 09, 2001 4:28 PM Subject: Re: [GENERAL] Case sensitive order by > > On my prod platform : > > aaa > > bbb > > ccc > > AAA > > BBB > > CCC > > > And on my dev platform : > > aaa > > AAA > > bbb > > BBB > > ccc > > CCC > > Are you sure that both databases are running with the same locale > settings? pg_controldata can tell you for sure. > > regards, tom lane >
> But I haven't found pg_controldata on my platform.
> Is it installed with Postgresql 7.1 RPM for Redhat 7.0 ?
Dunno. It's part of our contrib stuff. I thought there would be an RPM
for the contrib stuff for 7.1, but maybe not, or maybe you didn't
install that RPM.
If you don't have it, just looking at $PGDATA/global/pg_control with
"strings" would probably do well enough to let you spot the locale name...
regards, tom lane
Name : postgresql-contrib Version : 7.1 Release : 1 Install date: Thu Apr 26 22:04:45 2001 Group : Applications/Databases Size : 2421178 Packager : Lamar Owen <lamar.owen@wgcr.org> URL : http://www.postgresql.org/ Summary : Contributed source and binaries distributed with PostgreSQL Description : The postgresql-contrib package includes the contrib tree distributed with the PostgreSQL tarball. Selected contrib modules are prebuilt. The install location in the new RPMS is: /usr/lib/pgsql/contrib/pg_controldata/pg_controldata Thanks Lamar! Mike Mascari mascarm@mascari.com -----Original Message----- From: Tom Lane [SMTP:tgl@sss.pgh.pa.us] Sent: Wednesday, May 09, 2001 11:45 AM To: Michael Fiey Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Case sensitive order by > But I haven't found pg_controldata on my platform. > Is it installed with Postgresql 7.1 RPM for Redhat 7.0 ? Dunno. It's part of our contrib stuff. I thought there would be an RPM for the contrib stuff for 7.1, but maybe not, or maybe you didn't install that RPM. If you don't have it, just looking at $PGDATA/global/pg_control with "strings" would probably do well enough to let you spot the locale name... regards, tom lane
Hi,
As I previous searched a tool to convert Oracle database to PostgreSQL
and really found nothing, there's now a piece of perl code I've written
that
can become a great tool to do this job.
It currently extract the database schema table definition of an Oracle
database
and output a sql script to import into postgresql. You simply provide the
DBI
connection to the Oracle DB and the script do its best. It normally handle
unique, primary and foreign keys.
I need help to go further because I really don't know about Oracle and I go
very slowly. Also I did not have so much Oracle database to test with.
Things to do are:
- Extract grants (coming soon)
- More precision in type conversion based on length (I've no good DB to do
that)
- Extract triggers and internal function.
- Extract datas.
- SQL queries converter.
For extracting data if someone know a way to dump content of an Oracle DB
in ascii. I don't found anything than binary dump. Extracting them with
perl/DBI
can be very slow but if there's no other way...
You can found this tools at:
http://www.samse.fr/GPL/ora2pg/
Regards
Add on on ora2pg. Table grant extraction is done. It is based on group/users grants. Oracle has ROLES that I understand as groups and users associated to these roles. So I create a group for each role and alter it by adding the users. And then set grants to each tables. Let me now if I have to stop to send new update on the list. Regards
Where do people want this. Should it be in /contrib or on its own web page? I have an Xbase conversion utility too. Where should that go? > Hi, > > As I previous searched a tool to convert Oracle database to PostgreSQL > and really found nothing, there's now a piece of perl code I've written > that > can become a great tool to do this job. > > It currently extract the database schema table definition of an Oracle > database > and output a sql script to import into postgresql. You simply provide the > DBI > connection to the Oracle DB and the script do its best. It normally handle > unique, primary and foreign keys. > > I need help to go further because I really don't know about Oracle and I go > > very slowly. Also I did not have so much Oracle database to test with. > > Things to do are: > > - Extract grants (coming soon) > - More precision in type conversion based on length (I've no good DB to do > that) > - Extract triggers and internal function. > - Extract datas. > - SQL queries converter. > > For extracting data if someone know a way to dump content of an Oracle DB > in ascii. I don't found anything than binary dump. Extracting them with > perl/DBI > can be very slow but if there's no other way... > > You can found this tools at: > > http://www.samse.fr/GPL/ora2pg/ > > > Regards > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Wed, 9 May 2001, Bruce Momjian wrote: > > Where do people want this. Should it be in /contrib or on its own web > page? This is already linked on the related page. > I have an Xbase conversion utility too. Where should that go? What's the URL? > > > > Hi, > > > > As I previous searched a tool to convert Oracle database to PostgreSQL > > and really found nothing, there's now a piece of perl code I've written > > that > > can become a great tool to do this job. > > > > It currently extract the database schema table definition of an Oracle > > database > > and output a sql script to import into postgresql. You simply provide the > > DBI > > connection to the Oracle DB and the script do its best. It normally handle > > unique, primary and foreign keys. > > > > I need help to go further because I really don't know about Oracle and I go > > > > very slowly. Also I did not have so much Oracle database to test with. > > > > Things to do are: > > > > - Extract grants (coming soon) > > - More precision in type conversion based on length (I've no good DB to do > > that) > > - Extract triggers and internal function. > > - Extract datas. > > - SQL queries converter. > > > > For extracting data if someone know a way to dump content of an Oracle DB > > in ascii. I don't found anything than binary dump. Extracting them with > > perl/DBI > > can be very slow but if there's no other way... > > > > You can found this tools at: > > > > http://www.samse.fr/GPL/ora2pg/ > > > > > > Regards > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > > > > -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
> On Wed, 9 May 2001, Bruce Momjian wrote:
>
> >
> > Where do people want this. Should it be in /contrib or on its own web
> > page?
>
> This is already linked on the related page.
>
> > I have an Xbase conversion utility too. Where should that go?
>
> What's the URL?
It doesn't have a URL. It is on our own FTP site. You can link to it,
I guess, but it probably needs to hit a mirror that always will respond.
The URL is:
ftp://ftp.crimelabs.net/pub/postgresql/contrib/dbf2pg-3.1.tar.gz
but it is not on the mirrors yet. My problem is trying to get some
rules on what goes in /contrib and what doesn't. My assumption is that
loadable modules and stuff that deals with the backend internals go into
/contrib. That would mean our mysql stuff would be removed, I guess.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Hi All, I'm willing to put all PostgreSQL related matter on the techdocs site, or link to it, as the case may be. Is that of any assistance? Some stuff is definitely better in /contrib, as it is "already there" with PostgreSQL, some stuff might not be. Database conversion tools probably are better in /contrib is my thought though... time to clarify rules? :-) Regards and best wishes, Justin Clift Bruce Momjian wrote: > > > On Wed, 9 May 2001, Bruce Momjian wrote: > > > > > > > > Where do people want this. Should it be in /contrib or on its own web > > > page? > > > > This is already linked on the related page. > > > > > I have an Xbase conversion utility too. Where should that go? > > > > What's the URL? > > It doesn't have a URL. It is on our own FTP site. You can link to it, > I guess, but it probably needs to hit a mirror that always will respond. > > The URL is: > > ftp://ftp.crimelabs.net/pub/postgresql/contrib/dbf2pg-3.1.tar.gz > > but it is not on the mirrors yet. My problem is trying to get some > rules on what goes in /contrib and what doesn't. My assumption is that > loadable modules and stuff that deals with the backend internals go into > /contrib. That would mean our mysql stuff would be removed, I guess. > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
[ Charset US-ASCII unsupported, converting... ] > Hi All, > > I'm willing to put all PostgreSQL related matter on the techdocs site, > or link to it, as the case may be. Is that of any assistance? > > Some stuff is definitely better in /contrib, as it is "already there" > with PostgreSQL, some stuff might not be. Database conversion tools > probably are better in /contrib is my thought though... time to > clarify rules? My first guess is that loadable modules clearly should be in /contrib, and tools like pg_controldata, and things that workaround missing features in PostgreSQL. Looking in /contrib, that is pretty much what we have. Now, that leaves conversion tools as "unknown". You are voting the belong in /contrib? If I get another "yes", I will add the xbase and Oracle tools in there too. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Hi, Another point regarding /contrib or other directory like /tools is to centralize tools for Pg. Also I can't be sure to always have an URL. This one is dependant on the company I'm working now. Life is moving. Regards Bruce Momjian wrote: > [ Charset US-ASCII unsupported, converting... ] > > Hi All, > > > > I'm willing to put all PostgreSQL related matter on the techdocs site, > > or link to it, as the case may be. Is that of any assistance? > > > > Some stuff is definitely better in /contrib, as it is "already there" > > with PostgreSQL, some stuff might not be. Database conversion tools > > probably are better in /contrib is my thought though... time to > > clarify rules? > > My first guess is that loadable modules clearly should be in /contrib, > and tools like pg_controldata, and things that workaround missing > features in PostgreSQL. > > Looking in /contrib, that is pretty much what we have. Now, that leaves > conversion tools as "unknown". You are voting the belong in /contrib? > If I get another "yes", I will add the xbase and Oracle tools in there > too. > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl
On Thu, 10 May 2001, Gilles DAROLD wrote: > Hi, > > Another point regarding /contrib or other directory like /tools is to > centralize tools for Pg. Also I can't be sure to always have an URL. > This one is dependant on the company I'm working now. There's your "yes" Bruce. So when you move them there (hopefully to something like /contrib/tools) I'll change the info on the related page. Vince. > > Life is moving. > > Regards > > Bruce Momjian wrote: > > > [ Charset US-ASCII unsupported, converting... ] > > > Hi All, > > > > > > I'm willing to put all PostgreSQL related matter on the techdocs site, > > > or link to it, as the case may be. Is that of any assistance? > > > > > > Some stuff is definitely better in /contrib, as it is "already there" > > > with PostgreSQL, some stuff might not be. Database conversion tools > > > probably are better in /contrib is my thought though... time to > > > clarify rules? > > > > My first guess is that loadable modules clearly should be in /contrib, > > and tools like pg_controldata, and things that workaround missing > > features in PostgreSQL. > > > > Looking in /contrib, that is pretty much what we have. Now, that leaves > > conversion tools as "unknown". You are voting the belong in /contrib? > > If I get another "yes", I will add the xbase and Oracle tools in there > > too. > > > > -- > > Bruce Momjian | http://candle.pha.pa.us > > pgman@candle.pha.pa.us | (610) 853-3000 > > + If your life is a hard drive, | 830 Blythe Avenue > > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://www.postgresql.org/search.mpl > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
OK, applied in /contrib/oracle for 7.2. > Hi, > > As I previous searched a tool to convert Oracle database to PostgreSQL > and really found nothing, there's now a piece of perl code I've written > that > can become a great tool to do this job. > > It currently extract the database schema table definition of an Oracle > database > and output a sql script to import into postgresql. You simply provide the > DBI > connection to the Oracle DB and the script do its best. It normally handle > unique, primary and foreign keys. > > I need help to go further because I really don't know about Oracle and I go > > very slowly. Also I did not have so much Oracle database to test with. > > Things to do are: > > - Extract grants (coming soon) > - More precision in type conversion based on length (I've no good DB to do > that) > - Extract triggers and internal function. > - Extract datas. > - SQL queries converter. > > For extracting data if someone know a way to dump content of an Oracle DB > in ascii. I don't found anything than binary dump. Extracting them with > perl/DBI > can be very slow but if there's no other way... > > You can found this tools at: > > http://www.samse.fr/GPL/ora2pg/ > > > Regards > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026