Saving a Dictionary(Of String, Int16()) into a PostgreSQL cell

Question

I have a Dictionary(of String, INT16()). This dictionary represents analog values from a measurement. I want to store this dictionary into one cell of a PostgreSQL database. I am using Npgsql.

I was searching a Long time, but I have no idea how to do that.

My thought was to serialize the dictionary and save it as an array, but i am not sure if this is the correct way.

Is there anybody out there, who can help me in this matter?

Thanks for your answers

Answer 1

Have a look at CREATE TYPE

CREATE TYPE StringIntPairType AS (k string,v int);

UPDATE: Here is a full example of how to accomplish this from pg sql

Define the element type of the dictionary

CREATE TYPE StringIntPairType AS (k text,v int);

Create a sample table. note that the collection column is marked '[]' at the end

CREATE TABLE CollectionsTables
(
  Id SERIAL UNIQUE,
  Collection  StringIntPairType[],
  Description TEXT,
  TS TIMESTAMP NOT NULL DEFAULT now()
)

Inserts a single record with two dictionary elements

INSERT INTO CollectionsTables(Collection,Description) VALUES
(
    array
    [
       ('Test1',1)::StringIntPairType,
       ('Test2',2)::StringIntPairType
    ],
    'Description1'
);

Inserts another single record with two dictionary elements

INSERT INTO CollectionsTables(Collection,Description) VALUES
        (
            array
            [
               ('TestA',30)::StringIntPairType,
               ('TestB',40)::StringIntPairType
            ],
            'Description2'
        );

Note that this is regular table just like any other table

SELECT * FROM CollectionsTables

Here is some more advanced select.

SELECT Collection[1].k,Description FROM CollectionsTables

First column: The key of the first dictionary value
Second column: the description column