[SQL]

[Petr Jezek]

Hi I'll ask for help.
I have had used MySQL and the syntax of INSERT have a switch
IGNORE if You try to insert a row that in the table already is.

I'll ask if exist something like this in postgre SQL syntax.

    Petr Jezek

[SQL] INSERT [IGNORE] INTO TABLE

[Petr Jezek]

If I try to insert some row which in the table already exist the
postgre don't insert it and tell some error. Everything ok.
I'll insert only if there is not the same (same key) record,
else don't insert and don't tell me errors about it.
In MySQL is a switch [IGNORE]. 

Thanx for help

    Petr Jezek

RE: [SQL] INSERT [IGNORE] INTO TABLE

[Petr Jezek]

On Tue, 31 Oct 2000, Edmar Wiggers wrote:

> > If I try to insert some row which in the table already exist the
> > postgre don't insert it and tell some error. Everything ok.
> > I'll insert only if there is not the same (same key) record,
> > else don't insert and don't tell me errors about it.
> > In MySQL is a switch [IGNORE].
> 
> Not a good feature for me. What do you do if the record already exists?
> Update it?
> 
> Check existence and then insert or update. If you want, I guess you could
> wrap that inside a stored procedure.
> 
Well, that's a good idea :-) but I'm not sure about how to create
such procedure. I would appreciate Your help.

Petr Jezek

Re: [SQL] Bad SUM result

[Petr Jezek]

There're no another ways? It don't looks like optimal.

Petr Jezek
- Original Message -
From: "Jean-Luc Lachance" <[EMAIL PROTECTED]>
To: "Roy Souther" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, July 08, 2002 5:22 PM
Subject: Re: [SQL] Bad SUM result


> That is because your query is generating a cartesian product.
>
> Try:
>
> SELECT (
>   SELECT SUM(totalprice)
>   FROM invoices
>   WHERE custnumber = '1'
> ) - (
>   SELECT SUM(paymentamount)
>   FROM payments
>   WHERE custnumber = '1'
> )
>
>
>
> Roy Souther wrote:
> >
> > -BEGIN PGP SIGNED MESSAGE-
> > Hash: SHA1
> >
> > I have an invoice database that has two tables one for invoices and one
for
> > payments. I want to get the account balance for a client by subtracting
the
> > sum of all payments from the sum off all invoices for that client.
> >
> > Here is the SQL that I thought should work.
> > SELECT SUM(t0.totalprice)-SUM(t1.paymentamount) FROM invoices t0,
payments t1
> > WHERE t0.custnumber='1' AND t1.custnumber='1'
> >
> > It works fine if there is only one invoice and one payment but as soon
as
> > there is more then one of either it screws up. For each match found in
> > payments the invoice sum is added to the total. So if client 1 purchased
a
> > $100 item then maid a $10 payment the SQL would return the balance of
$90
> > just fine. When the client makes a second payment of $15 the balance is
$75
> > but this SQL returns ($100+$100)-($10+$15) = $175. A third payment of $1
> > would return ($100+$100+$100)-($10+$15+$1) = $274.
> >
> > Could some one explain this to me and recommend an SQL command that
would work
> > please? I could do this using a temp table but that would be very messy
as I
> > would really like it to be a single SQL command.
> > - --
> > Roy Souther <[EMAIL PROTECTED]>
> > http://www.SiliconTao.com
> >
> > Linux: May the source be with you.
> >
> > -BEGIN PGP SIGNATURE-
> > Version: GnuPG v1.0.6 (GNU/Linux)
> > Comment: For info see http://www.gnupg.org
> >
> > iEYEARECAAYFAj0oo9MACgkQCbnxcmEBt43qFQCgtjCs7khKGH+2LYd78O9mA3h4
> > vDQAn0GkKkuYl1Kybgm/ITO4LbO1WWLX
> > =1G4R
> > -END PGP SIGNATURE-
> >
> > ---(end of broadcast)---
> > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
>
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])