I have declared a custom domain tmoney
as
create domain tmoney as decimal (13,4);
Then I use an array of it in a table declaration,
create table test (
id int generated by default as identity primary key,
volume smallint[5] not null default '{0, 0, 0, 0, 0}',
price tmoney[5] not null default '{0, 0, 0, 0, 0}'
);
insert into test(volume, price)
values ('{1, 10, 50, 100, 250}', '{10, 9.75, 9.5, 9, 8.75}');
In PostgreSQL 12 there is no parsing exceptions, as it seems to have existed before (see Create array of custom domain postgres), however, a DBCException is found whenever I try to retrieve the values inserted as tmoney[]. Note that this error does not occur with smallint[].
select * from test;
id|volume |price |
--|-----------------|---------------------------------------------|
1|{1,10,50,100,250}|DBCException: Can't resolve data type _tmoney|
The documentation at https://www.postgresql.org/docs/current/sql-createdomain.html only specifies that
tdata_type – The underlying data type of the domain. This can include array specifiers.
This is consistent with a domain created as
create domain tmoney as decimal (13,4)[];
create table test (
id int generated by default as identity primary key,
volume smallint[5] not null default '{0, 0, 0, 0, 0}',
price tmoney not null default '{0, 0, 0, 0, 0}'
);
insert into test(volume, price)
values ('{1, 10, 50, 100, 250}', '{10, 9.75, 9.5, 9, 8.75}');
select * from test;
id|volume |price |
--|-----------------|------------------------|
1|{1,10,50,100,250}|{10.0,9.75,9.5,9.0,8.75}|
However, as the PostgreSQL 12 parser does not prevent using tmoney[5]
in a table declaration, I am wondering if there is a different syntax that allows me to use this first version of the custom domain.
Using arrays of domains was introduced in v11.
Your SQL statements work just fine with
psql
.You must be using a different client that probably doesn't support that properly. Consider filing a bug report or enhancement request with that software.