From: | Jeremy Finzel <finzelj(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | insert into inet from text automatically adding subnet |
Date: | 2019-10-30 11:36:00 |
Message-ID: | CAMa1XUjEEB3Jm-mBHJaBuP=0ohb5cU53Tbx00-J-6cvbjXFsHQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | PG Bug reporting form | 2019-10-30 12:54:31 | BUG #16089: Index only scan does not happen but expected |
Previous Message | Federico | 2019-10-30 09:59:59 | Re: BUG #16053: Query planner performance regression in sql query with multiple CTE in v12 |