Re: [GENERAL] Array comparison & prefix search

On Fri, Dec 4, 2009 at 12:58 PM, Denes Daniel wrote: > Hi, > > I have a table like this: > > CREATE TABLE test ( > type text NOT NULL, >     ident text[] NOT NULL, >     ... > ); > ALTER TABLE ONLY test ADD CONSTRAINT test_pkey PRIMARY KEY (type, ident); > > and I would like to query rows that

Re: [GENERAL] Array comparison & prefix search

2009/12/4 Sam Mason > I think you want to create a functional index on ident[1], something > like: > > CREATE INDEX test_my_idx ON test (type,(ident[1])); Sorry, but this approach is no good, since I may search like: SELECT * FROM test WHERE type = 'three' AND (ident[1] = 'foo' AND ident[2] =

Re: [GENERAL] Examples of using PQexecParams

On Fri, Dec 4, 2009 at 1:47 PM, Daniel wrote: > I was looking for examples of using PQexecParams but all I found was > this: http://sepp.oetiker.ch/postgresql-8.2.3-ds/libpq-example.html > > /* Convert integer value "2" to network byte order */ >    binaryIntVal = htonl((uint32_t) 2); > /* Set up

Re: [GENERAL] Examples of using PQexecParams

Daniel wrote: I was looking for examples of using PQexecParams but all I found was this: http://sepp.oetiker.ch/postgresql-8.2.3-ds/libpq-example.html /* Convert integer value "2" to network byte order */ binaryIntVal = htonl((uint32_t) 2); /* Set up parameter arrays for PQexecParams */

Re: [GENERAL] Catastrophic changes to PostgreSQL 8.4

Hello, Thanks for your response. On Thursday 03 December 2009 16:46:54 Tom Lane wrote: > Sam Mason writes: > > As others have said; BYTEA is probably the best datatype for you to > > use. The encoding of BYTEA literals is a bit of a fiddle and may need > > some changes, but it's going to be muc

Re: [GENERAL] Daily migration on Postgresql

Really depends on what you want to transfer. Now you can use proprietary software, which can be more expensive compared to open source solutions. You can use software such as Informatica. Or Talend Open Studio which is an open source program. Hope this helps. mrciken wrote: > > Hello, > >

Re: [GENERAL] Catastrophic changes to PostgreSQL 8.4

On Thursday 03 December 2009 16:42:58 Adrian Klaver wrote: > On Wednesday 02 December 2009 11:33:38 pm Kern Sibbald wrote: > > > ( BTW, one way to handle incorrectly encoded filenames and paths might > > > be to have a `bytea' field that's generally null to store such mangled > > > file names. Pers

[GENERAL] Examples of using PQexecParams

I was looking for examples of using PQexecParams but all I found was this: http://sepp.oetiker.ch/postgresql-8.2.3-ds/libpq-example.html /* Convert integer value "2" to network byte order */ binaryIntVal = htonl((uint32_t) 2); /* Set up parameter arrays for PQexecParams */ paramValues[0] =

Re: [GENERAL] Array comparison & prefix search

On Fri, Dec 04, 2009 at 06:58:21PM +0100, Denes Daniel wrote: > SELECT * FROM test WHERE type = 'two' AND ident[1] = 'test'; > > this query uses the primary key index only for the "type" field, and > then filters for ident[1]. Is there a way to make it use the index for the > array prefix search t

[GENERAL] Array comparison & prefix search

Hi, I have a table like this: CREATE TABLE test ( type text NOT NULL, ident text[] NOT NULL, ... ); ALTER TABLE ONLY test ADD CONSTRAINT test_pkey PRIMARY KEY (type, ident); and I would like to query rows that have a specific "type" and whose "ident" array starts with a some given co

[GENERAL] PostgreSQL Release Support Policy

After a great deal of discussion in the community, the project's core team have written a policy outlining the support lifecycle for major PostgreSQL releases, which can be found on the wiki with other project policies at http://wiki.postgresql.org/wiki/Policies. We hope this document will help ou

Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default

Andrew Gierth writes: > "Tom" == Tom Lane writes: > Tom> Hm, I think that's only a problem if we define it to be a > Tom> problem, and I'm not sure it's necessary to do so. > The complaint is that if plpgsql is installed by default, then it will > be owned by postgres rather than by the db own

Re: [GENERAL] Catastrophic changes to PostgreSQL 8.4

Kern Sibbald writes: > Concerning the future: I am still a bit concerning about the mention in the > document of possible future changes concerning SQL_ASCII and LC_CTYPE not C > or POSIX ("Using this combination of settings is deprecated and may someday > be forbidden altogether. "), so if you

Re: [GENERAL] Unexpected EOF on client connection

David Boreham wrote: Sampling the number of connections on my database I decided that the number of connections settled at 6 so I changed my MinPoolSize from 3 to 6. I checked the current state of the database and the number of connections is currently 12. Tonight I shall change the MinPoolSize

Re: [GENERAL] Installing PL/pgSQL by default

Jasen Betts wrote: > On 2009-12-04, Andrew Gierth wrote: > > The complaint is that if plpgsql is installed by default, then it will > > be owned by postgres rather than by the db owner, who will then not be > > able to drop it or use grant/revoke on it. > > The same problem is had with schema pu

Re: [GENERAL] Installing PL/pgSQL by default

On 2009-12-04, Andrew Gierth wrote: >> "Tom" == Tom Lane writes: > > > Andrew Dunstan writes: > >> Before we go too far with this, I'd like to know how we will handle the > >> problems outlined here: > >> > > Tom> Hm, I th

Re: [GENERAL] SELECTing every Nth record for better performance

Tom wrote: 1. taking the primary key (which is an auto increasing integer) and running modulo on it. This will not work in my case as the database is being used for a number of different logging 'runs', and these runs are not necessarily equally distributed over the primary keys. Problem #1 wi

Re: [GENERAL] SELECTing every Nth record for better performance

why create another table, straightforward on 8.4: SELECT * FROM (SELECT *, (row_number() OVER( ORDER BY id))%10 AS rn FROM table) sa WHERE sa.rn=1 LIMIT 10;

Re: [GENERAL] SELECTing every Nth record for better performance

In response to Ivan Voras : > A. Kretschmer wrote: > >In response to Tom : > >>I have a big table that is used for datalogging. I'm designing > >>graphing interface that will visualise the data. When the user is > >>looking at a small daterange I want the database to be queried for all > >>records,

Re: [GENERAL] SELECTing every Nth record for better performance

A. Kretschmer wrote: In response to Tom : I have a big table that is used for datalogging. I'm designing graphing interface that will visualise the data. When the user is looking at a small daterange I want the database to be queried for all records, but when the user is 'zoomed out', looking at

Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default

> "Tom" == Tom Lane writes: > Andrew Dunstan writes: >> Before we go too far with this, I'd like to know how we will handle the >> problems outlined here: >> Tom> Hm, I think that's only a problem if we define it to

Re: [GENERAL] SELECTing every Nth record for better performance

In response to Richard Broersma : > On Thu, Dec 3, 2009 at 9:26 PM, Tom wrote: > > > I > > want run a query that skips every nth record and returns a managable > > dataset that still gives a correct overview of the data without > > slowing the programme down. Is there an easy way to do this that