I am having the following table

This table contains information about the marks scored by students in various subject.

Now I need to store the marks in each row into an array. And I may add more subject column in future.

Now I need to change this

into, something like

array1[98,87,76,100] array2[90,100,64,71]

CodePudding user response：

Postgres has an array data type. You can add a new column of this data type and copy your data into that format.

If your original table is defined like this:

create table scores (
  studentid int, 
  subjectA int, 
  subjectB int, 
  subjectC int, 
  subjectD int);

Then add a new column (this can be a one-dimensional or multi-dimensional array, reference https://www.postgresql.org/docs/current/arrays.html:

alter table scores add column subjectArray int[4];

And save the data into that column:

update scores set subjectArray=ARRAY[subjectA,subjectB,subjectC,subjectD];

Then you can select individual values like this:

select subjectArray[2] from scores where studentid=1;

Or select the whole array like this:

select studentid, subjectArray from scores where studentid=1;

CodePudding user response：

This topic may have already answered here, however you can also add a column of json type to save the data more easily. In your case, you would save the data as a string (as a valid json) and, by retriving it you would format it again to array.

Here the doc about the json columns.