Thread: serial type question

I have a table that I want to add a serial type column. Is there a way 
to add it or do I have to create a new table and insert into it. I have 
experimented with:

insert into newdb (name) select name from olddb order by jobno;

however, pg does not allow the 'order by' during an insert/select

I am on version 7.0.3

Maybe I don't need to do this. What I am trying to accomplish is to 
have PG create job numbers. Today, I only have 2 workstations that 
push jobs into the server and PG tracks the job number. However, I 
have been informed that in the next 6-8 months the number of job 
creation workstations will grow to 8 - 10. 

I would like to migrate to a job number created when the insert is 
done.

Thanks for  your  help.

Ted Petrosky

On Mon, 19 Mar 2001, postgresql wrote:

> I have a table that I want to add a serial type column. Is there a way 
> to add it or do I have to create a new table and insert into it. I have 
> experimented with:
> 
> insert into newdb (name) select name from olddb order by jobno;
> 
> however, pg does not allow the 'order by' during an insert/select
> 
> I am on version 7.0.3

The type of query above seems to work in 7.1, but...

> Maybe I don't need to do this. What I am trying to accomplish is to 
> have PG create job numbers. Today, I only have 2 workstations that 
> push jobs into the server and PG tracks the job number. However, I 
> have been informed that in the next 6-8 months the number of job 
> creation workstations will grow to 8 - 10. 
> 
> I would like to migrate to a job number created when the insert is 
> done.

Because serial only is a column with a default gathered from a sequence,
if you can just use the jobno's from the old table, I'd say you
just might want to make it, load the jobno's you already have and set the
sequence value higher than those, new rows will get new automatic jobno's
and the old ones will keep their values.

postgresql wrote:
> 
> I have a table that I want to add a serial type column. Is there a way
> to add it or do I have to create a new table and insert into it. I have
> experimented with:
> 
> insert into newdb (name) select name from olddb order by jobno;
> 
> however, pg does not allow the 'order by' during an insert/select

Doesn't make sense on an insert - you want an alter table followed by an
update. An insert will add new rows, not just add values to a column.

> I am on version 7.0.3
> 
> Maybe I don't need to do this. What I am trying to accomplish is to
> have PG create job numbers. Today, I only have 2 workstations that
> push jobs into the server and PG tracks the job number. However, I
> have been informed that in the next 6-8 months the number of job
> creation workstations will grow to 8 - 10.

A serial type is basically nothing more than a sequence with a column
that uses the sequence as a default value. There's more on this in the
docs (a couple of lines in the notes I wrote off
techdocs.postgresql.org) but the simplest way to see how it works is to
define a table foo with a serial in it and do a \d or pg_dump it to see
how it works.

> I would like to migrate to a job number created when the insert is
> done.

Once you see how it works, pg_dump the database, edit the file and
re-import the data. Nice clean solution and easy to cope with if
something goes wrong.

- Richard Huxton