When loading data into a table, I get the following error:
ERROR: row is too big: size 8680, maximum size 8160
The table has 1000+ columns in it, which appears to be the problem. The general internet advice is "refactor!" or "normalize!". For instance, this post. Unfortunately, I don't believe such advice applies to my situation.
The table is to store data collected from a device. The device produces a PNG image as part of an analysis. The PNG consists of 1024 pixels. Each pixel has an associated numeric value. Along with the pixel data are various other fields related to the analysis. Breaking the table into parts doesn't really make sense. The fields are all logically associated with the particular object being analyzed.
Postgres doesn't seem to like that each pixel has its own field. The table has fields of the form:
pixel_1024. Note that this is fundamentally different from the usual example of
phone_number_2, etc. Each pixel is a unique object by virtue of its location.
pixel_1 has a different position than
pixel_123 and each pixel has an associated value. The common aspect between them is that they both are used to describe the same analysis object. They are the quantitative analog to the visual representation given in the PNG.
- Is there a way to increase the row size?
- If the table simply cannot have 1000+ columns, how could I refactor this?
- Assuming the first two answers are "No.", should I just stick the 1024 columns into an XML and throw that in a text field?
I hope I have made the context clear. I have tried to boil the problem down to its essence, but I suspect some clarification may be needed. Please let me know if clarification is needed.
EDIT: As an experiment, I tried breaking the pixels into a separate table. That seems to be the only possible way to refactor. But the 1024 columns produces the same error.