From: Josh Berkus <josh@agliodbs.com>
To: Jeff Self <jocknerd@home.com>
To: Carolyn Lu Wong <carolyn@greatpacific.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: weird duplicate data problem
Date: Fri, 14 Dec 2001 11:22:30 -0800
Message-ID: <web-529011@davinci.ethosmedia.com> (raw)
In-Reply-To: <Pine.LNX.4.31.0112141339070.11712-100000@mulder.self.hom>
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 information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, jocknerd@home.com, carolyn@greatpacific.com.au
Subject: Re: weird duplicate data problem
In-Reply-To: <web-529011@davinci.ethosmedia.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox