[SQL] inserting boolean values in dynamic queries

2008-09-18 Thread Raphael Bauduin
Hi, I'm building a dynamic query as described at http://www.postgresql.org/docs/8.3/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN : EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = ' || quote_literal(newvalue) || ' WHERE key = '

Re: [SQL] Doubts about FK

2008-09-18 Thread Rafael Domiciano
2008/9/12 Scott Marlowe <[EMAIL PROTECTED]> > On Fri, Sep 12, 2008 at 12:14 PM, Rafael Domiciano > <[EMAIL PROTECTED]> wrote: > > Hi there, > > > > In my DB I have a couple of FK, so the change of referenced columns is a > > quite complicated. > > Today, I DISABLE TRIGGER ALL on the tables envolve

Re: [SQL] inserting boolean values in dynamic queries

2008-09-18 Thread Tom Lane
"Raphael Bauduin" <[EMAIL PROTECTED]> writes: > EXECUTE 'UPDATE tbl SET ' > || quote_ident(colname) > || ' = ' > || quote_literal(newvalue) > || ' WHERE key = ' > || quote_literal(keyvalue); > It works fine, except when I want to include a boolean value: the

Re: [SQL] inserting boolean values in dynamic queries

2008-09-18 Thread Raphael Bauduin
On Thu, Sep 18, 2008 at 2:35 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Raphael Bauduin" <[EMAIL PROTECTED]> writes: >> EXECUTE 'UPDATE tbl SET ' >> || quote_ident(colname) >> || ' = ' >> || quote_literal(newvalue) >> || ' WHERE key = ' >> || quote_literal(ke

Re: [SQL] inserting boolean values in dynamic queries

2008-09-18 Thread Tom Lane
"Raphael Bauduin" <[EMAIL PROTECTED]> writes: > --> ERROR: function quote_literal(boolean) does not exist Oh, you must be using some version that didn't have a bool->text cast. I'd suggest making one. Or just make a quote_literal(boolean) function. regards, tom lane --

Re: [SQL] Doubts about FK

2008-09-18 Thread Scott Marlowe
On Thu, Sep 18, 2008 at 5:49 AM, Rafael Domiciano <[EMAIL PROTECTED]> wrote: > 2008/9/12 Scott Marlowe <[EMAIL PROTECTED]> >> >> On Fri, Sep 12, 2008 at 12:14 PM, Rafael Domiciano >> <[EMAIL PROTECTED]> wrote: >> > Hi there, >> > >> > In my DB I have a couple of FK, so the change of referenced colu

[SQL] ORDER BY collation order

2008-09-18 Thread Joe
Hi, I just found that two identical queries on two PG 8.2.7 databases with the same data and same encoding, one running on Debian and the other on FreeBSD, returned rows in a different order, even though both queries had an ORDER BY clause. Essentially, on FreeBSD a varchar starting with a d

Re: [SQL] ORDER BY collation order

2008-09-18 Thread Scott Marlowe
On Thu, Sep 18, 2008 at 6:48 PM, Joe <[EMAIL PROTECTED]> wrote: > Hi, > > I just found that two identical queries on two PG 8.2.7 databases with the > same data and same encoding, one running on Debian and the other on FreeBSD, > returned rows in a different order, even though both queries had an O

Re: [SQL] ORDER BY collation order

2008-09-18 Thread Joe
Hi Scott, Scott Marlowe wrote: no, not encoding, locale, such as en_US or C determine sort order. OK, so I guess you're saying that whatever was in the LC_COLLATE environment variable at the time the template0 database was created determines the collation/sort order? Is that stored and v

Re: [SQL] surrogate vs natural primary keys

2008-09-18 Thread Steve Midgley
At 08:20 AM 9/18/2008, [EMAIL PROTECTED] wrote: Message-ID: <[EMAIL PROTECTED]> Date: Wed, 17 Sep 2008 09:20:44 -0700 From: "Richard Broersma" <[EMAIL PROTECTED]> To: "Scott Marlowe" <[EMAIL PROTECTED]> Subject: Re: surrogate vs natural primary keys In-Reply-To: <[EMAIL PROTECTED]> References: