Re: weird duplicate data problem - Mailing list pgsql-sql

Carolyn, Jeff,

> > a table was created with:
> >
> > create table bills (
> >  id  serial not null,
> >  account_no  int4 not null,
> >  date_issued date not null,
> >  .....
> > );

> There is no reason to set id to not null. It is a serial field. It
> will
> automatically increase. Your account_no should probably be set to a
> char
> field. My old rule of thumb is not to make something int unless you
> plan
> to perform some type of math on it.

I have to disagree with this, Jeff.  A six-digit integer takes up only
20% of the storage space of a 6-character string.  As such, it will be
faster to index and faster to retrieve, and faster to perform JOINs and
sorts.  So if Carolyn will never want to store anything but numbers in
the field, integer is ideal.

> > The following SQL script returns only 1 row of data:
> >
> >  select oid, * from bills where id = xxxxx' order by oid;
> >
> If your id field is serial, all the records will have unique id's in
> them.
> Therefore you are only going to get one row of data.

Yup.  Why, BTW, is Carloyn interested in the OID?  It is not a useful
piece of information for query purposes ... the OID is a tool for the
Postgres DB engine and should not be used as data, since it may change
unexpectedly.

> > However with the following SQL:
> >
> >  select oid, * from bills where date_issued = '01/01/2001'
> >  order by oid;
> >
> 
> Can't help you here. I'm running 7.1.3 and the query works fine.

Try date_issued = '2001-01-01'.  Some platforms (Unix for one) get fussy
about using universal date format.

> > some of the data is duplicated, including the OID.
> >
> > When trying to 'vaccum' the database, get
> >
> > NOTICE:  Index bills_id_key: number of index tupples (4755) is not
> the
> > same as heap (7800).
> >
> > What is happening? What could have caused this?
> >
> > Database version used is V6.50. I know it's old, but i hope it's
> not the
> > version that's causing this problem.

Sorry, Carolyn, can't help you on Vacuum problems unless you upgrade.
6.5 does indeed have several major problems with Vacuum; time to get
7.1.3.  The core team ain't releasing new versions for the fun alone,
y'know!

-Josh

______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco