This appears to me to be a bug (using 11.4). But I would like some review to ensure I am not missing some intended functionality or nuance with this data type. The example below seems certainly surprising.
I found that using a writable CTE to insert an IP address without a subnet into a *text* field, then inserting result into another table with type *inet*, is automatically adding a subnet. This is the only case in which I found this happens.
In demo below, you can see everything returns 127.0.0.0, except for this 2-step writable CTE example. Also if I alter the type of the text field to inet, the subnet addition goes away. So it seems to only affect text -> inet insert.
Step to reproduce:
postgres=# CREATE TEMP TABLE a (host text);
CREATE TABLE
postgres=# CREATE TEMP TABLE b (host inet);
CREATE TABLE
postgres=# WITH insert_b AS (
INSERT INTO b VALUES ('127.0.0.0')
RETURNING *
)
SELECT host FROM insert_b;
host
-----------
127.0.0.0
(1 row)
postgres=# WITH insert_b AS (
INSERT INTO b VALUES ('127.0.0.0')
RETURNING *
)
INSERT INTO a SELECT host FROM insert_b RETURNING *;
host
--------------
127.0.0.0/32(1 row)
INSERT 0 1
postgres=# ALTER TABLE a ALTER COLUMN host TYPE inet USING host::inet;
ALTER TABLE
postgres=# WITH insert_b AS (
INSERT INTO b VALUES ('127.0.0.0')
RETURNING *
)
INSERT INTO a SELECT host FROM insert_b RETURNING *;
host
-----------
127.0.0.0
(1 row)
INSERT 0 1
Thanks,
Jeremy
