How to best store pixels in a database? 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_1, pixel_2, ..., pixel_1024.  Note that this is fundamentally different from the usual example of phone_number_1, 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.
Raster Data


  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.


That's called a "raster", not a PNG image. A PNG is in fact an RGB[A] raster stored with DEFLATE compression, but what you want to store has nothing to do with it being a PNG. If these terms don't make sense, it's like telling someone you want to store a PDF. That's possible, but likely what they want is to store the underlying text.

Here your underlying text from the PNG is the raster.

PostGIS

PostgreSQL supports storing actual raster types with PostGIS. I suggest checking out the PostGIS Raster Reference. If your rasters are currently encoded in the PNG channels, you can use GDAL to put them in the database

I would suggest checking that out for information on how to query, process, modify, and export raster data.

Native

Even if you decide not use PostGIS, I don't believe the other answers are adequate. As mentioned above, a RGB pixel is a 3-channel compound data type. So you'll need at least three dimensions for the pixel data.

In addition, you'll likely want information on the XY pixel data, or a method to calculate with that meta data later.


You could use a cube to represent the pixel data

-- white and black pixels (RGB)
SELECT ARRAY['(255,255,255)','(0,0,0)']::cube[]

You can use an array to store each channel (likely not useful for processing), and importing and exporting will be difficult.
You could use a type for the pixel., though a record type even of "char" is substantially larger than our solution below.
You could store each pixel as an int and ignore the channels (assuming you have less than four 1-byte channels). If you need to process this will get super ugly very fast, make analysis near impossible, and exporting and querying very difficult.
Or you could create your own DOMAIN, but at this point your basically recreating PostGIS's raster. With this approach you get to store all four bands as a single int. Access those bands with minimal cost, and construct the pixel from the bands. You can easily expand this to int8 if you need 8 bands.

CREATE DOMAIN pixel AS int4;

CREATE OR REPLACE FUNCTION get_band(p pixel, b int)
RETURNS int AS $$
  SELECT ((p::bit(32)&lt;&lt; ((4-b)*8)) &gt;&gt; (3*8))::int
$$ LANGUAGE sql
IMMUTABLE;

CREATE OR REPLACE FUNCTION create_pixel( int, int, int, int )
RETURNS pixel AS $$
  SELECT (
    $1::bit(32) |
    ($2::bit(32)&lt;&lt;8) |
    ($3::bit(32)&lt;&lt;16) |
    ($4::bit(32)&lt;&lt;24)
  )::pixel;
$$ LANGUAGE sql
IMMUTABLE;

-- Returns 128.
SELECT get_band(create_pixel(255,0,128,32),3)::int;

-- You can use this type in an array too 
CREATE TABLE f ( ps pixel[] );



As a side note, if using LIDAR you should check out PG Point Cloud