pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
serial type question 3+ messages / 3 participants [nested] [flat]
* serial type question @ 2001-03-19 17:41 postgresql <pgsql@symcom.com> 0 siblings, 2 replies; 3+ messages in thread From: postgresql @ 2001-03-19 17:41 UTC (permalink / raw) To: pgsql-sql 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: serial type question @ 2001-03-19 19:34 Stephan Szabo <sszabo@megazone23.bigpanda.com> parent: postgresql <pgsql@symcom.com> 1 sibling, 0 replies; 3+ messages in thread From: Stephan Szabo @ 2001-03-19 19:34 UTC (permalink / raw) To: postgresql <pgsql@symcom.com>; +Cc: pgsql-sql 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. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: serial type question @ 2001-03-19 23:11 Richard Huxton <dev@archonet.com> parent: postgresql <pgsql@symcom.com> 1 sibling, 0 replies; 3+ messages in thread From: Richard Huxton @ 2001-03-19 23:11 UTC (permalink / raw) To: postgresql <pgsql@symcom.com>; +Cc: pgsql-sql 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-03-19 23:11 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2001-03-19 17:41 serial type question postgresql <pgsql@symcom.com> 2001-03-19 19:34 ` Stephan Szabo <sszabo@megazone23.bigpanda.com> 2001-03-19 23:11 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox