[SQL] use of a composite type in CREATE TABLE?
Hi, does anyone know how it is posible to set a composite type as the data type of a column when creating a new table? CREATE TABLE tbl1 ( name VARCHAR(60) NOT NULL, address comp_type_address ); The exmaple above does not work. Any help is appreciated. -- Regards, Hannes ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Optimal query suggestion needed
Hi all, I'm trying to create a view for a client, but I'm failing miserably so I thought I'ld ask for some help. The initial analysis was quite complicated, and that specific need never came up until recently, and unfortunately, changing the tables is probably not an option :( Given the tables : create table t0 ( code integer, address varchar, mun integer ) create table t1 ( code integer, pname varchar ); create table t2 ( code integer, t0_fk integer, t1_fk integer, avail bool ); I want to create a view that will have: from table t0 the elements "code", "address" and "mun" from table t1 the elements "code" and "pname" from table t2 the total number of elements, and the total number of elements where avail = true, for every value t0_fk (foreign key to t0) and t1_fk (foreigh key to t1). After several attempts and changes as the requirements changed, I finaly came up with that : select t0.code, t0.address, t0.mun, t1.code as t1code, t1.pname count(t2.code) as t2total, (select count(t2.code) as t2avail from t2 where t2.avail = true and t2.t0_fk=t0.code and t2.t1_fk = t1.code) as t2avail from t0, t1, t2 where t2.t0_fk = t0.code and t2.t1_fk=t1.code group by t0.code, t0.address, t0.mun, t1.code, t1.pname but it gives wrong results... I tried several other queries that are extremely slow. Any help will be greatly appreciated. Thanks in advance ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Optimal query suggestion needed
Interzone wrote: Hi all, I'm trying to create a view for a client, but I'm failing miserably so I thought I'ld ask for some help. The initial analysis was quite complicated, and that specific need never came up until recently, and unfortunately, changing the tables is probably not an option :( Given the tables : create table t0 ( code integer, address varchar, mun integer ) create table t1 ( code integer, pname varchar ); create table t2 ( code integer, t0_fk integer, t1_fk integer, avail bool ); I want to create a view that will have: from table t0 the elements "code", "address" and "mun" from table t1 the elements "code" and "pname" from table t2 the total number of elements, and the total number of elements where avail = true, for every value t0_fk (foreign key to t0) and t1_fk (foreigh key to t1). So there's no connection between column "code" in any of the tables? I'm confused as to the correlation between t0 and t1. I'm also not clear what t2.code is supposed to be. Could you give a short (5 rows each) example of the contents of the tables and the expected results? -- Richard Huxton Archonet Ltd ---(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] use of a composite type in CREATE TABLE?
Hannes Korte <[EMAIL PROTECTED]> writes: > does anyone know how it is posible to set a composite type as the data > type of a column when creating a new table? Use 7.5 ;-). It's not supported in any existing release, but it does work in CVS tip ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Optimal query suggestion needed
On Thu, Jun 17, 2004 at 14:46:08 +, Interzone <[EMAIL PROTECTED]> wrote: > I want to create a view that will have: > from table t0 the elements "code", "address" and "mun" > from table t1 the elements "code" and "pname" > from table t2 the total number of elements, and the total number of > elements where avail = true, for every value t0_fk (foreign key to t0) > and t1_fk (foreigh key to t1). > > After several attempts and changes as the requirements changed, I finaly > came up with that : > > select t0.code, t0.address, t0.mun, t1.code as t1code, t1.pname > count(t2.code) as t2total, (select count(t2.code) as t2avail from t2 > where t2.avail = true and t2.t0_fk=t0.code and t2.t1_fk = t1.code) as > t2avail from t0, t1, t2 where t2.t0_fk = t0.code and t2.t1_fk=t1.code > group by t0.code, t0.address, t0.mun, t1.code, t1.pname This approach is actually pretty close. I think you just didn't pick a good way to count the avail = true rows. I think you can replace the above with: select t0.code, t0.address, t0.mun, t1.code as t1code, t1.pname count(t2.code) as t2total, count(case when t2.avail then 1 else NULL) as t2avail from t0, t1, t2 where t2.t0_fk = t0.code and t2.t1_fk=t1.code group by t0.code, t0.address, t0.mun, t1.code, t1.pname ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] [JDBC] Prepare Statement
Kirs, I re-compile with setUseServerPrepare(true), it works fine, thanks. However, reading from my log file, what I saw is that five same SELECTs with different argument, so I am wondering that the PrepareStatement really save time than individualy execute five SELECTs ??? If I use one "parepare sql command" and five "execute sql commands", the log file shown what I typed, so I think it really used server side prepared object! Any comment? Thanks. Jie Liang -Original Message- From: Kris Jurka [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 16, 2004 9:30 PM To: Jie Liang Cc: Tom Lane; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [JDBC] Prepare Statement On Wed, 16 Jun 2004, Jie Liang wrote: > Kris, > Thank you for your valuable response, I used the code you list > following: > > [7.5 code example] > > Then, the compiler complaint: > ServerSidePreparedStatement.java:20: cannot resolve symbol symbol : > method setPrepareThreshold (int) > location: interface org.postgresql.PGStatement > pgstmt.setPrepareThreshold(3); I downloaded > pg74.213.jdbc2.jar and pg74.213.jdbc2ee.jar at This example is from the 7.5 documentation and requires a pgdev.302.jdbcX.jar file. I mentioned this cvs example because this functionality is undocumented in the released version. In the 7.4 version the enabling of server side statements is only possible via a boolean flag at the statement level, namely PGStatement.setUseServerPrepare(true); Kris Jurka ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Optimal query suggestion needed
Richard Huxton wrote: Interzone wrote: Hi all, I'm trying to create a view for a client, but I'm failing miserably so I thought I'ld ask for some help. The initial analysis was quite complicated, and that specific need never came up until recently, and unfortunately, changing the tables is probably not an option :( Given the tables : create table t0 ( code integer, address varchar, mun integer ) create table t1 ( code integer, pname varchar ); create table t2 ( code integer, t0_fk integer, t1_fk integer, avail bool ); I want to create a view that will have: from table t0 the elements "code", "address" and "mun" from table t1 the elements "code" and "pname" from table t2 the total number of elements, and the total number of elements where avail = true, for every value t0_fk (foreign key to t0) and t1_fk (foreigh key to t1). So there's no connection between column "code" in any of the tables? I'm confused as to the correlation between t0 and t1. I'm also not clear what t2.code is supposed to be. Sorry, I should be more verbose. On table t2, the column t0_fk is a foreign key to table t0, and t1_fk is a foreign key to table t1. t1 and t0 are totaly independent from each other. Could you give a short (5 rows each) example of the contents of the tables and the expected results? Well, I could try: insert into t0 values (1,'area 1',1); insert into t0 values (2,'area 2',1); insert into t0 values (3,'area 3',1); insert into t1 values (1,'ptype : 1'); insert into t1 values (2,'ptype : 2'); insert into t1 values (3,'ptype : 3'); insert into t2 values (1,1,1,TRUE); insert into t2 values (2,1,2,TRUE); insert into t2 values (3,1,2,FALSE); insert into t2 values (4,2,3,TRUE); insert into t2 values (5,2,1,FALSE); insert into t2 values (6,2,1,TRUE); insert into t2 values (7,2,3,FALSE); insert into t2 values (8,2,3,TRUE); I want for every pair of t0.code / t1.code to be able to identify the total entries in t2, and the "available" entries in t2 (among other things from tables t0 and t1, which are easy to get, so I let them out). The result should be that (columns intentionally missing): t0.code---t1.code---count(t2)---avail(t2) ---1-11---1-- (t2 row 1) ---1-22---1-- (t2 rows 2,3) ---2-12---1-- (t2 rows 5,6) ---2-33---2-- (t2 rows 4,7,8) I hope it's clear now (and that I haven't done any mistake that will confuse things worse). Thanks in advance ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Optimal query suggestion needed
Bruno Wolff III wrote: On Thu, Jun 17, 2004 at 14:46:08 +, Interzone <[EMAIL PROTECTED]> wrote: I want to create a view that will have: from table t0 the elements "code", "address" and "mun" from table t1 the elements "code" and "pname" from table t2 the total number of elements, and the total number of elements where avail = true, for every value t0_fk (foreign key to t0) and t1_fk (foreigh key to t1). After several attempts and changes as the requirements changed, I finaly came up with that : select t0.code, t0.address, t0.mun, t1.code as t1code, t1.pname count(t2.code) as t2total, (select count(t2.code) as t2avail from t2 where t2.avail = true and t2.t0_fk=t0.code and t2.t1_fk = t1.code) as t2avail from t0, t1, t2 where t2.t0_fk = t0.code and t2.t1_fk=t1.code group by t0.code, t0.address, t0.mun, t1.code, t1.pname This approach is actually pretty close. I think you just didn't pick a good way to count the avail = true rows. I think you can replace the above with: select t0.code, t0.address, t0.mun, t1.code as t1code, t1.pname count(t2.code) as t2total, count(case when t2.avail then 1 else NULL) as t2avail from t0, t1, t2 where t2.t0_fk = t0.code and t2.t1_fk=t1.code group by t0.code, t0.address, t0.mun, t1.code, t1.pname Thanks the query you sent failed on v. 7.4, so I added an "end" to the case statement. I selected from the tables and the results seem to be correct. I rewrite it for archiving reasons: select t0.code, t0.address, t0.mun, t1.code as t1code, t1.pname count(t2.code) as t2total, count(case when t2.avail then 1 else NULL end) as t2avail from t0, t1, t2 where t2.t0_fk = t0.code and t2.t1_fk=t1.code group by t0.code, t0.address, t0.mun, t1.code, t1.pname Once again thank you. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Optimal query suggestion needed
On Thu, Jun 17, 2004 at 22:22:34 +0300, InterZone <[EMAIL PROTECTED]> wrote: > > Thanks > the query you sent failed on v. 7.4, so I added an "end" to the case > statement. I selected from the tables and the results seem to be correct. That was an unintentional typo. > > I rewrite it for archiving reasons: > > select t0.code, t0.address, t0.mun, t1.code as t1code, t1.pname > count(t2.code) as t2total, count(case when t2.avail then 1 else NULL > end) as t2avail from t0, t1, t2 where t2.t0_fk = t0.code and > t2.t1_fk=t1.code group by t0.code, t0.address, t0.mun, t1.code, t1.pname > > > Once again thank you. Your welcome. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [JDBC] Prepare Statement
Kris,
You are right, I modified that piece of code a little bit,
CallableStatement stmt = conn.prepareCall("{?=call chr(?)}");
Then my log file were:
Select * from chr(65) as result;
Select * from chr(66) as result;
..
However, if I use:
PrepareStatement stmt = conn.prepareStatement("SELECT chr(?)");
Then my log file are same as yours.i.e. it use PREPARE and EXECUTE.
So, I am getting confusion.
I think CallableStatement is extended from PrepareStatement, it should
have same behaviou.
Any comment?
Thanks.
Jie Liang
-Original Message-
From: Kris Jurka [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 17, 2004 11:59 AM
To: Jie Liang
Cc: Tom Lane; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: [JDBC] Prepare Statement
On Thu, 17 Jun 2004, Jie Liang wrote:
> Kirs,
>
> I re-compile with setUseServerPrepare(true), it works fine, thanks.
> However, reading from my log file, what I saw is that five same
> SELECTs with different argument, so I am wondering that the
> PrepareStatement really save time than individualy execute five
> SELECTs ???
>
This is what I see in the log file:
2004-06-17 11:55:35 [23254] LOG: statement: PREPARE
JDBC_STATEMENT_1(integer) AS SELECT $1 ; EXECUTE JDBC_STATEMENT_1(1)
2004-06-17 11:55:35 [23254] LOG: statement: EXECUTE JDBC_STATEMENT_1(2)
2004-06-17 11:55:35 [23254] LOG: statement: EXECUTE JDBC_STATEMENT_1(3)
2004-06-17 11:55:35 [23254] LOG: statement: EXECUTE JDBC_STATEMENT_1(4)
2004-06-17 11:55:35 [23254] LOG: statement: EXECUTE JDBC_STATEMENT_1(5)
2004-06-17 11:55:35 [23254] LOG: statement: DEALLOCATE JDBC_STATEMENT_1
I don't know why this would be different for you. What exact version of
the server and driver are you using?
Kris Jurka
---(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] [JDBC] Prepare Statement
Hmm, intersting.
I am using jdk 1.3.1, and pg74.213.jdbc2.jar driver, I hope this bug
could be fixed in later version.
Thanks.
Jie Liang
-Original Message-
From: Kris Jurka [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 17, 2004 3:26 PM
To: Jie Liang
Cc: Tom Lane; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: [JDBC] Prepare Statement
On Thu, 17 Jun 2004, Jie Liang wrote:
> Kris,
> You are right, I modified that piece of code a little bit,
> CallableStatement stmt = conn.prepareCall("{?=call chr(?)}"); Then my
> log file were: Select * from chr(65) as result;
> Select * from chr(66) as result;
> ..
> However, if I use:
> PrepareStatement stmt = conn.prepareStatement("SELECT chr(?)");
> Then my log file are same as yours.i.e. it use PREPARE and EXECUTE.
>
> So, I am getting confusion.
> I think CallableStatement is extended from PrepareStatement, it should
> have same behaviou.
>
What's happening here is that you can only use prepared statements for
certain operations. You can't for example prepare a CREATE TABLE
statement. The driver examines the query to see if it is valid for
preparing and I believe the problem here is that with a callable
statement
it is examinging the query with "call" before it is transformed to a
SELECT, so it doesn't recognize it as a preparable. This looks like a
bug
to me.
Kris Jurka
---(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] [JDBC] Prepare Statement
Kris,
I have another question, I saw some discussion regarding
PreparedStatement work with array argument, I get a error when I try to
play with it.
E.g.
I have myfunction(int[]),
So,
PrepareStatement st = conn.prepareStatment("SELECT myfunction(?)");
String arr="{1,2,3}";
St.setString(1,arr};
Result rs = st.executeQuery();
Then it will complaint when it run:
Myfuntion(text) does not exist!
Did I miss something??
Thanks.
Jie Liang
-Original Message-
From: Kris Jurka [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 17, 2004 3:26 PM
To: Jie Liang
Cc: Tom Lane; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: [JDBC] Prepare Statement
On Thu, 17 Jun 2004, Jie Liang wrote:
> Kris,
> You are right, I modified that piece of code a little bit,
> CallableStatement stmt = conn.prepareCall("{?=call chr(?)}"); Then my
> log file were: Select * from chr(65) as result;
> Select * from chr(66) as result;
> ..
> However, if I use:
> PrepareStatement stmt = conn.prepareStatement("SELECT chr(?)");
> Then my log file are same as yours.i.e. it use PREPARE and EXECUTE.
>
> So, I am getting confusion.
> I think CallableStatement is extended from PrepareStatement, it should
> have same behaviou.
>
What's happening here is that you can only use prepared statements for
certain operations. You can't for example prepare a CREATE TABLE
statement. The driver examines the query to see if it is valid for
preparing and I believe the problem here is that with a callable
statement
it is examinging the query with "call" before it is transformed to a
SELECT, so it doesn't recognize it as a preparable. This looks like a
bug
to me.
Kris Jurka
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
