[GENERAL] PostgreSQL triggers

2010-07-01 Thread Sebastian Ritter
Hi All, I was hoping you could help with a few queries regarding row-wise PostgreSQL triggers. Is it possible to see triggers appearing in the postgresql-8.3-main.log? I have "log_min_duration_statement" set to 0 (logs all statements) in my postgresql.conf but I can't seem to find any reference t

Re: [GENERAL] Find users that have ALL categories

2010-07-01 Thread David Fetter
On Wed, Jun 30, 2010 at 12:11:35AM -0700, Nick wrote: > Is this the most efficient way to write this query? Id like to get a > list of users that have the categories 1, 2, and 3? > > SELECT user_id FROM user_categories WHERE category_id IN (1,2,3) GROUP > BY user_id HAVING COUNT(*) = 3 > > users_

Re: [GENERAL] Find users that have ALL categories

2010-07-01 Thread Sam Mason
On Thu, Jul 01, 2010 at 04:26:38AM -0700, David Fetter wrote: > On Wed, Jun 30, 2010 at 12:11:35AM -0700, Nick wrote: > > Is this the most efficient way to write this query? Id like to get a > > list of users that have the categories 1, 2, and 3? > > > > SELECT user_id FROM user_categories WHERE c

Re: [GENERAL] Find users that have ALL categories

2010-07-01 Thread David Fetter
On Thu, Jul 01, 2010 at 12:37:55PM +0100, Sam Mason wrote: > On Thu, Jul 01, 2010 at 04:26:38AM -0700, David Fetter wrote: > > On Wed, Jun 30, 2010 at 12:11:35AM -0700, Nick wrote: > > > Is this the most efficient way to write this query? Id like to > > > get a list of users that have the categorie

[GENERAL] extracting total amount of time from an interval

2010-07-01 Thread Andrew Geery
Is there a way to extract the total amount of time in a given unit from an interval? For example, I would like to know the total number of milliseconds in an interval. The expression select extract('seconds' from '5 minutes'::interval) returns the value of the seconds field (0) in the interval;

Re: [GENERAL] extracting total amount of time from an interval

2010-07-01 Thread Thom Brown
On 1 July 2010 12:53, Andrew Geery wrote: > Is there a way to extract the total amount of time in a given unit from an > interval?  For example, I would like to know the total number of > milliseconds in an interval. > The expression > select extract('seconds' from '5 minutes'::interval) > returns

Re: [GENERAL] extracting total amount of time from an interval

2010-07-01 Thread A. Kretschmer
In response to Andrew Geery : > Is there a way to extract the total amount of time in a given unit from an > interval?  For example, I would like to know the total number of milliseconds > in an interval. > > The expression > > select extract('seconds' from '5 minutes'::interval) > > returns the

Re: [GENERAL] PostgreSQL triggers

2010-07-01 Thread Alban Hertroys
On 1 Jul 2010, at 12:29, Sebastian Ritter wrote: > Hi All, > > I was hoping you could help with a few queries regarding row-wise > PostgreSQL triggers. > > Is it possible to see triggers appearing in the postgresql-8.3-main.log? > I have "log_min_duration_statement" set to 0 (logs all statements

Re: [GENERAL] PostgreSQL triggers

2010-07-01 Thread Sebastian Ritter
Hi Alban, Thanks for your response and your time. I added some more explanatory text in-line. Kindest Regards, Sebastian On Thu, Jul 01, 2010 at 03:10:01PM +0200, Alban Hertroys wrote: > On 1 Jul 2010, at 12:29, Sebastian Ritter wrote: > > > Hi All, > > > > I was hoping you could help with a

Re: [GENERAL] Prevent characters not transposable to LATIN9

2010-07-01 Thread Arnaud Lesauvage
Le 30/06/2010 2:42, Howard Rogers a écrit : Something I do in Oracle: do a TRANSLATE on whatever string is being supplied, converting matching characters to spaces, and measure the length. If the length is greater than zero, your supplied string has something in it you're not expecting, at which

Re: [GENERAL] Prevent characters not transposable to LATIN9

2010-07-01 Thread Sam Mason
On Tue, Jun 29, 2010 at 04:52:22PM +0200, Arnaud Lesauvage wrote: > We have a database in UTF8, from which we have to export text files in > LATIN9 encoding (or WIN1252, which is almostthe same I believe). > > Records are entered via MSAccess forms (on psqlodbc-linked tables). > The problem is th

Re: [GENERAL] Prevent characters not transposable to LATIN9

2010-07-01 Thread Arnaud Lesauvage
Le 1/07/2010 16:48, Sam Mason a écrit : On Tue, Jun 29, 2010 at 04:52:22PM +0200, Arnaud Lesauvage wrote: We have a database in UTF8, from which we have to export text files in LATIN9 encoding (or WIN1252, which is almostthe same I believe). Records are entered via MSAccess forms (on psqlodb

Re: [GENERAL] Prevent characters not transposable to LATIN9

2010-07-01 Thread Sam Mason
On Thu, Jul 01, 2010 at 04:53:51PM +0200, Arnaud Lesauvage wrote: > Le 1/07/2010 16:48, Sam Mason a écrit : >> How about using the built in character conversion routines. Something >> like: >> >>col = convert_from(convert_to(col, 'LATIN9'),'LATIN9') >> >> as the check constraint, or its invers

[GENERAL] Process- or SessionID with ECPG

2010-07-01 Thread Leif Jensen
Hi guys, I have an ECPG based program and I would very much like to get either the process id of the backend or the session id for debugging purposes. Is there a way to call PQbackendPID() directly from my program (I suppose that will require getting hold of the PGconn from "within" ECPG?

Re: [GENERAL] Problems building from source

2010-07-01 Thread Mark Cave-Ayland
Magnus Hagander wrote: Faced with that, I then changed my configure line to be ./configure --prefix=ingw --with-gnu-ld --without-zlib (who needs zlib anyways, right?). Now I was thinking that surely this would be the end of it and I would soon have a fully built version of PostgreSQL sa

[GENERAL] Easy way to convert a database from WIN1252 to UTF8?

2010-07-01 Thread Mike Christensen
I'd like to convert a small database to UTF8 before it becomes too large. I'm running on 8.3.x on Windows. It doesn't seem that pgAdmin has any native way of doing this, what's the easiest way to go about doing this? Thanks! Mike -- Sent via pgsql-general mailing list (pgsql-general@postgresq

Re: [GENERAL] Can't EXTRACT number of months from an INTERVAL

2010-07-01 Thread Eliot, Christopher
Thanks! justify_interval is what I needed, I had never heard of it. I was already prepared to deal with getting the years and multiplying by 12. Topher [] On Jun 30, 2010, at 18:45 , Eliot, Christopher wrote: > I need to read a timestamp from the database and turn that into an integer > des

Re: [GENERAL] Easy way to convert a database from WIN1252 to UTF8?

2010-07-01 Thread Justin Graf
On 7/1/2010 11:08 AM, Mike Christensen wrote: > I'd like to convert a small database to UTF8 before it becomes too > large. I'm running on 8.3.x on Windows. It doesn't seem that pgAdmin > has any native way of doing this, what's the easiest way to go about > doing this? Thanks! > > Mike >

Re: [GENERAL] Easy way to convert a database from WIN1252 to UTF8?

2010-07-01 Thread Mike Christensen
On Thu, Jul 1, 2010 at 9:31 AM, Mike Christensen wrote: > On Thu, Jul 1, 2010 at 10:22 AM, Justin Graf wrote: >> >> >> On 7/1/2010 11:08 AM, Mike Christensen wrote: >>> I'd like to convert a small database to UTF8 before it becomes too >>> large.  I'm running on 8.3.x on Windows.  It doesn't seem

Re: [GENERAL] Easy way to convert a database from WIN1252 to UTF8?

2010-07-01 Thread Mike Christensen
On Thu, Jul 1, 2010 at 10:22 AM, Justin Graf wrote: > > > On 7/1/2010 11:08 AM, Mike Christensen wrote: >> I'd like to convert a small database to UTF8 before it becomes too >> large.  I'm running on 8.3.x on Windows.  It doesn't seem that pgAdmin >> has any native way of doing this, what's the ea

Re: [GENERAL] Easy way to convert a database from WIN1252 to UTF8?

2010-07-01 Thread Sam Mason
On Thu, Jul 01, 2010 at 09:31:12AM -0700, Mike Christensen wrote: > Then, I edited the file in Notepad and saved it as UTF8 which also > appears to have worked. I don't think you want to be doing this. The file should contain a "set client_encoding" of the correct value which will let PG to do the

Re: [GENERAL] Easy way to convert a database from WIN1252 to UTF8?

2010-07-01 Thread Mike Christensen
On Thu, Jul 1, 2010 at 9:44 AM, Sam Mason wrote: > On Thu, Jul 01, 2010 at 09:31:12AM -0700, Mike Christensen wrote: >> Then, I edited the file in Notepad and saved it as UTF8 which also >> appears to have worked. > > I don't think you want to be doing this. The file should contain a "set > client

Re: [GENERAL] Easy way to convert a database from WIN1252 to UTF8?

2010-07-01 Thread Sam Mason
On Thu, Jul 01, 2010 at 09:47:03AM -0700, Mike Christensen wrote: > On Thu, Jul 1, 2010 at 9:44 AM, Sam Mason wrote: > > On Thu, Jul 01, 2010 at 09:31:12AM -0700, Mike Christensen wrote: > >> Then, I edited the file in Notepad and saved it as UTF8 which also > >> appears to have worked. > > > > I

Re: [GENERAL] Easy way to convert a database from WIN1252 to UTF8?

2010-07-01 Thread Mike Christensen
On Thu, Jul 1, 2010 at 9:54 AM, Sam Mason wrote: > On Thu, Jul 01, 2010 at 09:47:03AM -0700, Mike Christensen wrote: >> On Thu, Jul 1, 2010 at 9:44 AM, Sam Mason wrote: >> > On Thu, Jul 01, 2010 at 09:31:12AM -0700, Mike Christensen wrote: >> >> Then, I edited the file in Notepad and saved it as

Re: [GENERAL] Easy way to convert a database from WIN1252 to UTF8?

2010-07-01 Thread Sam Mason
On Thu, Jul 01, 2010 at 10:01:02AM -0700, Mike Christensen wrote: > Yup, the problem is line 170 doesn't actually match up to the > DB.dbs.out file line 170 (which is a blank line). I believe it means > line 170 from the stdin pipe it was processing for the copy command. Doh, that's annoying. It

Re: [GENERAL] Easy way to convert a database from WIN1252 to UTF8?

2010-07-01 Thread Mike Christensen
On Thu, Jul 1, 2010 at 10:07 AM, Sam Mason wrote: > On Thu, Jul 01, 2010 at 10:01:02AM -0700, Mike Christensen wrote: >> Yup, the problem is line 170 doesn't actually match up to the >> DB.dbs.out file line 170 (which is a blank line).  I believe it means >> line 170 from the stdin pipe it was pro

[GENERAL] Uncable to commit: transaction marked for rollback

2010-07-01 Thread David Kerr
I'm intermittantly getting this error message in a java app. using Geronimo / Hibernate / Postgres 8.3.9 javax.transaction.RollbackException: Unable to commit: transaction marked for rollback Can someone give me a scenario where this would happen? "unable to commit" makes everyone immediatly go

Re: [GENERAL] Uncable to commit: transaction marked for rollback

2010-07-01 Thread Tom Lane
David Kerr writes: > I'm intermittantly getting this error message in a java app. > using Geronimo / Hibernate / Postgres 8.3.9 > javax.transaction.RollbackException: Unable to commit: transaction marked for > rollback You might have better luck asking about that on pgsql-jdbc --- there's no su

Re: [GENERAL] Uncable to commit: transaction marked for rollback

2010-07-01 Thread David Kerr
On 7/1/2010 11:10 AM, Tom Lane wrote: David Kerr writes: I'm intermittantly getting this error message in a java app. using Geronimo / Hibernate / Postgres 8.3.9 javax.transaction.RollbackException: Unable to commit: transaction marked for rollback You might have better luck asking about t

[GENERAL] problems with encoding and accent letters

2010-07-01 Thread aaaawwww
My DB is UTF8 and I cannot change it. When I try to insert accent letters like è ò ... I get Query failed: ERROR: invalid byte sequence for encoding "UTF8": 0xe82729 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "cli

Re: [GENERAL] Uncable to commit: transaction marked for rollback

2010-07-01 Thread Thomas Markus
Hi, it happens fi at transaction timeout or you executed a broken/failed statement und catched the exception try { call invalid sql } catch (Exception e) {} connection.commit() <- exception throws here, happens automatically inside your appserver regards Thomas Am 01.07.2010 20:06, schrieb D

Re: [GENERAL] problems with encoding and accent letters

2010-07-01 Thread Peter Geoghegan
On 1 July 2010 15:43, wrote: > My DB is UTF8 and I cannot change it. > When I try to insert accent letters like è ò ... I get > Query failed: ERROR: invalid byte sequence for encoding "UTF8": > 0xe82729 HINT: This error can also happen if the byte sequence does > not match the encoding ex

Re: [GENERAL] problems with encoding and accent letters

2010-07-01 Thread Peter Geoghegan
On 1 July 2010 15:43, wrote: > My DB is UTF8 and I cannot change it. > When I try to insert accent letters like è ò ... I get > Query failed: ERROR: invalid byte sequence for encoding "UTF8": > 0xe82729 HINT: This error can also happen if the byte sequence does > not match the encoding ex

Re: [GENERAL] problems with encoding and accent letters

2010-07-01 Thread aaaawwww
Sorry I forgot to say I am running an Italian php website with a postgresql DB. Users can write messages eachothers. This is my code: $connessione_db = pg_connect("dbname=x user=y password=zz"); $sql_insert_messaggio="insert into messaggio (text) values ('àèì');"; pg_exec ($connessione_

Re: [GENERAL] Is full-row updates slower than single-value updates

2010-07-01 Thread Björn Lindqvist
Den 28 juni 2010 20.22 skrev Tom Lane : > Pavel Stehule writes: >> 2010/6/28 Björn Lindqvist : >>> My question is like the subject, is it much slower to update all >>> columns values than just a single column? Generated update queries >>> from ORM:s generally have the following format: >>> >>> upd

Re: [GENERAL] problems with encoding and accent letters

2010-07-01 Thread John R Pierce
On 07/01/10 2:27 PM, wrote: Sorry I forgot to say I am running an Italian php website with a postgresql DB. Users can write messages eachothers. This is my code: $connessione_db = pg_connect("dbname=x user=y password=zz"); $sql_insert_messaggio="insert into messaggio (text) v

Re: [GENERAL] Is full-row updates slower than single-value updates

2010-07-01 Thread Tom Lane
=?ISO-8859-1?Q?Bj=F6rn_Lindqvist?= writes: > Den 28 juni 2010 20.22 skrev Tom Lane : >> But assigning a new value to a column costs something, even if it >> happens to be equal to the previous value. > But do I really need to worry about the cost of casting strings to > ints and other such parsin

[GENERAL] different language indexes

2010-07-01 Thread Dennis Gearon
Since a DATABASE in Pgsql is the lowest level of objects that can be assigned a collation, I'm wondering if there is a way around it. Can indexes be made which have their own individual collations? I'm thinking to make a table in a datbase that has either us_en-utf8 or 'c' collation, and then p

[GENERAL] change array dimension

2010-07-01 Thread Gerhard Heift
Hello, is it possible to change the dimension of an array? I am looking for something like this: SELECT change_array_dims('[1:2][1:2]', '{1,2,3,4}'::int[]); change_array_dims --- {{1,2},{3,4}} and the other way around: SELECT change_array_dims('[1:4]', '{{1,2},{3,4}}'::int[]);

[GENERAL] Help with pgAndroid Contest

2010-07-01 Thread Stephen Denne
Not having an Android phone, I've got no idea whether what I've produced for my entry to this contest works... I'd appreciate it if anybody with an Android phone could try out my pgQuilt application, and let me know whether it even runs! Screenshot: http://www.datacute.co.nz/pgQuilt.png Applicati

Re: [GENERAL] Prevent characters not transposable to LATIN9

2010-07-01 Thread Arnaud Lesauvage
Le 1/07/2010 17:12, Sam Mason a écrit : On Thu, Jul 01, 2010 at 04:53:51PM +0200, Arnaud Lesauvage wrote: Le 1/07/2010 16:48, Sam Mason a écrit : How about using the built in character conversion routines. Something like: col = convert_from(convert_to(col, 'LATIN9'),'LATIN9') as the