Обсуждение: insert into inet from text automatically adding subnet
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
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
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
Jeremy Finzel <finzelj@gmail.com> writes: > 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. I think this is just a complex version of this behavior: regression=# select '127.0.0.0'::inet; inet ----------- 127.0.0.0 (1 row) regression=# select '127.0.0.0'::inet::text; text -------------- 127.0.0.0/32 (1 row) That's documented in table 9.38 in https://www.postgresql.org/docs/current/functions-net.html where it says text(inet) text extract IP address and netmask length as text text(inet '192.168.1.5') 192.168.1.5/32 Admittedly, there's not an explicit mention here that this is also describing the behavior of a cast to text, though you could infer that if you remembered the discussion at https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS It's also worth noting that per the definition of the inet type at https://www.postgresql.org/docs/current/datatype-net-types.html#DATATYPE-INET these two strings are equivalent anyway, because /32 is the default assumption for an IPv4 inet value. inet_out is just omitting the netmask when it has the default value. There's also, a bit further down on that page, Tip If you do not like the output format for inet or cidr values, try the functions host, text, and abbrev. So you might try host() or abbrev() to get a text conversion you like better. (My very vague recollection is that this state of affairs emerged because of disagreements over exactly how the text conversion ought to work in such cases. It'd probably be better if the default conversion to text matched what inet_out does, but that's water over the dam now; changing it twenty years later would cause more problems than it'd solve.) regards, tom lane