Re: [SQL] Mail about duplicate rows - Mailing list pgsql-sql

Dear Jackson and Michael,
   Thanks very much for your help the query worked. Also can you help in one
more way. It took a lot of time to update the radius table around 5 minutes, the
size of the table was around 360 KB's only, so could you suggest a way of
improving the performance since if i am having a table of around 120 - 165 MB it
does not take much time to update the radius table.

Thanks for your help once again.

Regards,
Vicky


"Jackson, DeJuan" wrote:

> Then you would use:
> INSERT INTO radius
> SELECT * FROM raduistemp r1
> WHERE NOT EXISTS(SELECT 1 FROM radius r2
>     WHERE r1.uname=r2.uname AND r1.logdate=r2.logdate AND
> r1.logtime=r2.logtime);
>         -DEJ
>
> > -----Original Message-----
> > From: Vikrant Rathore [SMTP:vikrant@chemquick.com]
> > Sent: Monday, June 07, 1999 12:37 AM
> > To:   Fomichev Michael
> > Cc:   pgsql-sql@postgreSQL.org
> > Subject:      Re: [SQL] Mail about duplicate rows
> >
> > Thanks for your help. But as you know the UNAME is not unique in radius
> > logs. The
> > primary key for this table is (Uname,logdate,logtime).
> >
> > So this three fields together forms a primary key of the table.
> >
> > Thanks & regards,
> > Vicky
> >
> >
> > Fomichev Michael wrote:
> >
> > > On Mon, 7 Jun 1999, Vikrant Rathore wrote:
> > >
> > > > I am trying to append a table radius from another table radiustemp,
> > both
> > > > having the same structure, but want to append only those tuples from
> > > > radiustemp which are not there in radius. The size of the table can be
> > > > up to 165 MB.
> > > > So anyone can suggest me a better way of doing this.
> > > >
> > > > The structure if table is like this .
> > > >
> > > > Table    = radius
> > > >
> > +----------------------------------+----------------------------------+---
> > ----+
> > > >
> > > > |              Field               |              Type
> > |
> > > > Length|
> > > >
> > +----------------------------------+----------------------------------+---
> > ----+
> > > >
> > > > | uname                            | char()
> > > > |   256 |
> > > > | logdate                          | date
> > > > |     4 |
> > > > | logtime                          | time
> > > > |     8 |
> > > > | duration                         | int4
> > > > |     4 |
> > > > | status                           | char()
> > > > |    20 |
> > > > | nasadd                           | char()
> > > > |    20 |
> > > > | port                             | int4
> > > > |     4 |
> > > > | bytesin                          | int4
> > > > |     4 |
> > > > | bytesout                         | int4
> > > > |     4 |
> > > > | packin                           | int4
> > > > |     4 |
> > > > | packout                          | int4
> > > > |     4 |
> > > > | misc                             | int4
> > > > |     4 |
> > > >
> > +----------------------------------+----------------------------------+---
> > ----+
> > > >
> > > > Thank's in advance for your help.
> > > >
> > > > Thanks & regards,
> > > > Vicky
> > > >
> > > insert into RADIUS select * from RADIUSTEMP where UNAME not in (select
> > > UNAME from RADIUS)
> > >
> > > UNAME must be unique. If it is not, than you need to use another field
> > > (for example "code") which will be unique.
> > >
> > > P.S. Nice book about SQL: "Understanding SQL" by Martin Gruber. ISBN
> > > 5-85582-010-6
> > >
> > > @------------------+-----------------------------------------------@
> > > | Fomichev Mikhail | The Government of Kamchatka region.           |
> > > | Vladimirovich    | The Labour and Social Development Department. |
> > > |<fomichev@null.ru>|                                               |
> > > @------------------+-----------------------------------------------@
> >