Postgresql – psql: id SERIAL PRIMARY KEY not starting with 1

bulkcopypostgresqlserialization

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,

Best Answer

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

Related Solutions

Postgresql table with one integer column, sorted index, with duplicate primary key

I think you're asking how to impliment a solution you'e already decided on for a more general problem you don't describe. If you were to outline the actual problem that this is supposed to solve you might get better suggestions about how to solve it.

Working within the very limited information provided:

Update: I found your other question, which you really should've linked to. You seem to be trying to roll your own message queue. Don't do that. Read these:

Have I convinced you that you shouldn't try to do this yourself yet? Look into:

RabbitMQ
ZeroMQ
Job::Machine
ActiveMQ
PGQ
Celery

Some of what you want isn't available in current PostgreSQL versions. For example:

INSERTs should not do any query in that table or any kind of unique index. INSERTs shall just locate the best page for the main file/main btree for this table and just insert the row in between two other rows, ordered by ID.`

That'd require an index-organized table, which PostgreSQL doesn't have yet. The closest you'll get would be a one-column table with a PRIMARY KEY. With regular VACUUM on PostgreSQL 9.2 you'd be able to use index-only scans to access it most of the time.

As for allowing duplicates, you don't really seem to want to permit them at all, you're just saying you want to work around concurrency issues by temporarily permitting them.

You can remove such duplicates during INSERT so the table its self doesn't need to permit them. However, that'll cause issues with:

INSERTs will happen in bulk (about 1000 per transaction) and must not fail, expect for disc full, etc. There must not be any chance for deadlocks.

... assuming that those inserts occur concurrently from multiple transactions. You'll have races between the checks for existence and the insert that can cause insert batches to fail and have to be re-tried.

I suspect that your best bet is to have a one-column table without a PRIMARY KEY. Just create an ordinary b-tree index on it, and leave the table without a PRIMARY KEY. Since it genuinely has no primary key (the only column may have duplicates) this is entirely reasonable.

(BTW, given that SQL is supposedly all about sets, it astounds me how awful it is at "add this entry to the set if not already present").

PostgreSQL – How to Retrieve Max Primary Key Column to Insert a New Row

Postgres has the serial datatype which matches SQL Server's IDENTITY or MySQL's AUTO_INCREMENT.

Internally it is shorthand for a SEQUENCE but does that matter? It acts like IDENTITY/AUTO_INCREMENT:

The data types 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). In the current implementation, specifying:

CREATE TABLE tablename (
    colname SERIAL
);

is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Edit,

I think what OP means is "is there SCOPE_IDENTITY or such" in PostgreSQL. Yes. You'd need currval or another one

Best Answer

Related Solutions

Postgresql table with one integer column, sorted index, with duplicate primary key

PostgreSQL – How to Retrieve Max Primary Key Column to Insert a New Row

Related Question