Thread: Records exactly the same.

I'm writing a program in Delphi.
In SQL I'm a beginner.

I have a file in which there are records with what people have done. In the file
are records with name, date, what they have done, time-length, etc. It is
possible that there are two records that are exactly the same (somebody has done
the same, on the same day for one hour). If I use a SELECT with GROUP on a list
with some records that are exactly the same, only ONE of the identicals is
shown. But I want to see them all.

It probably is possible; but how ?

Thanks,
Fons.
fonzzz@xs4all.nl

Fons,

> I have a file in which there are records with what people have done.
> In the file
> are records with name, date, what they have done, time-length, etc.
> It is
> possible that there are two records that are exactly the same
> (somebody has done
> the same, on the same day for one hour). If I use a SELECT with GROUP
> on a list
> with some records that are exactly the same, only ONE of the
> identicals is
> shown. But I want to see them all.

Well, there isn't an easy answer for you ... because you've designed
your database wrong.  Records should *never* be the same.  That is, ni
fact, one of the cardinal rules of Relational Database Design.

You need to add an arbitrary primary key (e.g. act_key SERIAL NOT NULL
PRIMARY KEY) or some other piece of data (such as start_time) to make
each record unique.  Then, by including that unique data in your query,
you can insure that all records are displayed.

Frankly, I'm surprise that you're not getting errors when you try to
DELETE a record.   This usually happens in tables without a unique key.

It's been my argument for some time that PostgreSQL should require a
primary key on table creation.

-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

Fons Rave wrote:
> 
> I'm writing a program in Delphi.
> In SQL I'm a beginner.
> 
> I have a file in which there are records with what people have done. In the file
> are records with name, date, what they have done, time-length, etc. It is
> possible that there are two records that are exactly the same (somebody has done
> the same, on the same day for one hour). If I use a SELECT with GROUP on a list
> with some records that are exactly the same, only ONE of the identicals is
> shown. But I want to see them all.
> 
> It probably is possible; but how ?
> 

First give us some information on your table structures. But it could be
something like:

select   person, activity, sum(hours)
from     activities
group by person, activity


Groet,

Nils
-- 
Alles van waarde is weerloos
Lucebert

If you include "oid" in your GROUP BY clause, you will get each distinct
record.
That will get you by for right now, but Josh's point is correct.  You need
some kind of unique key in your table.
But... if you want to see every distinct record: why are you using a GROUP
BY?

> -----Original Message-----
> From:    Josh Berkus [SMTP:josh@agliodbs.com]
> Sent:    Friday, July 20, 2001 9:48 AM
> To:    Fons Rave; pgsql-sql@postgresql.org
> Subject:    Re: Records exactly the same.
> 
> Fons,
> 
> > I have a file in which there are records with what people have done.
> > In the file
> > are records with name, date, what they have done, time-length, etc.
> > It is
> > possible that there are two records that are exactly the same
> > (somebody has done
> > the same, on the same day for one hour). If I use a SELECT with GROUP
> > on a list
> > with some records that are exactly the same, only ONE of the
> > identicals is
> > shown. But I want to see them all.
> 
> Well, there isn't an easy answer for you ... because you've designed
> your database wrong.  Records should *never* be the same.  That is, ni
> fact, one of the cardinal rules of Relational Database Design.
> 
> You need to add an arbitrary primary key (e.g. act_key SERIAL NOT NULL
> PRIMARY KEY) or some other piece of data (such as start_time) to make
> each record unique.  Then, by including that unique data in your query,
> you can insure that all records are displayed.
> 
> Frankly, I'm surprise that you're not getting errors when you try to
> DELETE a record.   This usually happens in tables without a unique key.
> 
> It's been my argument for some time that PostgreSQL should require a
> primary key on table creation.
> 
> -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 << File:  >>  <<
> File:  >>  << File:  >>  << File: ATT09948.txt >> 

> Well, there isn't an easy answer for you ... because you've designed
> your database wrong.  Records should *never* be the same.  That is, ni
> fact, one of the cardinal rules of Relational Database Design.

Well, I started with "I'm a beginner". But I'm sure there's no reason NOT to
accept two records that are exactly the same. In the example I gave, it is clear
that the information I want to store can contain two records that are exactly
the same; doing the same thing, on the same day, for the same amount of time. In
this case it is the technical structure that doesn't want it like that. So I
have to change it to make it work.

Fons.
fonzzz@xs4all.nl

"Fons Rave" <fonzzz@xs4all.nl> wrote in message
news:9jbrpj$r67$1@news1.xs4all.nl...
> > Well, there isn't an easy answer for you ... because you've designed
> > your database wrong.  Records should *never* be the same.  That is, ni
> > fact, one of the cardinal rules of Relational Database Design.
>
> Well, I started with "I'm a beginner". But I'm sure there's no reason NOT
to
> accept two records that are exactly the same. In the example I gave, it is
clear
> that the information I want to store can contain two records that are
exactly
> the same; doing the same thing, on the same day, for the same amount of
time. In
> this case it is the technical structure that doesn't want it like that. So
I
> have to change it to make it work.

OK - so you enter two records, one hour in the morning and one hour in the
afternoon. You then realise you *didn't* work in the afternoon and you want
to delete one of the records. You can't because you can't build a query that
specifies the one without the other. You can't delete only one, you can't
update only one and you can't select only one. This is because the only way
to pick out a piece of data in SQL is by it's values - there is no "file
position" or "record number" (well there's OID but you're best off leaving
that).

If you want to store two separate pieces of information, make sure they
differ in some way (timestamp, serial-number, whatever). If you can't think
of anything useful to distinguish between records add a SERIAL column called
"id".

If you don't *want* two records, but just want the total hours worked that
day, then restrict your system to one record per person (or job or whatever
it was) per day. You can either do this in your application, or (better but
harder) do it with triggers in PostgreSQL itself.

If you can't quite see where the problem is at the present time, just add an
"id" or "seq" field of type SERIAL and get on with your project. The reason
people on the list are shouting is because we got burnt at some time with
exactly this thing and we're trying to stop that happening to you.

Oh - check the glossary at techdocs.postgresql.org and look at "Normal
Forms" (numbered 1st NF, 2nd NF etc). Check out a book on RDBMS theory too
if you get a chance.

HTH

- Richard Huxton

The content of your record is not limited to user data.  Including a field
that provides a unique key is simple: look at the documentation for data
type SERIAL for an easy way to do this.
You can also include information about when the record was inserted, and by
whom, just by including fields in your table definition like:
when_inserted    timestamp default now(),
who_inserted    text default current_user,
which will be populated automatically every time a record is inserted.
Also, see documentation on triggers for more sophisticated ways of doing
this kind of thing.

> -----Original Message-----
> From:    Fons Rave [SMTP:fonzzz@xs4all.nl]
> Sent:    Saturday, July 21, 2001 7:15 AM
> To:    pgsql-sql@postgresql.org
> Subject:    Re: Records exactly the same.
> 
> > Well, there isn't an easy answer for you ... because you've designed
> > your database wrong.  Records should *never* be the same.  That is, ni
> > fact, one of the cardinal rules of Relational Database Design.
> 
> Well, I started with "I'm a beginner". But I'm sure there's no reason NOT
> to
> accept two records that are exactly the same. In the example I gave, it is
> clear
> that the information I want to store can contain two records that are
> exactly
> the same; doing the same thing, on the same day, for the same amount of
> time. In
> this case it is the technical structure that doesn't want it like that. So
> I
> have to change it to make it work.
> 
> Fons.
> fonzzz@xs4all.nl
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

Fons,

> Well, I started with "I'm a beginner". But I'm sure there's no reason
> NOT to
> accept two records that are exactly the same. In the example I gave,
> it is clear
> that the information I want to store can contain two records that are
> exactly
> the same; doing the same thing, on the same day, for the same amount
> of time. In
> this case it is the technical structure that doesn't want it like
> that. So I
> have to change it to make it work.

Yes, there is a reason not to accept them.  And the requirement is
conceptual, not technical -- that is, the way relational databases work
-- *all* relational databases -- is founded on 12 concepts, one of which
is the uniqueness of relations (records).

Or, to phrase it another way, if your design allows identical records
(person, task, time period) then how are *you* going to seperate
illegitimate duplicate data-entry from legitimate identical records?
You can't, and the questionable accuracy of your tables will haunt you
for years.

I'm trying to save you months of pain & suffering here by conveying a
very important concept in database design, one I learned the hard way.

For a more exhaustive explanation of the necessity of uniqueness and
primary keys, please pick up a copy of Fabian Pascal's "Practical Issues
in Database Design."

-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