pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
INSERT requires SERIAL column?
3+ messages / 3 participants
[nested] [flat]

* INSERT requires SERIAL column?
@ 2002-02-27 07:38  otisg@ivillage.com
  0 siblings, 1 reply; 3+ messages in thread

From: otisg@ivillage.com @ 2002-02-27 07:38 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: INSERT requires SERIAL column?
@ 2002-02-27 07:50  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: otisg@ivillage.com
  0 siblings, 1 reply; 3+ messages in thread

From: Christopher Kings-Lynne @ 2002-02-27 07:50 UTC (permalink / raw)
  To: otisg@ivillage.com; pgsql-sql

> 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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: INSERT requires SERIAL column?
@ 2002-02-27 08:51  D'Arcy J.M. Cain <darcy@druid.net>
  parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 0 replies; 3+ messages in thread

From: D'Arcy J.M. Cain @ 2002-02-27 08:51 UTC (permalink / raw)
  To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; otisg@ivillage.com; +Cc: pgsql-sql

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.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2002-02-27 08:51 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-27 07:38 INSERT requires SERIAL column? otisg@ivillage.com
2002-02-27 07:50 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-02-27 08:51   ` D'Arcy J.M. Cain <darcy@druid.net>

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