pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcreate table with default value...
4+ messages / 2 participants
[nested] [flat]
* create table with default value...
@ 1999-11-05 08:38 Frederic boucher <dziablo@hotmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Frederic boucher @ 1999-11-05 08:38 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] create table with default value...
@ 1999-11-05 10:25 Frederic boucher <dziablo@hotmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Frederic boucher @ 1999-11-05 10:25 UTC (permalink / raw)
To: pgsql-sql
>From: Stuart Rison <rison@biochemistry.ucl.ac.uk>
>To: Frederic boucher <dziablo@hotmail.com>
>CC: pgsql-sql@postgreSQL.org
>Subject: Re: [SQL] create table with default value...
>Date: Fri, 5 Nov 1999 18:07:05 +0000 (GMT)
>MIME-Version: 1.0
From owner-pgsql-sql@hub.org Fri Nov 05 10:11:14 1999
>Received: from [216.126.84.1] by hotmail.com (3.2) with ESMTP id
>MHotMailB9EC68C1009BD820F397D87E540112450; Fri Nov 05 10:11:14 1999
>Received: from hub.org (hub.org [216.126.84.1])by hub.org (8.9.3/8.9.3)
>with ESMTP id NAA78902;Fri, 5 Nov 1999 13:04:06 -0500 (EST)(envelope-from
>owner-pgsql-sql@hub.org)
>Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Fri, 05
>Nov 1999 13:03:05 +0000 (EST)
>Received: (from majordom@localhost)by hub.org (8.9.3/8.9.3) id NAA78819for
>pgsql-sql-outgoing; Fri, 5 Nov 1999 13:02:10 -0500 (EST)(envelope-from
>owner-pgsql-sql@postgreSQL.org)
>Received: from mail-b.bcc.ac.uk (mail-b.bcc.ac.uk [144.82.100.22])by
>hub.org (8.9.3/8.9.3) with ESMTP id NAA78741for <pgsql-sql@postgreSQL.org>;
>Fri, 5 Nov 1999 13:01:24 -0500 (EST)(envelope-from
>rison@biochemistry.ucl.ac.uk)
>Received: from biochem.ucl.ac.uk (actually host bsmori1.biochem.ucl.ac.uk)
> by mail-b.bcc.ac.uk with SMTP (XT-PP) with ESMTP; Fri, 5
>Nov 1999 18:00:47 +0000
>Received: from bsmlx17.biochem.ucl.ac.uk (bsmlx17 [128.40.46.142]) by
>biochem.ucl.ac.uk (8.9.2/8.9.1) with ESMTP id RAA19693; Fri, 5 Nov
>1999 17:56:40 GMT
>Received: from localhost (rison@localhost) by
>bsmlx17.biochem.ucl.ac.uk (8.9.3/8.6.6) with ESMTP id SAA09370;
>Fri, 5 Nov 1999 18:07:05 GMT
>X-Authentication-Warning: bsmlx17.biochem.ucl.ac.uk: rison owned process
>doing -bs
>X-Sender: rison@bsmlx17
>In-Reply-To: <19991105163855.90355.qmail@hotmail.com>
>Message-ID: <Pine.LNX.4.10.9911051759480.7824-100000@bsmlx17>
>Sender: owner-pgsql-sql@postgreSQL.org
>Precedence: bulk
>
>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@biochem.ucl.ac.uk
>
>
>************
>
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] create table with default value...
@ 1999-11-05 10:33 Frederic boucher <dziablo@hotmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Frederic boucher @ 1999-11-05 10:33 UTC (permalink / raw)
To: pgsql-sql
Hi,
First, thanks a lot for your answer... I had this possibility in mind but I
wasn't sure about the security of this... Is it possible for an other
thread to request NEXTVAL('u_id_seq') in the middle of the creation of a
record? Because if this is possible the unique id would be let's say 1 and
the concatenation would be 2 (because the "new" last_value as been
changed)...?!?!
I'm not sure I'm quite clear ...
Second, to answer your question "Why are you wanting this?" it's because,
like the create string says, it a DEFAULT value... for lazy, or stupid user
that would put anything unusefull in there... But it can be changed too an
must me remebered... Isnt it the right way to do this?
Third, A simple one : How can I get the current datetime? like a oracle's
"select sysdate from dual;"
Thanks
PS: By the way, where could I find difference, or exact definition, of data
type... Like what's the difference between "text" and "varchar" or between
"integer" and "int8" (in this case I suppose that integer is "int4"
>
>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.
>
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] create table with default value...
@ 1999-11-05 18:07 Stuart Rison <rison@biochemistry.ucl.ac.uk>
parent: Frederic boucher <dziablo@hotmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Stuart Rison @ 1999-11-05 18:07 UTC (permalink / raw)
To: Frederic boucher <dziablo@hotmail.com>; +Cc: pgsql-sql
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@biochem.ucl.ac.uk
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1999-11-05 18:07 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-11-05 08:38 create table with default value... Frederic boucher <dziablo@hotmail.com>
1999-11-05 18:07 ` Stuart Rison <rison@biochemistry.ucl.ac.uk>
1999-11-05 10:25 Re: [SQL] create table with default value... Frederic boucher <dziablo@hotmail.com>
1999-11-05 10:33 Re: [SQL] create table with default value... Frederic boucher <dziablo@hotmail.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