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
Let's say that you had a table that looked like this:
CREATE TABLE foo (
rev int default 0,
And you created a function and trigger that looked
CREATE FUNCTION update_rev() RETURNS OPAQUE AS '
NEW.rev := NEW.rev + 1;
' LANGUAGE 'plpgsql';
CREATE TRIGGER foo_update_rev BEFORE INSERT OR UPDATE
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
--- 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
> 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
> 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
> I hope my english is not too obscure !
> Any advice will be welcome
> Franois THOMAS
> ---------------------------(end of
> TIP 6: Have you searched our list archives?
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
In response to
pgsql-novice by date
|Next:||From: Robby Slaughter||Date: 2001-07-12 23:48:10|
|Subject: RE: Serial field|
|Previous:||From: SuperDutyTA73||Date: 2001-07-12 19:47:16|
|Subject: Best formal training for PostgreSQL use?|