pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
add serial no
4+ messages / 4 participants
[nested] [flat]

* add serial no
@ 2004-08-22 09:47 Keith <siu_keith@hotmail.com>
  2004-08-24 18:43 ` Re: add serial no Uwe C. Schroeder <uwe@oss4u.com>
  2004-08-24 21:07 ` Re: add serial no Gaetano Mendola <mendola@bigfoot.com>
  2004-08-24 21:48 ` Re: add serial no Steve Crawford <scrawford@pinpointresearch.com>
  0 siblings, 3 replies; 4+ messages in thread

From: Keith @ 2004-08-22 09:47 UTC (permalink / raw)
  To: pgsql-admin

Dear All,

Someone can help me to solve the below problems

1. I create a table for a period of time, there is huge records already posted. 
I would like to alter table and add serial primary key on that table.  It's impossible to add serial no by hand. Please adv  how can I add the serial number automatically.

2. Is there any library to translate digit number to English, I mean translate '1234' to 'one thousand two hundred thirty four'.. Pls help.

regards

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: add serial no
  2004-08-22 09:47 add serial no Keith <siu_keith@hotmail.com>
@ 2004-08-24 18:43 ` Uwe C. Schroeder <uwe@oss4u.com>
  2 siblings, 0 replies; 4+ messages in thread

From: Uwe C. Schroeder @ 2004-08-24 18:43 UTC (permalink / raw)
  To: Keith <siu_keith@hotmail.com>; pgsql-admin

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sunday 22 August 2004 02:47 am, Keith wrote:
> Dear All,
>
> Someone can help me to solve the below problems
>
> 1. I create a table for a period of time, there is huge records already
> posted. I would like to alter table and add serial primary key on that
> table.  It's impossible to add serial no by hand. Please adv  how can I add
> the serial number automatically.

Create a table with the same structure as your original table, just add a

sid int4 serial

column to it. (sid can be any column name you want it to be) The serial column 
has to be at the end!
Then do a
INSERT INTO <new table> SELECT FROM <old table>

The serial column will automatically be set to 1,2,3,4,5 etc.
After that check if all the data made it to the new table and then you can 
drop the old table and rename the new table to the name of the old table.

As usual I'd make a backup of the original table before doing all this.

> 2. Is there any library to translate digit number to English, I mean
> translate '1234' to 'one thousand two hundred thirty four'.. Pls help.

Sorry, can't help with that one.


	UC

- --
Open Source Solutions 4U, LLC	2570 Fleetwood Drive
Phone:  +1 650 872 2425		San Bruno, CA 94066
Cell:   +1 650 302 2405		United States
Fax:    +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFBK4w8jqGXBvRToM4RAm5MAJ0c5ygo1V9ISdn7nOy51rHazWbnDACg0QBF
1nlwxalbrc8vVb64dxk7QgE=
=hi8w
-----END PGP SIGNATURE-----




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: add serial no
  2004-08-22 09:47 add serial no Keith <siu_keith@hotmail.com>
@ 2004-08-24 21:07 ` Gaetano Mendola <mendola@bigfoot.com>
  2 siblings, 0 replies; 4+ messages in thread

From: Gaetano Mendola @ 2004-08-24 21:07 UTC (permalink / raw)
  To: Keith <siu_keith@hotmail.com>

Keith wrote:

> Dear All,
>  
> Someone can help me to solve the below problems
>  
> 1. I create a table for a period of time, there is huge records already 
> posted.
> I would like to alter table and add serial primary key on that table.  
> It's impossible to add serial no by hand. Please adv  how can I add the 
> serial number automatically.


Just to inform you that with the future 8.0 postgresl version you can
do this task easily:

kalman=# select * from test;
 field_1
---------
       3
       5
       7
       6
       8
(5 rows)

kalman=# alter table test add column pk serial primary key;
NOTICE:  ALTER TABLE will create implicit sequence "test_pk_seq" for serial column "test.pk"
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "test_pkey" for table "test"
ALTER TABLE
kalman=# select * from test;
 field_1 | pk
---------+----
       3 |  1
       5 |  2
       7 |  3
       6 |  4
       8 |  5
(5 rows)




Regards
Gaetano Mendola











^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: add serial no
  2004-08-22 09:47 add serial no Keith <siu_keith@hotmail.com>
@ 2004-08-24 21:48 ` Steve Crawford <scrawford@pinpointresearch.com>
  2 siblings, 0 replies; 4+ messages in thread

From: Steve Crawford @ 2004-08-24 21:48 UTC (permalink / raw)
  To: Keith <siu_keith@hotmail.com>; pgsql-admin

On Sunday 22 August 2004 2:47 am, Keith wrote:
> Dear All,
>
> Someone can help me to solve the below problems
>
> 1. I create a table for a period of time, there is huge records
> already posted. I would like to alter table and add serial primary
> key on that table.  It's impossible to add serial no by hand.
> Please adv  how can I add the serial number automatically.
>
> 2. Is there any library to translate digit number to English, I
> mean translate '1234' to 'one thousand two hundred thirty four'..
> Pls help.
>
> regards

Going from memory (and note, this will only give you unique numbers - 
they won't be in any specific order)...

create sequence foo_sequence;

alter table foo add column (serialnumber bigint);

alter table foo alter column serialnumber set default 
nextval('foo_sequence');

update foo set serialnumber = nextval('foo_sequence') where 
serialnumber is null;

alter table foo alter column serialnumber set not null;

Cheers,
Steve




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2004-08-24 21:48 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-22 09:47 add serial no Keith <siu_keith@hotmail.com>
2004-08-24 18:43 ` Uwe C. Schroeder <uwe@oss4u.com>
2004-08-24 21:07 ` Gaetano Mendola <mendola@bigfoot.com>
2004-08-24 21:48 ` Steve Crawford <scrawford@pinpointresearch.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