sql insert fails on postgresql database

Question

I am trying to run the following insert statement using pgadmin3:

INSERT INTO device 
VALUES
(12345, 
'asdf',
'OY8YuDFLYdv', 
'2', 
'myname', 
'2013-04-24 11:30:08',
 Null,Null)

But I keep getting the following error message:

ERROR:  invalid input syntax for integer: "asdf"
LINE 4: 'asdf',
        ^


********** Error **********

ERROR: invalid input syntax for integer: "asdf"
SQL state: 22P02
Character: 42

Here's the table definition:

CREATE TABLE device
(
  device_id integer NOT NULL DEFAULT nextval('device_device_id_seq'::regclass),
  userid integer NOT NULL,
  description character varying(255),
  password character varying(255) NOT NULL,
  user_id integer NOT NULL,
  createdname character varying(255),
  createddatetime timestamp without time zone,
  updatedname character varying(255),
  updateddatetime timestamp without time zone,
  CONSTRAINT device_pkey PRIMARY KEY (device_id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE device
  OWNER TO appadmin;

Can you tell me where I'm going wrong? I've tried changing the single quotes to double quotes but that didn't help. I don't want to have to list all the column names in the INSERT if I dont have to.

Thanks.

You need to specify the columns to which you are inserting... — mechanical_meat
– mechanical_meat, Commented Apr 24, 2013 at 17:31

mu is too short · Accepted Answer · 2013-04-24 17:35:51Z

Apparently you're expecting the INSERT to skip device_id since it is the primary key and has a default that comes from a sequence. That's not going to happen so PostgreSQL thinks you mean this:

insert into device (device_id, userid, ...)
            values (12345,     'asdf', ...);

If you insist on not listing your columns explicitly (and making the people that get to maintain your code suffer needlessly) then you can specify DEFAULT in the VALUES to tell PostgreSQL to use the PK's default value; from the fine manual:

INSERT INTO table_name [ ( column_name [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

[...] DEFAULT
The corresponding column will be filled with its default value.

For example:

INSERT INTO device 
VALUES
(DEFAULT,
12345, 
'asdf',
...

But really, you should just specify the columns to make the SQL easier to understand and more robust when the schema changes.

Collectives™ on Stack Overflow

sql insert fails on postgresql database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related