pg.ddx.io pgsql-php@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed Help!!
4+ messages / 4 participants
[nested] [flat]
* Need Help!!
@ 2001-05-21 14:09 Gurudutt <guru@indvalley.com>
2001-10-04 14:17 ` Re: Need Help!! Heather Johnson <hjohnson@nypost.com>
2001-10-04 22:33 ` Re: Need Help!! Ross J. Reedstrom <reedstrm@rice.edu>
2001-10-05 13:42 ` Re: Need Help!! Papp Gyozo <pgerzson@freestart.hu>
0 siblings, 3 replies; 4+ messages in thread
From: Gurudutt @ 2001-05-21 14:09 UTC (permalink / raw)
To: pgsql-sql@postgresql.org; +Cc: pgsql-php
Hello pgsql-sql,
I am the new member for the postgres mailing list. Actually I have
been working with mysql, php and perl for a very long time now, and
offlate shifted to pgsql. I have many technical difficulties
1. I need to port mysql data to pgsql. I tried both mysql2pg.pl and
my2pg.sql. Both have some problem. I think it is got something to do
with the auto increment feature that i used in mysql. Can that issue
be addressed while porting.
2. Some of the joins that were successfully working in mysql are not
working, most importantly LEFT JOIN.
eg.
SELECT SUM(ACT_DueTab.CableAmount) as NetworkTotal FROM
ACT_NetworkTab,ACT_DueTab, ACT_InvoiceTab LEFT JOIN ACT_CustomerTab ON
(ACT_CustomerTab.CustCode=ACT_InvoiceTab.CustCode) WHERE
ACT_DueTab.InvCode=ACT_InvoiceTab.InvNumber and
ACT_NetworkTab.NetCode=3 and
ACT_CustomerTab.NetCode=ACT_NetworkTab.NetCode and
(ACT_InvoiceTab.InvGenDate <= '2001-08-31' and
ACT_InvoiceTab.InvGenDate >= '2001-08-01')
ORDER BY ACT_InvoiceTab.InvGenDate DESC
This query works fine in mysql, but suffers in pgsql.
3. I was using PEAR for data abstraction layer ( to make code
independent of the database), I find that PEAR which worked fine with
mysql doesn't work so well with pgsql
Any help on all these issues will be greatly appreciated. I am in the
midst of a porject porting exercise.
--
Best regards,
Gurudutt mailto:guru@indvalley.com
Life is not fair - get used to it.
Bill Gates
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Need Help!!
2001-05-21 14:09 Need Help!! Gurudutt <guru@indvalley.com>
@ 2001-10-04 14:17 ` Heather Johnson <hjohnson@nypost.com>
2 siblings, 0 replies; 4+ messages in thread
From: Heather Johnson @ 2001-10-04 14:17 UTC (permalink / raw)
To: Gurudutt <guru@indvalley.com>; pgsql-sql@postgresql.org; +Cc: pgsql-php
Hi Gurudutt--
Concerning #1, I had a similar problem when porting data from mysql to psql.
I finally ended up just using mysql's COPY command to get the data into
delimited text form, then imported that into psql using its COPY command.
This seems to me to be the easiest way to port over data if your table
structures are exactly the same. If they aren't, then I'd export the mysql
data to delimited text anyway, and write a quick script to import it into a
structurally distinct psql table.
I don't have any good advice for the other two difficulties you're
having---hopefully others on the list can help.
Good luck.
Heather
----- Original Message -----
From: "Gurudutt" <guru@indvalley.com>
To: <pgsql-sql@postgresql.org>
Cc: <pgsql-php@postgresql.org>
Sent: Monday, May 21, 2001 10:09 AM
Subject: [PHP] Need Help!!
> Hello pgsql-sql,
>
> I am the new member for the postgres mailing list. Actually I have
> been working with mysql, php and perl for a very long time now, and
> offlate shifted to pgsql. I have many technical difficulties
>
> 1. I need to port mysql data to pgsql. I tried both mysql2pg.pl and
> my2pg.sql. Both have some problem. I think it is got something to do
> with the auto increment feature that i used in mysql. Can that issue
> be addressed while porting.
>
> 2. Some of the joins that were successfully working in mysql are not
> working, most importantly LEFT JOIN.
>
> eg.
>
> SELECT SUM(ACT_DueTab.CableAmount) as NetworkTotal FROM
> ACT_NetworkTab,ACT_DueTab, ACT_InvoiceTab LEFT JOIN ACT_CustomerTab ON
> (ACT_CustomerTab.CustCode=ACT_InvoiceTab.CustCode) WHERE
> ACT_DueTab.InvCode=ACT_InvoiceTab.InvNumber and
> ACT_NetworkTab.NetCode=3 and
> ACT_CustomerTab.NetCode=ACT_NetworkTab.NetCode and
> (ACT_InvoiceTab.InvGenDate <= '2001-08-31' and
> ACT_InvoiceTab.InvGenDate >= '2001-08-01')
> ORDER BY ACT_InvoiceTab.InvGenDate DESC
>
> This query works fine in mysql, but suffers in pgsql.
>
> 3. I was using PEAR for data abstraction layer ( to make code
> independent of the database), I find that PEAR which worked fine with
> mysql doesn't work so well with pgsql
>
>
> Any help on all these issues will be greatly appreciated. I am in the
> midst of a porject porting exercise.
>
>
> --
> Best regards,
> Gurudutt mailto:guru@indvalley.com
>
> Life is not fair - get used to it.
> Bill Gates
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Need Help!!
2001-05-21 14:09 Need Help!! Gurudutt <guru@indvalley.com>
@ 2001-10-04 22:33 ` Ross J. Reedstrom <reedstrm@rice.edu>
2 siblings, 0 replies; 4+ messages in thread
From: Ross J. Reedstrom @ 2001-10-04 22:33 UTC (permalink / raw)
To: pgsql-sql@postgresql.org
On Mon, May 21, 2001 at 07:39:06PM +0530, Gurudutt wrote:
> Hello pgsql-sql,
>
> I am the new member for the postgres mailing list. Actually I have
> been working with mysql, php and perl for a very long time now, and
> offlate shifted to pgsql. I have many technical difficulties
>
> 2. Some of the joins that were successfully working in mysql are not
> working, most importantly LEFT JOIN.
>
> eg.
>
> SELECT SUM(ACT_DueTab.CableAmount) as NetworkTotal FROM
> ACT_NetworkTab,ACT_DueTab, ACT_InvoiceTab LEFT JOIN ACT_CustomerTab ON
> (ACT_CustomerTab.CustCode=ACT_InvoiceTab.CustCode) WHERE
> ACT_DueTab.InvCode=ACT_InvoiceTab.InvNumber and
> ACT_NetworkTab.NetCode=3 and
> ACT_CustomerTab.NetCode=ACT_NetworkTab.NetCode and
> (ACT_InvoiceTab.InvGenDate <= '2001-08-31' and
> ACT_InvoiceTab.InvGenDate >= '2001-08-01')
> ORDER BY ACT_InvoiceTab.InvGenDate DESC
>
> This query works fine in mysql, but suffers in pgsql.
suffers? What's suffers? It's slower? It doesn't work at all? What?
Looking at it, I'd guess that you get something about lack of GROUPing
when using an aggregate, right? So, you'll need to use correct SQL to
express the summation your trying to achieve. I don't have your schema,
nor the time to reverse engineer it from your example query, but if what
your expecting back from that is 31 rows in order, each one representing
the total invoices due on that day, you need to add:
GROUP BY BY ACT_InvoiceTab.InvGenDate
just before the ORDER BY line
Or, if you want the summation of all of them, and only expect one number
back, why are you ORDERing it?
>
> 3. I was using PEAR for data abstraction layer ( to make code
> independent of the database), I find that PEAR which worked fine with
> mysql doesn't work so well with pgsql
Again, vague. What "doesn't work so well" ? What is PEAR? Hmm, seems
to be some PHP specific thing. I guess I'll let PHP PostgreSQL people
answer this one.
>
>
> Any help on all these issues will be greatly appreciated. I am in the
> midst of a porject porting exercise.
>
Hope I helped.
Ross
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Need Help!!
2001-05-21 14:09 Need Help!! Gurudutt <guru@indvalley.com>
@ 2001-10-05 13:42 ` Papp Gyozo <pgerzson@freestart.hu>
2 siblings, 0 replies; 4+ messages in thread
From: Papp Gyozo @ 2001-10-05 13:42 UTC (permalink / raw)
To: Gurudutt <guru@indvalley.com>; pgsql-php
Hello,
next time post the error message what you got and your pgsql version number.
It helps us to find the weak point in the "whole mess".
JOIN syntax was introduced in v7.1 and some fixes was made up to the recent
version (v7.1.3), AFAIR.
SELECT SUM(ACT_DueTab.CableAmount) as NetworkTotal
FROM ACT_NetworkTab,
ACT_DueTab,
ACT_InvoiceTab LEFT JOIN ACT_CustomerTab
-- ON (ACT_CustomerTab.CustCode=ACT_InvoiceTab.CustCode)
-- instead, and pg won't duplicate this column:
USING(CustCode)
-- you might give a table alias for this JOIN and use it instead
-- I'm just guessing ...
WHERE
ACT_DueTab.InvCode=ACT_InvoiceTab.InvNumber AND
ACT_NetworkTab.NetCode=3 AND
ACT_CustomerTab.NetCode=ACT_NetworkTab.NetCode AND
ACT_InvoiceTab.InvGenDate <= '2001-08-31' AND
ACT_InvoiceTab.InvGenDate >= '2001-08-01')
ORDER BY ACT_InvoiceTab.InvGenDate DESC
So:
SELECT SUM(ACT_DueTab.CableAmount) as NetworkTotal
FROM ACT_NetworkTab,
ACT_DueTab,
(ACT_InvoiceTab LEFT JOIN ACT_CustomerTab
USING(CustCode)) AS A
WHERE
ACT_DueTab.InvCode=A.InvNumber AND
ACT_NetworkTab.NetCode=3 AND
A.NetCode=ACT_NetworkTab.NetCode AND
A.InvGenDate <= '2001-08-31' AND
A.InvGenDate >= '2001-08-01'
ORDER BY ACT_InvoiceTab.InvGenDate DESC
> 3. I was using PEAR for data abstraction layer ( to make code
> independent of the database), I find that PEAR which worked fine with
> mysql doesn't work so well with pgsql
>
Can you detail it a bit? What's wrong with PEAR or its abstraction?
----- Original Message -----
From: "Gurudutt" <guru@indvalley.com>
To: <pgsql-sql@postgresql.org>
Cc: <pgsql-php@postgresql.org>
Sent: Monday, May 21, 2001 4:09 PM
Subject: [PHP] Need Help!!
> Hello pgsql-sql,
>
> I am the new member for the postgres mailing list. Actually I have
> been working with mysql, php and perl for a very long time now, and
> offlate shifted to pgsql. I have many technical difficulties
>
> 1. I need to port mysql data to pgsql. I tried both mysql2pg.pl and
> my2pg.sql. Both have some problem. I think it is got something to do
> with the auto increment feature that i used in mysql. Can that issue
> be addressed while porting.
>
> 2. Some of the joins that were successfully working in mysql are not
> working, most importantly LEFT JOIN.
>
> eg.
>
> SELECT SUM(ACT_DueTab.CableAmount) as NetworkTotal FROM
> ACT_NetworkTab,ACT_DueTab, ACT_InvoiceTab LEFT JOIN ACT_CustomerTab ON
> (ACT_CustomerTab.CustCode=ACT_InvoiceTab.CustCode) WHERE
> ACT_DueTab.InvCode=ACT_InvoiceTab.InvNumber and
> ACT_NetworkTab.NetCode=3 and
> ACT_CustomerTab.NetCode=ACT_NetworkTab.NetCode and
> (ACT_InvoiceTab.InvGenDate <= '2001-08-31' and
> ACT_InvoiceTab.InvGenDate >= '2001-08-01')
> ORDER BY ACT_InvoiceTab.InvGenDate DESC
>
> This query works fine in mysql, but suffers in pgsql.
>
> 3. I was using PEAR for data abstraction layer ( to make code
> independent of the database), I find that PEAR which worked fine with
> mysql doesn't work so well with pgsql
>
>
> Any help on all these issues will be greatly appreciated. I am in the
> midst of a porject porting exercise.
>
>
> --
> Best regards,
> Gurudutt mailto:guru@indvalley.com
>
> Life is not fair - get used to it.
> Bill Gates
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-10-05 13:42 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-21 14:09 Need Help!! Gurudutt <guru@indvalley.com>
2001-10-04 14:17 ` Heather Johnson <hjohnson@nypost.com>
2001-10-04 22:33 ` Ross J. Reedstrom <reedstrm@rice.edu>
2001-10-05 13:42 ` Papp Gyozo <pgerzson@freestart.hu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox