How to store all the values in a row to array in postgresql?

Question

I am having the following table

Student Id	Subject A	Subject B	Subject C	Subject D
1	98	87	76	100
2	90	100	64	71

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

Student Id	Subject A	Subject B	Subject C	Subject D
1	98	87	76	100
2	90	100	64	71

into, something like

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

You shouldn't have one column per subject to begin with - that's bad database design. You should have one row per subject. — user330315
– user330315, Commented Jan 7, 2023 at 13:20

Valerie Parham-Thompson · Accepted Answer · 2023-01-07 12:54:33Z

0

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;

answered Jan 7, 2023 at 12:54

Valerie Parham-Thompson

1,5861 gold badge11 silver badges21 bronze badges

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

2 Comments

Sanjay Kumar Over a year ago

Hi Valerie Parham Thompson. Thank You So much for your time. I still face a difficulty which is, I don't want to add the columns into the array manually, because in future I may add more subject columns to the table. I don't want to alter the code everytime I modify the columns of the table. Can you suggest some other ways?

Valerie Parham-Thompson Over a year ago

The above is for converting existing data into an array. Could you share the code you are using to clarify the question?

Juan Castillo · Accepted Answer · 2023-01-07 13:03:01Z

0

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.

answered Jan 7, 2023 at 13:03

Juan Castillo

761 silver badge10 bronze badges

Collectives™ on Stack Overflow

How to store all the values in a row to array in postgresql?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related