PostgreSQL creates a table with 841 columns

Question

I use PostgreSQL as an SQL Server to store the datasets used to train models in Weka (machine learning tool).

Weka then reads the table, creating a function from each column. For this project, the data consists of 24x35 px images, where each pixel is a function. So I need to create a table with 841 columns (840 pixel values, 1 id (primary key)).

Images are grayscale images. Therefore, each pixel value varies from 0 to 255. Therefore, I want to either save it as one unit per pixel / column, or one byte per pixel / column. However, the "id" -column must be an integer.

What is the best / easiest way to set up a table of this size?

Answer 1

Listed below are 1 answer to the question about maximum columns and another option to switch to 1 for many.

Maximum number of columns and types

Below is the details of what you should check.

What is the maximum number of columns in a PostgreSQL select query

Transition to use 1 for many

The disadvantage of this is that you have to recreate the database schema every time the pixel size (image size) changes.

Instead, you can create relationships from 1 to many and have a table with:

image_id, pixel_number, value

So, for a single image with N pixels, you would:

1, 1, value
1, 2, value
....
1, N, value

Answer 2

? , , , , 1 1 1, :

create table picuture
(
  pic_id integer,
  pic_value <whatevertypeyouwant>,

  pk_picture primary key pic_id
);

-- option 1
create table your_table
(
  id integer,
  pic_id_001 integer,
  pic_id_002 integer,
  ...
  pic_id_840 integer,

  pk_your_table primary key id
);

-- option 2
create table your_table
(
  id integer,
  pic_id integer,

  pk_your_table primary key id, pic_id
);