Re: [SQL] create table with default value...

Salut Frederic,

Not quite sure why you'd want to do what you're asking to do but the
following works:

DROP TABLE prova1;
CREATE TABLE prova1 (
u_id int8 DEFAULT nextval('u_id_seq'),
name varchar(50) DEFAULT 'NT_' || currval('u_id_seq'),
extra_field text
);

test=> select * from prova1;
u_id|name|extra_field
----+----+-----------
(0 rows)

test=> insert into prova1 (extra_field) values ('look');
INSERT 2217966 1
test=> insert into prova1 (extra_field) values ('no');
INSERT 2217967 1
test=> insert into prova1 (extra_field) values ('hands!');
INSERT 2217968 1
test=> select * from prova1;
u_id|name|extra_field
----+----+-----------
1|NT_1|look
2|NT_2|no
3|NT_3|hands!
(3 rows)

test=>

The reason why I'm not quite sure why you'd want to do this is that the
field name would be redundant. You could simply obtain it using a select.
e.g.:

test=> SELECT u_id, 'NT_'::text || u_id::text AS name, extra_field FROM
prova1;
u_id|name|extra_field
----+----+-----------
1|NT_1|look
2|NT_2|no
3|NT_3|hands!
(3 rows)

test=>

HTH,

Regards,

Stuart.

On Fri, 5 Nov 1999, Frederic boucher wrote:

> Hi,
>
> I have a table with a Unique ID named U_ID that come from a sequence like
> that :
>
> create table the_table(
> U_ID integer default NEXTVAL('THE_SEQUENCE'),
> ...
>
> and I would like to have a field of type varchar that would be something
> like
>
> create table the_table(
> U_ID integer default NEXTVAL('THE_SEQUENCE'),
> name varchar(50) default 'NT_'+itoa(U_ID)
> );
>
> so the field 'name' would be the string concatenation of "NT_" and the
> string value of the field U_ID.
>
> Is there a way to do this?
>
> Thanks for your precious help! ;)
>
> \fb
>
> ______________________________________________________
> Get Your Private, Free Email at http://www.hotmail.com
>
> ************
>

Stuart C. G. Rison
Department of Biochemistry and Molecular Biology
6th floor, Darwin Building, University College London (UCL)
Gower Street, London, WC1E 6BT, United Kingdom
Tel. 0207 504 2303, Fax. 0207 380 7193
e-mail: rison(at)biochem(dot)ucl(dot)ac(dot)uk