Re: [SQL] Fetching a single column from a record returning function
Yes it worked. Thanks - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Kumar" <[EMAIL PROTECTED]> Cc: "psql" <[EMAIL PROTECTED]> Sent: Tuesday, January 20, 2004 9:28 PM Subject: Re: [SQL] Fetching a single column from a record returning function > "Kumar" <[EMAIL PROTECTED]> writes: > > select * from fn_email(1) > > as (email_folder_id int4,email_folder_name varchar,descrip varchar,msgcount > > int8,unreadcount int8,size int8); > > > Is it possible to fetch only one column (the 'msgcount') from the function.= > > Because I am interested in SUM(msgcount). Please shed some light. > > select sum(msgcount) from fn_email(1) > as (email_folder_id int4,email_folder_name varchar,descrip varchar,msgcount > int8,unreadcount int8,size int8); > > regards, tom lane > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] cygwin warnings in the log file
I get these LOG: shmdt(0xf8) failed: Invalid argument anyone know what's up with that? ---(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] time series data
Hi,
i want to create time table & the structure as below :
Column
Type
Modifiers
time_key
yr_id
month_id
month_desc
day_id
integer
integer
integer
text
integer
not null default nextval('time_seq'::text)
Example of data in time table:
Time_key
yr_id
month_id
month_desc
day_id
1999
1
Jan
1
1999
1
Jan
2
2000
1
Jan
1
time_key data will keep increment reading from sequence. i'm going to have a data from yr_id =1994 --> 2009 , month_id =1 -->12
and day_id =1 -->31 for each month. So is there any solution (using function, or else) that can give me the above data
in Postgresql??.
thanks in advance.
Re: [SQL] time series data
Dnia 2004-01-21 09:53, Użytkownik [EMAIL PROTECTED] napisał:
time_key integer not null default nextval('time_seq'::text)
> Example of data in time table:
> Time_keyyr_idmonth_idmonth_desc day_id
> 1999 1 Jan 1
> 1999 1 Jan 2
> 2000 1 Jan 1
Use this integer sequence and interval datatype to get date result:
your_date='1994-01-01'::date+'1 day'::integer * time_key
Now you can do whatever you want with this date - look at Postgresql
documentation "6.8. Date/Time Functions and Operators" -> "extract"
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] time series data
Dnia 2004-01-21 10:37, Użytkownik Tomasz Myrta napisał: Use this integer sequence and interval datatype to get date result: your_date='1994-01-01'::date+'1 day'::integer * time_key ^^^ Sorry, use interval here. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] array_lower /array_prepend doubt
Greetings!
can anyone explain why
SELECT array_lower(array_prepend(0, ARRAY[1,2,3]), 1);
returns 0 not 1
because
tradein_clients=# SELECT array_prepend(0, ARRAY[1,2,3]);
+---+
| array_prepend |
+---+
| {0,1,2,3} |
+---+
(1 row)
and
tradein_clients=# SELECT array_lower( ARRAY[0,1,2,3],1 );
+-+
| array_lower |
+-+
| 1 |
+-+
(1 row)
Time: 402.614 ms
Regds
Mallah.
---(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] array_lower /array_prepend doubt
On Wed, 21 Jan 2004, Rajesh Kumar Mallah wrote:
> can anyone explain why
> SELECT array_lower(array_prepend(0, ARRAY[1,2,3]), 1);
> returns 0 not 1
>
> because
>
> tradein_clients=# SELECT array_prepend(0, ARRAY[1,2,3]);
> +---+
> | array_prepend |
> +---+
> | {0,1,2,3} |
> +---+
> (1 row)
It looks like array_prepend is basically inserting the new value at the
index before the start of the array, so 0 is the lower bound. This means
that array_prepend(0,ARRAY[1,2,3]) is not the same array as
ARRAY[0,1,2,3]. If you stick both in a table, and select col[1], in one
you appear to get back 1, in the other 0. However, I think there is a bug
here somewhere, because the former array does not appear to dump/restore
as the same value (both seem to dump as (0,1,2,3})
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] array_lower /array_prepend doubt
Rajesh Kumar Mallah <[EMAIL PROTECTED]> writes: > can anyone explain why > SELECT array_lower(array_prepend(0, ARRAY[1,2,3]), 1); > returns 0 not 1 Because array_prepend keeps the subscripts of the existing array elements the same. This was discussed during development of the code, but I don't see anything in the documentation that mentions it. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] array_lower /array_prepend doubt
Tom Lane wrote: Rajesh Kumar Mallah <[EMAIL PROTECTED]> writes: can anyone explain why SELECT array_lower(array_prepend(0, ARRAY[1,2,3]), 1); returns 0 not 1 Because array_prepend keeps the subscripts of the existing array elements the same. This was discussed during development of the code, but I don't see anything in the documentation that mentions it. It could perhaps be added to the "Functions and Operators" page for arrays, but it is mentioned here: http://www.postgresql.org/docs/current/static/arrays.html#AEN5183 "When a single element is pushed on to the beginning of a one-dimensional array, the result is an array with a lower bound subscript equal to the right-hand operand's lower bound subscript, minus one. When a single element is pushed on to the end of a one-dimensional array, the result is an array retaining the lower bound of the left-hand operand. For example: SELECT array_dims(1 || ARRAY[2,3]); array_dims [0:2] (1 row) SELECT array_dims(ARRAY[1,2] || 3); array_dims [1:3] (1 row) " Joe ---(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] array_lower /array_prepend doubt
Joe Conway <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Because array_prepend keeps the subscripts of the existing array >> elements the same. This was discussed during development of the >> code, but I don't see anything in the documentation that mentions it. > It could perhaps be added to the "Functions and Operators" page for > arrays, but it is mentioned here: > http://www.postgresql.org/docs/current/static/arrays.html#AEN5183 Ah. I was looking at the mentions of array_prepend() ... but of course the manual also says that you should use the || operator instead ... so I guess this mention is sufficient. The other point about pg_dump failing to correctly restore arrays with nondefault lower bounds is a good one, though. We need to think about how to fix that. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] array_lower /array_prepend doubt
Joe Conway <[EMAIL PROTECTED]> writes: > I'll put some thought into it, but note that it is hardly a new issue -- Of course; I suppose this bug goes back to Berkeley days. We just hadn't recognized it before (or at least I hadn't). regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] array_lower /array_prepend doubt
Tom Lane wrote:
The other point about pg_dump failing to correctly restore arrays with
nondefault lower bounds is a good one, though. We need to think about
how to fix that.
I'll put some thought into it, but note that it is hardly a new issue --
it's been possible to create an array with < 1 lower bound since well
before 7.4:
regression=# select version();
version
---
PostgreSQL 7.3.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2
20030222 (Red Hat Linux 3.2.2-5)
(1 row)
regression=# create table a(f1 int, f2 int[]);
CREATE TABLE
regression=# insert into a values (1,'{1,2}');
INSERT 565511 1
regression=# update a set f2[0] = 0 where f1 = 1;
UPDATE 1
regression=# select array_dims(f2) from a;
array_dims
[0:2]
(1 row)
Joe
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] array_lower /array_prepend doubt
Tom Lane wrote: Of course; I suppose this bug goes back to Berkeley days. We just hadn't recognized it before (or at least I hadn't). Neither had I. But the changes in 7.4 probably make it more likely people will bump into this as a problem. Without looking to confirm, I believe SQL99 defines an array as always having a lower bound of 1, making our behavior an extension to the standard. We may need another extension to the array literal syntax in order to deal with this. I'll report back after I've had some time to study it. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] array_lower /array_prepend doubt
Joe Conway <[EMAIL PROTECTED]> writes: > We may need another extension to the array literal syntax in > order to deal with this. I'll report back after I've had some time to > study it. There already is support in array_in for specification of the array dimensions (though it may be suffering bit rot for lack of use/testing). I think the main thing needed is some thought about when array_out should print dimensions; we don't want it doing so all the time, for both clutter and backwards compatibility reasons. Maybe "whenever any lower bound is not 1" would do; or maybe we want to invent a GUC switch to control its behavior. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Batch Updates problem
Hi, my first question is, is pg74jdbc3.2.jar the latest driver for postgresql? If so, does it support batch updates? I ask this because I am using Hibernate, or trying to should I say!, to do EJB stuff to postgresql and when I try to commit() data to a postgresql table i get the following error: 'Could not execute JDBC batch update' Uzo
[SQL] Batch Updates issue
Hi, my first question is, is pg74jdbc3.2.jar the latest driver for postgresql? If so, does it support batch updates? I ask this because I am using Hibernate, or trying to should I say!, to do EJB stuff to postgresql and when I try to commit() data to a postgresql table i get the following error: 'Could not execute JDBC batch update' Uzo
