PostgreSQL insert with nested query fails with large numbers of rows

Question

I'm trying to insert data into a PostgreSQL table using a nested SQL statement. I'm finding that my inserts work with a small (a few thousand) rows being returned from the nested query. For instance, when I attempt:

insert into the_target_table (a_few_columns, average_metric)

    SELECT a_few_columns, AVG(a_metric)
    FROM a table
    GROUP BY a_few_columns LIMIT 5000)

However, this same query fails when I remove my LIMIT (the inner query without limit returns about 30,000 rows):

ERROR: Integer out of range

a_metric is a double precision, and a_few_columns are text. I've played around with the LIMIT rows, and it seems like the # of rows it can insert without throwing an error is 14,000. I don't know if this is non-deterministic, or a constant threshold before the error is thrown.

I've looked through a few other SO posts on this topic, including this one, and changed my table primary key data type to BIGINT. I still get the same error. I don't think it's an issue w/ numerical overflow, however, as the number of inserts I'm making is small and nowhere even close to hitting the threshold.

Anyone have any clues what is causing this error?

Inserting several million rows in this way (insert ... select ...) is not a problem. 30000 rows really cannot impress the server. You should search for a cause of the error somewhere else. Does the standalone select work properly? Are the types of the columns in insert and select exactly the same? (etc) — klin
– klin, Commented Jul 4, 2018 at 0:08
What is the data type of columns a_few_columns, average_metric in table the_target_table — Gaj
– Gaj, Commented Jul 4, 2018 at 4:24
is that possible that AVG(a_metric) return more than 15 decimal? can you try with round(AVG(a_metric),2) without limit — Gaj
– Gaj, Commented Jul 4, 2018 at 4:48
This is impossible to answer without knowing the definitions of all tables involved. — Laurenz Albe
– Laurenz Albe, Commented Jul 4, 2018 at 5:32

Yu Chen · Accepted Answer · 2018-07-06 05:30:03Z

0

The issue here was an improper definition of the avg_metric field in my table that I wanted to insert it into. I accidentally had defined it as an integer. This normally isn’t a huge issue, but I also had a handful of infinity values ( inf). Once I switched my field data type to double precision I was able to insert successfully. Of course, it’s probably best if my application had checked beforehand for finite values prior to attempting the insert- normally I’d do this programmatically via asserts, but with a nested query I hadn’t bothered to check.

The final query I used was

insert into the_target_table (a_few_columns, average_metric)

SELECT a_few_columns, CASE WHEN AVG(a_metric) = 'inf' THEN NULL ELSE AVG(a_metric) END
FROM a_table
GROUP BY a_few_columns LIMIT 5000)

An even better solution would have been to go through a_table and replace all inf values first.

edited Jul 6, 2018 at 5:30

answered Jul 4, 2018 at 5:37

Yu Chen

7,66011 gold badges59 silver badges102 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL insert with nested query fails with large numbers of rows

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related