PostgreSQL Array Insert Size Limitation Error

Question

I get the following Postgres error:

ERROR: value too long for type character varying(1024)

The offending statement is:

INSERT INTO integer_array_sensor_data (sensor_id, "time", status, "value") 
VALUES (113, 86651204, 0, '{7302225, 7302161, 7302593, 730211, 
        ... <total of 500 values>... 7301799, 7301896}');

The table:

CREATE TABLE integer_array_sensor_data (
    id SERIAL PRIMARY KEY, 
    sensor_id INTEGER NOT NULL, 
    "time" INTEGER NOT NULL, 
    status INTEGER NOT NULL, 
    "value" INTEGER[] NOT NULL, 
    server_time TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW()
);

Researching PostgreSQL documentation doesn't mention anything about limitation on the array size.

Any idea how to fix this?

The error message is not related to the cited statement. Search in a script for a query updating (inserting to) a table with a column of varchar(1024) type. — klin
– klin, Commented Dec 16, 2015 at 3:16

Guillaume F. · Accepted Answer · 2015-12-16 16:23:43Z

3

The problem doesn't come from the Array itself, but from the Varchar String declaring the array values in your Insert. Some drivers type the string literals as varchar(1024) causing that issue.

Instead of

'{1,2,3}'

try using

ARRAY[1,2,3]

otherwise you can try declaring the type of your string as TEXT (unlimited)

'{1,2,3}'::text

edited Dec 16, 2015 at 16:23

answered Dec 16, 2015 at 3:26

Guillaume F.

6,5332 gold badges36 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Erwin Brandstetter Over a year ago

The string literal is not typed as varchar(1024), and there is no such length limitation for string literals. And an explicit type cast is also not needed in an INSERT statement, where the data type is derived from the target column. The question is just as broken as this answer.

Guillaume F. Over a year ago

Some drivers do type the string literals as a varchar(1024). It's a known issue.

major4x · Accepted Answer · 2015-12-17 08:03:24Z

0

I start understanding my question although I have not found a solution. Problem is that there is a limitation on the string somewhere library level. I am actually using pqxx and you can't have strings longer than 1024 chars. I have accepted the answer of Guillaume F. because he figured this out but the casting doesn't work. I will edit my reply once I find a solution so people know what to do.

I just tried prepared statements and they have the same limitation.

The workaround is to use COPY or its pqxx binding pqxx:tablewriter.

edited Dec 17, 2015 at 8:03

answered Dec 16, 2015 at 22:35

major4x

4424 silver badges19 bronze badges

Collectives™ on Stack Overflow

PostgreSQL Array Insert Size Limitation Error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related