insert into inet from text automatically adding subnet

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

insert into inet from text automatically adding subnet

jfinzel
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
Reply | Threaded
Open this post in threaded view
|

Re: insert into inet from text automatically adding subnet

Tom Lane-2
Jeremy Finzel <[hidden email]> 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