Re: weird duplicate data problem

On Fri, 14 Dec 2001, Carolyn Lu Wong wrote:

> 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.

>
> 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.

>
>
> 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.

> 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.
>
> Thanks in advance for any suggestions.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

-- 
Jeff Self
Powered by Debian GNU/Linux
http://members.home.net/jocknerd