CODEWITHSUNDEEP
sqlarrayspostgresqltypes
Vinicius Roberto
Vinicius Roberto

Reputation: 5

Insert an array of custom type into a table in postgresql

I'm studying SQL using PostgreSQL, but now I have a problem that I can't resolve. I have a type that I call Phones:

CREATE TYPE Phone as 
(
  DDD varchar(3),
  Number Varchar(10),
  TypeOf Varchar(15)
)

And i have to create a Array of Phones into the table Students:

CREATE TABLE Students
(
   Id INT NOT NULL,
   PRIMARY KEY (Id),
   name Varchar(50),
   status Char,
   codCourse int,
    FOREIGN KEY (codCourse )  REFERENCES Course (IdCourse) ON DELETE CASCADE,
   phones Phone[] 
)

And I have created. But, when i try insert into Students table like that:

INSERT INTO Students (Id, name, status, codCourse , phones) 
VALUES (
    00001, 'Joaquim Soares Fernando', 'I', 4,       
    Array[('22','33548795','Telefone')])

I got this error message:

ERROR: column "phones" is of type phone[] but expression is of type record[]
SQL state: 42804
Hint: You will need to rewrite or cast the expression.
Character: 118"

How can i do that insert?

Upvotes: 0

Views: 1536

Answers (1)

klin
klin

Reputation: 121899

You have to explicitly cast an array to the type phone[]:

INSERT INTO students (id, name, status, codcourse , phones) 
VALUES (
    00001, 'Joaquim Soares Fernando', 'I', 4, 
    array[('22','33548795','Telefone')]::phone[]);

Upvotes: 1

Related Questions