[SQL]maximum parameters limit to function & manipulating array in plpgsql

Hi,   I have 2 major problems bothering me and probably u are the best person to help me out :-   1) Well I read one of ur solutions to sending more tham 16 parameters to a function in plpgsql. I have changed the value of FUNC_MAX_ARGS (a/k/a INDEX_MAX_KEYS) in \usr\local\plpgsql\include\c

Re: [SQL] EXISTS Keyword

> Hello! > > I vaguely remember that someone said somewhere that > EXISTS runs faster than IN: > > SELECT * FROM table1 where field1 EXISTS (SELECT > field4 FROM table2) Move field1 into the subquery and join it to table2. See the FAQ for an example. -- Bruce Momjian

Re: [SQL] SubQuery

On Thu, 4 Oct 2001 [EMAIL PROTECTED] wrote: > What mistake have I made? > > database1=# UPDATE mytable SET > NextNumber=NextNumber+1 > database1-# WHERE id='ID1' AND EffectiveDate= > database1-# (SELECT MAX(s2.EffectiveDate) FROM > mytable s2 > database1(# WHERE s2.id=id AND > s2.EffectiveDate<

[SQL] EXISTS Keyword

Hello! I vaguely remember that someone said somewhere that EXISTS runs faster than IN: SELECT * FROM table1 where field1 EXISTS (SELECT field4 FROM table2) However, all I got from version 7.1.3 is: ERROR: parser: parse error at or near "exists" While below works: SELECT * FROM table1 where

[SQL] SubQuery

Hi! I am using MyTable to serve the "next number" functionality like PGSQL embeded "sequence" offers. The only difference of the two is MyTable has 2 more fields - "ID" and "Starting Effective Date". The UPDATE SQL fails when I am trying to update record ID1 | 2001-08-01| 11 to

Re: [SQL] to_date/to timestamp going to BC

Thanks Tom that worked great. I guess I should have not skipped the FM prefix section of the date conversion doc. drw_dev-> to_timestamp(dt, 'FMMonth dd, ') from test_date; dt | to_date | to_timestamp ++ March 11,

Re: [SQL] to_date/to timestamp going to BC

[EMAIL PROTECTED] writes: > Can someone tell me if this is a bug with the date functions or am I using > them incorrectly? I get the right thing when I use the right format: regression=# select dt, to_timestamp(dt, 'FMMonth dd, ') from test_date; dt | to_timestamp -

[SQL] to_date/to timestamp going to BC

Can someone tell me if this is a bug with the date functions or am I using them incorrectly? If anyone has a workaround for this I could use some help this data conversion. dev=> create table test_date (dt varchar(100)); CREATE dev=> insert into test_date values ('March 11, 1997'); INSERT 706020

Re: [SQL] 7.0.3 and 7.1.3 different results?

Stephan, > Actually it seems to me that one NULL row is correct... > > 7.9 > 1) Case: > a) If T is not a grouped table, then You are correct according to the SQL spec. However, depending on what interface I use for the database, I can get an empty recordset rather than a single NULL row.

Re: [SQL] 7.0.3 and 7.1.3 different results?

On Wed, 3 Oct 2001, Josh Berkus wrote: > Joel, > > > A query such as "select sum(pages) from job_documents where > > delivery_type='print'" returned 0 in version 7.0.3 if there were no > > rows > > matching the query. In 7.1.3 the result is NULL if no rows match the > > query. Why the change? Wh

Re: [SQL] Question about indexing!

On Wed, 3 Oct 2001, [euc-kr] Jeong Jaeick, Á¤ÀçÀÍ wrote: > pgsql_bbs table has about 15,000 rows! > And almost of them are satify (topic='qna' and deleted<2) condition. Ah, so it's getting it wrong. It *shouldn't* be using that index. :( [Index scans over most of the table is slower than the s

Re: [SQL] 7.0.3 and 7.1.3 different results?

Joel, > A query such as "select sum(pages) from job_documents where > delivery_type='print'" returned 0 in version 7.0.3 if there were no > rows > matching the query. In 7.1.3 the result is NULL if no rows match the > query. Why the change? Which result is "correct" according to the > SQL > stand

Re: [SQL] ORDER BY case insensitive?

In article , "Bob Swerdlow" <[EMAIL PROTECTED]> wrote: > How do I get the rows sorted in a case insensitive way? > SELECT * FROM MyTable ORDER BY Name; Try SELECT * FROM MyTable ORDER BY upper(Name); (or 'lower(Name)'). -- Jeff Boes

[SQL]

Please can someone help I tried to subccribe to pgsl-admin but I have been unable. I just upgraded to Mac osx 10.1. When I try to configure --with-tcl I get an error that 'wish' can not be found. Where do I get this? (this is not so important but I would like to use pgaccess) 2. below

Re: [SQL] Question about indexing!

pgsql_bbs table has about 15,000 rows! And almost of them are satify (topic='qna' and deleted<2) condition. This explain result have a large cost. I want to low this query cost. Thanks for your concern :-) >> select * from pgsql_bbs where topic = 'qna' and deleted < 2 >>order by gid des