CODEWITHSUNDEEP
sqlpostgresqltypes
pifor
pifor

Reputation: 7892

Why is syntax different for composite types in postgreSQL INSERT statements and depends on the fact that the data type is named or unnamed?

If I use unnamed composite types in tables I can use following syntax in INSERT:

create table t1(
    c1  varchar(2) array[2] 
);
CREATE TABLE

create table t2(
    c1  varchar(2)  
);
CREATE TABLE

create table t3(
    c1  int array[2]    
);
CREATE TABLE

insert into t1 values ('{"aa", "bb"}');
INSERT 0 1

insert into t2 values ('aa');
INSERT 0 1

insert into t3 values ('{ 1, 2 }');
INSERT 0 1

But if I use names types it does not work:

CREATE TYPE test1 AS (field varchar(2) ARRAY[2]);
CREATE TYPE

CREATE TYPE test2 AS (field varchar(2) );
CREATE TYPE

CREATE TYPE test3 AS (field int ARRAY[2]);
CREATE TYPE

create table tt1(
    c1  test1   
);
CREATE TABLE

create table tt2(
    c1  test2   
);
CREATE TABLE

create table tt3(
    c1  test3   
);

CREATE TABLE

insert into tt1 values ('{"aa", "bb"}');
psql:tt0.sql:37: ERROR:  malformed record literal: "{"aa", "bb"}"
LINE 1: insert into tt1 values ('{"aa", "bb"}');
                                ^
DETAIL:  Missing left parenthesis.


insert into tt2 values ('aa');
psql:tt0.sql:38: ERROR:  malformed record literal: "aa"
LINE 1: insert into tt2 values ('aa');
                                ^
DETAIL:  Missing left parenthesis.

insert into tt3 values ('{ 1, 2 }');
psql:tt0.sql:39: ERROR:  malformed record literal: "{ 1, 2 }"
LINE 1: insert into tt3 values ('{ 1, 2 }');
                                ^
DETAIL:  Missing left parenthesis.

If I add parenthesis it still does not work:

insert into tt1 values (('{"aa", "bb"}'));
ERROR:  malformed record literal: "{"aa", "bb"}"
LINE 1: insert into tt1 values (('{"aa", "bb"}'));
                                 ^
DETAIL:  Missing left parenthesis.

insert into tt2 values (('aa'));
ERROR:  malformed record literal: "aa"
LINE 1: insert into tt2 values (('aa'));
                                 ^
DETAIL:  Missing left parenthesis.

insert into tt3 values (('{ 1, 2 }'));
ERROR:  malformed record literal: "{ 1, 2 }"
LINE 1: insert into tt3 values (('{ 1, 2 }'));
                                 ^
DETAIL:  Missing left parenthesis.

If i use ROW and ARRAY syntax it works:

insert into tt1(c1) values (ROW(ARRAY ['aa', 'bb' ]));
INSERT 0 1

insert into tt2 values (ROW ('aa'));
INSERT 0 1

insert into tt3 values (ROW(ARRAY[ 1, 2 ]));
INSERT 0 1

I have used PostgreSQL 12.2.

Upvotes: 0

Views: 374

Answers (1)

JGH
JGH

Reputation: 17906

It works in the 1st case because the cast between your input and the expected datatype exists by default

ex: 

select '1'::int;
 int4
------
    1
(1 row)

It does not work for the custom type because nobody wrote the cast.

CREATE TYPE testtype AS (field int);
select '1'::testtype;
ERROR:  malformed record literal: "1"
LINE 1: select '1'::testtype;
               ^
DETAIL:  Missing left parenthesis.

It does work if you construct a row before the doing the cast, because you are not casting directly to the type anymore but rather each underlying components (which is a single one in this example), which in turn are compatible with the custom type

select row('1')::testtype;
 row
-----
 (1)

Upvotes: 2

Related Questions