[SQL] update syntax
Hi, While using Ms SQL server I used to write update statements like this, UPDATE a SET a.col1 = b.col1 FROM table1 a INNER JOIN table1 b ON a.col2 = b.col2 WHERE a.col3 = ‘something’ AND b.col3 = ‘somethingelse’ But I can’t seem to do this in postgres, it gives me an error saying ‘syntax error at or near “.”’. How can I do something similar to this i.e. join on the same table? Thanks, /P
[SQL] Converting varchar to bool
Hi, I have currently trouble working with boolean values and variables in functions. As one would expect, a select '1'::bool, 't'::bool, 'true'::unknown::boolean works. As a select '1' tells us this seems as a conversion unknown->bool or ??maybe?? a boolean literal?? what-o-ever, at least my function gets not accepted: CREATE OR REPLACE FUNCTION testbool(varchar) RETURNS boolean AS $BODY$ SELECT $1::boolean $BODY$ LANGUAGE 'sql' STABLE STRICT SECURITY DEFINER; Postgres complains that it can't convert varchar to boolean. Actually neither $1::unknown::bool works. It turns out that there exists no conversion varchar/text/unknown -> boolean. Actually there's not any conversion -> boolean. How should this be treaten? *being confused* sorry for duplicating (I'm sure) this topic, at least I tried to find it in the listarcive. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] update syntax
Try something like this UPDATE table1SET col1 = b.col1FROM table1 bWHERE table1.col2 = b.col2 andtable1.col3 = something andb.col3 = somethingelse - Original Message - From: Praveen Raja To: pgsql-sql@postgresql.org Sent: Wednesday, June 15, 2005 7:39 AM Subject: [SQL] update syntax Hi, While using Ms SQL server I used to write update statements like this, UPDATE a SET a.col1 = b.col1 FROM table1 a INNER JOIN table1 b ON a.col2 = b.col2 WHERE a.col3 = something AND b.col3 = somethingelse But I cant seem to do this in postgres, it gives me an error saying syntax error at or near .. How can I do something similar to this i.e. join on the same table? Thanks, /P
Re: [SQL] update syntax
Thanks. The UPDATE works ok now. But using the same logic it doesn’t seem possible to delete rows. Is this also possible? /P -Original Message- From: Bruno Prévost [mailto:[EMAIL PROTECTED] Sent: 15 June 2005 14:22 To: Praveen Raja Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] update syntax Try something like this UPDATE table1 SET col1 = b.col1 FROM table1 b WHERE table1.col2 = b.col2 and table1.col3 = ‘something’ and b.col3 = ‘somethingelse’ - Original Message - From: Praveen Raja To: pgsql-sql@postgresql.org Sent: Wednesday, June 15, 2005 7:39 AM Subject: [SQL] update syntax Hi, While using Ms SQL server I used to write update statements like this, UPDATE a SET a.col1 = b.col1 FROM table1 a INNER JOIN table1 b ON a.col2 = b.col2 WHERE a.col3 = ‘something’ AND b.col3 = ‘somethingelse’ But I can’t seem to do this in postgres, it gives me an error saying ‘syntax error at or near “.”’. How can I do something similar to this i.e. join on the same table? Thanks, /P
Re: [SQL] update syntax
I don't know if you can do that using the same logic. Perhaps someone else can help? For sure, you can use a subselect. delete from table1where col1 in (SELECT a.col1 FROM table1 a JOIN table1 b ON a.col2 = b.col2 WHERE a.col3 = something AND b.col3 = somethingelse) - Original Message - From: Praveen Raja To: 'Bruno Prévost' Cc: pgsql-sql@postgresql.org Sent: Wednesday, June 15, 2005 8:35 AM Subject: RE: [SQL] update syntax Thanks. The UPDATE works ok now. But using the same logic it doesnt seem possible to delete rows. Is this also possible? /P -Original Message-From: Bruno Prévost [mailto:[EMAIL PROTECTED] Sent: 15 June 2005 14:22To: Praveen RajaCc: pgsql-sql@postgresql.orgSubject: Re: [SQL] update syntax Try something like this UPDATE table1SET col1 = b.col1FROM table1 bWHERE table1.col2 = b.col2 andtable1.col3 = something andb.col3 = somethingelse - Original Message - From: Praveen Raja To: pgsql-sql@postgresql.org Sent: Wednesday, June 15, 2005 7:39 AM Subject: [SQL] update syntax Hi, While using Ms SQL server I used to write update statements like this, UPDATE a SET a.col1 = b.col1 FROM table1 a INNER JOIN table1 b ON a.col2 = b.col2 WHERE a.col3 = something AND b.col3 = somethingelse But I cant seem to do this in postgres, it gives me an error saying syntax error at or near .. How can I do something similar to this i.e. join on the same table? Thanks, /P
Re: [SQL] Converting varchar to bool
On Wed, Jun 15, 2005 at 14:14:46 +0200,
KÖPFERL Robert <[EMAIL PROTECTED]> wrote:
> Hi,
>
>
> I have currently trouble working with boolean values and variables in
> functions.
>
> As one would expect, a
> select '1'::bool, 't'::bool, 'true'::unknown::boolean
>
> works.
>
> As a select '1' tells us this seems as a conversion unknown->bool
> or ??maybe?? a boolean literal??
No it is not a conversion, '1', 't' and 'true' are all valid boolean
strings representing TRUE. The input is not being converted from text
to boolean.
>
> what-o-ever, at least my function gets not accepted:
>
>
> CREATE OR REPLACE FUNCTION testbool(varchar)
> RETURNS boolean AS
> $BODY$
> SELECT $1::boolean
> $BODY$
> LANGUAGE 'sql' STABLE STRICT SECURITY DEFINER;
>
> Postgres complains that it can't convert varchar to boolean.
> Actually neither $1::unknown::bool works.
> It turns out that there exists no conversion varchar/text/unknown ->
> boolean.
> Actually there's not any conversion -> boolean.
>
> How should this be treaten?
> *being confused*
>
>
> sorry for duplicating (I'm sure) this topic, at least I tried to find it in
> the listarcive.
It has been discussed in the last week.
test.sql:
CREATE OR REPLACE FUNCTION testbool(varchar)
RETURNS boolean AS
$BODY$
SELECT boolin(textout($1));
$BODY$
LANGUAGE 'sql' STABLE STRICT SECURITY DEFINER;
SELECT testbool('true');
bruno=> \i test.sql
CREATE FUNCTION
testbool
--
t
(1 row)
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] update syntax
On Wed, Jun 15, 2005 at 14:35:42 +0200, Praveen Raja <[EMAIL PROTECTED]> wrote: > Thanks. The UPDATE works ok now. But using the same logic it doesnt > seem possible to delete rows. Is this also possible? Yes. When you use table names in the where clause they are automatically added to the join list if they aren't list in the from item list. For 8.1 this will be changing. There will be a USING clause on DELETE statements that can be used to list extra tables and the implied from feature will be disabled by default. > > Try something like this > > > > UPDATE table1 > SET col1 = b.col1 > FROM table1 b > WHERE table1.col2 = b.col2 and > table1.col3 = something and > b.col3 = somethingelse ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] SELECT very slow
PFC wrote on 14.06.2005 14:26: [...] Now I fire up python, do a SELECT * from the table and retrieve all the data as native objects... Hm, it takes about 1.3 seconds... on my Pentium-M 1600 laptop... Don't you have a problem somewhere ? Are you sure it's not swapping ? did you check memory ? Are you transferring all this data over the network ? Might an obscure cabling problem have reverted your connection to 10 Mbps ? I'm sure. Everything is running on the same machine, about 350 MB free memory. Ouch. I saw you're on Windows so I tried it on the windows machine there which has a postgres installed, over a 100Mbps network, querying from my linux laptop. The windows machine is a piece of crap, Pentium-II 300 and 256 MB Ram, it takes 7 seconds to retrieve the whole table in a python native object. It's not the program or Java. The same program takes about 20 seconds with Firebird and the exactly same data. Thomas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SELECT very slow
It's not the program or Java. The same program takes about 20 seconds with Firebird and the exactly same data. Hm, that's still very slow (it should do it in a couple seconds like my PC does... maybe the problem is common to postgres and firebird ?) Try eliminating disk IO by writing a set returning function which returns 100 rows, something simple like just a sequence number and a text value... if this is slow too... i don't know... do you have an antivirus or zonealarm or something ? Have you tried connecting from another machine ? Thomas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] SELECT very slow
PFC wrote on 15.06.2005 22:04:
It's not the program or Java. The same program takes about 20 seconds
with Firebird and the exactly same data.
Hm, that's still very slow (it should do it in a couple seconds like
my PC does... maybe the problem is common to postgres and firebird ?)
Try eliminating disk IO by writing a set returning function which
returns 100 rows, something simple like just a sequence number and
a text value... if this is slow too... i don't know...
> do you have an antivirus or zonealarm or something ?
Wouldn't that affect all DB access not only PG? And as I said, all other
The 20 seconds are ok. This includes processing of the data in the
application. If I simply loop over the result set and get each column's
value without further processing it takes 4 seconds with Firebird.
Basically I'm doing the following:
rs = stmt.executeQuery("select * from foo");
while (rs.next())
{
for (int i=0; i < 4; i++)
{
Object o = rs.getObject(i+1);
}
}
As I said in my other post, the behaviour/performance in PG is dependent on
the autocommit setting for the connection.
With autocommit set to false the above code takes about 3 seconds in PG
but wit autocommit set to true, PG takes 3 minutes! It seems that it also
is very dependent on the fetchsize (apparently the number of rows that are
cached by the driver). Anything above 100 seems to slow down the overall
process.
Regards
Thomas
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] cursor "" does not exist
Hi, I'm querying a Postgres 8.0.3 database from Java via the 8.0-311 JDBC driver. It mostly works. I ran into the "cursor does not exist" problem, but was able to fix it with connection.setAutoCommit(false); Or so I thought. There are several JSP pages which still throw the 'cursor "portal 1>" does not exist' error. Everything works well if I call the same Java fuction on the command line. I tried to observe the network packets and found an interesting thing: the "select *" queries and responses that works look like this: Request S_1 BEGIN select * from user_pkg.GetPerson_fcn($1) as result 571 Response BEGIN result C SELECT While those that fail look like this: Request select * from material_pkg.ListCautions_fcn($1,$2) as result B Response result C SELECT Note that the successful ones contain strings "S_1" and "BEGIN", and the failed ones do not. However, there also are successful queries without these strings, but they are not "select *" queries. Eg, Request SELECT typname FROM pg_catalog.pg_type WHERE oid=$1 1790 Response typname refcursorC SELECT When I do it successfully on the command line, the "S_1" and "BEGIN" lines are also present in the query. I have a feeling it's some idiosyncrasy that I'm not familiar with. Does "BEGIN" refer to the beginning of a trasaction? I will be grateful for any suggestions. Simon -- Simon (Vsevolod ILyushchenko) [EMAIL PROTECTED] http://www.simonf.com Terrorism is a tactic and so to declare war on terrorism is equivalent to Roosevelt's declaring war on blitzkrieg. Zbigniew Brzezinski, U.S. national security advisor, 1977-81 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] PostgreSQL and Delphi 6
I have a client who wants to use Delphi as a front end to a Database, I would like to use PostgreSQL over MSSQL and have been looking at the psqlodbc project. Will psqlodbc connect with Delphi 6? Basically, I'm wondering if anyone has experience with it? Any help will be appreciated. Thanks, J ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] SELECT with sum on groups ORDERING by the subtotals
Hi Guys!
I need to make a complex query. I am thinking to use plpgsql BUT I am
confused how I can solve this.
What I have:
CREATE TABLE test
(
code varchar(15),
description varchar(60),
group varchar(10),
quant float8,
price float8,
total float8
)
WITHOUT OIDS;
INSERT INTO test (code, description, quant, price, total) VALUES
('92110', 'PRODUCT A', 10, 1, 10);
INSERT INTO test (code, description, quant, price, total) VALUES
('92110', 'PRODUCT A', 5, 0.90, 9);
INSERT INTO test (code, description, quant, price, total) VALUES
('92110', 'PRODUCT A', 100, 0.9, 90);
INSERT INTO test (code, description, quant, price, total) VALUES
('92110', 'PRODUCT A', 10, 1.1, 11);
INSERT INTO test (code, description, quant, price, total) VALUES
('92190', 'PRODUCT b', 10, 1.1, 11);
INSERT INTO test (code, description, quant, price, total) VALUES
('92190', 'PRODUCT b', 10, 1.1, 11);
INSERT INTO test (code, description, quant, price, total) VALUES
('92190', 'PRODUCT b', 10, 1.1, 11);
INSERT INTO test (code, description, quant, price, total) VALUES
('92190', 'PRODUCT b', 20, 0.8, 8);
INSERT INTO test (code, description, quant, price, total) VALUES
('99120', 'PRODUCT C', 10, 0.8, 8);
INSERT INTO test (code, description, quant, price, total) VALUES
('99120', 'PRODUCT C', 100, 0.8, 80);
INSERT INTO test (code, description, quant, price, total) VALUES
('99120', 'PRODUCT C', 200, 0.8, 160);
INSERT INTO test (code, description, quant, price, total) VALUES
('99120', 'PRODUCT C', 100, 0.9, 90);
I need an subtotal for all the products with the same group and that the
query be ordered by the bigger subtotal.
For example, I need an output like this:
Ex.
code | description | quant | price | total | subtotal
---+-+---+---+---+--
99120 | PRODUCT C |10 | 0.8 | 8 | 8
99120 | PRODUCT C | 100 | 0.8 |80 | 88
99120 | PRODUCT C | 200 | 0.8 | 160| 168
99120 | PRODUCT C | 100 | 0.9 |90 | 667
92110 | PRODUCT A |10 | 1 |10 | 10
92110 | PRODUCT A | 5 | 0.9 | 9 | 19
92110 | PRODUCT A | 100 | 0.9 |90 | 109
92110 | PRODUCT A |10 | 1.1 |11 | 120
92190 | PRODUCT b |10 | 1.1 |11 | 11
92190 | PRODUCT b |10 | 1.1 |11 | 22
92190 | PRODUCT b |10 | 1.1 |11 | 33
92190 | PRODUCT b |20 | 0.8 | 8 | 41
The subtotal column must sum all the products with the same code and put
the result in order of the bigger sultotals.
Only make a function that sum the last value + the subtotal it's not
hard BUT how I can make the subtotal restart when the code changes and
how I will order the result by the bigger subtotal code groups?
Thanks!
Rodrigo Carvalhaes
--
Esta mensagem foi verificada pelo sistema de antivírus e
acredita-se estar livre de perigo.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] SELECT with sum on groups ORDERING by the subtotals
I think it will be better to add one more column for subtotal and
write an "on before insert" trigger to update the subtotal with sum of
total.
with regards,
S.Gnanavel
> -Original Message-
> From: [EMAIL PROTECTED]
> Sent: Thu, 16 Jun 2005 00:56:42 -0300
> To: pgsql-sql@postgresql.org
> Subject: [SQL] SELECT with sum on groups ORDERING by the subtotals
>
> Hi Guys!
>
> I need to make a complex query. I am thinking to use plpgsql BUT I am
> confused how I can solve this.
>
> What I have:
> CREATE TABLE test
> (
> code varchar(15),
> description varchar(60),
> group varchar(10),
> quant float8,
> price float8,
> total float8
> )
> WITHOUT OIDS;
>
> INSERT INTO test (code, description, quant, price, total) VALUES
> ('92110', 'PRODUCT A', 10, 1, 10);
> INSERT INTO test (code, description, quant, price, total) VALUES
> ('92110', 'PRODUCT A', 5, 0.90, 9);
> INSERT INTO test (code, description, quant, price, total) VALUES
> ('92110', 'PRODUCT A', 100, 0.9, 90);
> INSERT INTO test (code, description, quant, price, total) VALUES
> ('92110', 'PRODUCT A', 10, 1.1, 11);
> INSERT INTO test (code, description, quant, price, total) VALUES
> ('92190', 'PRODUCT b', 10, 1.1, 11);
> INSERT INTO test (code, description, quant, price, total) VALUES
> ('92190', 'PRODUCT b', 10, 1.1, 11);
> INSERT INTO test (code, description, quant, price, total) VALUES
> ('92190', 'PRODUCT b', 10, 1.1, 11);
> INSERT INTO test (code, description, quant, price, total) VALUES
> ('92190', 'PRODUCT b', 20, 0.8, 8);
> INSERT INTO test (code, description, quant, price, total) VALUES
> ('99120', 'PRODUCT C', 10, 0.8, 8);
> INSERT INTO test (code, description, quant, price, total) VALUES
> ('99120', 'PRODUCT C', 100, 0.8, 80);
> INSERT INTO test (code, description, quant, price, total) VALUES
> ('99120', 'PRODUCT C', 200, 0.8, 160);
> INSERT INTO test (code, description, quant, price, total) VALUES
> ('99120', 'PRODUCT C', 100, 0.9, 90);
>
>
> I need an subtotal for all the products with the same group and that the
> query be ordered by the bigger subtotal.
>
> For example, I need an output like this:
> Ex.
>
>
> code | description | quant | price | total | subtotal
> ---+-+---+---+---+--
> 99120 | PRODUCT C |10 | 0.8 | 8 | 8
> 99120 | PRODUCT C | 100 | 0.8 |80 | 88
> 99120 | PRODUCT C | 200 | 0.8 | 160| 168
> 99120 | PRODUCT C | 100 | 0.9 |90 | 667
> 92110 | PRODUCT A |10 | 1 |10 | 10
> 92110 | PRODUCT A | 5 | 0.9 | 9 | 19
> 92110 | PRODUCT A | 100 | 0.9 |90 | 109
> 92110 | PRODUCT A |10 | 1.1 |11 | 120
> 92190 | PRODUCT b |10 | 1.1 |11 | 11
> 92190 | PRODUCT b |10 | 1.1 |11 | 22
> 92190 | PRODUCT b |10 | 1.1 |11 | 33
> 92190 | PRODUCT b |20 | 0.8 | 8 | 41
>
> The subtotal column must sum all the products with the same code and put
> the result in order of the bigger sultotals.
>
> Only make a function that sum the last value + the subtotal it's not
> hard BUT how I can make the subtotal restart when the code changes and
> how I will order the result by the bigger subtotal code groups?
>
> Thanks!
>
> Rodrigo Carvalhaes
>
> --
> Esta mensagem foi verificada pelo sistema de antivírus e
> acredita-se estar livre de perigo.
>
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 8: explain analyze is your friend
