Search code examples
insertpsqlcomposite-types

PSQL custom type insertion results NULL

I have a table and a composite type:

CREATE TYPE server AS (
    id      text,
    load    int
)
CREATE TABLE live_log (
    id              serial      PRIMARY KEY,
    edges           server[],
    origins         server[],
    time            time        NOT NULL,
    date            date        NOT NULL,
    day             text
)

I'm trying to insert a row in live_log table:

INSERT INTO live_log(edges, origins, day, date, time)
VALUES (
    ARRAY[ROW('edge1', 20),ROW('edge2', 30)]::server[],
    ARRAY[ROW('origin1', 20),ROW('origin2', 30)]::server[],
    'Monday',
    '11-01-1994',
    '11:00:30'
);

But the result of insertion of composite type arrays remains null, regardless to any changes at INSERT query. my db results

Can anyone help me to figure out whats wrong with my INSERT?

Solution

  • Write a select query to cast the arrays to text and DBeaver will give you a representation of the server array:

    SELECT 
    id,
    edges::text,
    origins::text,
    time,
    date,
    day
FROM live_log

    DBeaver Output Window

    DBeaver doesn't know how to represent the "server" type. From what I can tell, DBeaver checks against the standard Postgres types and if the type doesn't match one of those, it bails and throws the following:

    org.jkiss.dbeaver.model.exec.DBCException: Can't resolve struct type 'server'

    While you can't directly open the table object and view the "Data" tab using this method, you could create a view based on the table for easier inspection.

    Also, serge-rider is usually responsive to feature requests submitted via GitHub. Don't want to speak on his behalf, but this might be a case for an enhancement.