Question 90237 in PostgreSQL Question posted in
The official documentation can be found here.

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

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]

Tags:

2

Answers


  1. 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;
    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search