postgresql – Get values ​​from a composite type

Question:

There is a type:

   CREATE TYPE ComprositiontTransfer AS (
        Id BIGINT,
        Name CHARACTER VARYING,
        COUNT BIGINT,
        Price NUMERIC,
        IdExport CHARACTER VARYING,  
        IdImport CHARACTER VARYING
    );

In the table:

CREATE TABLE "Transfer" (
    "Id" serial NOT NULL,
    "Date" TIMESTAMP WITHOUT TIME zone NOT NULL,
    "OrgPostav" CHARACTER VARYING NOT NULL,
    "NameSklad" CHARACTER VARYING NOT NULL,
    "Сomposition" ComprositiontTransfer[] NOT NULL,
    "Operator" CHARACTER VARYING NOT NULL,
    "Total" NUMERIC,
    "Changed" BOOLEAN,
    "OneCid" CHARACTER VARYING,
    CONSTRAINT Transfer_pk PRIMARY KEY ("Id")
) WITH (
  OIDS=FALSE
);

How can I, for example, receive all transportation for the specified employee? (IdExport field in composite type)

Answer:

You can do it like this:

 SELECT id,  u  FROM "Transfer", unnest("Transfer"."Сomposition") u WHERE u.IdExport = '25' ;
Scroll to Top
AllEscort