[SQL] operator contains in older Pgsql

I need a functionality of "@>" array operator in 8.1 Pg server. i.ex. SELECT * FROM table WHERE array_col @> ARRAY ['xxx'] (works in 8.2, error in 8.1) How to performe such a query ? Is it possible ? Thanks for help. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make change

Re: [SQL] operator contains in older Pgsql

On 2009-08-18, W. Kinastowski wrote: > I need a functionality of "@>" array operator in 8.1 Pg server. i.ex. > SELECT * FROM table WHERE array_col @> ARRAY ['xxx'] (works in 8.2, > error in 8.1) > How to performe such a query ? Is it possible ? Thanks for help. SELECT * FROM table WHERE 'xxx'

Re: [SQL] operator contains in older Pgsql

Jasen Betts wrote: On 2009-08-18, W. Kinastowski wrote: I need a functionality of "@>" array operator in 8.1 Pg server. i.ex. SELECT * FROM table WHERE array_col @> ARRAY ['xxx'] (works in 8.2, error in 8.1) How to performe such a query ? Is it possible ? Thanks for help. SELECT

Re: [SQL] operator contains in older Pgsql

On 2009-08-18, W. Kinastowski wrote: > Jasen Betts wrote: >> On 2009-08-18, W. Kinastowski wrote: >> >>> I need a functionality of "@>" array operator in 8.1 Pg server. i.ex. >>> SELECT * FROM table WHERE array_col @> ARRAY ['xxx'] (works in 8.2, >>> error in 8.1) >>> How to performe such a

[SQL] SQL Query Performance - what gives?

Let's take the following EXPLAIN results: ticker=# explain select * from post, forum where forum.name = post.forum and invisible <> 1 and to_tsvector('english', message) @@ to_tsquery('violence') order by modified desc limit 100;

Re: [SQL] [PERFORM] SQL Query Performance - what gives?

First query: ticker=# explain analyze select * from post, forum where forum.name = post.forum and invisible <> 1 and to_tsvector('english', message) @@ to_tsquery('violence') order by modified desc limit 100; QUERY PLAN

FW: [SQL] simple? query

Hi, Thanks for the suggestion, the only problem is, if primary key is used then each row should be unique what is not true; since I have a column 'registered' what only can be 1 or 0... Regards, Jan -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postg

Re: [SQL] [PERFORM] SQL Query Performance - what gives?

Karl Denninger wrote: > Let's take the following EXPLAIN results: We could tell a lot more from EXPLAIN ANALYZE results. The table definitions (with index information) would help, too. -Kevin -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscripti

[SQL] Updating one table with data from another

Hey all, There are two things I need to do: 1. Update existing rows with new data 2. Append new rows I need to update only some of the fields table1 with data from table2. These tables have the exact same fields. So here's what I have currently for appending new rows (rows where CID does not cur

Re: [SQL] [PERFORM] SQL Query Performance - what gives?

Karl Denninger wrote: >-> Index Scan using forum_name on forum > (cost=0.00..250.63 rows=1 width=271) (actual time=0.013..0.408 > rows=63 loops=1) > Filter: (((contrib IS NULL) OR (contrib = ' > '::text) OR (contrib ~~ '%b%'::text)) AND ((permission & 127) =

Re: [SQL] [PERFORM] SQL Query Performance - what gives?

Kevin Grittner wrote: > Karl Denninger wrote: > >>-> Index Scan using forum_name on forum >> (cost=0.00..250.63 rows=1 width=271) (actual time=0.013..0.408 >> rows=63 loops=1) >> Filter: (((contrib IS NULL) OR (contrib = ' >> '::text) OR (contrib ~~ '%b%':

Re: [SQL] FW: simple? query

Jan Verheyden wrote: > Thanks for the suggestion, the only problem is, if primary key is used then > each row should be unique what is not true; since I have a column > 'registered' what only can be 1 or 0... > [...] I have no idea what you are trying to say. Tim -- Sent via pgsql-sql mail

Re: [SQL] Multiple simultaneous queries on single connection

On 17/08/2009 8:49 PM, Yeb Havinga wrote: Hello list, We want to access a postgres database with multiple queries / result sets that are read simultaneously (hence async). The documentation says explicitly that no new PQsendQuery can be send on the same channel before the pqgetresults has return

Re: [SQL] [PERFORM] SQL Query Performance - what gives?

Karl Denninger writes: > The problem appearsa to lie in the "nested loop", and I don't understand > why that's happening. It looks to me like there are several issues here. One is the drastic underestimate of the number of rows satisfying the permission condition. That leads the planner to think

Re: [SQL] [PERFORM] SQL Query Performance - what gives?

Tom Lane wrote: > Karl Denninger writes: > >> The problem appearsa to lie in the "nested loop", and I don't understand >> why that's happening. >> > It looks to me like there are several issues here. > > One is the drastic underestimate of the number of rows satisfying the > permission con