Postgres Insert elements to array inside user defined type column-CodePudding

So I'm trying to insert 3 elements inside an array found inside a user defined type which is a column of a table. This is what I have right now:

INSERT INTO pais(pais)
VALUES (ROW ('Espanya', ARRAY['Barcelona', 'Madrid', 'Sevilla'])::t_pais);

The type looks like this:

CREATE TYPE t_pais AS
(
    nom_pais VARCHAR(35),
    localitzacions t_localitzacio ARRAY
);

The t_localitzacio type looks like this:

create type t_localitzacio as
(
    nom_localitzacio varchar(35)
);

And the table looks like this:

CREATE TABLE Pais
(
    id_pais serial PRIMARY KEY,
    pais t_pais
);

And i'm getting the following error:

malformed record literal: <<Barcelona>>

Thanks in advance!

CodePudding user response：

In your insert, you declare the array to be of type t_pais, but the table expects an array of t_localitzacio:

INSERT INTO pais(pais)
VALUES (ROW ('Espanya', ARRAY['Barcelona', 'Madrid', 'Sevilla'])::t_localitzacio);

But why do you do want to store the cities as an array in the country table in the first place? You will get much better performance by using a standard SQL normalized table design:

CREATE TABLE country (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);
CREATE TABLE city (
    id SERIAL PRIMARY KEY,
    country_id INT,
    name VARCHAR(255)
);
INSERT INTO country (id, name) VALUES
(1, 'Espanya');
INSERT INTO city (id, country_id, name) VALUES
(1, 1, 'Barcelona'),
(2, 1, 'Madrid'),
(3, 1, 'Sevilla');

SELECT city.*
FROM country
  LEFT JOIN city ON city.country_id = country.id
WHERE country.name = 'Espanya'

CodePudding user response：

If you simplify the t_pais type like this :

CREATE TYPE t_pais AS
(
    nom_pais VARCHAR(35),
    localitzacions VARCHAR(35)[]
);

then your insert will work.