PostgreSQL matrix transformations

Question

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: -

matrix_2[1] is the absolute (positive) value of matrix[1] (but only if matrix[1] < 0) and
matrix_2[2] is the value of matrix[1] (but only if matrix[1] > 0).
(matrix_2[3] and matrix_2[4] follow the same pattern for values of matrix[2] and so on ...)
Values of 0 in the matrix array become values of 0 in the 2 entries in the matrix_2 array.

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: -

matrix_2 is of type record - not an array of double precision []
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?

Just small question - there is R lang for Postgresql - PL/R and it allows all these operations in very easy way. Maybe you could try this? SQL or PL/pgSQL is not the best choice for these things.... — JosMac
– JosMac, Commented Dec 10, 2016 at 12:40
I may go down this route eventually but it's basically to create a database view which I can apply further aggregations on, filtering, sorting etc. — bobmarksie
– bobmarksie, Commented Dec 10, 2016 at 13:13
PL/R allows you to use pg tables of course. No problem here. Beauty of PostgreSQL is you can have one set of tables and work with them with procedures written in different languages. I for example combine PL/pgSQL and Javascript for PostgreSQL PL/V8 in one database - because I work heavily with JSON data and PL/pgSQL is also quite poor in working with JSON. — JosMac
– JosMac, Commented Dec 10, 2016 at 13:18

krokodilko · Accepted Answer · 2016-12-10 13:40:44Z

1

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;

answered Dec 10, 2016 at 13:40

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

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

1 Comment

bobmarksie Over a year ago

Works beautifully - nice one!

Collectives™ on Stack Overflow

PostgreSQL matrix transformations

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related