pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed Help!!
35+ messages / 27 participants
[nested] [flat]
* Need Help!!
@ 2001-05-21 14:09 Gurudutt <guru@indvalley.com>
0 siblings, 3 replies; 35+ messages in thread
From: Gurudutt @ 2001-05-21 14:09 UTC (permalink / raw)
To: pgsql-sql; +Cc: pgsql-php@postgresql.org
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] 35+ messages in thread
* Re: Need Help!!
@ 2001-10-04 14:17 Heather Johnson <hjohnson@nypost.com>
parent: Gurudutt <guru@indvalley.com>
2 siblings, 0 replies; 35+ messages in thread
From: Heather Johnson @ 2001-10-04 14:17 UTC (permalink / raw)
To: Gurudutt <guru@indvalley.com>; pgsql-sql; +Cc: pgsql-php@postgresql.org
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] 35+ messages in thread
* Re: Need Help!!
@ 2001-10-04 22:33 Ross J. Reedstrom <reedstrm@rice.edu>
parent: Gurudutt <guru@indvalley.com>
2 siblings, 0 replies; 35+ messages in thread
From: Ross J. Reedstrom @ 2001-10-04 22:33 UTC (permalink / raw)
To: pgsql-sql
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] 35+ messages in thread
* Re: Need Help!!
@ 2001-10-05 13:42 Papp Gyozo <pgerzson@freestart.hu>
parent: Gurudutt <guru@indvalley.com>
2 siblings, 0 replies; 35+ messages in thread
From: Papp Gyozo @ 2001-10-05 13:42 UTC (permalink / raw)
To: Gurudutt <guru@indvalley.com>; pgsql-php@postgresql.org
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] 35+ messages in thread
* Need help
@ 2002-01-01 11:51 Shamik Majumder <shamik.majumder@wipro.com>
0 siblings, 2 replies; 35+ messages in thread
From: Shamik Majumder @ 2002-01-01 11:51 UTC (permalink / raw)
To: pgsql-admin@postgresql.org; pgsql-general@postgresql.org; pgsql-sql
Hi ,
We are facing some problems with the creation of tables of same name but
owned by different user .
We followed the following steps .
Lets say, we have a database DBTest and this database was created by the
user postgres.
We created tables - Table1, Table2 and Table3 in it.
Now, by using the createuser command - one more database user is
created, say dbuser1.
Now, when I login as dbuser1 on the DBTest database, I can see all the
tables Table1, Table2 and Table3 by /dt command.
Even, I am able to create new tables ( i.e table with new names ) in the
same database DBTest but with the owner dbuser1.
Now, when I try to create the same table like Table1 ( which has been
created by the
postgress user previously ) as dbuser1 user - the create table command
fails with the following o/p :
ERROR: Relation 'Table1' already exists
My question - is it possible in Postgres, to create the tables with
same name but with different users ?
i.e we create Table1 table both as postgres as well as dbuser1 .
Is it possible kindly let me know .
Thanks and Regards,
Shamik
-------------------------------------------------------------------------------------------------------------------------
Information transmitted by this E-MAIL is proprietary to Wipro and/or its Customers and
is intended for use only by the individual or entity to which it is
addressed, and may contain information that is privileged, confidential or
exempt from disclosure under applicable law. If you are not the intended
recipient or it appears that this mail has been forwarded to you without
proper authority, you are notified that any use or dissemination of this
information in any manner is strictly prohibited. In such cases, please
notify us immediately at mailto:mailadmin@wipro.com and delete this mail
from your records.
----------------------------------------------------------------------------------------------------------------------
Attachments:
[text/plain] InterScan_Disclaimer.txt (854B, ../../3C31A2AF.B15798EE@wipro.com/2-InterScan_Disclaimer.txt)
download | inline:
-------------------------------------------------------------------------------------------------------------------------
Information transmitted by this E-MAIL is proprietary to Wipro and/or its Customers and
is intended for use only by the individual or entity to which it is
addressed, and may contain information that is privileged, confidential or
exempt from disclosure under applicable law. If you are not the intended
recipient or it appears that this mail has been forwarded to you without
proper authority, you are notified that any use or dissemination of this
information in any manner is strictly prohibited. In such cases, please
notify us immediately at mailto:mailadmin@wipro.com and delete this mail
from your records.
----------------------------------------------------------------------------------------------------------------------
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: Need help
@ 2002-01-08 15:17 Holger Krug <hkrug@rationalizer.com>
parent: Shamik Majumder <shamik.majumder@wipro.com>
1 sibling, 0 replies; 35+ messages in thread
From: Holger Krug @ 2002-01-08 15:17 UTC (permalink / raw)
To: Shamik Majumder <shamik.majumder@wipro.com>; +Cc: pgsql-general@postgresql.org
On Tue, Jan 01, 2002 at 05:21:12PM +0530, Shamik Majumder wrote:
> My question - is it possible in Postgres, to create the tables with
> same name but with different users ?
No.
--
Holger Krug
hkrug@rationalizer.com
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: [GENERAL] Need help
@ 2002-01-08 17:17 Jason Earl <jason.earl@simplot.com>
parent: Shamik Majumder <shamik.majumder@wipro.com>
1 sibling, 1 reply; 35+ messages in thread
From: Jason Earl @ 2002-01-08 17:17 UTC (permalink / raw)
To: Shamik Majumder <shamik.majumder@wipro.com>; +Cc: pgsql-admin@postgresql.org; pgsql-general@postgresql.org; pgsql-sql
Why do you want a database where two tables have the same name? When
you do a "SELECT * from Table1" what table do you expect PostgreSQL to
use?
Now, that being said, it's possible to create temporary tables in
different connections with the same name. These tables will dissapear
when the connection is terminated, however. For example you could
have something like this:
conn1: CREATE TEMP TABLE foo (bar text);
conn2: CREATE TEMP TABLE foo (bar text);
conn1: INSERT INTO foo (bar) VALUES ('baz');
conn2: SELECT * from foo; [returns zero rows]
conn1: SELECT * from foo; [returns 'baz']
If you actually want two permanent tables with the same name, you are
going to have to put them in separate databases. Otherwise use
temporary tables.
Jason
Shamik Majumder <shamik.majumder@wipro.com> writes:
> Hi ,
>
> We are facing some problems with the creation of tables of same name
> but owned by different user .
>
> We followed the following steps .
>
> Lets say, we have a database DBTest and this database was created by
> the user postgres. We created tables - Table1, Table2 and Table3 in
> it. Now, by using the createuser command - one more database user
> is created, say dbuser1. Now, when I login as dbuser1 on the DBTest
> database, I can see all the tables Table1, Table2 and Table3 by /dt
> command. Even, I am able to create new tables ( i.e table with new
> names ) in the same database DBTest but with the owner dbuser1.
> Now, when I try to create the same table like Table1 ( which has
> been created by the postgress user previously ) as dbuser1 user -
> the create table command fails with the following o/p :
>
> ERROR: Relation 'Table1' already exists
>
> My question - is it possible in Postgres, to create the tables with
> same name but with different users ?
>
> i.e we create Table1 table both as postgres as well as dbuser1 .
>
> Is it possible kindly let me know .
>
> Thanks and Regards,
> Shamik
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: [SQL] Need help
@ 2002-01-08 20:23 Bill Cunningham <billc@ballydev.com>
parent: Jason Earl <jason.earl@simplot.com>
0 siblings, 1 reply; 35+ messages in thread
From: Bill Cunningham @ 2002-01-08 20:23 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Actually this concept is used in production environments or classes for
seperating student's school work.
I can see the need to create a Table1 under several different names.
Does postgresql have a schema concept like so:
dbuser1.Table1
postgres.Table1
This is how DB2 does it.
- Bill Cunningham
Technical Lead
Bally Gaming and Systems
Jason Earl wrote:
>Why do you want a database where two tables have the same name? When
>you do a "SELECT * from Table1" what table do you expect PostgreSQL to
>use?
>
>Now, that being said, it's possible to create temporary tables in
>different connections with the same name. These tables will dissapear
>when the connection is terminated, however. For example you could
>have something like this:
>
>conn1: CREATE TEMP TABLE foo (bar text);
>conn2: CREATE TEMP TABLE foo (bar text);
>conn1: INSERT INTO foo (bar) VALUES ('baz');
>conn2: SELECT * from foo; [returns zero rows]
>conn1: SELECT * from foo; [returns 'baz']
>
>If you actually want two permanent tables with the same name, you are
>going to have to put them in separate databases. Otherwise use
>temporary tables.
>
>Jason
>
>Shamik Majumder <shamik.majumder@wipro.com> writes:
>
>>Hi ,
>>
>>We are facing some problems with the creation of tables of same name
>>but owned by different user .
>>
>>We followed the following steps .
>>
>>Lets say, we have a database DBTest and this database was created by
>>the user postgres. We created tables - Table1, Table2 and Table3 in
>>it. Now, by using the createuser command - one more database user
>>is created, say dbuser1. Now, when I login as dbuser1 on the DBTest
>>database, I can see all the tables Table1, Table2 and Table3 by /dt
>>command. Even, I am able to create new tables ( i.e table with new
>>names ) in the same database DBTest but with the owner dbuser1.
>>Now, when I try to create the same table like Table1 ( which has
>>been created by the postgress user previously ) as dbuser1 user -
>>the create table command fails with the following o/p :
>>
>>ERROR: Relation 'Table1' already exists
>>
>>My question - is it possible in Postgres, to create the tables with
>>same name but with different users ?
>>
>>i.e we create Table1 table both as postgres as well as dbuser1 .
>>
>>Is it possible kindly let me know .
>>
>>Thanks and Regards,
>>Shamik
>>
>>
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: [SQL] Need help
@ 2002-01-09 00:04 Doug McNaught <doug@wireboard.com>
parent: Bill Cunningham <billc@ballydev.com>
0 siblings, 0 replies; 35+ messages in thread
From: Doug McNaught @ 2002-01-09 00:04 UTC (permalink / raw)
To: Bill Cunningham <billc@ballydev.com>; +Cc: pgsql-general@postgresql.org
Bill Cunningham <billc@ballydev.com> writes:
> Actually this concept is used in production environments or classes
> for seperating student's school work.
>
> I can see the need to create a Table1 under several different
> names. Does postgresql have a schema concept like so:
> dbuser1.Table1
> postgres.Table1
>
> This is how DB2 does it.
I understand that schemas are tentatively planned for 7.3, but they
are not currently available.
-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: [GENERAL] Need help
@ 2002-01-09 09:47 Maarten.Boekhold@reuters.com
0 siblings, 0 replies; 35+ messages in thread
From: Maarten.Boekhold@reuters.com @ 2002-01-09 09:47 UTC (permalink / raw)
To: Jason Earl <jason.earl@simplot.com>; +Cc: pgsql-admin@postgresql.org; pgsql-general@postgresql.org; pgsql-sql; shamik.majumder@wipro.com
On 01/08/2002 09:17:03 PM Jason Earl wrote:
> Why do you want a database where two tables have the same name? When
> you do a "SELECT * from Table1" what table do you expect PostgreSQL to
> use?
I suspect Shamik is trying to do something like having a separate 'schema'
(Oracle) for every user, and in Oracle it is possible to create tables
with the same name but in different schemas.
Maarten
----
Maarten Boekhold, maarten.boekhold@reuters.com
Reuters Consulting / TIBCO Finance Technology Inc.
Dubai Media City
Building 1, 5th Floor
PO Box 1426
Dubai, United Arab Emirates
tel:+971(0)4 3918300 ext 249
fax:+971(0)4 3918333
mob:+971(0)505526539
-------------------------------------------------------------- --
Visit our Internet site at http://www.reuters.com
Any views expressed in this message are those of the individual
sender, except where the sender specifically states them to be
the views of Reuters Ltd.
^ permalink raw reply [nested|flat] 35+ messages in thread
* Need Help
@ 2003-11-14 01:04 Abdul Wahab Dahalan <wahab@mimos.my>
0 siblings, 1 reply; 35+ messages in thread
From: Abdul Wahab Dahalan @ 2003-11-14 01:04 UTC (permalink / raw)
To: pgsql-sql
Hi!
If I've a table like this
kk kj pngk vote
01 02 a 12
01 02 b 10
01 03 c 5
and I want to have a query so that it give me a result as below.
The condition is for each record with the same kk and kj
but difference pngk will be give a mark *;
[In this example for record 1 and record 2 we have same kk=01 and kj=02
but difference pngk a and b so we give * for the mark]
kk kj pngk vote mark
01 02 a 12 *
01 02 b 10 *
01 03 c 5
How should I write the query?
Thanks in advanced.
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: Need Help
@ 2003-11-14 05:11 Bruno Wolff III <bruno@wolff.to>
parent: Abdul Wahab Dahalan <wahab@mimos.my>
0 siblings, 0 replies; 35+ messages in thread
From: Bruno Wolff III @ 2003-11-14 05:11 UTC (permalink / raw)
To: Abdul Wahab Dahalan <wahab@mimos.my>; +Cc: pgsql-sql
On Fri, Nov 14, 2003 at 09:04:47 +0800,
Abdul Wahab Dahalan <wahab@mimos.my> wrote:
> Hi!
>
> If I've a table like this
>
> kk kj pngk vote
> 01 02 a 12
> 01 02 b 10
> 01 03 c 5
>
> and I want to have a query so that it give me a result as below.
>
> The condition is for each record with the same kk and kj
> but difference pngk will be give a mark *;
> [In this example for record 1 and record 2 we have same kk=01 and kj=02
> but difference pngk a and b so we give * for the mark]
>
>
> kk kj pngk vote mark
> 01 02 a 12 *
> 01 02 b 10 *
> 01 03 c 5
>
> How should I write the query?
You could do something like:
select a.kk, a.kj, a.pngk, a.vote, b.star
from table a left join
(select '*' star, kk, kj from table group by kk, kj having count(*) > 1) b
on (a.kk = b.kk and a.kj = b.kj);
I didn't test this so there might be a syntax problem, but it should make it
clear how to do what you want.
^ permalink raw reply [nested|flat] 35+ messages in thread
* need help
@ 2005-12-06 08:38 Jenny <jenny_tania@yahoo.com>
0 siblings, 2 replies; 35+ messages in thread
From: Jenny @ 2005-12-06 08:38 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-sql; pgsql-performance@postgresql.org
I'm running PostgreSQL 8.0.3 on i686-pc-linux-gnu (Fedora Core 2). I've been
dealing with Psql for over than 2 years now, but I've never had this case
before.
I have a table that has about 20 rows in it.
Table "public.s_apotik"
Column | Type | Modifiers
-------------------+------------------------------+------------------
obat_id | character varying(10) | not null
stock | numeric | not null
s_min | numeric | not null
s_jual | numeric |
s_r_jual | numeric |
s_order | numeric |
s_r_order | numeric |
s_bs | numeric |
last_receive | timestamp without time zone |
Indexes:
"s_apotik_pkey" PRIMARY KEY, btree(obat_id)
When I try to UPDATE one of the row, nothing happens for a very long time.
First, I run it on PgAdminIII, I can see the miliseconds are growing as I
waited. Then I stop the query, because the time needed for it is unbelievably
wrong.
Then I try to run the query from the psql shell. For example, the table has
obat_id : A, B, C, D.
db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='A';
(.... nothing happens.. I press the Ctrl-C to stop it. This is what comes out
:)
Cancel request sent
ERROR: canceling query due to user request
(If I try another obat_id)
db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='B';
(Less than a second, this is what comes out :)
UPDATE 1
I can't do anything to that row. I can't DELETE it. Can't DROP the table.
I want this data out of my database.
What should I do? It's like there's a falsely pointed index here.
Any help would be very much appreciated.
Regards,
Jenny Tania
__________________________________________
Yahoo! DSL Something to write home about.
Just $16.99/mo. or less.
dsl.yahoo.com
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: need help
@ 2005-12-06 08:54 Tino Wildenhain <tino@wildenhain.de>
parent: Jenny <jenny_tania@yahoo.com>
1 sibling, 1 reply; 35+ messages in thread
From: Tino Wildenhain @ 2005-12-06 08:54 UTC (permalink / raw)
To: Jenny <jenny_tania@yahoo.com>; +Cc: pgsql-general@postgresql.org; pgsql-sql; pgsql-performance@postgresql.org
Jenny schrieb:
> I'm running PostgreSQL 8.0.3 on i686-pc-linux-gnu (Fedora Core 2). I've been
> dealing with Psql for over than 2 years now, but I've never had this case
> before.
>
> I have a table that has about 20 rows in it.
>
> Table "public.s_apotik"
> Column | Type | Modifiers
> -------------------+------------------------------+------------------
> obat_id | character varying(10) | not null
> stock | numeric | not null
> s_min | numeric | not null
> s_jual | numeric |
> s_r_jual | numeric |
> s_order | numeric |
> s_r_order | numeric |
> s_bs | numeric |
> last_receive | timestamp without time zone |
> Indexes:
> "s_apotik_pkey" PRIMARY KEY, btree(obat_id)
>
> When I try to UPDATE one of the row, nothing happens for a very long time.
> First, I run it on PgAdminIII, I can see the miliseconds are growing as I
> waited. Then I stop the query, because the time needed for it is unbelievably
> wrong.
>
> Then I try to run the query from the psql shell. For example, the table has
> obat_id : A, B, C, D.
> db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='A';
> (.... nothing happens.. I press the Ctrl-C to stop it. This is what comes out
> :)
> Cancel request sent
> ERROR: canceling query due to user request
>
> (If I try another obat_id)
> db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='B';
> (Less than a second, this is what comes out :)
> UPDATE 1
>
> I can't do anything to that row. I can't DELETE it. Can't DROP the table.
> I want this data out of my database.
> What should I do? It's like there's a falsely pointed index here.
> Any help would be very much appreciated.
>
1) lets hope you do regulary backups - and actually tested restore.
1a) if not, do it right now
2) reindex the table
3) try again to modify
Q: are there any foreign keys involved? If so, reindex those
tables too, just in case.
did you vacuum regulary?
HTH
Tino
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: need help
@ 2005-12-06 10:45 Alban Hertroys <alban@magproductions.nl>
parent: Jenny <jenny_tania@yahoo.com>
1 sibling, 0 replies; 35+ messages in thread
From: Alban Hertroys @ 2005-12-06 10:45 UTC (permalink / raw)
To: Jenny <jenny_tania@yahoo.com>; +Cc: pgsql-general@postgresql.org
Jenny wrote:
> I'm running PostgreSQL 8.0.3 on i686-pc-linux-gnu (Fedora Core 2). I've been
> dealing with Psql for over than 2 years now, but I've never had this case
> before.
> Then I try to run the query from the psql shell. For example, the table has
> obat_id : A, B, C, D.
> db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='A';
> (.... nothing happens.. I press the Ctrl-C to stop it. This is what comes out
> :)
> Cancel request sent
> ERROR: canceling query due to user request
>
> (If I try another obat_id)
> db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='B';
> (Less than a second, this is what comes out :)
> UPDATE 1
It could well be another client has a lock on that record, for example
by doing a SELECT FOR UPDATE w/o a NOWAIT.
You can verify by querying pg_locks. IIRC you can also see what query
caused the lock by joining against some other system table, but the
details escape me atm (check the archives, I learned that by following
this list).
If it's indeed a locked record, the process causing the lock is listed.
Either kill it or call it's owner back from his/her coffee break ;)
I doubt it's anything serious.
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
//Showing your Vision to the World//
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: [PERFORM] need help
@ 2005-12-21 11:10 Alban Medici (NetCentrex) <amedici@fr.netcentrex.net>
parent: Tino Wildenhain <tino@wildenhain.de>
0 siblings, 0 replies; 35+ messages in thread
From: Alban Medici \(NetCentrex\) @ 2005-12-21 11:10 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-sql; pgsql-performance@postgresql.org
Try to execute your query (in psql) with prefixing by EXPLAIN ANALYZE and
send us the result
db=# EXPLAIN ANALYZE UPDATE s_apotik SET stock = 100 WHERE obat_id='A';
regards
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tino Wildenhain
Sent: mardi 6 décembre 2005 09:55
To: Jenny
Cc: pgsql-general@postgresql.org; pgsql-sql@postgresql.org;
pgsql-performance@postgresql.org
Subject: Re: [PERFORM] [GENERAL] need help
Jenny schrieb:
> I'm running PostgreSQL 8.0.3 on i686-pc-linux-gnu (Fedora Core 2).
> I've been dealing with Psql for over than 2 years now, but I've never
> had this case before.
>
> I have a table that has about 20 rows in it.
>
> Table "public.s_apotik"
> Column | Type | Modifiers
> -------------------+------------------------------+------------------
> obat_id | character varying(10) | not null
> stock | numeric | not null
> s_min | numeric | not null
> s_jual | numeric |
> s_r_jual | numeric |
> s_order | numeric |
> s_r_order | numeric |
> s_bs | numeric |
> last_receive | timestamp without time zone |
> Indexes:
> "s_apotik_pkey" PRIMARY KEY, btree(obat_id)
>
> When I try to UPDATE one of the row, nothing happens for a very long time.
> First, I run it on PgAdminIII, I can see the miliseconds are growing
> as I waited. Then I stop the query, because the time needed for it is
> unbelievably wrong.
>
> Then I try to run the query from the psql shell. For example, the
> table has obat_id : A, B, C, D.
> db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='A'; (.... nothing
> happens.. I press the Ctrl-C to stop it. This is what comes out
> :)
> Cancel request sent
> ERROR: canceling query due to user request
>
> (If I try another obat_id)
> db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='B'; (Less than a
> second, this is what comes out :) UPDATE 1
>
> I can't do anything to that row. I can't DELETE it. Can't DROP the table.
> I want this data out of my database.
> What should I do? It's like there's a falsely pointed index here.
> Any help would be very much appreciated.
>
1) lets hope you do regulary backups - and actually tested restore.
1a) if not, do it right now
2) reindex the table
3) try again to modify
Q: are there any foreign keys involved? If so, reindex those tables too,
just in case.
did you vacuum regulary?
HTH
Tino
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 35+ messages in thread
* need help
@ 2007-05-14 06:50 Penchalaiah P. <penchalaiahp@infics.com>
0 siblings, 3 replies; 35+ messages in thread
From: Penchalaiah P. @ 2007-05-14 06:50 UTC (permalink / raw)
To: pgsql-sql
Hi ...
Create table cdano_nya(cdano int4,nyano int4) ... I created
this table and then I inserted some values to this( 234576,86)...
Now when I am updating this table .. its not updating
..query is continuously running...
When I am stopping query its giving this message....ERROR:
canceling statement due to user request..
May I know the reason y its not running.. and I am unable to drop this
table also...when I am selecting this table in pgAdmin..its strucking
the pgAdmin.....
Any one can help in this
Thanks & Regards
Penchal Reddy
Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at info.in@infics.com and delete this email from your records.
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: need help
@ 2007-05-14 06:55 Ashish Karalkar <ashish.karalkar@info-spectrum.com>
parent: Penchalaiah P. <penchalaiahp@infics.com>
2 siblings, 0 replies; 35+ messages in thread
From: Ashish Karalkar @ 2007-05-14 06:55 UTC (permalink / raw)
To: Penchalaiah P. <penchalaiahp@infics.com>; pgsql-sql
Anyone else is using this table simulteniously?
With Regards
Ashish...
----- Original Message -----
From: Penchalaiah P.
To: pgsql-sql@postgresql.org
Sent: Monday, May 14, 2007 12:20 PM
Subject: [SQL] need help
Hi .
Create table cdano_nya(cdano int4,nyano int4) . I created this table and then I inserted some values to this( 234576,86).
Now when I am updating this table .. its not updating ..query is continuously running.
When I am stopping query its giving this message..ERROR: canceling statement due to user request..
May I know the reason y its not running.. and I am unable to drop this table also.when I am selecting this table in pgAdmin..its strucking the pgAdmin...
Any one can help in this
Thanks & Regards
Penchal Reddy
Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at info.in@infics.com and delete this email from your records.
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: need help
@ 2007-05-14 06:59 Andrej Ricnik-Bay <andrej.groups@gmail.com>
parent: Penchalaiah P. <penchalaiahp@infics.com>
2 siblings, 0 replies; 35+ messages in thread
From: Andrej Ricnik-Bay @ 2007-05-14 06:59 UTC (permalink / raw)
To: Penchalaiah P. <penchalaiahp@infics.com>; +Cc: pgsql-sql
On 5/14/07, Penchalaiah P. <penchalaiahp@infics.com> wrote:
> Any one can help in this
Operating system? Postgres version?
How does psql behave? Anything in the logs?
Cheers,
Andrej
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: need help
@ 2007-05-14 18:40 Aaron Bono <postgresql@aranya.com>
parent: Penchalaiah P. <penchalaiahp@infics.com>
2 siblings, 0 replies; 35+ messages in thread
From: Aaron Bono @ 2007-05-14 18:40 UTC (permalink / raw)
To: Penchalaiah P. <penchalaiahp@infics.com>; +Cc: pgsql-sql
On 5/14/07, Penchalaiah P. <penchalaiahp@infics.com> wrote:
>
> Hi …
>
> Create table cdano_nya(cdano int4,nyano int4) … I created this
> table and then I inserted some values to this( 234576,86)…
>
>
>
> Now when I am updating this table .. its not updating ..query
> is continuously running…
>
>
>
> When I am stopping query its giving this message….ERROR:
> canceling statement due to user request..
>
> May I know the reason y its not running.. and I am unable to drop this
> table also…when I am selecting this table in pgAdmin..its strucking the
> pgAdmin…..
>
>
>
> Any one can help in this
>
>
What does your query look like? Are you using locking or transactions where
other queries are blocking your query from running?
-Aaron
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 35+ messages in thread
* need help
@ 2007-12-26 19:19 A. Wiryawan <awiryawan@yahoo.com>
0 siblings, 1 reply; 35+ messages in thread
From: A. Wiryawan @ 2007-12-26 19:19 UTC (permalink / raw)
To: pgsql-sql
is there any one online in yahoo messenger right now..?
____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: need help
@ 2007-12-26 20:22 Richard Broersma Jr <rabroersma@yahoo.com>
parent: A. Wiryawan <awiryawan@yahoo.com>
0 siblings, 0 replies; 35+ messages in thread
From: Richard Broersma Jr @ 2007-12-26 20:22 UTC (permalink / raw)
To: pgsql-sql@postgresql.org, "A. Wiryawan" <awiryawan@yahoo.com>
--- On Wed, 12/26/07, A. Wiryawan <awiryawan@yahoo.com> wrote:
> From: A. Wiryawan <awiryawan@yahoo.com>
> Subject: [SQL] need help
> To: pgsql-sql@postgresql.org
> Date: Wednesday, December 26, 2007, 11:19 AM
> is there any one online in yahoo messenger right now..?
I am not, but you can find alot of PostgreSQL people on IRC:
http://www.postgresql.org/community/irc
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: need help
@ 2007-12-26 20:42 Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 0 replies; 35+ messages in thread
From: Richard Broersma Jr @ 2007-12-26 20:42 UTC (permalink / raw)
To: A. Wiryawan <awiryawan@yahoo.com>; +Cc: pgsql-sql
--- On Wed, 12/26/07, A. Wiryawan <awiryawan@yahoo.com> wrote:
> niwey do you have any e-books abour postgresql to be
> shared, if you don't mind please sent me
Sure, There are lots of books on the Postgresql site:
http://www.postgresql.org/docs/8.2/interactive/index.html
http://www.postgresql.org/docs/techdocs.4
http://www.postgresql.org/docs/techdocs.2
Also, I should let you know that there are good practices to follow when sending emails to any of the PostgreSQL mailing lists.
1) when ever you reply to an email from the mailing list, make sure to reply all. Sometimes other list subscribers can join in answering your emails. However, if you only reply to me they will not have that chance. Also, I might be to busy to repond at times, so repling to the whole mailing list will help you get answers more quickly.
2) the community of behind this mailing list asks that we use bottom posting when we reply to an email. It is not preferred to put your replies at the top of the email.
3) Try to use descriptive email subjects. For example, your email subject was "need help". This subject could be improved to say "Need links to online postgresql references."
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 35+ messages in thread
* need help
@ 2013-02-21 17:36 denero team <deneroteam@gmail.com>
0 siblings, 1 reply; 35+ messages in thread
From: denero team @ 2013-02-21 17:36 UTC (permalink / raw)
To: pgsql-sql
Hi All,
I need some help for my problem.
Problem :
I have following tables
1. Location :
id, name, code
2. Product
id, name, code, location ( ref to location table)
2. Product_Move
id, product_id ( ref to product table), source_location (ref to
location table) , destination_location ( ref to location table) ,
datetime ( date when move is created)
now i want to know for given period of dates, where is the product actually.
can anyone help me ??
Thanks,
Dhaval
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: need help
@ 2013-02-21 19:56 Carlos Chapi <carlos.chapi@2ndquadrant.com>
parent: denero team <deneroteam@gmail.com>
0 siblings, 1 reply; 35+ messages in thread
From: Carlos Chapi @ 2013-02-21 19:56 UTC (permalink / raw)
To: denero team <deneroteam@gmail.com>; +Cc: pgsql-sql
Hello,
Maybe this query can help you
SELECT p.name, l.name
FROM location l
INNER JOIN product_move m ON m.source_location = location.id
INNER JOIN product p ON m.product_id = p.id
WHERE p.id = $product_id
AND m.datetime < $given_date
ORDER BY datetime DESC LIMIT 1
It will return the name of the product and the location for a given id and
date.
2013/2/21 denero team <deneroteam@gmail.com>
> Hi All,
>
> I need some help for my problem.
> Problem :
> I have following tables
> 1. Location :
> id, name, code
> 2. Product
> id, name, code, location ( ref to location table)
> 2. Product_Move
> id, product_id ( ref to product table), source_location (ref to
> location table) , destination_location ( ref to location table) ,
> datetime ( date when move is created)
>
> now i want to know for given period of dates, where is the product
> actually.
>
> can anyone help me ??
>
> Thanks,
>
> Dhaval
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: need help
@ 2013-02-21 20:20 denero team <deneroteam@gmail.com>
parent: Carlos Chapi <carlos.chapi@2ndquadrant.com>
0 siblings, 2 replies; 35+ messages in thread
From: denero team @ 2013-02-21 20:20 UTC (permalink / raw)
To: Carlos Chapi <carlos.chapi@2ndquadrant.com>; +Cc: pgsql-sql
Hi,
Thanks for replying me. yes you are right at some level for my case.
but its not what I want. I am explaining you a case by example.
Consider following are data in each table
Location :
id , name, code
1, stock, stock
2, customer, customer
3, asset, asset
Product :
id, name, code, location
1, product1, p1, 1
2, product2, p2, 3
Product_Move :
id, product_id, source_location, destination_location, datetime
1, 1, Null, 1, 2012-10-15 10:00:00
2, 2, Null, 1, 2012-10-15 10:05:00
3, 2, 1, 3, 2012-12-01 09:00:00
Please review all data , you can see, current location of product1
(p1) is 1 (stock) and current location of product2 (p2) is 3 (asset).
now i want to find location of all products for given period
for example : 2012-11-01 to 2012-11-30, then i need result should be like below
move_id, product_id, location_id
1, 1, 1
2, 2, 1
another example : 2012-11-01 to 2012-12-31
move_id, product_id, location_id
1, 1, 1
2, 2, 1
3, 2, 3
Now I really don't know how to do this.
can you advise me more ?
Thanks,
Dhaval
On Fri, Feb 22, 2013 at 1:26 AM, Carlos Chapi
<carlos.chapi@2ndquadrant.com> wrote:
> Hello,
>
> Maybe this query can help you
>
> SELECT p.name, l.name
> FROM location l
> INNER JOIN product_move m ON m.source_location = location.id
> INNER JOIN product p ON m.product_id = p.id
> WHERE p.id = $product_id
> AND m.datetime < $given_date
> ORDER BY datetime DESC LIMIT 1
>
> It will return the name of the product and the location for a given id and
> date.
>
>
> 2013/2/21 denero team <deneroteam@gmail.com>
>>
>> Hi All,
>>
>> I need some help for my problem.
>> Problem :
>> I have following tables
>> 1. Location :
>> id, name, code
>> 2. Product
>> id, name, code, location ( ref to location table)
>> 2. Product_Move
>> id, product_id ( ref to product table), source_location (ref to
>> location table) , destination_location ( ref to location table) ,
>> datetime ( date when move is created)
>>
>> now i want to know for given period of dates, where is the product
>> actually.
>>
>> can anyone help me ??
>>
>> Thanks,
>>
>> Dhaval
>>
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: need help
@ 2013-02-21 20:35 Oliver d'Azevedo Cristina <oliveiros.cristina@gmail.com>
parent: denero team <deneroteam@gmail.com>
1 sibling, 1 reply; 35+ messages in thread
From: Oliver d'Azevedo Cristina @ 2013-02-21 20:35 UTC (permalink / raw)
To: denero team <deneroteam@gmail.com>; +Cc: Carlos Chapi <carlos.chapi@2ndquadrant.com>; pgsql-sql
SELECT move_id, product_id,destination_location as location_id
FROM product_move
Where datetime BETWEEN $first
AND $last
Have you tried something like this?
Best,
Oliver
Enviado via iPhone
Em 21/02/2013, às 08:20 PM, denero team <deneroteam@gmail.com> escreveu:
> Hi,
>
> Thanks for replying me. yes you are right at some level for my case.
> but its not what I want. I am explaining you a case by example.
>
> Consider following are data in each table
>
> Location :
> id , name, code
> 1, stock, stock
> 2, customer, customer
> 3, asset, asset
>
> Product :
> id, name, code, location
> 1, product1, p1, 1
> 2, product2, p2, 3
>
>
> Product_Move :
> id, product_id, source_location, destination_location, datetime
> 1, 1, Null, 1, 2012-10-15 10:00:00
> 2, 2, Null, 1, 2012-10-15 10:05:00
> 3, 2, 1, 3, 2012-12-01 09:00:00
>
> Please review all data , you can see, current location of product1
> (p1) is 1 (stock) and current location of product2 (p2) is 3 (asset).
>
> now i want to find location of all products for given period
>
> for example : 2012-11-01 to 2012-11-30, then i need result should be like below
> move_id, product_id, location_id
> 1, 1, 1
> 2, 2, 1
>
> another example : 2012-11-01 to 2012-12-31
> move_id, product_id, location_id
> 1, 1, 1
> 2, 2, 1
> 3, 2, 3
>
> Now I really don't know how to do this.
>
> can you advise me more ?
>
>
> Thanks,
>
> Dhaval
>
>
> On Fri, Feb 22, 2013 at 1:26 AM, Carlos Chapi
> <carlos.chapi@2ndquadrant.com> wrote:
>> Hello,
>>
>> Maybe this query can help you
>>
>> SELECT p.name, l.name
>> FROM location l
>> INNER JOIN product_move m ON m.source_location = location.id
>> INNER JOIN product p ON m.product_id = p.id
>> WHERE p.id = $product_id
>> AND m.datetime < $given_date
>> ORDER BY datetime DESC LIMIT 1
>>
>> It will return the name of the product and the location for a given id and
>> date.
>>
>>
>> 2013/2/21 denero team <deneroteam@gmail.com>
>>>
>>> Hi All,
>>>
>>> I need some help for my problem.
>>> Problem :
>>> I have following tables
>>> 1. Location :
>>> id, name, code
>>> 2. Product
>>> id, name, code, location ( ref to location table)
>>> 2. Product_Move
>>> id, product_id ( ref to product table), source_location (ref to
>>> location table) , destination_location ( ref to location table) ,
>>> datetime ( date when move is created)
>>>
>>> now i want to know for given period of dates, where is the product
>>> actually.
>>>
>>> can anyone help me ??
>>>
>>> Thanks,
>>>
>>> Dhaval
>>>
>>>
>>> --
>>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: need help
@ 2013-02-21 21:07 Russell Keane <Russell.Keane@inps.co.uk>
parent: Oliver d'Azevedo Cristina <oliveiros.cristina@gmail.com>
0 siblings, 2 replies; 35+ messages in thread
From: Russell Keane @ 2013-02-21 21:07 UTC (permalink / raw)
To: deneroteam@gmail.com <deneroteam@gmail.com>; +Cc: pgsql-sql
> Consider following are data in each table
>
> Location :
> id , name, code
> 1, stock, stock
> 2, customer, customer
> 3, asset, asset
>
> Product :
> id, name, code, location
> 1, product1, p1, 1
> 2, product2, p2, 3
>
>
> Product_Move :
> id, product_id, source_location, destination_location, datetime 1, 1,
> Null, 1, 2012-10-15 10:00:00 2, 2, Null, 1, 2012-10-15 10:05:00 3, 2,
> 1, 3, 2012-12-01 09:00:00
>
> Please review all data , you can see, current location of product1
> (p1) is 1 (stock) and current location of product2 (p2) is 3 (asset).
>
> now i want to find location of all products for given period
>
> for example : 2012-11-01 to 2012-11-30, then i need result should be
> like below move_id, product_id, location_id 1, 1, 1 2, 2, 1
>
> another example : 2012-11-01 to 2012-12-31 move_id, product_id,
> location_id 1, 1, 1 2, 2, 1 3, 2, 3
>
> Now I really don't know how to do this.
>
> can you advise me more ?
>
>
> Thanks,
>
> Dhaval
I think these are the sqls you are looking for:
SELECT pm.id as move_id, p.id as product_id, l.id as location_id
FROM product_move pm inner join product p on pm.product_id = p.id inner join location l on pm.destination_location = l.id
and datetime BETWEEN '2010-1-01' AND '2012-12-31'
Regards,
Russell Keane
INPS
Follow us on twitter | visit www.inps.co.uk
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: need help
@ 2013-02-21 21:28 Russell Keane <Russell.Keane@inps.co.uk>
parent: Russell Keane <Russell.Keane@inps.co.uk>
1 sibling, 1 reply; 35+ messages in thread
From: Russell Keane @ 2013-02-21 21:28 UTC (permalink / raw)
To: Russell Keane <Russell.Keane@inps.co.uk>; deneroteam@gmail.com <deneroteam@gmail.com>; +Cc: pgsql-sql
> > Now I really don't know how to do this.
> >
> > can you advise me more ?
> >
> >
> > Thanks,
> >
> > Dhaval
>
>
> I think these are the sqls you are looking for:
>
> SELECT pm.id as move_id, p.id as product_id, l.id as location_id
> FROM product_move pm inner join product p on pm.product_id = p.id inner join location l on pm.destination_location = l.id
> and datetime BETWEEN '2010-1-01' AND '2012-12-31'
Sorry, that should have been:
For your 2 examples:
SELECT pm.id as move_id, p.id as product_id, l.id as location_id
FROM product_move pm inner join product p on pm.product_id = p.id inner join location l on pm.destination_location = l.id
and datetime < '2012-11-30'
SELECT pm.id as move_id, p.id as product_id, l.id as location_id
FROM product_move pm inner join product p on pm.product_id = p.id inner join location l on pm.destination_location = l.id
and datetime < '2012-12-31'
I'm not what the use of the 'from' date is in your examples.
Do you need to know the final destination of the product in that time period?
Or every destination location of the product in that time period?
Regards,
Russell Keane
INPS
Follow us on twitter | visit www.inps.co.uk
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: need help
@ 2013-02-21 22:26 Oliver d'Azevedo Cristina <oliveiros.cristina@gmail.com>
parent: Russell Keane <Russell.Keane@inps.co.uk>
0 siblings, 1 reply; 35+ messages in thread
From: Oliver d'Azevedo Cristina @ 2013-02-21 22:26 UTC (permalink / raw)
To: Russell Keane <Russell.Keane@inps.co.uk>; +Cc: Russell Keane <Russell.Keane@inps.co.uk>; deneroteam@gmail.com <deneroteam@gmail.com>; pgsql-sql
Sorry, why do you need the joins?
Best,
Oliver
Enviado via iPhone
Em 21/02/2013, às 09:28 PM, Russell Keane <Russell.Keane@inps.co.uk> escreveu:
>>> Now I really don't know how to do this.
>>>
>>> can you advise me more ?
>>>
>>>
>>> Thanks,
>>>
>>> Dhaval
>>
>>
>> I think these are the sqls you are looking for:
>>
>> SELECT pm.id as move_id, p.id as product_id, l.id as location_id
>> FROM product_move pm inner join product p on pm.product_id = p.id inner join location l on pm.destination_location = l.id
>> and datetime BETWEEN '2010-1-01' AND '2012-12-31'
>
>
> Sorry, that should have been:
>
> For your 2 examples:
>
> SELECT pm.id as move_id, p.id as product_id, l.id as location_id
> FROM product_move pm inner join product p on pm.product_id = p.id inner join location l on pm.destination_location = l.id
> and datetime < '2012-11-30'
>
> SELECT pm.id as move_id, p.id as product_id, l.id as location_id
> FROM product_move pm inner join product p on pm.product_id = p.id inner join location l on pm.destination_location = l.id
> and datetime < '2012-12-31'
>
> I'm not what the use of the 'from' date is in your examples.
> Do you need to know the final destination of the product in that time period?
> Or every destination location of the product in that time period?
>
> Regards,
>
> Russell Keane
> INPS
>
> Follow us on twitter | visit www.inps.co.uk
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: need help
@ 2013-02-22 03:55 Jaime Casanova <jaime@2ndquadrant.com>
parent: denero team <deneroteam@gmail.com>
1 sibling, 0 replies; 35+ messages in thread
From: Jaime Casanova @ 2013-02-22 03:55 UTC (permalink / raw)
To: denero team <deneroteam@gmail.com>; +Cc: Carlos Chapi <carlos.chapi@2ndquadrant.com>; pgsql-sql
On Thu, Feb 21, 2013 at 3:20 PM, denero team <deneroteam@gmail.com> wrote:
> Hi,
>
> Thanks for replying me. yes you are right at some level for my case.
> but its not what I want. I am explaining you a case by example.
>
[...]
>
> Now I really don't know how to do this.
>
> can you advise me more ?
>
I'm not really sure if you even know what you want, because the
examples you showed were just a:
select * from prodct_move where datetime < $given_date
but from the description you gave before i understood another thing,
so this is my only attempt to get an answer from thin air for you:
SELECT distinct on (p.name) p.name, l.name, datetime
FROM location l
INNER JOIN product_move m ON m.destination_location = l.id
INNER JOIN product p ON m.product_id = p.id
WHERE
m.datetime < '2012-12-31'
ORDER BY p.name, datetime DESC
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566 Cell: +593 987171157
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: need help
@ 2013-02-22 08:31 Russell Keane <Russell.Keane@inps.co.uk>
parent: Oliver d'Azevedo Cristina <oliveiros.cristina@gmail.com>
0 siblings, 0 replies; 35+ messages in thread
From: Russell Keane @ 2013-02-22 08:31 UTC (permalink / raw)
To: Oliver d'Azevedo Cristina <oliveiros.cristina@gmail.com>; +Cc: pgsql-sql
> Sorry, why do you need the joins?
>
> Best,
> Oliver
Strictly speaking, for the examples and results given, the joins are pointless when you can get all the info from the 'move' table (but then the problem is like the 'hello world' of SQL)
But then the other 2 tables are completely redundant in the examples (even for context they're not very useful), so I made the assumption that he would also want to retrieve some other information about the products or locations and the joins would then be required.
I think the question may be missing some details about what is actually wanted...
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: need help
@ 2013-02-22 09:26 Russell Keane <Russell.Keane@inps.co.uk>
parent: Russell Keane <Russell.Keane@inps.co.uk>
1 sibling, 1 reply; 35+ messages in thread
From: Russell Keane @ 2013-02-22 09:26 UTC (permalink / raw)
To: deneroteam@gmail.com <deneroteam@gmail.com>; +Cc: pgsql-sql
> Or every destination location of the product in that time period?
Ok, I've had another look at this this morning on the assumption you need every location that a product has been in that time period.
This also assumes you're getting all the data you're interested in from the product_move table (no need to join to the other tables).
The query will get:
Every product_move item for each product between the 'from' and 'to' dates
AND
The most recent product_move item for each product before the 'from' date.
SELECT id as move_id, product_id, destination_location as location_id
FROM product_move
where datetime between '2012-11-01' and '2012-12-31'
union
SELECT pm.id as move_id, pm.product_id, pm.destination_location as location_id
FROM product_move pm
inner join
(
SELECT product_id, max(datetime) as datetime
FROM product_move
where datetime < '2012-11-01'
group by product_id
) X
on pm.product_id = X.product_id and pm.datetime = X.datetime
Thus you will know where every product was coming into the period and every subsequent destination it was moved to within that period.
(although I'm still not sure this is what you want)
Regards,
Russell Keane
INPS
Follow us on twitter | visit www.inps.co.uk
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: need help
@ 2013-02-22 10:41 denero team <deneroteam@gmail.com>
parent: Russell Keane <Russell.Keane@inps.co.uk>
0 siblings, 1 reply; 35+ messages in thread
From: denero team @ 2013-02-22 10:41 UTC (permalink / raw)
To: Russell Keane <Russell.Keane@inps.co.uk>; +Cc: pgsql-sql
Thanks Russell,
let me check the query.
On Fri, Feb 22, 2013 at 2:56 PM, Russell Keane <Russell.Keane@inps.co.uk> wrote:
>> Or every destination location of the product in that time period?
>
> Ok, I've had another look at this this morning on the assumption you need every location that a product has been in that time period.
> This also assumes you're getting all the data you're interested in from the product_move table (no need to join to the other tables).
>
> The query will get:
> Every product_move item for each product between the 'from' and 'to' dates
> AND
> The most recent product_move item for each product before the 'from' date.
>
> SELECT id as move_id, product_id, destination_location as location_id
> FROM product_move
> where datetime between '2012-11-01' and '2012-12-31'
> union
> SELECT pm.id as move_id, pm.product_id, pm.destination_location as location_id
> FROM product_move pm
> inner join
> (
> SELECT product_id, max(datetime) as datetime
> FROM product_move
> where datetime < '2012-11-01'
> group by product_id
> ) X
> on pm.product_id = X.product_id and pm.datetime = X.datetime
>
> Thus you will know where every product was coming into the period and every subsequent destination it was moved to within that period.
> (although I'm still not sure this is what you want)
>
> Regards,
>
> Russell Keane
> INPS
>
> Follow us on twitter | visit www.inps.co.uk
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: need help
@ 2013-02-22 18:30 denero team <deneroteam@gmail.com>
parent: denero team <deneroteam@gmail.com>
0 siblings, 0 replies; 35+ messages in thread
From: denero team @ 2013-02-22 18:30 UTC (permalink / raw)
To: Russell Keane <Russell.Keane@inps.co.uk>; +Cc: pgsql-sql
Hey, Thanks Russell and all others.
The query worked well. I got result what I expected.
Thanks again,
Dhaval
On Fri, Feb 22, 2013 at 4:11 PM, denero team <deneroteam@gmail.com> wrote:
> Thanks Russell,
>
> let me check the query.
>
> On Fri, Feb 22, 2013 at 2:56 PM, Russell Keane <Russell.Keane@inps.co.uk> wrote:
>>> Or every destination location of the product in that time period?
>>
>> Ok, I've had another look at this this morning on the assumption you need every location that a product has been in that time period.
>> This also assumes you're getting all the data you're interested in from the product_move table (no need to join to the other tables).
>>
>> The query will get:
>> Every product_move item for each product between the 'from' and 'to' dates
>> AND
>> The most recent product_move item for each product before the 'from' date.
>>
>> SELECT id as move_id, product_id, destination_location as location_id
>> FROM product_move
>> where datetime between '2012-11-01' and '2012-12-31'
>> union
>> SELECT pm.id as move_id, pm.product_id, pm.destination_location as location_id
>> FROM product_move pm
>> inner join
>> (
>> SELECT product_id, max(datetime) as datetime
>> FROM product_move
>> where datetime < '2012-11-01'
>> group by product_id
>> ) X
>> on pm.product_id = X.product_id and pm.datetime = X.datetime
>>
>> Thus you will know where every product was coming into the period and every subsequent destination it was moved to within that period.
>> (although I'm still not sure this is what you want)
>>
>> Regards,
>>
>> Russell Keane
>> INPS
>>
>> Follow us on twitter | visit www.inps.co.uk
>>
>>
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 35+ messages in thread
end of thread, other threads:[~2013-02-22 18:30 UTC | newest]
Thread overview: 35+ 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>
2002-01-01 11:51 Need help Shamik Majumder <shamik.majumder@wipro.com>
2002-01-08 15:17 ` Re: Need help Holger Krug <hkrug@rationalizer.com>
2002-01-08 17:17 ` Re: [GENERAL] Need help Jason Earl <jason.earl@simplot.com>
2002-01-08 20:23 ` Re: [SQL] Need help Bill Cunningham <billc@ballydev.com>
2002-01-09 00:04 ` Re: [SQL] Need help Doug McNaught <doug@wireboard.com>
2002-01-09 09:47 Re: [GENERAL] Need help Maarten.Boekhold@reuters.com
2003-11-14 01:04 Need Help Abdul Wahab Dahalan <wahab@mimos.my>
2003-11-14 05:11 ` Re: Need Help Bruno Wolff III <bruno@wolff.to>
2005-12-06 08:38 need help Jenny <jenny_tania@yahoo.com>
2005-12-06 08:54 ` Re: need help Tino Wildenhain <tino@wildenhain.de>
2005-12-21 11:10 ` Re: [PERFORM] need help Alban Medici (NetCentrex) <amedici@fr.netcentrex.net>
2005-12-06 10:45 ` Re: need help Alban Hertroys <alban@magproductions.nl>
2007-05-14 06:50 need help Penchalaiah P. <penchalaiahp@infics.com>
2007-05-14 06:55 ` Re: need help Ashish Karalkar <ashish.karalkar@info-spectrum.com>
2007-05-14 06:59 ` Re: need help Andrej Ricnik-Bay <andrej.groups@gmail.com>
2007-05-14 18:40 ` Re: need help Aaron Bono <postgresql@aranya.com>
2007-12-26 19:19 need help A. Wiryawan <awiryawan@yahoo.com>
2007-12-26 20:22 ` Re: need help Richard Broersma Jr <rabroersma@yahoo.com>
2007-12-26 20:42 Re: need help Richard Broersma Jr <rabroersma@yahoo.com>
2013-02-21 17:36 need help denero team <deneroteam@gmail.com>
2013-02-21 19:56 ` Re: need help Carlos Chapi <carlos.chapi@2ndquadrant.com>
2013-02-21 20:20 ` Re: need help denero team <deneroteam@gmail.com>
2013-02-21 20:35 ` Re: need help Oliver d'Azevedo Cristina <oliveiros.cristina@gmail.com>
2013-02-21 21:07 ` Re: need help Russell Keane <Russell.Keane@inps.co.uk>
2013-02-21 21:28 ` Re: need help Russell Keane <Russell.Keane@inps.co.uk>
2013-02-21 22:26 ` Re: need help Oliver d'Azevedo Cristina <oliveiros.cristina@gmail.com>
2013-02-22 08:31 ` Re: need help Russell Keane <Russell.Keane@inps.co.uk>
2013-02-22 09:26 ` Re: need help Russell Keane <Russell.Keane@inps.co.uk>
2013-02-22 10:41 ` Re: need help denero team <deneroteam@gmail.com>
2013-02-22 18:30 ` Re: need help denero team <deneroteam@gmail.com>
2013-02-22 03:55 ` Re: need help Jaime Casanova <jaime@2ndquadrant.com>
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