Another thing I realized when testing this is that partitioning over a
domain doesn't work very nicely (tested in 10 and master):
create domain overint as int;
create table pt (a overint) partition by range (a);
create table pt1 partition of pt for values from (0) to (100);
results in:
ERROR: specified value cannot be cast to type overint for column "a"
LÍNEA 1: create table pt1 partition of pt for values from (0) to (100...
^
DETALLE: The cast requires a non-immutable conversion.
SUGERENCIA: Try putting the literal value in single quotes.
I tried to do what the HINT says, but it fails in the same way. I also
tried to add casts, but those are rejected as syntax errors.
Tracing it down, turns out that transformPartitionBoundValue gets from
coerce_to_target_type a CoerceToDomain node. It then tries to apply
expression_planner() to simplify the expression, but that one doesn't
want anything to do with a domain coercion (for apparently good reasons,
given other comments in that file). However, if we take out the
expression_planner() and replace it with a call to
strip_implicit_coercions(), not only it magically starts working, but
also the regression tests start failing with the attached diff, which
seems a Good Thing to me.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
*** /pgsql/source/REL_10_STABLE/src/test/regress/expected/create_table.out
2018-07-09 13:13:57.397491338 -0400
---
/home/alvherre/Code/pgsql/build/REL_10_STABLE/src/test/regress/results/create_table.out
2018-07-09 17:43:00.794556357 -0400
***************
*** 496,507 ****
a money
) PARTITION BY LIST (a);
CREATE TABLE moneyp_10 PARTITION OF moneyp FOR VALUES IN (10);
- ERROR: specified value cannot be cast to type money for column "a"
- LINE 1: ...EATE TABLE moneyp_10 PARTITION OF moneyp FOR VALUES IN (10);
- ^
- DETAIL: The cast requires a non-immutable conversion.
- HINT: Try putting the literal value in single quotes.
CREATE TABLE moneyp_10 PARTITION OF moneyp FOR VALUES IN ('10');
DROP TABLE moneyp;
-- immutable cast should work, though
CREATE TABLE bigintp (
--- 496,503 ----
a money
) PARTITION BY LIST (a);
CREATE TABLE moneyp_10 PARTITION OF moneyp FOR VALUES IN (10);
CREATE TABLE moneyp_10 PARTITION OF moneyp FOR VALUES IN ('10');
+ ERROR: relation "moneyp_10" already exists
DROP TABLE moneyp;
-- immutable cast should work, though
CREATE TABLE bigintp (
======================================================================
