Thread: INSERT requires SERIAL column?

INSERT requires SERIAL column?

From

otisg@ivillage.com

Date:

27 February 2002, 05:39:40

Hello,

I've got a table with these columns:

user_id      SERIAL       CONSTRAINT pk_user_auth_user_id PRIMARY KEY,
email        VARCHAR(64)    NOT NULL UNIQUE        ,
password     VARCHAR(16)    NOT NULL        ,
status       SMALLINT    NOT NULL DEFAULT 1

But when I tried using the following INSERT statement I got an error about not being able to parse the input.

INSERT INTO user_auth VALUES ('foo@example.com', 'password', 1);

psql:../data/user_auth.dat:13: ERROR:  pg_atoi: error in "foo@example.com": can't parse "foo@example.com"

So this indicates that an integer was expected as the first value.

In order to get it to do what I want I had to use this:

INSERT INTO user_auth VALUES (nextval('user_auth_user_id_seq'), 'foo@example.com', 'password', 1);

Question:
Is this really necessary or am I missing something?
I thought specifying SERIAL columns is not neccessary in INSERT statements.

Thank you,
Otis

_________________________________________________________________
iVillage.com: Solutions for Your Life 
Check out the most exciting women's community on the Web   
http://www.ivillage.com

Re: INSERT requires SERIAL column?

From

"Christopher Kings-Lynne"

Date:

27 February 2002, 05:50:32

> Hello,
>
> I've got a table with these columns:
>
> user_id      SERIAL
>         CONSTRAINT pk_user_auth_user_id PRIMARY KEY,
> email        VARCHAR(64)    NOT NULL UNIQUE        ,
> password     VARCHAR(16)    NOT NULL        ,
> status       SMALLINT    NOT NULL DEFAULT 1
>
> But when I tried using the following INSERT statement I got an
> error about not being able to parse the input.
>
> INSERT INTO user_auth VALUES ('foo@example.com', 'password', 1);
>
> psql:../data/user_auth.dat:13: ERROR:  pg_atoi: error in
> "foo@example.com": can't parse "foo@example.com"
>
> So this indicates that an integer was expected as the first value.
>
> In order to get it to do what I want I had to use this:
>
> INSERT INTO user_auth VALUES (nextval('user_auth_user_id_seq'),
> 'foo@example.com', 'password', 1);
>
> Question:
> Is this really necessary or am I missing something?
> I thought specifying SERIAL columns is not neccessary in INSERT
> statements.

You need to specify the columns to insert into, skipping over the SERIAL
column:

INSERT INTO user_auth (email, password, status) VALUES ('foo@example.com',
'password', 1);

Chris

Re: INSERT requires SERIAL column?

From

"D'Arcy J.M. Cain"

Date:

27 February 2002, 06:52:43

On February 27, 2002 02:50 am, Christopher Kings-Lynne wrote:
> > Question:
> > Is this really necessary or am I missing something?
> > I thought specifying SERIAL columns is not neccessary in INSERT
> > statements.
>
> You need to specify the columns to insert into, skipping over the SERIAL
> column:

Good advice for any INSERT statement except perhaps one time queries typed
directly into the monitor.  Databases change and INSERT statements in code
that assume otherwise can bite you on the ass in hard to find ways.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.