Re: [GENERAL] OR-clause support for indexes

[David Rowley]

On 8 October 2017 at 21:30, Andreas Joseph Krogh  wrote:
> There was a while ago a proposed patch for adding $subject;
> https://commitfest.postgresql.org/8/454/

That looks like it's been abandoned, but perhaps it's worth asking the
author directly?

> Is this being worked on? Any progress in btree-support?

Not exactly what you're asking, but perhaps
https://commitfest.postgresql.org/14/1001/ could improve your
workload, or perhaps you could just manually rewrite the query.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] OR-clause support for indexes

[Andreas Joseph Krogh]

Hi.
 
There was a while ago a proposed patch for adding 
$subject; https://commitfest.postgresql.org/8/454/
 
Is this being worked on? Any progress in btree-support?

 --
 Andreas Joseph Krogh

[GENERAL] OR-clause support for indexes

[Andreas Joseph Krogh]

Hi.
 
Back in 9.6 dev-cycle Teodor (from PostgresPro) posted a patch providing 
indexed OR-clauses: https://commitfest.postgresql.org/8/454/
Sadly it didn't make it to 9.6, and wasn't re-submitted for the 10 dev-cycle.
 
@Teodor; Do you have plans to submit a patch for PG-11?
And, have you made any progress making it work for btree?
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 

Re: [GENERAL] LIMIT clause slowing down query in some cases, accelerating in others

[Vik Fearing]

On 04/26/2017 08:11 PM, Klaus P. Pieper wrote:
>
> Running PostgreSQL 9.6 on a Windows Server.
>
> Table “t” is kind of a materialized view with > 100 columns and 2.24
> Mio rows. Queries are generated by an ORM framework – fairly difficult
> to modify.
>
> Vacuum analyze was carried out – no impact.
>
>  
>
> The framework generates queries like this:
>
>  
>
> select N0."uorderid" from "t" N0
>
> where (N0."szzip" like E'33%')
>
> order by N0."szzip" asc nulls last LIMIT 128 OFFSET 0
>

The planner has to choose whether to use an index for filtering or an
index for sorting.  If you're always doing prefix searches like in your
two examples, then you want an index which can do both.

CREATE INDEX ON t (szzip text_pattern_ops, uorderid);

I invite you to read the documentation about text_pattern_ops at
https://www.postgresql.org/docs/current/static/indexes-opclass.html

-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

[GENERAL] LIMIT clause slowing down query in some cases, accelerating in others

[Klaus P. Pieper]

Running PostgreSQL 9.6 on a Windows Server. 

Table "t" is kind of a materialized view with > 100 columns and 2.24 Mio
rows. Queries are generated by an ORM framework - fairly difficult to
modify. 

Vacuum analyze was carried out - no impact. 

 

The framework generates queries like this: 

 

select N0."uorderid" from "t" N0

where (N0."szzip" like E'33%')

order by N0."szzip" asc nulls last LIMIT 128 OFFSET 0

 

EXPLAIN ANALYZE: 

Limit  (cost=0.43..547.08 rows=128 width=21) (actual time=402.247..402.386
rows=128 loops=1)

  ->  Index Only Scan using t_szzip_uorderid_idx1 on t n0
(cost=0.43..83880.65 rows=19641 width=21) (actual time=402.244..402.344
rows=128 loops=1)

Filter: ((szzip)::text ~~ '33%'::text)

Rows Removed by Filter: 699108

Heap Fetches: 0

Planning time: 0.687 ms

Execution time: 402.443 ms

 

EXPLAIN ANALYZE without LIMIT and OFFSET:

Sort  (cost=66503.14..66552.24 rows=19641 width=21) (actual
time=151.598..156.155 rows=24189 loops=1)

  Sort Key: szzip

  Sort Method: quicksort  Memory: 2658kB

  ->  Bitmap Heap Scan on t n0  (cost=200.22..65102.58 rows=19641 width=21)
(actual time=21.267..90.272 rows=24189 loops=1)

Recheck Cond: ((szzip)::text ~~ '33%'::text)

Rows Removed by Index Recheck: 26

Heap Blocks: exact=23224

->  Bitmap Index Scan on t_szzip_idx_gin  (cost=0.00..195.31
rows=19641 width=0) (actual time=14.235..14.235 rows=24215 loops=1)

  Index Cond: ((szzip)::text ~~ '33%'::text)

Planning time: 0.669 ms

Execution time: 161.860 ms

 

With LIMIT, a btree index is used whereas without the LIMIT clause, a GIN
index is used. 

 

Unfortunately, modifying the LIKE query parameter from E'33%' to E'10%'
gives completely different results:

select N0."uorderid" from "t" N0

where (N0."szzip" like E'10%')

order by N0."szzip" asc nulls last LIMIT 128 OFFSET 0

 

EXPLAIN ANALYZE: 

Limit  (cost=0.43..195.08 rows=128 width=21) (actual time=88.699..88.839
rows=128 loops=1)

  ->  Index Only Scan using t_szzip_uorderid_idx1 on t n0
(cost=0.43..83880.65 rows=55158 width=21) (actual time=88.696..88.793
rows=128 loops=1)

Filter: ((szzip)::text ~~ '10%'::text)

Rows Removed by Filter: 142107

Heap Fetches: 0

Planning time: 0.669 ms

Execution time: 88.900 ms

 

EXPLAIN ANALYZE without LIMIT and OFFSET:

Index Only Scan using t_szzip_uorderid_idx1 on t n0  (cost=0.43..83880.65
rows=55158 width=21) (actual time=88.483..1263.396 rows=53872 loops=1)

  Filter: ((szzip)::text ~~ '10%'::text)

  Rows Removed by Filter: 2192769

  Heap Fetches: 0

Planning time: 0.671 ms

Execution time: 1274.761 ms

 

In this case, the GIN index is not used at all. 

 

Anything else I can do about this? 

 

 

Re: [GENERAL] SELECT clause without parameters

[Albe Laurenz]

pinker wrote:
 I would like to ask what's the reason of change SELECT behaviour.
 In distributions below 9.4 SELECT without any parameters caused a syntax
 error and now gives empty set. Was it made for some bigger aim ? :)
 
 for instance 8.4:
 postgres=# select version();
version
 -
  PostgreSQL 8.4.17 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real
 (Debian 4.4.5-8) 4.4.5, 32-bit
 (1 wiersz)
 
 postgres=# select
 postgres-# ;
 ERROR:  syntax error at or near ;
 LINIA 2: ;
 
 
 and 9.4:
 psql (9.4.4)
 Type help for help.
 
 postgres=# select
 postgres-# ;
 --
 (1 row)

That must be this change:
http://www.postgresql.org/message-id/e1vs0qu-0004bc...@gemulon.postgresql.org

The explanation is in the commit message.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] SELECT clause without parameters

[pinker]

I would like to ask what's the reason of change SELECT behaviour.
In distributions below 9.4 SELECT without any parameters caused a syntax
error and now gives empty set. Was it made for some bigger aim ? :)

for instance 8.4:
postgres=# select version();
   version  
 
-
 PostgreSQL 8.4.17 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real
(Debian 4.4.5-8) 4.4.5, 32-bit
(1 wiersz)

postgres=# select
postgres-# ;
ERROR:  syntax error at or near ;
LINIA 2: ;


and 9.4:
psql (9.4.4)
Type help for help.

postgres=# select
postgres-# ;
--
(1 row)






--
View this message in context: 
http://postgresql.nabble.com/SELECT-clause-without-parameters-tp5862355.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] returning clause and source columns

[Jack Douglas]

Hi

 

Related to this post: http://dba.stackexchange.com/q/50693/1396

 

Would it be a major change to allow the `returning` clause of `insert` to
return values that are *not* inserted? It seems you can already do so for
literals (and perhaps that should be made explicit in the docs here:
http://www.postgresql.org/docs/9.3/static/sql-insert.html#VARIABLELIST).

 

For example:

 

insert into t1(id) select id from t2 returning *, t2.foo;

 

Kindest regards

Jack

Re: [GENERAL] if-clause to an exiting statement

[Rob Sargent]

Jasen Betts wrote:

On 2010-12-07, Kobi Biton k...@comns.co.il wrote:
  

hi i am a newbie to sql statments  , I am running postgres 8.1 with
application called opennms version 1.8.5 due to an application bug
queries that I execute aginst the DB which returns raw-count=0 are being
ignored and will not process a certain trigger I need to process.



I think you want this:

 ORIGINAL QUERY
 union
   select DUMMY ROW DATA
 where 
   not exists ( ORIGINAL QUERY )



you need to return something to get a rowcount of 1 this is what the
dummy row data provides. the where not exists part blocks the dummy
row data when the main query returns something.

  


Simple enough, but I suspect it runs the same query twice, so I hope 
it's not to expensive.  I wonder what the app is/was doing with the 
vacuous single row  or will do with the dummy data row?  Seems the 
app/trigger simply needs to know the execution of the query was 
successful irrespective of the actual row count - or is that the bug 
with 1.8.5?


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] if-clause to an exiting statement

[Jasen Betts]

On 2010-12-07, Kobi Biton k...@comns.co.il wrote:
 hi i am a newbie to sql statments  , I am running postgres 8.1 with
 application called opennms version 1.8.5 due to an application bug
 queries that I execute aginst the DB which returns raw-count=0 are being
 ignored and will not process a certain trigger I need to process.

I think you want this:

 ORIGINAL QUERY
 union
   select DUMMY ROW DATA
 where 
   not exists ( ORIGINAL QUERY )


you need to return something to get a rowcount of 1 this is what the
dummy row data provides. the where not exists part blocks the dummy
row data when the main query returns something.

-- 
⚂⚃ 100% natural

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] if-clause to an exiting statement

[Kobi Biton]

hi i am a newbie to sql statments  , I am running postgres 8.1 with
application called opennms version 1.8.5 due to an application bug
queries that I execute aginst the DB which returns raw-count=0 are being
ignored and will not process a certain trigger I need to process.

My question is :  Can I use an if-clause into my statement (see below)
which will check if the returned raw-count =0 then will return
raw-count=1 ?
--
SELECT a.eventuei AS _eventuei,
 a.nodeid AS _nodeid,
 a.ipaddr AS _ipaddr,
 now() AS _ts
FROM events a
WHERE
a.eventuei='uei.opennms.org/comns/backup-success-trap' AND
(eventcreatetime gt; now() - interval '10 minutes')
--

Thanks!


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] if-clause to an exiting statement

[Grzegorz Jaśkiewicz]

lookup CASE WHEN END in docs.



-- 
GJ

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] if-clause to an exiting statement

[kobi.biton]

hi thanks for the reply I did look at the CASE statement however cannot seem
to alter the returned row-count ...

  CASE WHEN (@@ROW-COUNT = 0) THEN

 [what do I write here?]  @@ROW-COUNT = 1?

  END 
-- 
View this message in context: 
http://postgresql.1045698.n5.nabble.com/if-clause-to-an-exiting-statement-tp3295519p3295641.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] if-clause to an exiting statement

[Adrian Klaver]

On Tuesday 07 December 2010 3:58:46 am kobi.biton wrote:
 hi thanks for the reply I did look at the CASE statement however cannot
 seem to alter the returned row-count ...

   CASE WHEN (@@ROW-COUNT = 0) THEN

  [what do I write here?]  @@ROW-COUNT = 1?

   END
 --
 View this message in context:
 http://postgresql.1045698.n5.nabble.com/if-clause-to-an-exiting-statement-t
p3295519p3295641.html Sent from the PostgreSQL - general mailing list
 archive at Nabble.com.

See below:

test= SELECT count(*) from bool_test ;
 count
---
33
(1 row)

test= SELECT count(*) , 
case count(*) when 0 
  then 1 
else 
  count(*) 
end 
from 
  bool_test ;

 count | count
---+---
33 |33
(1 row)

test= SELECT count(*) , 
case count(*) when 0 
  then 1 
else 
  count(*) 
end 
from 
  bool_test 
where 
  ifd=0;

 count | count
---+---
 0 | 1
(1 row)


-- 
Adrian Klaver
adrian.kla...@gmail.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: *****SPAM***** Re: [GENERAL] if-clause to an exiting statement

[Scott Ribe]

On Dec 7, 2010, at 4:58 AM, kobi.biton wrote:
 
 hi thanks for the reply I did look at the CASE statement however cannot seem
 to alter the returned row-count ...

Well, yeah. The row count is the count of rows returned. If there are no rows 
matched by the query, then what exactly do you expect to happen? Set the row 
count to 1, so that the application then tries to access the 1st row of 0???

If you need some dummy row returned even in the case where there's no match, 
then you'll have to construct your query that way...

-- 
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: *****SPAM***** Re: [GENERAL] if-clause to an exiting statement

[Scott Ribe]

On Dec 7, 2010, at 9:58 AM, Kobi Biton wrote:
 
 I know it does not sound logic however I do need to set the row count
 to 1 in case row count returns 0

Perhaps I didn't make myself clear: you can't do that. The only thing you can 
do is make sure your query returns a row, and in the case where it currently 
doesn't return a row I have absolutely no idea what it would be that you would 
need to return.

If it would be acceptable to always return some hard-wired dummy row in 
addition to the 0 or more rows that match the current query, then you could use 
a UNION to add the dummy row to the selection. Otherwise, perhaps the real 
problem is that you do not have a matching event in the database and the real 
solution is to add such an event.

In your original post you referred to an application bug where a trigger does 
not run if the row count is 0. It's hard for me to imagine how it's a bug to 
not take action when there is no event that needs processing...

-- 
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] if-clause to an exiting statement

[Adrian Klaver]

On 12/07/2010 08:04 AM, Kobi Biton wrote:

Adrian hi,

Thanks for the reply can you please show me how to incorporate the below
into my below statement ?

SELECT   a.eventuei AS _eventuei,
  a.nodeid AS _nodeid,
  a.ipaddr AS _ipaddr,
  now() AS _ts
  FROM events a
  WHERE
eventuei='uei.opennms.org/comns/backup-success-trap' AND
(eventcreatetimegt; now() - interval '10 minutes')

Thanks!
Kobi









Not sure this is what you want but here, reminder count(*) can have 
performance issues for large values of count():


SELECT   a.eventuei AS _eventuei,
 case count(*) when 0
  then 1
 else
  count(*)
 end
 AS _ct,
  a.nodeid AS _nodeid,
   a.ipaddr AS _ipaddr,
   now() AS _ts
   FROM events a
   WHERE
 eventuei='uei.opennms.org/comns/backup-success-trap' AND
 (eventcreatetimegt; now() - interval '10 minutes')




--
Adrian Klaver
adrian.kla...@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: *****SPAM***** Re: [GENERAL] if-clause to an exiting statement

[Kobi Biton]

I know it does not make sens application bug however consider the
following scenarion , looking at the Statement I sent I would like to
check if over the last 10 minutes a certain type of event was logged and
if NOT (row-count=0) then I would like to trigger and action.

hope it makes more sense.

Kobi.
On Tue, 2010-12-07 at 10:44 -0700, Scott Ribe wrote:
 On Dec 7, 2010, at 9:58 AM, Kobi Biton wrote:
  
  I know it does not sound logic however I do need to set the row count
  to 1 in case row count returns 0
 
 Perhaps I didn't make myself clear: you can't do that. The only thing you can 
 do is make sure your query returns a row, and in the case where it currently 
 doesn't return a row I have absolutely no idea what it would be that you 
 would need to return.
 
 If it would be acceptable to always return some hard-wired dummy row in 
 addition to the 0 or more rows that match the current query, then you could 
 use a UNION to add the dummy row to the selection. Otherwise, perhaps the 
 real problem is that you do not have a matching event in the database and the 
 real solution is to add such an event.
 
 In your original post you referred to an application bug where a trigger does 
 not run if the row count is 0. It's hard for me to imagine how it's a bug to 
 not take action when there is no event that needs processing...
 
 -- 
 Scott Ribe
 scott_r...@elevated-dev.com
 http://www.elevated-dev.com/
 (303) 722-0567 voice
 
 
 
 
 

-- 
Kobi Biton
Com N S Ltd.

Mobile: +972 (54) 8017668


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: *****SPAM***** Re: [GENERAL] if-clause to an exiting statement

[Kobi Biton]

Scott hi,

  I know it does not sound logic however I do need to set the row count
to 1 in case row count returns 0  , can you show how to add that case
clause and dummy line in my below code ? 

SELECT a.eventuei AS _eventuei,
 a.nodeid AS _nodeid,
 a.ipaddr AS _ipaddr,
 now() AS _ts
FROM events a
WHERE
a.eventuei='uei.opennms.org/comns/backup-success-trap' AND
(eventcreatetime gt; now() - interval '10 minutes')

Thanks 
Kobi

On Tue, 2010-12-07 at 09:51 -0700, Scott Ribe wrote:
 On Dec 7, 2010, at 4:58 AM, kobi.biton wrote:
  
  hi thanks for the reply I did look at the CASE statement however cannot seem
  to alter the returned row-count ...


 
 Well, yeah. The row count is the count of rows returned. If there are no rows 
 matched by the query, then what exactly do you expect to happen? Set the row 
 count to 1, so that the application then tries to access the 1st row of 0???
 
 If you need some dummy row returned even in the case where there's no match, 
 then you'll have to construct your query that way...
 
 -- 
 Scott Ribe
 scott_r...@elevated-dev.com
 http://www.elevated-dev.com/
 (303) 722-0567 voice
 
 
 
 
 

-- 
Kobi Biton
Com N S Ltd.

Mobile: +972 (54) 8017668


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] if-clause to an exiting statement

[Kobi Biton]

Adrian hi,

Thanks for the reply can you please show me how to incorporate the below
into my below statement ?

SELECT   a.eventuei AS _eventuei,
 a.nodeid AS _nodeid,
 a.ipaddr AS _ipaddr,
 now() AS _ts
 FROM events a
 WHERE
eventuei='uei.opennms.org/comns/backup-success-trap' AND
(eventcreatetime gt; now() - interval '10 minutes')

Thanks!
Kobi


On Tue, 2010-12-07 at 07:36 -0800, Adrian Klaver wrote:
 On Tuesday 07 December 2010 3:58:46 am kobi.biton wrote:
  hi thanks for the reply I did look at the CASE statement however cannot
  seem to alter the returned row-count ...
 
CASE WHEN (@@ROW-COUNT = 0) THEN
 
   [what do I write here?]  @@ROW-COUNT = 1?
  

END
  --
  View this message in context:
  http://postgresql.1045698.n5.nabble.com/if-clause-to-an-exiting-statement-t
 p3295519p3295641.html Sent from the PostgreSQL - general mailing list
  archive at Nabble.com.
 
 See below:
 
 test= SELECT count(*) from bool_test ;
  count
 ---
 33
 (1 row)
 
 test= SELECT count(*) , 
 case count(*) when 0 
   then 1 
 else 
   count(*) 
 end 
 from 
   bool_test ;
 
  count | count
 ---+---
 33 |33
 (1 row)
 
 test= SELECT count(*) , 
 case count(*) when 0 
   then 1 
 else 
   count(*) 
 end 
 from 
   bool_test 
 where 
   ifd=0;
 
  count | count
 ---+---
  0 | 1
 (1 row)
 
 
 -- 
 Adrian Klaver
 adrian.kla...@gmail.com
 

-- 
Kobi Biton
Com N S Ltd.

Mobile: +972 (54) 8017668


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] where clause question

[Scott Frankel]

Hello,

Is it possible to perform selects in a where clause of a statement?

Given a statement as follows:

SELECT foo.foo_id, foo.name
FROM foo, bar
WHERE foo.bar_id = bar.bar_id
AND bar.name = 'martini';

I'm looking for a way to recast it so that the select and from clauses  
refer to a single table and the join referencing the second table  
occurs in the where clause.  For example, something like this:


SELECT foo.foo_id, foo.name
FROM foo
WHERE (SELECT * FROM foo, bar WHERE ...)
foo.bar_id = bar.bar_id
AND bar.name = 'martini';

I've explored the where exists clause, but it's not supported by the  
application toolkit I'm using.  AFAIK, I've only got access to where ...


Thanks in advance!
Scott



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] where clause question

[Alban Hertroys]

On 4 Sep 2009, at 15:47, Scott Frankel wrote:



Hello,

Is it possible to perform selects in a where clause of a statement?

Given a statement as follows:

   SELECT foo.foo_id, foo.name
   FROM foo, bar
   WHERE foo.bar_id = bar.bar_id
   AND bar.name = 'martini';


I've explored the where exists clause, but it's not supported by  
the application toolkit I'm using.  AFAIK, I've only got access to  
where ...



Have you tried a view?

Is that some in-house toolkit you're using? If not, could you tell  
what it is so that people can chime in with ways to use that toolkit  
to get it do what you want or at least know what toolkit to avoid?


Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4aa1375011861997820494!



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] where clause question

[Merlin Moncure]

On Fri, Sep 4, 2009 at 9:47 AM, Scott Frankellekn...@pacbell.net wrote:

 Hello,

 Is it possible to perform selects in a where clause of a statement?

 Given a statement as follows:

    SELECT foo.foo_id, foo.name
    FROM foo, bar
    WHERE foo.bar_id = bar.bar_id
    AND bar.name = 'martini';

 I'm looking for a way to recast it so that the select and from clauses refer
 to a single table and the join referencing the second table occurs in the
 where clause.  For example, something like this:

    SELECT foo.foo_id, foo.name
    FROM foo
    WHERE (SELECT * FROM foo, bar WHERE ...)
    foo.bar_id = bar.bar_id
    AND bar.name = 'martini';

 I've explored the where exists clause, but it's not supported by the
 application toolkit I'm using.  AFAIK, I've only got access to where ...

where clauses is basically a set of boolean expressions.  It's not
completely clear how to wrap that inside what you are trying to do.

you can do this:
WHERE something = (SELECT * FROM foo, bar WHERE ...)

or this:

WHERE (SELECT count(*) FROM foo, bar WHERE ...)  0

for example.  however, I'd advise dumping the application framework as
a long term objective.  Another general tactic to try and express what
you are looking for in a view and query the view in a more regular
way.  This is likely your best bet.

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] where clause question

[David Fetter]

On Fri, Sep 04, 2009 at 06:47:24AM -0700, Scott Frankel wrote:

 Hello,

 Is it possible to perform selects in a where clause of a statement?

 Given a statement as follows:

 SELECT foo.foo_id, foo.name
 FROM foo, bar
 WHERE foo.bar_id = bar.bar_id
 AND bar.name = 'martini';

 I'm looking for a way to recast it so that the select and from clauses  
 refer to a single table and the join referencing the second table occurs 
 in the where clause.  For example, something like this:

 SELECT foo.foo_id, foo.name
 FROM foo
 WHERE (SELECT * FROM foo, bar WHERE ...)
 foo.bar_id = bar.bar_id
 AND bar.name = 'martini';

 I've explored the where exists clause, but it's not supported by the  
 application toolkit I'm using.  AFAIK, I've only got access to where ...

Sounds like a great reason to modify, or if you can't modify, replace,
that application toolkit.  This won't be the last time it will get in
your way.

Cheers,
David.
-- 
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] where clause question

[Scott Frankel]

Hello,

Is it possible to perform selects in a where clause of a statement?

Given a statement as follows:

   SELECT foo.foo_id, foo.name
   FROM foo, bar
   WHERE foo.bar_id = bar.bar_id
   AND bar.name = 'martini';

I'm looking for a way to recast it so that the select and from clauses  
refer to a single table and the join referencing the second table  
occurs in the where clause.  For example, something like this:


   SELECT foo.foo_id, foo.name
   FROM foo
   WHERE (SELECT * FROM foo, bar WHERE ...)
   foo.bar_id = bar.bar_id
   AND bar.name = 'martini';

I've explored the where exists clause, but it's not supported by the  
application toolkit I'm using. AFAIK, I've only got access to where ...


Thanks in advance!
Scott


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] where clause question

[Martin Gainty]

you'll need to create an alias beforehand

SELECT foo.foo_id, foo.name
FROM foo, (SELECT * FROM foo, bar WHERE ...) bar
WHERE foo.bar_id = bar.bar_id
AND bar.name = 'martini';

Martin Gainty 
__ 
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
 
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger 
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung 
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem 
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. 
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung 
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le 
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez 
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est 
interdite. Ce message sert à l'information seulement et n'aura pas n'importe 
quel effet légalement obligatoire. Étant donné que les email peuvent facilement 
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité 
pour le contenu fourni.




 Date: Fri, 4 Sep 2009 10:21:24 -0700
 From: da...@fetter.org
 To: lekn...@pacbell.net
 CC: pgsql-general@postgresql.org
 Subject: Re: [GENERAL] where clause question
 
 On Fri, Sep 04, 2009 at 06:47:24AM -0700, Scott Frankel wrote:
 
  Hello,
 
  Is it possible to perform selects in a where clause of a statement?
 
  Given a statement as follows:
 
  SELECT foo.foo_id, foo.name
  FROM foo, bar
  WHERE foo.bar_id = bar.bar_id
  AND bar.name = 'martini';
 
  I'm looking for a way to recast it so that the select and from clauses  
  refer to a single table and the join referencing the second table occurs 
  in the where clause.  For example, something like this:
 
  SELECT foo.foo_id, foo.name
  FROM foo
  WHERE (SELECT * FROM foo, bar WHERE ...)
  foo.bar_id = bar.bar_id
  AND bar.name = 'martini';
 
  I've explored the where exists clause, but it's not supported by the  
  application toolkit I'm using.  AFAIK, I've only got access to where ...
 
 Sounds like a great reason to modify, or if you can't modify, replace,
 that application toolkit.  This won't be the last time it will get in
 your way.
 
 Cheers,
 David.
 -- 
 David Fetter da...@fetter.org http://fetter.org/
 Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
 Skype: davidfetter  XMPP: david.fet...@gmail.com
 
 Remember to vote!
 Consider donating to Postgres: http://www.postgresql.org/about/donate
 
 -- 
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

_
Windows Live: Keep your friends up to date with what you do online.
http://windowslive.com/Campaign/SocialNetworking?ocid=PID23285::T:WLMTAGL:ON:WL:en-US:SI_SB_online:082009

[GENERAL] RETURNING clause in 8.2

[Brandon Metcalf]

I see that 8.2 has added a RETURNING clause option to the INSERT
command.  Is there anyway to achieve the same thing in versions prior
to 8.2?  Specifically, I need to return a default sequence number
generated from an INSERT.

Thanks.

-- 
Brandon

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] RETURNING clause in 8.2

[Scott Marlowe]

On Wed, Jun 18, 2008 at 1:32 PM, Brandon Metcalf [EMAIL PROTECTED] wrote:
 I see that 8.2 has added a RETURNING clause option to the INSERT
 command.  Is there anyway to achieve the same thing in versions prior
 to 8.2?  Specifically, I need to return a default sequence number
 generated from an INSERT.

Not really the same.  You can use currval('seqname') in versions before 8.2

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] RETURNING clause: how to specifiy column indexes?

[Ken Johanson]

Kris and all,

Here is the query I will call to the get the name of columns by ordinal 
position. Do you see any compatibility drivers will older server 
versions, or other issues?


SELECT column_name
FROM information_schema.columns
WHERE table_catalog=? AND table_schema=? AND table_name=?
ORDER BY ordinal_position

Ken



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] RETURNING clause: how to specifiy column indexes?

[Kris Jurka]

On Thu, 13 Dec 2007, Ken Johanson wrote:

Here is the query I will call to the get the name of columns by ordinal 
position. Do you see any compatibility drivers will older server versions, or 
other issues?


SELECT column_name
FROM information_schema.columns
WHERE table_catalog=? AND table_schema=? AND table_name=?
ORDER BY ordinal_position



Using pg_catalog tables is better than using information_schema because of 
the way permissions work.  For information_schema you must be the table 
owner, while people who only have permissions to access a table will most 
likely be able to read pg_catalog.


Kris Jurka


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] RETURNING clause: how to specifiy column indexes?

[Ken Johanson]

Kris Jurka wrote:



Using pg_catalog tables is better than using information_schema because 
of the way permissions work.  For information_schema you must be the 
table owner, while people who only have permissions to access a table 
will most likely be able to read pg_catalog.




Do you have an equivalent query/join handy that will get the catalog and 
schema and table and column names frm the pg tables?


SELECT column_name
FROM information_schema.columns
WHERE table_catalog=? AND table_schema=? AND table_name=?
ORDER BY ordinal_position




---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] RETURNING clause: how to specifiy column indexes?

[Ken Johanson]

Tom Lane wrote:

Kris Jurka [EMAIL PROTECTED] writes:

I think the expectation is that:



CREATE TABLE t(a int, b int);
INSERT INTO t(b,a) VALUES (1,2) RETURNING *;



will return 1,2 instead of 2,1 as it does now.


Hmm ... I see your point, but on what grounds could one argue that
a * targetlist here should return something different from what
SELECT * FROM t would return?

I'd say that an app that wants that should write

INSERT INTO t(b,a) VALUES (1,2) RETURNING b,a;

which is surely not that hard if you've got the code to produce
the (b,a) part.

In any case it's not clear this is the same thing Ken is complaining
about ...



I am only seeking to have the columns returned in the order they appear 
naturally. JDBC says This array contains the indexes of the columns in 
the target table that contain the auto-generated keys that should be 
made available.


For the record I was not complaining, only citing in advance the fact 
that while some consider selecting the keys by index to be dubious, it 
nonetheless must be done because an API requires it. Casting my question 
into a complaint is another topic.




---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] RETURNING clause: how to specifiy column indexes?

[Kris Jurka]

On Wed, 12 Dec 2007, Ken Johanson wrote:

Kris, do you have pointers to a spec that says the named-columns should 
specify the index, or that it should instead be the order in the table? My 
interpretation from the JDBC spec was that the latter is true, I may be 
wrong...


No, I was actually misremembering what the JDBC spec said, although I 
think it's an interesting case to consider regardless of any specs.


In the case where it is table-order, then I presume in PG that the natural 
order of the columns (even if reordering is allowed at a alter date) is 
specified by data in one of the pg_* tables (pg_class, pg_index, etc). Does 
anyone know if this is true/false?


pg_attribute.attnum stores column order at the moment.  If/when 
reordering is allowed, there will be another column indicating the 
logical order of the colums.


If true, my next idea would be to derive the column name using a subquery in 
the returning clause. But it sounds like this may have potential security 
contraints (will any INSERT query always have read access to the PG tables?). 
And no guarantee of the order matching in the long term.


There is no requirement that insert permission on a user table implies 
read access to pg_catalog.  Still many clients will break if they can't 
read pg_catalog.  For example, all of the JDBC driver's MetaData results 
need to query pg tables, updatable ResultSets need to query pg tables to 
know what the primary key is and so on.  So if this functionality required 
access to pg_catalog that would neither be unprecedented nor unreasonable.



Is there a more elegant approach, like?:

INSERT... RETURNING (PG_LIST_KEYS(tblname))



You can't dynamically derive the returning clause for the same reason you 
can't say INSERT INTO (SELECT myfunc()) VALUES (...), using myfunc to 
determine the table name at runtime.  The planner needs to know all the 
tables/columns/other database parts up front before executing anything.


Kris Jurka


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] RETURNING clause: how to specifiy column indexes?

[Ken Johanson]

Kris Jurka wrote:


I think the expectation is that:

CREATE TABLE t(a int, b int);
INSERT INTO t(b,a) VALUES (1,2) RETURNING *;

will return 1,2 instead of 2,1 as it does now.  In this case the op is 
not expecting that the (potentially reorganized) table order is driving 
the results, but the order that they've actually specified the columns 
in creates the result.




Kris, do you have pointers to a spec that says the named-columns should 
specify the index, or that it should instead be the order in the table? 
My interpretation from the JDBC spec was that the latter is true, I may 
be wrong...


In the case where it is table-order, then I presume in PG that the 
natural order of the columns (even if reordering is allowed at a alter 
date) is specified by data in one of the pg_* tables (pg_class, 
pg_index, etc). Does anyone know if this is true/false?


If true, my next idea would be to derive the column name using a 
subquery in the returning clause. But it sounds like this may have 
potential security contraints (will any INSERT query always have read 
access to the PG tables?). And no guarantee of the order matching in the 
long term.


Is there a more elegant approach, like?:

INSERT... RETURNING (PG_LIST_KEYS(tblname))

I looked but did not find such a utility. It seems that such function 
would be best implemented in the server instead of in a driver (eg. 
having hardcoded subquery to the schema).


Ken



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] RETURNING clause: how to specifiy column indexes?

[Ken Johanson]

If true, my next idea would be to derive the column name using a 
subquery in the returning clause. But it sounds like this may have 
potential security contraints (will any INSERT query always have read 
access to the PG tables?). And no guarantee of the order matching in 
the long term.


There is no requirement that insert permission on a user table implies 
read access to pg_catalog.  Still many clients will break if they can't 
read pg_catalog.  For example, all of the JDBC driver's MetaData results 
need to query pg tables, updatable ResultSets need to query pg tables to 
know what the primary key is and so on.  So if this functionality 
required access to pg_catalog that would neither be unprecedented nor 
unreasonable.




So it sounds like this may be the best approach, do you agree? I'll try 
and find the cycles to code this up although the limited value of 
getGeneratedKeys by index makes me think my time would be better spent 
elsewhere on the JDBC driver. For now at least. If you can respond to my 
earlier query (5 Dec) about what robustness improvements are needed, 
I'll start there..


Thanks,
Ken



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] RETURNING clause: how to specifiy column indexes?

[Ken Johanson]

I am attempting to implement (in a driver)(PG JDBC) support for 
specifying which column indexes (that generated keys) to return, so I'm 
searching for a way to get the server to return the values of the 
columns by their index, not name. By name, it is simply to append the 
RETURNING clause and column names to the query:


INSERT... RETURNING foo,bar

Does anyone know how (if) this is possible by index? A standard or 
server-specific syntax is fine since this is being implemented in a 
server-driver.


Something like?:

INSERT... RETURNING [1],[2] (obviously this will not work)

Would I otherwise need to?:

INSERT... RETURNING *

then extract the user-requested columns? This seems inefficient as it 
returns all columns / non-key ones (blobs etc).


While the values of an API that specifies the table's columns by 
ordinaility may seem dubious, it is an API that I think should be 
implemented anyway.


Thanks,
Ken



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] RETURNING clause: how to specifiy column indexes?

[Tom Lane]

Ken Johanson [EMAIL PROTECTED] writes:
 While the values of an API that specifies the table's columns by 
 ordinaility may seem dubious, it is an API that I think should be 
 implemented anyway.

Every few weeks we get a complaint from someone who thinks that it
should be easy to rearrange the logical order of table columns.
If that comes to pass, it would be a seriously bad idea to have
encouraged applications to rely on table column numbers.  And given
the number of votes for that (probably in the hundreds by now)
versus the number of votes for this idea (one), I think column
reordering is much more likely to get done ...

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] RETURNING clause: how to specifiy column indexes?

[Kris Jurka]

On Wed, 12 Dec 2007, Tom Lane wrote:


Every few weeks we get a complaint from someone who thinks that it
should be easy to rearrange the logical order of table columns.
If that comes to pass, it would be a seriously bad idea to have
encouraged applications to rely on table column numbers.


I think the expectation is that:

CREATE TABLE t(a int, b int);
INSERT INTO t(b,a) VALUES (1,2) RETURNING *;

will return 1,2 instead of 2,1 as it does now.  In this case the op is 
not expecting that the (potentially reorganized) table order is 
driving the results, but the order that they've actually specified the 
columns in creates the result.


Kris Jurka


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] RETURNING clause: how to specifiy column indexes?

[Tom Lane]

Kris Jurka [EMAIL PROTECTED] writes:
 I think the expectation is that:

 CREATE TABLE t(a int, b int);
 INSERT INTO t(b,a) VALUES (1,2) RETURNING *;

 will return 1,2 instead of 2,1 as it does now.

Hmm ... I see your point, but on what grounds could one argue that
a * targetlist here should return something different from what
SELECT * FROM t would return?

I'd say that an app that wants that should write

INSERT INTO t(b,a) VALUES (1,2) RETURNING b,a;

which is surely not that hard if you've got the code to produce
the (b,a) part.

In any case it's not clear this is the same thing Ken is complaining
about ...

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

[GENERAL] IN clause performance

[Paul Codler]

Basic query optimization question- does Postgres process

  x IN (y1, y2)
as fast as
  (x = y1 OR x = y2)

in a function?

Re: [GENERAL] IN clause performance

[Peter Wiersig]

On Thu, Jul 19, 2007 at 05:52:30AM -0700, Paul Codler wrote:
  Basic query optimization question- does Postgres process
 
x IN (y1, y2)
  as fast as
(x = y1 OR x = y2)
 
  in a function?

EXPLAIN indicates this.

Peter

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] IN clause performance

[Pg Coder]

Basic query optimization question- does Postgres process

  x IN (y1, y2)
as fast as
  (x = y1 OR x = y2)

in a function?

[GENERAL] HAVING clause working in postgres 8.0, but not in 8.2

[ujkavlade]

Hello all,

We have recently upgrade our postgres server from 8.0 to 8.2. I am
experiencing some difficulties in SQL queries.
Let's say I have a table NUMBERS (number (integer)) which has values
1, 5 and 8.

SELECT number FROM numbers; will return 1, 5 and 8.

In PostgreSql 8.0, SELECT number FROM numbers HAVING number = 5;
returns 5

But in 8.2, it gives me the following error: ERROR:  column
numbers.number must appear in the GROUP BY clause or be used in an
aggregate function.

In the documentation, it says that HAVING can be used without GROUP BY
or aggregate functions.

Has anybody already experienced this, is this a bug or am I missing
something?

TIA,
Vladimir


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] HAVING clause working in postgres 8.0, but not in 8.2

[Tom Lane]

[EMAIL PROTECTED] writes:
 In PostgreSql 8.0, SELECT number FROM numbers HAVING number = 5;
 returns 5

 But in 8.2, it gives me the following error: ERROR:  column
 numbers.number must appear in the GROUP BY clause or be used in an
 aggregate function.

 In the documentation, it says that HAVING can be used without GROUP BY
 or aggregate functions.

It can; whether it's useful or not is another question.

Per the 8.1 release notes:

* Fix HAVING without any aggregate functions or GROUP BY so that the
  query returns a single group

  Previously, such a case would treat the HAVING clause the same as
  a WHERE clause. This was not per spec.

The above query is incorrect because it hasn't done anything to create
a grouped column.

regards, tom lane

---(end of broadcast)---
TIP 1: 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: [GENERAL] EXCEPTION clause not identified

[Prashant Ranjalkar]

Hi,

Probably you might be using reserved words (ip_address). Please try with out
using the reserved words.

Regards
Prashant Ranjalkar



On 5/14/07, Jasbinder Singh Bali [EMAIL PROTECTED] wrote:


Hi,
In one of my trigger functions, i'm trying to catch invalid ip address
exception

CREATE OR REPLACE FUNCTION func_client_socket()
  RETURNS trigger AS
$BODY$
DECLARE
  ip_address_present int4;
BEGIN
  ip_address_present = 1;
SELECT inet(NEW.canonical_name);
EXCEPTION WHEN invalid_text_representation THEN
ip_address=0;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

when i run this function, it gives me the followin error

ERROR:  syntax error at or near EXCEPTION at character 1343
which is the line where I have the EXCEPTION clause.

Can anyone please tell me whats going wrong here?

Thanks,
~Jas

Re: [GENERAL] EXCEPTION clause not identified

[Tom Lane]

Jasbinder Singh Bali [EMAIL PROTECTED] writes:
 In one of my trigger functions, i'm trying to catch invalid ip address
 exception

 CREATE OR REPLACE FUNCTION func_client_socket()
   RETURNS trigger AS
 $BODY$
 DECLARE
   ip_address_present int4;
 BEGIN
   ip_address_present = 1;
 SELECT inet(NEW.canonical_name);
 EXCEPTION WHEN invalid_text_representation THEN
 ip_address=0;
 END;
 $BODY$
   LANGUAGE 'plpgsql' VOLATILE;

 when i run this function, it gives me the followin error

 ERROR:  syntax error at or near EXCEPTION at character 1343
 which is the line where I have the EXCEPTION clause.

When I run the function as given, it doesn't complain about the
EXCEPTION, but it does complain about the misspelled variable name
on the next line.  Maybe you miscounted lines?  It'd be worth your
while to update to a more recent PG version that gives better-localized
syntax error messages.  8.1 or 8.2 will say something like

ERROR:  syntax error at or near ip_address at character 1
QUERY:  ip_address=0
CONTEXT:  SQL statement in PL/PgSQL function func_client_socket near line 7
LINE 1: ip_address=0
^

regards, tom lane

---(end of broadcast)---
TIP 1: 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

[GENERAL] EXCEPTION clause not identified

[Jasbinder Singh Bali]

Hi,
In one of my trigger functions, i'm trying to catch invalid ip address
exception

CREATE OR REPLACE FUNCTION func_client_socket()
 RETURNS trigger AS
$BODY$
   DECLARE
 ip_address_present int4;
BEGIN
 ip_address_present = 1;
SELECT inet(NEW.canonical_name);
   EXCEPTION WHEN invalid_text_representation THEN
   ip_address=0;
END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE;

when i run this function, it gives me the followin error

ERROR:  syntax error at or near EXCEPTION at character 1343
which is the line where I have the EXCEPTION clause.

Can anyone please tell me whats going wrong here?

Thanks,
~Jas

[GENERAL] where clause help

[Ketema]

i have a record set like below:

num_prods|num_open_issues|num_provisioned|num_canceled
1|0|1|0
2|0|0|2
3|0|1|1
2|0|1|1
1|0|01
2|0|0|0
3|3|0|0
3|0|0|3
3|1|0|2
3|2|0|1
2|0|2|0

Of the list above only row 3 and row 6 should be returned.

Plain english definition:
With a result set like above eliminate all rows that should not show
up on the provision List.  Provision List Definition: All rows that
have products that need provisioning.  Provisioning means its NOT
canceled and it does NOT have an open issue. Some facts:
num_open_issues + num_provisioned + num_canceled will never be more
than num_prods.
no individual column will ever be more than num_prods.

thanks!


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] where clause help

[George Pavlov]

where num_prods  num_open_issues + num_provisioned + num_canceled

if those columns are nullable (which they don't seem to be) you'd have
to convert the NULLs (i.e. coalesce(num_canceled,0) )


 -Original Message-
 From: [EMAIL PROTECTED] 
 [mailto:[EMAIL PROTECTED] On Behalf Of Ketema
 Sent: Monday, April 23, 2007 4:21 PM
 To: pgsql-general@postgresql.org
 Subject: [GENERAL] where clause help
 
 i have a record set like below:
 
 num_prods|num_open_issues|num_provisioned|num_canceled
 1|0|1|0
 2|0|0|2
 3|0|1|1
 2|0|1|1
 1|0|01
 2|0|0|0
 3|3|0|0
 3|0|0|3
 3|1|0|2
 3|2|0|1
 2|0|2|0
 
 Of the list above only row 3 and row 6 should be returned.
 
 Plain english definition:
 With a result set like above eliminate all rows that should not show
 up on the provision List.  Provision List Definition: All rows that
 have products that need provisioning.  Provisioning means its NOT
 canceled and it does NOT have an open issue. Some facts:
 num_open_issues + num_provisioned + num_canceled will never be more
 than num_prods.
 no individual column will ever be more than num_prods.
 
 thanks!
 

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] where clause help

[Ketema]

Man so simple!  is your solution the same as:

num_provisioned  num_products AND (num_open_issues + num_provisioned
+ num_canceled)  num_prods

which is what i finally came up with



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] where clause help

[Brent Wood]

Ketema wrote:

i have a record set like below:

num_prods|num_open_issues|num_provisioned|num_canceled
1|0|1|0
2|0|0|2
3|0|1|1  *
2|0|1|1
1|0|0|1
2|0|0|0  *
3|3|0|0
3|0|0|3
3|1|0|2
3|2|0|1
2|0|2|0

Of the list above only row 3 and row 6 should be returned.

Plain english definition:
With a result set like above eliminate all rows that should not show
up on the provision List.  Provision List Definition: All rows that
have products that need provisioning.  Provisioning means its NOT
canceled and it does NOT have an open issue. 


If I understand this correctly, we start with:
where num_cancelled  num_prods and num_open_issues  num_prods


Some facts:
num_open_issues + num_provisioned + num_canceled will never be more
than num_prods.
no individual column will ever be more than num_prods.
  

Then in addition to this, we also only retrieve records where:

num_open_issues + num_provisioned + num_canceled  num_prods
and
num_open_issues  num_prods (already there above, no need to have it twice)
and
num_provisioned  num_prods
and
num_canceled  num_prods (already there above, no need to have it twice)

giving the query:

select * from table
where num_open_issues  num_prods
   and num_provisioned  num_prods
   and num_canceled  num_prods
   and (num_open_issues + num_provisioned + num_canceled)  num_prods;

With (I think) the result of:

records 1,11 fail as num_provisioned  is not  num_prods
records 2,8 fail as num_cancelled is not  num_prods
record 3 passes all constraints
records 4,5,9, 10 fail as num_open_issues + num_provisioned + 
num_canceled is not  num_prods

record 6  passes all constraints
record 7 fails as num_open_issues is not  num_prods


Is this what you were after?

Brent Wood


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] where clause help

[Jorge Godoy]

Ketema [EMAIL PROTECTED] writes:

 Man so simple!  is your solution the same as:

 num_provisioned  num_products AND (num_open_issues + num_provisioned
 + num_canceled)  num_prods

 which is what i finally came up with

This can be simplified to num_open_issues + num_provisioned +
num_canceled  num_prods, without the AND and the other statement. 

-- 
Jorge Godoy  [EMAIL PROTECTED]

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] From Clause

[Alban Hertroys]

Bob Pawley wrote:
 I am receiving an error message about a missing From Clause in an
 insert statement.
  
 I've tried a number of variations of adding a from clause with no success.
  
 Could someone help with the following statement??
  
  Insert Into p_id.loop_sequence (monitor)  values(p_id.loops.monitor) ;

You can add that schema name to your search ppath, you know... saves typing.

What kind of value is p_id.loops.monitor? I think you meant to do a
select instead of values; probably this:

set search_path to 'p_id,public';
insert into loop_sequence (monitor) select monitor from loops;

 Bob Pawley


-- 
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] From Clause

[Bob Pawley]

I am receiving an error message about a missing From Clause in an insert 
statement.

I've tried a number of variations of adding a from clause with no success.

Could someone help with the following statement??

 Insert Into p_id.loop_sequence (monitor)  values(p_id.loops.monitor) ;

Bob Pawley

Re: [GENERAL] IN clause in a cursor

[Nik]

Nevermind. I used:

OPEN test FOR EXECUTE 'SELECT a, b, c FROM tbl WHERE d IN ' values;

Nik wrote:
 I have a dynamic set of clauses that I would like to use in the cursor.
 Is there a way to achieve this using the IN clause and a string, or
 multiple OR clauses coupled with strings.

 This doesn't work, but it's an example of what I'm trying to do.

 -
 DECLARE
 test refcursor;
 values varchar;

 BEGIN
 -- This will be dynamically generated
 values := '(1, 2, 3)';

 OPEN test FOR SELECT a, b, c FROM tbl WHERE d IN values;
 CLOSE test;

 END;
 -

 or

 -
 DECLARE
 test refcursor;
 values varchar;

 BEGIN
 -- This will be dynamically generated
 values := '(d=1 OR d=2 OR d=3)';

 OPEN test FOR SELECT a, b, c FROM tbl WHERE values;
 CLOSE test;
 
 END;
 -


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] IN clause

[Martijn van Oosterhout]

On Mon, Nov 27, 2006 at 05:24:31PM -0600, Jim Nasby wrote:
 In any case, like Oracle, PostgreSQL does not index NULL values (at  
 least not in btree).

Actually, PostgreSQL does store NULL values in an index, otherwise you
could never use them for full index scans (think multicolumn indexes).
You can't use the index for IS NULL tests, although patches exist for
that.

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 From each according to his ability. To each according to his ability to 
 litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] IN clause

[Jim Nasby]

On Nov 24, 2006, at 9:04 AM, Marcus Engene wrote:

There is one other case where I personally find nullable
columns a good thing: process_me ish flags. When a row
is not supposed to be processed that field is null and
when a field is null it wont be in the index [at least
on Oracle].


Actually, that's abuse of NULL. NULL is supposed to mean I don't know.

In any case, like Oracle, PostgreSQL does not index NULL values (at  
least not in btree).

--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

[GENERAL] IN clause

[surabhi.ahuja]

Hi,
 
i have a table 
and i have the query select * from table where col_name is null;
 
it returns some rows
 
now, say i have to implement the same query using the in clause how shold it be 
done?
 
select * from table where col_name in (null);
 
but it does not return any rows.
 
Can you please suggest some way of doing it?
 
thanks,
regards
Surabhi

Re: [GENERAL] IN clause

[A. Kretschmer]

am  Fri, dem 24.11.2006, um 14:42:30 +0530 mailte surabhi.ahuja folgendes:
 Hi,
  
 i have a table
 and i have the query select * from table where col_name is null;
  
 it returns some rows
  
 now, say i have to implement the same query using the in clause how shold it 
 be
 done?
  
 select * from table where col_name in (null);

select * from table where col_name is null or col_name in (...);

Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: - Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] IN clause

[surabhi.ahuja]

That is fine 
but what I was actually expecting is this
if 
select * from table where col_name in (null, 'a', 'b');

to return those rows where col_name is null or if it = a or if it is = b
 
But i think in does not not support null queries , am i right?



From: [EMAIL PROTECTED] on behalf of A. Kretschmer
Sent: Fri 11/24/2006 2:59 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] IN clause



am  Fri, dem 24.11.2006, um 14:42:30 +0530 mailte surabhi.ahuja folgendes:
 Hi,
 
 i have a table
 and i have the query select * from table where col_name is null;
 
 it returns some rows
 
 now, say i have to implement the same query using the in clause how shold it 
 be
 done?
 
 select * from table where col_name in (null);

select * from table where col_name is null or col_name in (...);

Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: - Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] IN clause

[Martijn van Oosterhout]

On Fri, Nov 24, 2006 at 05:31:07PM +0530, surabhi.ahuja wrote:
 That is fine 
 but what I was actually expecting is this
 if 
 select * from table where col_name in (null, 'a', 'b');
 
 to return those rows where col_name is null or if it = a or if it is = b
  
 But i think in does not not support null queries , am i right?

You'll need to check the standard, but IN() treats NULL specially, I
think it returns NULL if any of the elements is null, or something like
that. It certainly doesn't work the way you think it does.

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 From each according to his ability. To each according to his ability to 
 litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] IN clause

[Alban Hertroys]

surabhi.ahuja wrote:
 That is fine 
 but what I was actually expecting is this
 if 
 select * from table where col_name in (null, 'a', 'b');
 
 to return those rows where col_name is null or if it = a or if it is = b
  
 But i think in does not not support null queries , am i right?

Expressions comparing NULL usually result in NULL, and not in true or
false. That's why there are special operators on NULL, like IS and
COALESCE().

The problem is that the WHERE clause interprets a NULL value similar
to false (as per the SQL spec). There's some interesting literature
about this, for example by C.J.Date.

As an example,
NULL = NULL and NULL IS NULL;
have two different results (NULL and true respectively). You'll also
find that concatenation
'a' || NULL
results in NULL.

The same goes for IN (...).

-- 
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] IN clause

[Brandon Aiken]

Hasn't it been said enough?  Don't allow NULLs in your database.
Databases are for storing data, not a lack of it.  The only time NULL
should appear is during outer joins.

--
Brandon Aiken
CS/IT Systems Engineer
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Martijn van
Oosterhout
Sent: Friday, November 24, 2006 7:20 AM
To: surabhi.ahuja
Cc: A. Kretschmer; pgsql-general@postgresql.org
Subject: Re: [GENERAL] IN clause

On Fri, Nov 24, 2006 at 05:31:07PM +0530, surabhi.ahuja wrote:
 That is fine 
 but what I was actually expecting is this
 if 
 select * from table where col_name in (null, 'a', 'b');
 
 to return those rows where col_name is null or if it = a or if it is =
b
  
 But i think in does not not support null queries , am i right?

You'll need to check the standard, but IN() treats NULL specially, I
think it returns NULL if any of the elements is null, or something like
that. It certainly doesn't work the way you think it does.

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 From each according to his ability. To each according to his ability
to litigate.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] IN clause

[Marcus Engene]

I see we have a C J Date fan on the list! ;-)

There is one other case where I personally find nullable
columns a good thing: process_me ish flags. When a row
is not supposed to be processed that field is null and
when a field is null it wont be in the index [at least
on Oracle].

Best regards,
Marcus

Brandon Aiken skrev:

Hasn't it been said enough?  Don't allow NULLs in your database.
Databases are for storing data, not a lack of it.  The only time NULL
should appear is during outer joins.

--
Brandon Aiken
CS/IT Systems Engineer
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Martijn van
Oosterhout
Sent: Friday, November 24, 2006 7:20 AM
To: surabhi.ahuja
Cc: A. Kretschmer; pgsql-general@postgresql.org
Subject: Re: [GENERAL] IN clause

On Fri, Nov 24, 2006 at 05:31:07PM +0530, surabhi.ahuja wrote:
That is fine 
but what I was actually expecting is this
if 
select * from table where col_name in (null, 'a', 'b');


to return those rows where col_name is null or if it = a or if it is =

b
 
But i think in does not not support null queries , am i right?


You'll need to check the standard, but IN() treats NULL specially, I
think it returns NULL if any of the elements is null, or something like
that. It certainly doesn't work the way you think it does.

Have a nice day,



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] IN clause

[Richard Broersma Jr]

 That is fine 
 but what I was actually expecting is this
 if 
 select * from table where col_name in (null, 'a', 'b');
 
 to return those rows where col_name is null or if it = a or if it is = b
  
 But i think in does not not support null queries , am i right?
 

that is correct: if col_name was actually 'a' then you would get:

'a' in ( null, 'a', 'b', ...) works the same as:

'a' = null  ~ resolves to Unknown
or
'a' = 'a'   ~ resovles to true
or
'a' = 'b'   ~ resovles to false
or
...

so you end up with:
(unknown or true or false) = true
but if you have
(unknown or false or false) = false

Regards,

Richard Broersma Jr.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] IN clause

[Ragnar]

On fös, 2006-11-24 at 10:10 -0800, Richard Broersma Jr wrote:
  That is fine 
  but what I was actually expecting is this
  if 
  select * from table where col_name in (null, 'a', 'b');
  
  to return those rows where col_name is null or if it = a or if it is = b
   
  But i think in does not not support null queries , am i right?
  
 
 that is correct: if col_name was actually 'a' then you would get:
 
 'a' in ( null, 'a', 'b', ...) works the same as:
 
 'a' = null  ~ resolves to Unknown
 or
 'a' = 'a'   ~ resovles to true
 or
 'a' = 'b'   ~ resovles to false
 or
 ...
 
 so you end up with:
 (unknown or true or false) = true
 but if you have
 (unknown or false or false) = false

yes, except I think you meant:
(unknown or false or false) = unknown

as can be demonstrated by:

test=# \pset null 'null'
Null display is null.
test=# select (null or true);
 ?column?
--
 t
(1 row)

test=# select (null or false);
 ?column?
--
 null
(1 row)


and indeed the IN operator does behave this way:

test=# select 'a' in (null,'a');
 ?column?
--
 t
(1 row)

test=# select 'a' in (null,'b');
 ?column?
--
 null
(1 row)

test=# select 'a' in ('a','b');
 ?column?
--
 t
(1 row)

test=# select 'a' in ('b','c');
 ?column?
--
 f
(1 row)


and finally: NULL IN (NULL,'b') will return NULL
because it will translate to
(NULL = NULL) or (NULL = 'b')

test=# select null in (null,'b');
 ?column?
--
 null
(1 row)




---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] IN clause

[Richard Broersma Jr]

 yes, except I think you meant:
 (unknown or false or false) = unknown
 
 as can be demonstrated by:
 
 test=# \pset null 'null'
 Null display is null.
 test=# select (null or true);
  ?column?
 --
  t
 (1 row)
 
 test=# select (null or false);
  ?column?
 --
  null
 (1 row)

Thanks for the clearification.  I was totally wrong on that point. :-)

Regards,

Richard Broersma Jr.

---(end of broadcast)---
TIP 1: 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: [GENERAL] having clause question

[Ian Barwick]

On Sat, 30 Oct 2004 15:17:16 -0700, Shane Wegner
[EMAIL PROTECTED] wrote:
 Hello,
(...)
 I want to retrieve any last names with more than 1
 occurence in the table.  Under MySQL, this query does the
 trick.
 select lastname,count(*) as c from names group by lastname
 having c  1;
 
 But under PG, it errors out
 ERROR:  column c does not exist
 
 Is it possible to do a query like this with PostgreSQL?

select lastname,count(*) as c from names group by lastname having count(*)  1;

HTH

Ian Barwick
[EMAIL PROTECTED]

---(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: [GENERAL] having clause question

[Tom Lane]

Ian Barwick [EMAIL PROTECTED] writes:
 On Sat, 30 Oct 2004 15:17:16 -0700, Shane Wegner
 [EMAIL PROTECTED] wrote:
 Under MySQL, this query does the trick.
 select lastname,count(*) as c from names group by lastname
 having c  1;
 
 Is it possible to do a query like this with PostgreSQL?

 select lastname,count(*) as c from names group by lastname having count(*)  1;

Just to expand on that: MySQL's abbrevation is not legal SQL, and it's
not even very sensible, because logically speaking the SELECT output
list is only evaluated after (and if) the HAVING clause succeeds.
So it makes no sense for the HAVING clause to refer to SELECT values.

Postgres does optimize the case of multiple identical aggregate-function
invocations in a query, BTW, so the apparent inefficiency is not real.

regards, tom lane

---(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: [GENERAL] Where clause limited to 8 items?

[Greg Stark]

Henry Combrinck [EMAIL PROTECTED] writes:

 The above works fine - the index is used.  However, extend the where
 clause with an extra line (say, col1 = 9) and the index is no longer used.

Do

  explain analyze select ...

with both versions and send the results (preferably without line wrapping it).

I'm a bit skeptical about your description since I don't see how either query
could possibly be using an index here. 

-- 
greg


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Where clause limited to 8 items?

[Henry Combrinck]

 Henry Combrinck [EMAIL PROTECTED] writes:

 The above works fine - the index is used.  However, extend the where
 clause with an extra line (say, col1 = 9) and the index is no longer used.

 Do

   explain analyze select ...

 with both versions and send the results (preferably without line wrapping it).

 I'm a bit skeptical about your description since I don't see how either query
 could possibly be using an index here.


Why?  Either it uses an index, or it doesn't.  Being skeptical doesn't
change the reality of what is in fact happening.  Anyway, the suggestion
from Stephan Szabo was the right one.

Just in case you're still feeling skeptical:

DB=# set enable_seqscan=on;
SET
DB=# explain analyse select count(*) from test1 where a=1 or a=2 or a=3 or a=4 or a=5 
or a=6 or a=7 or a=8;
   
  QUERY PLAN
-
 Aggregate  (cost=38.75..38.75 rows=1 width=0) (actual time=0.291..0.292 rows=1 
loops=1)
   -  Index Scan using test1_pkey, test1_pkey, test1_pkey, test1_pkey, test1_pkey, 
test1_pkey, test1_pkey, test1_pkey on test1  (cost=0.00..38.72 rows=8 width=0) (actual 
time=0.089..0.228 rows=8 loops=1)
 Index Cond: ((a = 1) OR (a = 2) OR (a = 3) OR (a = 4) OR (a = 5) OR (a = 6) 
OR (a = 7) OR (a = 8))
 Total runtime: 0.744 ms
(4 rows)

DB=# explain analyse select count(*) from test1 where a=1 or a=2 or a=3 or a=4 or a=5 
or a=6 or a=7 or a=8 or a=9;
QUERY PLAN
---
 Aggregate  (cost=42.52..42.52 rows=1 width=0) (actual time=0.249..0.250 rows=1 
loops=1)
   -  Seq Scan on test1  (cost=0.00..42.50 rows=9 width=0) (actual time=0.067..0.182 
rows=9 loops=1)
 Filter: ((a = 1) OR (a = 2) OR (a = 3) OR (a = 4) OR (a = 5) OR (a = 6) OR (a 
= 7) OR (a = 8) OR (a = 9))
 Total runtime: 0.493 ms
(4 rows)

DB=#

When used on a real table (ie, with hundreds of thousands of records),
the total runtime peaks at over 8000ms (seq scan)...



This message was sent using MetroWEB's AirMail service.
http://www.metroweb.co.za/ - full access for only R73.
Free Web Accelerator, WebMail, Calendar, Anti-Virus, 
Anti-Spam, 10 emails, 100MB personal webspace, and more!
Phone Now!  086 11 11 440

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] Where clause limited to 8 items?

[Henry Combrinck]

Hello

Searched around, but could not find this mentioned.

I've noticed the following behaviour in 7.4.5:

[explain analyse] select * from foo where
col1 = 1 or
col1 = 2 or
col1 = 3 or
col1 = 4 or
col1 = 5 or
col1 = 6 or
col1 = 7 or
col1 = 8;

where an index on foo.col1 exists.

The above works fine - the index is used.  However, extend the where
clause with an extra line (say, col1 = 9) and the index is no longer used.

Is there a parameter I can SET to extend the number of items allowed for
index usage?

Any pointers would be appreciated.

Henry



This message was sent using MetroWEB's AirMail service.
http://www.metroweb.co.za/ - full access for only R73.
Free Web Accelerator, WebMail, Calendar, Anti-Virus, 
Anti-Spam, 10 emails, 100MB personal webspace, and more!
Phone Now!  086 11 11 440

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Where clause limited to 8 items?

[Stephan Szabo]

On Wed, 20 Oct 2004, Henry Combrinck wrote:

 Hello

 Searched around, but could not find this mentioned.

 I've noticed the following behaviour in 7.4.5:

 [explain analyse] select * from foo where
 col1 = 1 or
 col1 = 2 or
 col1 = 3 or
 col1 = 4 or
 col1 = 5 or
 col1 = 6 or
 col1 = 7 or
 col1 = 8;

 where an index on foo.col1 exists.

 The above works fine - the index is used.  However, extend the where
 clause with an extra line (say, col1 = 9) and the index is no longer used.

Check the estimated number of rows returned.  It's presumably believing
that the a sequential scan will be cheaper for the estimated number of
rows.

If the estimated number of rows is significantly off, you may wish to
change the statistics target (see ALTER TABLE) for col1 and analyze the
table again.

If it still is choosing a sequential scan over an index scan and the
number of rows is similar, you may want to look at the random_page_cost
variable.  You have to be careful not too lower it too far that other
queries are pessimized the other direction, but some experimentation
comparing the real times and estimated costs of queries with and without
enable_seqscan=off may help.

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] WHERE CLAUSE

[Sameer Maggon]

Hi,

  Well i have seen somewhere
  WHERE somefiled @ '{123,324}'

  what does this mean

  Sameer

_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] BETWEEN clause

[Paul Tomblin]

Is the BETWEEN clause inclusive or exclusive?  ie if I say WHERE
latitude BETWEEN 45 and 55, will I get examples where the latitude equals
45 or not?  Also, is latitude BETWEEN 45 and 55 any more efficient than
latitude = 45 AND latitude = 55, or is it just a stylistic thing?

-- 
Paul Tomblin [EMAIL PROTECTED], not speaking for anybody
There is no substitute for good manners, except, perhaps, fast reflexes.

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster