Обсуждение: Serial field

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

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/

Jason and Francois:

Jason: That's not how I interepreted Francois' request,
but it's  a clever approach to what sounds like a common
need in database design. And yes, using a recent version
of PostgreSQL is a good idea!

Francois: It sounds like you just want an
auto-increment field but you don't want to
values you've already have in the table.

I think your best option is to do create the table
first just using standard INTEGERs and import your
data into the table. Then do an ALTER TABLE and
change the table design.

I don't have a convienent way to try it right now
but that should work.

If it doesn't, you count always just create your
own pgplsql stored procedure that works like
a standard sequence. As far as I can tell, when
you create a SERIAL type, that's what happens
anyway. The table definition ends up having
a DEFAULT value for the serial field, which
is a function call to nextval('tablename_fieldname_seq');

You could implement the same code yourself and just
not "turn on" the function until after your original
data is entered.

Hope that helps.

-Robby

> 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
>
> --
> Frangois 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/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

I actually was torn in my interpretation between the
question that I answered and the one that you
answered.  Since I wasn't sure what the problem was I
chose to answer the question that I thought most
interesting.

If you are correct and Francois simply needs to be
able to import data from another source and "then"
have future values auto increment then that is also
quite possible.  In fact, if you used versions of
PostgreSQL before the SERIAL type was available you
would already know the answer on how to import data
that already has information in the field that you
want to have become auto increment field.

The secret lies in how the SERIAL type is implemented
in PostgreSQL.  For example if I were to create the
table orgs like this:

processdata=> CREATE TABLE orgs (id serial, name
char(10));
NOTICE:  CREATE TABLE will create implicit sequence
'orgs_id_seq' for SERIAL column 'orgs.id'
NOTICE:  CREATE TABLE/UNIQUE will create implicit
index 'orgs_id_key' for table 'orgs'
CREATE

You will notice that the command also creates a
sequence orgs_id_seq.  This sequence is where orgs
gets its values for orgs.id.  Once you know how the
serial type actually works it becomes straightforward
to simply create the table like:

processdata=> CREATE TABLE orgs (id int primary key,
name char(10));
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit
index 'orgs_pkey' for table 'orgs'
CREATE

Once the table is created you can easily import your
data in whatever manner makes you the happiest.  Once
your data is imported it is a simple manner to find
out the largest value for orgs.id (or whatever) with a
select statement like this:

SELECT max(id) FROM orgs;

Then create a new sequence with a start value one
higher than the value that is returned:

CREATE SEQUENCE orgs_id_seq START <value>;

Once you have got a sequence then you simply alter the
table so that it gets it's default values from that
sequence:

alter table orgs alter id set default
nextval('orgs_id_seq');

Presto, you have just created an auto increment field
from the ground up.

Hope this is helpful,
Jason

--- Robby Slaughter <webmaster@robbyslaughter.com>
wrote:
> Jason and Francois:
>
> Jason: That's not how I interepreted Francois'
> request,
> but it's  a clever approach to what sounds like a
> common
> need in database design. And yes, using a recent
> version
> of PostgreSQL is a good idea!
>
> Francois: It sounds like you just want an
> auto-increment field but you don't want to
> values you've already have in the table.
>
> I think your best option is to do create the table
> first just using standard INTEGERs and import your
> data into the table. Then do an ALTER TABLE and
> change the table design.
>
> I don't have a convienent way to try it right now
> but that should work.
>
> If it doesn't, you count always just create your
> own pgplsql stored procedure that works like
> a standard sequence. As far as I can tell, when
> you create a SERIAL type, that's what happens
> anyway. The table definition ends up having
> a DEFAULT value for the serial field, which
> is a function call to
> nextval('tablename_fieldname_seq');
>
> You could implement the same code yourself and just
> not "turn on" the function until after your original
> data is entered.
>
> Hope that helps.
>
> -Robby
>
> > 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
> >
> > --
> > Frangois 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/
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
> ---------------------------(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/

Hi,

does anybody know, what happens, if a client-app. generates it's own id
like:

INSERT INTO orgs VALUES (
(SELECT max(id)+1 from org),
'orgname');

Will the next insert of just a name throw an error? (duplicate value on
primary key?)
I never tried that. But I do have a postgresql server accessed by two
clients. An old one (generating it's own id's) an a newer one (using DEFAULT
values). So I wrote my own function with pl/pgsql to generate my id's.
If I could handle the duplicate values with a sequenc, maybe it would be the
easier way.

kind regards,
Andre
----- Original Message -----
From: Jason Earl
To: webmaster@robbyslaughter.com ; Francois Thomas ;
pgsql-novice@postgresql.org
Sent: Friday, July 13, 2001 3:39 AM
Subject: RE: [NOVICE] Serial field
......
....Once you know how the
serial type actually works it becomes straightforward
to simply create the table like:

processdata=> CREATE TABLE orgs (id int primary key,
name char(10));
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit
index 'orgs_pkey' for table 'orgs'
CREATE

Once the table is created you can easily import your
data in whatever manner makes you the happiest.  Once
your data is imported it is a simple manner to find
out the largest value for orgs.id (or whatever) with a
select statement like this:

SELECT max(id) FROM orgs;

Then create a new sequence with a start value one
higher than the value that is returned:

CREATE SEQUENCE orgs_id_seq START <value>;

Once you have got a sequence then you simply alter the
table so that it gets it's default values from that
sequence:

alter table orgs alter id set default
nextval('orgs_id_seq');

Presto, you have just created an auto increment field
from the ground up.

Hope this is helpful,
Jason

Francois,
    It sounds like you want your migrated data to keep their sequence
values but have new records have n+1 as the sequence value where n is the
largest previous sequence value.  Here is how I would approach this problem.
Create a new table with a serial(sequence) and other fields.  Write a perl
script to read and sort your migration data by sequence number and then
output a sql command file to insert the migrated data 1 row at a time
setting the sequence nextval appropriately before each insert.  Run the sql
file via psql.  Now your table has the migrated data with correct sequence
values and future inserts will work normally.

Thanks,
Dale


> --- 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/
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

Ok, I was directed that I would be taking over an old mac based database
and was told that we would be moving to a SQL based DB.  I looked into
MySQL (sorry, I know thats a bad word around these parts :) )and sorta
got into it.  Well, I saw the light and have decided to move to
PstgreSQL.  I have downloaded and installed on my SuSE linux server the
latest version (7.1.2) and got it to work... But I have noticed some
differences (duh) that I can't find answers for in the online docs.
In the command line interface in MySQL, if I wanted to see all the
databases on my server, or if I wanted to see all the tables in a given
database, I would type "show databases" or "show tables"  but that
doesnt seem to work in PostgreSQL...

Also, is there a painless way to get all of my MySQL databases and data
moved over to PostgreSQL?

thanks!
-Jim

Hi,

for documentation of the command line interface to PostgreSQL you may look
at the docs for psql (client applications)
http://www.postgresql.org/idocs/index.php?app-psql.html

One of the commands, you are looking for, is "\d". Equivalent to "show
tables".

More informations can be found in the system catalogs (or system tables) of
PostgreSQL.
docs are at:
http://www.postgresql.org/idocs/index.php?catalogs.html

So "select * from pg_database;" should be equivalent to MySQL's "show
databases"

CU,
Andre

----- Original Message -----
From: jim davis
To: 'pgsql-novice@postgresql.org'
Sent: Friday, July 13, 2001 5:44 PM
Subject: [NOVICE] Silly newbie questions


Ok, I was directed that I would be taking over an old mac based database
and was told that we would be moving to a SQL based DB.  I looked into
MySQL (sorry, I know thats a bad word around these parts :) )and sorta
got into it.  Well, I saw the light and have decided to move to
PstgreSQL.  I have downloaded and installed on my SuSE linux server the
latest version (7.1.2) and got it to work... But I have noticed some
differences (duh) that I can't find answers for in the online docs.
In the command line interface in MySQL, if I wanted to see all the
databases on my server, or if I wanted to see all the tables in a given
database, I would type "show databases" or "show tables"  but that
doesnt seem to work in PostgreSQL...

Also, is there a painless way to get all of my MySQL databases and data
moved over to PostgreSQL?

thanks!
-Jim


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

On Fri, Jul 13, 2001 at 08:44:44AM -0700, some SMTP stream spewed forth:
> Ok, I was directed that I would be taking over an old mac based database
> and was told that we would be moving to a SQL based DB.  I looked into
> MySQL (sorry, I know thats a bad word around these parts :) )and sorta
> got into it.  Well, I saw the light and have decided to move to
> PstgreSQL.

Congratulations, you have found the One True Database. :)

> I have downloaded and installed on my SuSE linux server the
> latest version (7.1.2) and got it to work... But I have noticed some
> differences (duh) that I can't find answers for in the online docs.

> In the command line interface in MySQL, if I wanted to see all the
> databases on my server, or if I wanted to see all the tables in a given
> database, I would type "show databases" or "show tables"  but that
> doesnt seem to work in PostgreSQL...

Databases: select * from pg_database
Tables can be shown by \dt.
Look into the 'backslash' commands in psql (i.e. refer to \?).
You might also gain something from the psql manpage.
Starting psql with -E will show the actual queries used in the backslash
commands.

> Also, is there a painless way to get all of my MySQL databases and data
> moved over to PostgreSQL?

I know there have been some efforts, and I think there are some completed
systems, but I am not familiar with these.

> thanks!
> -Jim

gh
--
> What, no one sings along with Ricky Martin anymore?
My kid sister does (but then, she prefers pico to vi ...)
    -- Suresh Ramasubramanian, alt.sysadmin.recovery

Just wanted to chime in here, I'm not on the mailing list so please cc me
any replies.

The techniques you have described I am surprised to not hear the solution
I am employing:

 =# create table foo (id int default nextval('foo_seq'::text),
 (# data text);
CREATE
 =# create sequence foo_seq;
CREATE
 =#

Then if you insert data, the default value will be a sequence that has
several properties to it:

=# \d foo_seq
   Sequence "foo_seq"
   Attribute   |  Type
---------------+---------
 sequence_name | name
 last_value    | integer
 increment_by  | integer
 max_value     | integer
 min_value     | integer
 cache_value   | integer
 log_cnt       | integer
 is_cycled     | "char"
 is_called     | "char"

=# select * from foo_seq;
 sequence_name | last_value | increment_by | max_value  | min_value | cache_value | log_cnt | is_cycled | is_called
---------------+------------+--------------+------------+-----------+-------------+---------+-----------+-----------
 foo_seq       |          1 |            1 | 2147483647 |         1 |           1 |       1 | f         | f
(1 row)

=# insert into foo (data) values ('fee');
INSERT 107739 1
=# insert into foo (data) values ('fie');
INSERT 107740 1
=# insert into foo (data) values ('fo');
INSERT 107741 1
=# insert into foo (data) values ('fum');
INSERT 107742 1
=# select * from foo;
 id | data
----+------
  1 | fee
  2 | fie
  3 | fo
  4 | fum
(4 rows)

=# select * from foo_seq;
 sequence_name | last_value | increment_by | max_value  | min_value | cache_value | log_cnt | is_cycled | is_called
---------------+------------+--------------+------------+-----------+-------------+---------+-----------+-----------
 foo_seq       |          4 |            1 | 2147483647 |         1 |           1 |      30 | f         | t
(1 row)

=#

I believe this is how things were meant to be, no?
--
Todd Fries .. todd@fries.net