Thread: add serial no
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
-----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-----
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
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
Pretty basic question. Is it necessary to add NOT NULL or UNIQUE NOT
NULL to SERIAL or is this implicit and unnecessary?
ie.
CREATE TABLE new_table (
id SERIAL UNIQUE NOT NULL,
description TEXT NOT NULL
);
Or should I just use below because Serial type implies this.
CREATE TABLE new_table (
id SERIAL,
description TEXT NOT NULL
);
Regards,
David
On Sat, May 28, 2005 at 14:27:17 -0300, David Pratt <fairwinds@eastlink.ca> wrote: > Pretty basic question. Is it necessary to add NOT NULL or UNIQUE NOT > NULL to SERIAL or is this implicit and unnecessary? Serials no longer generate a uniqie index by default. So in practice you will normally want to declare them as PRIMARY KEYs. However there are cases where you don't need this and the index is extra overhead.
On Saturday, May 28, 2005, at 03:27 PM, Bruno Wolff III wrote:
> On Sat, May 28, 2005 at 14:27:17 -0300,
> David Pratt <fairwinds@eastlink.ca> wrote:
>> Pretty basic question. Is it necessary to add NOT NULL or UNIQUE NOT
>> NULL to SERIAL or is this implicit and unnecessary?
>
> Serials no longer generate a uniqie index by default. So in practice
> you will normally want to declare them as PRIMARY KEYs. However there
> are cases where you don't need this and the index is extra overhead.
>
Alright. so would it be better form for me to to this in a
create_tables.sql
CREATE TABLE new_table (
id SERIAL,
description TEXT NOT NULL
);
And then in a create_primary_keys.sql do this for the tables requiring
it.
ALTER TABLE new_table ADD CONSTRAINT new_table_pkey PRIMARY KEY (id);
Does the PRIMARY KEY declaration ensure that the id values are unique?
Serial should always give me an incremented value that's different so I
am assuming it is unnecessary to use UNIQUE. Am I correct?
Regards,
David
On Sat, May 28, 2005 at 16:18:30 -0300,
David Pratt <fairwinds@eastlink.ca> wrote:
>
> On Saturday, May 28, 2005, at 03:27 PM, Bruno Wolff III wrote:
>
> >On Sat, May 28, 2005 at 14:27:17 -0300,
> > David Pratt <fairwinds@eastlink.ca> wrote:
> >>Pretty basic question. Is it necessary to add NOT NULL or UNIQUE NOT
> >>NULL to SERIAL or is this implicit and unnecessary?
> >
> >Serials no longer generate a uniqie index by default. So in practice
> >you will normally want to declare them as PRIMARY KEYs. However there
> >are cases where you don't need this and the index is extra overhead.
> >
>
> Alright. so would it be better form for me to to this in a
> create_tables.sql
>
> CREATE TABLE new_table (
> id SERIAL,
Its simpler to use:
id SERIAL PRIMARY KEY,
> description TEXT NOT NULL
> );
>
> And then in a create_primary_keys.sql do this for the tables requiring
> it.
>
> ALTER TABLE new_table ADD CONSTRAINT new_table_pkey PRIMARY KEY (id);
>
> Does the PRIMARY KEY declaration ensure that the id values are unique?
> Serial should always give me an incremented value that's different so I
> am assuming it is unnecessary to use UNIQUE. Am I correct?
PRIMARY KEY implies UNIQUE and NOT NULL and in Postgres will result in a
unique index being created to enforce this. It will also make id the
default column in new_table for foreign key references to that table.
On May 28, 2005, at 1:27 PM, David Pratt wrote: > CREATE TABLE new_table ( > id SERIAL UNIQUE NOT NULL, > description TEXT NOT NULL > ); > > Or should I just use below because Serial type implies this. > > CREATE TABLE new_table ( > id SERIAL, > description TEXT NOT NULL > ); The first one because it is possible for the serial to wrap around or some other bad thing could happen if the serial is accidently reset. But if id is really the primary key, then I would use SERIAL PRIMARY KEY which implies UNIQUE NOT NULL. From the CREATE TABLE documentation: The primary key constraint specifies that a column or columns of a table may contain only unique (non-duplicate), nonnull values. Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL, but identifying a set of columns as primary key also provides metadata about the design of the schema, as a primary key implies that other tables may rely on this set of columns as a unique identifier for rows. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
On Sat, May 28, 2005 at 16:18:30 -0300, David Pratt <fairwinds@eastlink.ca> wrote: > > Serial should always give me an incremented value that's different so I > am assuming it is unnecessary to use UNIQUE. Am I correct? Unless someone uses setval to lower the value or if you change the configuration of the sequence to allow it to wrap around.