CODEWITHSUNDEEP
sqlpostgresql
Souvik Ray
Souvik Ray

Reputation: 3038

Store a 2D array in postgres

I have a 2D array which looks like below

[['2018-05-15', 6.7580658761256265], ['2018-05-16', 7.413963464926804], ['2018-05-17', 8.801776892107043], ['2018-05-18', 10.292505686766823], ['2018-05-19', 10.292505686766823], ['2018-05-20', 10.292505686766823], ['2018-05-21', 10.292505686766823]]

I want to store this 2D array in postgres.I checked postgres's documentation https://www.postgresql.org/docs/9.1/static/arrays.html and found the below example

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

INSERT INTO sal_emp
    VALUES ('Bill',
    ARRAY[10000, 10000, 10000, 10000],
    ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);

For a list that contains both of its values as string ['meeting', 'lunch'], we use text[][] But as you can see, I have one string and one float as a content of a list

['2018-05-15', 6.7580658761256265]

Then what do I set the column type as?

Upvotes: 1

Views: 5793

Answers (1)

sticky bit
sticky bit

Reputation: 37487

It's not an array anymore if the types are different. Arrays only can have one base type. You'll have to use an array of a composite type instead.

CREATE TYPE your_type AS (text_member text,
                          float_member float);

CREATE TABLE your_table
             (your_type_column your_type[]);

INSERT INTO your_table
            (your_type_column)
            VALUES (array[row('2018-05-15',
                              6.7580658761256265)::your_type,
                          row('2018-05-16',
                              7.413963464926804)::your_type,
                          ...
                          row('2018-05-21',
                              10.292505686766823)::your_type]);

But maybe consider not using arrays at all if possible, have another table and a normalized schema. Also consider using date instead of text if it's actually a date. (I just used text because you said it was a "string", though the sample data suggests differently.)

Upvotes: 5

Related Questions