Re: Using complex PRIMARY KEY

In response to A. Kretschmer :
> In response to Zsolt :
> >
> > This is our first project using PostgerSQL, where I have a problem I cant solve
> > on a neat way (I assume PGSQL should provide a nice solution...).
> >
> > So we have an old xBase based program we are trying to port to PostgreSQL while
> > we should keep the original data structure especially the ID fields must be
> > kept as this IDs are already used in other systems.
> >
> > The problem is with two table, one is storing the data of houses the other the
> > data of tenants in a given houses.
> >
> >
> >
> > For a given house I would like to start the numbering of tenants from 1. Each
> > house could have tenant_ID=1, obviously in this case the house_ID will differ.
> > The combination of tenant_ID and house_ID will be the unique identifier of each
> > tenant.
>
> Do you have PostgreSQL 8.4?
>
> If yes, you can use CTE-functions for that. row_number().
>
> Unfortunately, at the moment i haven't access to my database to create
> an example, maybe later.

Okay, i'm back and here my example:

test=*# select * from house;
id | name
----+--------
1 | house1
2 | house2
(2 rows)

test=*# select * from tenant;
id | house_id | name
----+----------+---------
1 | 1 | tenant1
2 | 1 | tenant2
3 | 1 | tenant3
4 | 2 | tenant4
5 | 2 | tenant5
6 | 2 | tenant6
7 | 2 | tenant7
(7 rows)

test=*# select id, house_id, row_number() over (partition by house_id) as tenant_house, name from tenant order by id, house_id;
id | house_id | tenant_house | name
----+----------+--------------+---------
1 | 1 | 1 | tenant1
2 | 1 | 2 | tenant2
3 | 1 | 3 | tenant3
4 | 2 | 1 | tenant4
5 | 2 | 2 | tenant5
6 | 2 | 3 | tenant6
7 | 2 | 4 | tenant7
(7 rows)

Hope that helps, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)