CODEWITHSUNDEEP
sqlpostgresqlpsqlpostgresql-9.4
Dan
Dan

Reputation: 33

Create "counter" array in psql

I have a table in postgresql-9.4.2 with an array column.

create schema test_schema;
create table test_schema.test_table;
(
    array_column int[]
);
insert into test_schema.test_table values
    (ARRAY[5,12,6,2]),
    (ARRAY[51,4,2]),
    (ARRAY[2]),
    (ARRAY[3,16]);

Which will look like this

    | array_column |
    | integer[]    |
--------------------
  1 | {5,12,6,2}   |
  2 | {51,4,2}     |
  3 | {2}          |
  4 | {3,16}       |

I want to query this table, and add a column to my query which is an array counting from 1 to the size of the array.

I have figured out how to create an array of the same size as shown below

select
    array_column,
    array_fill(1,array[array_length(array_column,1)],array[1]) as counter
from
    test_schema.test_table;

which returns the following results

    | array_column | counter    |
    | integer[]    | integer[]  |
---------------------------------
  1 | {5,12,6,2}   | {1,1,1,1}  |
  2 | {51,4,2}     | {1,1,1}    |
  3 | {2}          | {1}        |
  4 | {3,16}       | {1,1}      |

I was going to take the rolling sum of counter which would give me the desired result, but I can't figure out how to do it.

This is the desired result:

    | array_column | counter    |
    | integer[]    | integer[]  |
---------------------------------
  1 | {5,12,6,2}   | {1,2,3,4}  |
  2 | {51,4,2}     | {1,2,3}    |
  3 | {2}          | {1}        |
  4 | {3,16}       | {1,2}      |

Thank you for your help

Upvotes: 3

Views: 143

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125254

select array_column, a
from
    test_table
    cross join lateral (
        select array_agg(a)
        from generate_series(1, array_length(array_column, 1)) s(a)
    ) s(a)
;

Upvotes: 1

klin
klin

Reputation: 121604

Use function generate_subscripts(array anyarray, dim int):

select
    array_column,
    array_agg(subscripts) subscripts
from (
    select
        array_column, 
        generate_subscripts(array_column, 1) subscripts
    from
        test_schema.test_table
    ) sub
group by 1;

 array_column | subscripts 
--------------+------------
 {2}          | {1}
 {3,16}       | {1,2}
 {5,12,6,2}   | {1,2,3,4}
 {51,4,2}     | {1,2,3}
(4 rows)

Upvotes: 1

Related Questions