1

This is something I don't understand, can somebody explain?

I create a table

CREATE TABLE cali (id SERIAL PRIMARY KEY,
    ALK_from char,
    ALK_to char,
    AND_from char,
    AND_to char);

Then I simply try to insert some data from a csv file like so

\COPY cali FROM '/home/.../data/output/id_cali.csv' (FORMAT CSV);

Which gives me this Error:

ERROR:  value "4372840000" is out of range for type integer
CONTEXT:  COPY cali, line 1, column id: "4372840000"

I know that 4372840000 results in an integer overflow. That is why I'm trying to insert the values as chars in the first place.

But my understanding is that id SERIAL PRIMARY KEY is some sort of auto increment why is the value 4372840000 used for the column id?

The file looks like this

4372840012,4372840013,13,14,
4372840014,4372840014,15,15,
6

The table should be

create table cali (
    id serial primary key,
    alk_from bigint,
    alk_to bigint,
    and_from int,
    and_to int
);

And do

\COPY cali (alk_from, alk_to, and_from, and_to) 
FROM '/home/.../data/output/id_cali.csv' 
(FORMAT CSV);

So that it knows to not insert into the id serial column. The serial type is not a true type. From the manual:

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases)

Thus, we have created an integer column and arranged for its default values to be assigned from a sequence generator

  • I see. So id will be auto populated by the first column if you don't specify the column names with \copy? – LarsVegas Mar 26 '14 at 13:24
  • copy will of course start putting values into the columns in the order they are, unless you tell it otherwise. Serial is no special value, it's just a way to say "this is an int column, please create a sequence and put the default value to be taken from there." – Sami Kuhmonen Mar 28 '14 at 11:03
0

You can create the table with same statement but subsequently execute following statement - basically you are altering the data-type for your id column.

alter table cali alter column id type bigint;

  • This will not result in correct data in the database by itself in this case. – Sami Kuhmonen Sep 9 '15 at 20:31
-3

and for your problem you should take columns datatype as bigint so you will not get this error.

you can not insert value in primary key column which is autoincremented

CREATE TABLE cali (id SERIAL PRIMARY KEY, ALK_from bigint, ALK_to bigint, AND_from bigint, AND_to bigint);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.