Thread: How to insert with a serial
Hi,
I have a table defined like this:
Attribute | Type | Modifier
-------------+-----------------------+------------------------------------------
name | character varying(40) |
type | integer |
id | integer | not null default nextval('serial'::text)
ownerteamid | integer |
Index: devices_pkey
How do I insert into it so that id is automatically incremented?
Konstantin
--
Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood@agouros.de
Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185
----------------------------------------------------------------------------
"Captain, this ship will not survive the forming of the cosmos." B'Elana Torres
Specify all fields except the serial field in your insert so it will use the default (sequence number) value.
INSERT INTO DEVICES (name, type, ownerteamid) VALUES ('scoobydoo', 1, 43)
One funny thing about serial fields is that you CAN insert a value into them, but the sequence has no idea what you
did,so it will merrily assign the next value it knows about when you don't provide a value, potentially causing a
conflict. Therefore, don't do that. Always let the value be assigned as above.
PS Check out Bruce's book!
Ian A. Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
(253) 798-3549
mailto: ianh@tpchd.org
>>> Konstantinos Agouros <elwood@agouros.de> 10/21/01 06:16AM >>>
Hi,
I have a table defined like this:
Attribute | Type | Modifier
-------------+-----------------------+------------------------------------------
name | character varying(40) |
type | integer |
id | integer | not null default nextval('serial'::text)
ownerteamid | integer |
Index: devices_pkey
How do I insert into it so that id is automatically incremented?
Konstantin
--
Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood@agouros.de
Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185
----------------------------------------------------------------------------
"Captain, this ship will not survive the forming of the cosmos." B'Elana Torres
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
insert 'ing a NULL in place of the serial will work too, yes?
INSERT INTO DEVICES (my_serial_id, name, type, ownerteamid) VALUES
(NULL,'scoobydoo', 1, 43);
-Mitch
----- Original Message -----
From: "Ian Harding" <ianh@tpchd.org>
To: <elwood@agouros.de>; <pgsql-general@postgresql.org>
Sent: Monday, October 22, 2001 12:43 PM
Subject: Re: [GENERAL] How to insert with a serial
> Specify all fields except the serial field in your insert so it will use
the default (sequence number) value.
>
> INSERT INTO DEVICES (name, type, ownerteamid) VALUES ('scoobydoo', 1, 43)
>
> One funny thing about serial fields is that you CAN insert a value into
them, but the sequence has no idea what you did, so it will merrily assign
the next value it knows about when you don't provide a value, potentially
causing a conflict. Therefore, don't do that. Always let the value be
assigned as above.
>
> PS Check out Bruce's book!
>
> Ian A. Harding
> Programmer/Analyst II
> Tacoma-Pierce County Health Department
> (253) 798-3549
> mailto: ianh@tpchd.org
>
> >>> Konstantinos Agouros <elwood@agouros.de> 10/21/01 06:16AM >>>
> Hi,
>
> I have a table defined like this:
>
> Attribute | Type | Modifier
> -------------+-----------------------+------------------------------------
------
> name | character varying(40) |
> type | integer |
> id | integer | not null default
nextval('serial'::text)
> ownerteamid | integer |
> Index: devices_pkey
>
> How do I insert into it so that id is automatically incremented?
>
> Konstantin
> --
> Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood@agouros.de
> Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185
> --------------------------------------------------------------------------
--
> "Captain, this ship will not survive the forming of the cosmos." B'Elana
Torres
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
HI Konstantin
Use insert into table (type) values ('typedesc');
The database will create a sequence number to put in id
Flávio Brito
Rio de Janeiro
Brasil
Em Dom 21 Out 2001 11:16, Konstantinos Agouros escreveu:
> Hi,
>
> I have a table defined like this:
>
> Attribute | Type | Modifier
> -------------+-----------------------+-------------------------------------
>----- name | character varying(40) |
> type | integer |
> id | integer | not null default
> nextval('serial'::text) ownerteamid | integer |
> Index: devices_pkey
>
> How do I insert into it so that id is automatically incremented?
>
> Konstantin
On 21 Oct 2001, Konstantinos Agouros wrote:
> I have a table defined like this:
>
> Attribute | Type | Modifier
> -------------+-----------------------+------------------------------------------
> name | character varying(40) |
> type | integer |
> id | integer | not null default nextval('serial'::text)
> ownerteamid | integer |
> Index: devices_pkey
>
> How do I insert into it so that id is automatically incremented?
Look at the SERIAL data type.
Create a table to hold the numbers:
CREATE SEQUENCE name_id_seq;
CREATE TABLE person (id INT4 NOT NULL DEFAULT nextval('name_id_seq'), name
VCHAR(40), ...);
CREATE UNIQUE INDEX name_id_seq ON person (id);
HTH,
Rich
Dr. Richard B. Shepard, President
Applied Ecosystem Services, Inc. (TM)
2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
+ 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard@appl-ecosys.com
http://www.appl-ecosys.com
In <elwood.1003670142@news.agouros.de> elwood@agouros.de (Konstantinos Agouros) writes:
>Hi,
>I have a table defined like this:
> Attribute | Type | Modifier
>-------------+-----------------------+------------------------------------------
> name | character varying(40) |
> type | integer |
> id | integer | not null default nextval('serial'::text)
> ownerteamid | integer |
>Index: devices_pkey
>How do I insert into it so that id is automatically incremented?
Ok I created it with 7.0 after using type serial everything works fine \:)
Thanks for all the help,
Konstantin
--
Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood@agouros.de
Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185
----------------------------------------------------------------------------
"Captain, this ship will not survive the forming of the cosmos." B'Elana Torres
"Mitch Vincent" <mvincent@cablespeed.com> writes:
> insert 'ing a NULL in place of the serial will work too, yes?
> INSERT INTO DEVICES (my_serial_id, name, type, ownerteamid) VALUES
> (NULL,'scoobydoo', 1, 43);
No, that'll insert a NULL.
regards, tom lane
No, inserting a null will not work, it will insert the null
Dave
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Mitch Vincent
Sent: October 22, 2001 2:02 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to insert with a serial
insert 'ing a NULL in place of the serial will work too, yes?
INSERT INTO DEVICES (my_serial_id, name, type, ownerteamid) VALUES
(NULL,'scoobydoo', 1, 43);
-Mitch
----- Original Message -----
From: "Ian Harding" <ianh@tpchd.org>
To: <elwood@agouros.de>; <pgsql-general@postgresql.org>
Sent: Monday, October 22, 2001 12:43 PM
Subject: Re: [GENERAL] How to insert with a serial
> Specify all fields except the serial field in your insert so it will
> use
the default (sequence number) value.
>
> INSERT INTO DEVICES (name, type, ownerteamid) VALUES ('scoobydoo', 1,
> 43)
>
> One funny thing about serial fields is that you CAN insert a value
> into
them, but the sequence has no idea what you did, so it will merrily
assign the next value it knows about when you don't provide a value,
potentially causing a conflict. Therefore, don't do that. Always let
the value be assigned as above.
>
> PS Check out Bruce's book!
>
> Ian A. Harding
> Programmer/Analyst II
> Tacoma-Pierce County Health Department
> (253) 798-3549
> mailto: ianh@tpchd.org
>
> >>> Konstantinos Agouros <elwood@agouros.de> 10/21/01 06:16AM >>>
> Hi,
>
> I have a table defined like this:
>
> Attribute | Type | Modifier
> -------------+-----------------------+--------------------------------
> -------------+-----------------------+----
------
> name | character varying(40) |
> type | integer |
> id | integer | not null default
nextval('serial'::text)
> ownerteamid | integer |
> Index: devices_pkey
>
> How do I insert into it so that id is automatically incremented?
>
> Konstantin
> --
> Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet:
> elwood@agouros.de Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89
> 69370185
>
------------------------------------------------------------------------
--
--
> "Captain, this ship will not survive the forming of the cosmos."
> B'Elana
Torres
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster