CODEWITHSUNDEEP
sqlpostgresql
ocket8888
ocket8888

Reputation: 1140

Cannot cast to type alias

I created a type alias gp for the PostgreSQL type money, and I can't figure out from the documentation how I'm supposed to use this.

I've set up a test table (and database):

CREATE TYPE gp AS (amt money);
CREATE TABLE test (val gp PRIMARY KEY);

But no matter how I try to insert rows, it fails

INSERT INTO test VALUES (1); -- column "val" is of type gp but expression is of type integer
INSERT INTO test VALUES ((1)); -- column "val" is of type gp but expression is of type integer
INSERT INTO test VALUES (CAST (1 AS gp)); -- cannot cast type integer to gp
INSERT INTO test VALUES (CAST (CAST (1 AS money) AS gp)); -- cannot cast type money to gp
INSERT INTO test VALUES ((amt=1)); -- column "amt" does not exist
INSERT INTO test VALUES ((amt=1)::gp); -- column "amt" does not exist

From what I understand, the gp type should be an alias: gp::money::numeric, so why can't it cast from money to an alias of money?

Upvotes: 2

Views: 340

Answers (2)

klin
klin

Reputation: 121754

You should use a row constructor:

insert into test values (row(1));

Per the documentation:

The key word ROW is optional when there is more than one expression in the list.

Note, that a composite type with a single component makes a little sense. You can use a domain instead.

Upvotes: 2

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30625

Check the below snippet

CREATE TYPE gp AS (amt money);
CREATE TABLE test (val gp PRIMARY KEY);
INSERT INTO test VALUES (row(1::money))

✓

✓

1 rows affected

db<>fiddle here

Upvotes: 0

Related Questions