Re: [SQL] Learning about WITH RECURSIVE

2009-11-04 Thread Tom Lane
Richard Broersma writes: > Actually I'm still confused. I must me missing something. When I > manually following the directions of: > http://www.postgresql.org/docs/8.4/interactive/queries-with.html > I get the following when I try: > WITH RECURSIVE t(n) AS ( > VALUES (1) > UNION ALL >

Re: [SQL] Learning about WITH RECURSIVE

2009-11-04 Thread Richard Broersma
On Wed, Nov 4, 2009 at 2:11 PM, Tom Lane wrote: > Richard Broersma writes: >> Can anyone one explain why a "WITH RECURSIVE" query has the same >> results regardless whether UNION or UNION ALL is specified? > > Well, if the rows are all different anyway, UNION isn't going to > eliminate any ...

Re: [SQL] Learning about WITH RECURSIVE

2009-11-04 Thread Richard Broersma
On Wed, Nov 4, 2009 at 2:11 PM, Tom Lane wrote: > Richard Broersma writes: >> Can anyone one explain why a "WITH RECURSIVE" query has the same >> results regardless whether UNION or UNION ALL is specified? > > Well, if the rows are all different anyway, UNION isn't going to > eliminate any ... O

Re: [SQL] Learning about WITH RECURSIVE

2009-11-04 Thread Tom Lane
Richard Broersma writes: > Can anyone one explain why a "WITH RECURSIVE" query has the same > results regardless whether UNION or UNION ALL is specified? Well, if the rows are all different anyway, UNION isn't going to eliminate any ... regards, tom lane -- Sent via pgs

[SQL] Learning about WITH RECURSIVE

2009-11-04 Thread Richard Broersma
Can anyone one explain why a "WITH RECURSIVE" query has the same results regardless whether UNION or UNION ALL is specified? broersr=> WITH RECURSIVE t(n) AS ( broersr(> VALUES (1) broersr(> UNION broersr(> SELECT n+1 FROM t WHERE n < 100 broersr(> ) broersr-> SELECT sum(n) FROM t; sum

[SQL] Mangled high bit characters with pg_server_prepare=0 in DBD::Pg

2009-11-04 Thread Bryce Nesbitt
I've just tracked down a serious bug with PG_BYTEA columns, which is probably perl specific. But I thought people might want to know: Package: libdbd-pg-perl Version: upgrading to 2.8.7 compared to 1.49. Our 1.49 was patched for the memory leak in BYTEA. With 2.8.7, if pg_server_prepare is s

Re: [SQL] DDL problems: Referential issue?

2009-11-04 Thread Scott Marlowe
On Wed, Nov 4, 2009 at 1:14 PM, Leif Biberg Kristensen wrote: > On Wednesday 4. November 2009 21.03.26 Scott Marlowe wrote: >> On Wed, Nov 4, 2009 at 11:53 AM, Leif Biberg Kristensen >> > This looks strange to me, but it works: >> > >> > pgslekt=> CREATE TABLE participant_notes ( >> > pgslekt(>  

Re: [SQL] DDL problems: Referential issue?

2009-11-04 Thread Leif Biberg Kristensen
On Wednesday 4. November 2009 21.03.26 Scott Marlowe wrote: > On Wed, Nov 4, 2009 at 11:53 AM, Leif Biberg Kristensen > > This looks strange to me, but it works: > > > > pgslekt=> CREATE TABLE participant_notes ( > > pgslekt(> person_fk INTEGER NOT NULL, > > pgslekt(> event_fkINTEGER

Re: [SQL] DDL problems: Referential issue?

2009-11-04 Thread Scott Marlowe
On Wed, Nov 4, 2009 at 11:53 AM, Leif Biberg Kristensen wrote: > On Wednesday 4. November 2009 19.37.41 Scott Marlowe wrote: >> On Wed, Nov 4, 2009 at 11:36 AM, Leif Biberg Kristensen >> wrote: >>> I'd missed that particular syntax. >>> >>> This table is now without a primary key, but is that a p

Re: [SQL] DDL problems: Referential issue?

2009-11-04 Thread Leif Biberg Kristensen
On Wednesday 4. November 2009 19.37.41 Scott Marlowe wrote: > On Wed, Nov 4, 2009 at 11:36 AM, Leif Biberg Kristensen > wrote: >> I'd missed that particular syntax. >> >> This table is now without a primary key, but is that a problem? I don't >> expect it to grow beyond maybe a few thousand rows.

Re: [SQL] DDL problems: Referential issue?

2009-11-04 Thread Scott Marlowe
On Wed, Nov 4, 2009 at 11:36 AM, Leif Biberg Kristensen wrote: > On Wednesday 4. November 2009 19.24.29 Scott Marlowe wrote: >> You're referencing a single column, which does not have a unique key >> on it.  Being part of a two column unique PK index doesn't count, as >> you could have an entry wh

Re: [SQL] DDL problems: Referential issue?

2009-11-04 Thread Leif Biberg Kristensen
On Wednesday 4. November 2009 19.24.29 Scott Marlowe wrote: > You're referencing a single column, which does not have a unique key > on it. Being part of a two column unique PK index doesn't count, as > you could have an entry where one column or the other repeats on its > own while the other colu

Re: [SQL] DDL problems: Referential issue?

2009-11-04 Thread Scott Marlowe
On Wed, Nov 4, 2009 at 11:03 AM, Leif Biberg Kristensen wrote: > PostgreSQL 8.3.8 on Gentoo Linux. > > I've got a junction table: > > CREATE TABLE participants ( >    person_fk INTEGER REFERENCES persons (person_id), >    event_fk INTEGER REFERENCES events (event_id) ON DELETE CASCADE, >    sort_o

[SQL] DDL problems: Referential issue?

2009-11-04 Thread Leif Biberg Kristensen
PostgreSQL 8.3.8 on Gentoo Linux. I've got a junction table: CREATE TABLE participants ( person_fk INTEGER REFERENCES persons (person_id), event_fk INTEGER REFERENCES events (event_id) ON DELETE CASCADE, sort_order INTEGER NOT NULL DEFAULT 1, is_principal BOOLEAN NOT NULL DEFAULT