Re: Serial field
От | Jason Earl |
---|---|
Тема | Re: Serial field |
Дата | |
Msg-id | 20010712205740.9007.qmail@web10001.mail.yahoo.com обсуждение исходный текст |
Ответ на | Serial field (Francois Thomas <FrancoisT@alsdesign.fr>) |
Ответы |
RE: Serial field
|
Список | pgsql-novice |
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@alsdesign.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 > > -- > Fran�ois 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/
В списке pgsql-novice по дате отправления: