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

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

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:

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

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

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

"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