Gregory,
Thanks for the reply.
The data has been added via a file, as it is static data, no user input
for this table, and yes I have run the file many times.
I have accidently left some information out however that I believe will
probably add some light on this. The data has been added to this table
via an inherited table. Reference is the parent or base table, the
region table is the child table, I have inserted records into the region
table, and hence they go into the base table also. I never specify the
id column in my inserts it is a serial column.
I have since read some doco that postgres does not support referential
integrity with inherited tables (shame this, I love the feature), but
was'nt sure under what circumstance.
This is most likely why this is not working. This is perhaps not a bug
after all, appologies.
Tried your query with set enable_seqscan=off and then the result of the
query brings back the one row as expected.
I'll search around and try to find when and if inhertance will be
supported with RI.
Thanks again.
Regards
David
On Thu, 2007-06-28 at 22:48, Gregory Stark wrote:
> "David Boesch" <davidboesch@datasc.com.au> writes:
>
> > I add data to reference
> >
> > select * from reference shows as
> >
> > id | name | description
> > ----+----------------+--------------------------------
> > 11 | rd | road
> > 12 | st | street
> > 13 | way | way
> > 14 | close | close
> > 15 | bend | bend
> > 3 | vic | victoria
> > 4 | nsw | new south wales
> > 5 | qld | queensland
> > 6 | nt | northern territory
> > 7 | sa | south australia
>
> How did you add this data? Given that the ids are out of order I assume you've
> updated or deleted and re-inserted records a few times? That shouldn't break
> anything but it's possible the insert on table a doesn't see the same version
> of this table that you're looking at with the select.
>
> Also, just to check that there's nothing wrong with the inex, what do you get
> if you do:
>
> enable_seqscan = off;
> select * from reference where id = 7;
--
David Boesch
0410452873
95633008
9 Ferntree Gully Rd Oakleigh 3166