Re: insert into inet from text automatically adding subnet

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