Re: Serial field

I am not entirely sure I understand what you mean, but
I think I understand, so I am going to give answering
your question a shot. But first of all I would highly
recommend using a newer version of PostgreSQL than
6.5.2. The latest stable version is 7.1.2 and it is
literally light years ahead of 6.5.2.

That being said the trick that I will outline should
work for a version 6.5 PostgreSQL database. At least
variations of this trick worked for me when I was
using a PostgreSQL version 6.5.3.

First off it appears to me that what you want is a
column that keeps track of the "revision level" of a
particular row. Every time the row got updated it's
particular revision number should increment. There
isn't a PostgreSQL type that does this for you, but it
is fairly easy to do using triggers and a simple
function.

Let's say that you had a table that looked like this:

CREATE TABLE foo (
rev int default 0,
name char(10)
);

And you created a function and trigger that looked
like this:

CREATE FUNCTION update_rev() RETURNS OPAQUE AS '
BEGIN
NEW.rev := NEW.rev + 1;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER foo_update_rev BEFORE INSERT OR UPDATE
ON foo
FOR EACH ROW EXECUTE PROCEDURE update_rev();

Now if you inserted a record into foo without
specifically setting rev like this:

INSERT INTO foo (name) VALUES ('Jason');

Your database would contain the following row:

rev | name
-----+------------
1 | Jason

So far so good. New rows added to the database would
automagically get a rev of 1. More importantly if you
decided to update this particular record with an
update statement like:

UPDATE foo SET name = 'Francois' WHERE name = 'Jason'

Your database would look like this. Note that when
the row was updated it triggered our trigger and
incremented rev by one.

rev | name
-----+------------
2 | Francois

Hopefully this is what you had in mind. If not please
try and restate your question with a little more
detail.

Take Care,
Jason

--- Francois Thomas <FrancoisT(at)alsdesign(dot)fr> wrote:
>
> Hello to all
>
> Unable to find an answer by searching the list
> archive, I ask my question
> here:
> I want to migrate a database to PostgreSQL 6.5.2
> I need an auto-increment field. I first tried the
> SERIAL type, but it
> doesn't fit my needs.
> In fact, I want to:
> 1/ keep the original INT value of my existing
> records from another server
> (say, first row can be "1", second "3", next one
> "17",...)
> 2/ have an automatic incrementation of the last
> value for a new record. For
> exemple, with the values above and a SERIAL field,
> the default value for a
> new field would be "1" (first use of the sequence)
> instead of "18" (last
> value+1)..
> I hope my english is not too obscure !
> Any advice will be welcome
> Regards
>
> --
> Franois THOMAS
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl

__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/