CODEWITHSUNDEEP
arrayspostgresql
bobmarksie
bobmarksie

Reputation: 3646

PostgreSQL matrix transformations

I have a PostgreSQL table called test which has 2 columns - (1) id & (2) matrix as follows: -

create table test (id integer, 
                   matrix double precision[]);

insert into test (id, matrix) values
    (1, '{ 0.1,  0.2,  0.3,  0.4}'),
    (2, '{-0.1, -0.2, -0.3, -0.4}'),
    (3, '{ 0.2, -0.2,  0.4, -0.4}'),
    (4, '{-0.5,  0.6, -0.7,  0.8}');

The matrix column is always 4 in size and numbers ranging from -1 to 1.

I want to transform matrix into an array of size 8 - lets call it matrix_2. If we assume array indexes start at 1 (PostgreSQL style) then: -

SQL wise, this is as far as I have got: -

select 
    id, 
    matrix,   -- in purely for comparing numbers
    (
        case when matrix[1] < 0::double precision then @matrix[1] else 0::double precision end,
        case when matrix[1] > 0::double precision then matrix[1] else 0::double precision end,    
        case when matrix[2] < 0::double precision then @matrix[2] else 0::double precision end,
        case when matrix[2] > 0::double precision then matrix[2] else 0::double precision end,
        case when matrix[3] < 0::double precision then @matrix[3] else 0::double precision end,
        case when matrix[3] > 0::double precision then matrix[3] else 0::double precision end,
        case when matrix[4] < 0::double precision then @matrix[4] else 0::double precision end,
        case when matrix[4] > 0::double precision then matrix[4] else 0::double precision end
    ) as matrix_2
from 
    test;

The returns the following: -

---------+-----------------------+--------------------------
id       | matrix                |  matrix_2
interger | double precision []   |  record
---------+-----------------------+--------------------------
1        | {0.1,0.2,0.3,0.4}     | (0,0.1,0,0.2,0,0.3,0,0.4)
2        | {-0.1,-0.2,-0.3,-0.4} | (0.1,0,0.2,0,0.3,0,0.4,0)
3        | {0.2,-0.2,0.4,-0.4}   | (0,0.2,0.2,0,0,0.4,0.4,0)
4        | {-0.5,0.6,-0.7,0.8}   | (0.5,0,0,0.6,0.7,0,0,0.8)
---------+-----------------------+--------------------------

The query returns the correct values in the correct order but: -

  1. matrix_2 is of type record - not an array of double precision []
  2. SQL query is pretty horrible i.e. lots of repetition - could defo be more DRY.

Does anyone have any advice on the aforementioned 2 points?

Upvotes: 0

Views: 619

Answers (1)

krokodilko
krokodilko

Reputation: 36107

You may try something linke below:

SELECT * 
FROM test,
LATERAL (
    SELECT array_agg( val ) As matrix_2
    FROM (
        SELECT xxx, 1 as ord, 
               case when  matrix[ xxx ] < 0 then abs(matrix[ xxx ]) else 0 end as val
        FROM generate_subscripts( matrix, 1) xxx
        UNION ALL
        SELECT xxx, 2 as ord, 
               case when  matrix[ xxx ] > 0 then abs(matrix[ xxx ]) else 0 end as val
        FROM generate_subscripts( matrix, 1) xxx
        ORDER BY xxx, ord
    ) q
) x;

Upvotes: 1

Related Questions