Macaddr/Inet类型的postgres [英] Macaddr/Inet type of postgres in slick
问题描述
我的应用程序使用slick(v2.0.0)管理一个postgresql(9.1)数据库.
我的一张表包含网络设备，因此包含MAC地址和ip地址.我使用了postgres类型macaddr和inet，因为它们似乎是完成任务的理想工具.现在，我想使用光滑的emebedding，但很难定义我的表.当我为表自动生成代码时，我注意到使用了String而不是那些类型，但是并不介意.
这对于从数据库中读取数据很好，但是当我尝试更新或插入一行时会导致
org.postgresql.util.PSQLException: ERROR:
column "mac" is of type macaddr but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
看起来很合乎逻辑.
现在的问题是我该如何告诉滑溜溜:
- 此列实际上是
macaddr/inet类型
或
- 此列必须在插入之前进行转换，否则可以视为字符串
?
更新:
如Craig所述，我已经使用围绕macaddr_in和inet_in
\dC macaddr
List of casts
Source type | Target type | Function | Implicit?
-------------+-------------+----------------+-----------
text | macaddr | macaddr_intext | yes
\dC inet
List of casts
Source type | Target type | Function | Implicit?
-------------+-------------------+--------------------+---------------
cidr | inet | (binary coercible) | yes
inet | character | text | in assignment
inet | character varying | text | in assignment
inet | cidr | cidr | in assignment
inet | text | text | in assignment
text | inet | inet_intext | yes
\df+ macaddr_intext
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Owner | Language | Source code | Description
--------+----------------+------------------+---------------------+--------+------------+----------+----------+---------------------------------+-------------
public | macaddr_intext | macaddr | text | normal | immutable | postgres | sql | |
: select macaddr_in($1::cstring);
:
\df+ inet_intext
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Owner | Language | Source code | Description
--------+-------------+------------------+---------------------+--------+------------+----------+----------+------------------------------+-------------
public | inet_intext | inet | text | normal | immutable | postgres | sql | |
: select inet_in($1::cstring);
:
错误消息仍然与上面显示的完全相同.
再现的劝告:
psql <tablename>中:create or replace function macaddr_intext(text) returns macaddr as $$
select macaddr_in($1::cstring);
$$ language sql immutable;
create cast (text as macaddr) with function macaddr_intext(text) as implicit;
create or replace function inet_intext(text) returns inet as $$
select inet_in($1::cstring);
$$ language sql immutable;
create cast (text as inet) with function inet_intext(text) as implicit;
Update2:
我将其范围缩小为权限错误，因为如果我以用户postgres身份运行:
mydb=# create table test(i inet, m macaddr)
CREATE TABLE
mydb=# insert into test values ('1.1.1.1'::text, '00:00:00:00:00:00'::text);
INSERT 0 1
但是如果我尝试以实际尝试插入的用户身份运行
mydb=> insert into test values ('1.1.1.1'::text, '00:00:00:00:00:00'::text);
ERROR: permission denied for relation test
设置我已运行的数据库时:
template1=# GRANT ALL PRIVILEGES ON DATABASE mydb to myuser;
Update3:
Update2原来只是问题所在，因为创建的表由postgres而不是myuser
这是许多人使用json或XML遇到的问题的一个变体，并归结为PostgreSQL对转换之间的转换过于严格的事实数据类型.
请参见此答案，其中讨论了与json类似的问题.使用转换功能创建演员表的方法与此相同.
您的演员表功能是macaddr_in和inet_in.由于某些令人烦恼的类型问题，您需要编写带有text参数的包装SQL函数.请参阅上面的链接.
另请参见与xml类型相关的答案.
更新后，我测试了您的功能，发现它们可以正常工作:
postgres=# CREATE TABLE inetmac (i inet, m macaddr);
CREATE TABLE
postgres=# PREPARE insinet(text) AS INSERT INTO inetmac(i) VALUES ($1);
PREPARE
postgres=# EXECUTE insinet('10.1.1.1');
INSERT 0 1
postgres=#
...但是，令人惊讶的是，Pg并没有隐式地从varchar强制转换为text以便使用text强制转换:
postgres=# PREPARE insinet(varchar) AS INSERT INTO inetmac(i) VALUES ($1);
ERROR: column "i" is of type inet but expression is of type character varying
LINE 1: ...PARE insinet(varchar) AS INSERT INTO inetmac(i) VALUES ($1);
^
HINT: You will need to rewrite or cast the expression.
如果您使用的是varchar输入，则需要使用varchar而不是text输入的另一组强制转换.
请注意，如果您只是做到这一点，就不会发生
INSERT INTO inetmac(i) VALUES ('10.1.1.1');
直接，因为这里'10.1.1.1是伪类型unknown，并且被解释为inet，因为这就是输入所需要的.有点像是可隐式转换为任何输入函数的类型.相比之下，text和varchar是Pem必须参考强制转换规则的具体类型，因此，如果不创建强制转换，则将不起作用:
INSERT INTO inetmac(i) VALUES ('10.1.1.1'::text);
我要在-hackers列表上对此大惊小怪，让人很难从客户端接口使用PostgreSQL的扩展类型，这真让人痛苦.当然，客户机接口应该公开了一种方法来告诉JDBC驱动程序基本类型是什么，但是我们很幸运，如果大多数事情都可以处理诸如索引和复合键之类的极端基础知识，更不用说详细了类型处理. Pg确实需要对此稍加注意，或者提供一种在参数绑定中指定'unknown'伪类型的方法.
大惊小怪:
My application uses slick (v2.0.0) to manage a postgresql (9.1) database.
One of my tables contains network devices and therefore mac- and ip-addresses. I used the postgres types macaddr and inet as they seemed like the perfect tool for the task. Now I want to use slick with lifted emebedding but struggle to define my tables. When I auto generated the code for my tables I noticed that String was used instead of those types but didn't mind.
This works fine for reading from the DB but when I try to update or insert a row it causes
org.postgresql.util.PSQLException: ERROR:
column "mac" is of type macaddr but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
wich seems pretty logical.
Now the question is how can I tell slick that:
- This collumn is actually of type
macaddr/inet
or
- This collumn needs to be casted before insertion but can otherwise be treated as a string
?
Update:
As Craig described I have created implicit casts using wrapper functions around macaddr_in and inet_in
\dC macaddr
List of casts
Source type | Target type | Function | Implicit?
-------------+-------------+----------------+-----------
text | macaddr | macaddr_intext | yes
\dC inet
List of casts
Source type | Target type | Function | Implicit?
-------------+-------------------+--------------------+---------------
cidr | inet | (binary coercible) | yes
inet | character | text | in assignment
inet | character varying | text | in assignment
inet | cidr | cidr | in assignment
inet | text | text | in assignment
text | inet | inet_intext | yes
\df+ macaddr_intext
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Owner | Language | Source code | Description
--------+----------------+------------------+---------------------+--------+------------+----------+----------+---------------------------------+-------------
public | macaddr_intext | macaddr | text | normal | immutable | postgres | sql | |
: select macaddr_in($1::cstring);
:
\df+ inet_intext
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Owner | Language | Source code | Description
--------+-------------+------------------+---------------------+--------+------------+----------+----------+------------------------------+-------------
public | inet_intext | inet | text | normal | immutable | postgres | sql | |
: select inet_in($1::cstring);
:
Errormessage is still exactly the same as shown above.
Commads to reproduce:
in psql <tablename>:
create or replace function macaddr_intext(text) returns macaddr as $$
select macaddr_in($1::cstring);
$$ language sql immutable;
create cast (text as macaddr) with function macaddr_intext(text) as implicit;
create or replace function inet_intext(text) returns inet as $$
select inet_in($1::cstring);
$$ language sql immutable;
create cast (text as inet) with function inet_intext(text) as implicit;
Update2:
I narrowed it down to a permission error because if I run as user postgres:
mydb=# create table test(i inet, m macaddr)
CREATE TABLE
mydb=# insert into test values ('1.1.1.1'::text, '00:00:00:00:00:00'::text);
INSERT 0 1
but if I try to run it as the user that actually tries to insert
mydb=> insert into test values ('1.1.1.1'::text, '00:00:00:00:00:00'::text);
ERROR: permission denied for relation test
when setting up the database I had run:
template1=# GRANT ALL PRIVILEGES ON DATABASE mydb to myuser;
Update3:
Update2 turned out to be only the problem because the created table was owned by postgres instead of myuser
This is a variant of the problem many people have with json or XML and comes down to the fact that PostgreSQL is way too strict about casts between data types.
See this answer which discusses a similar issue with json. The same approach, of creating a cast using the conversion function, is appropriate here.
Your cast functions are macaddr_in and inet_in. You'll need to write wrapper SQL functions that take text arguments because of some irritating type issues. See the above link.
See also this related answer for the xml type.
After your update I tested your functions and found them to work as expected:
postgres=# CREATE TABLE inetmac (i inet, m macaddr);
CREATE TABLE
postgres=# PREPARE insinet(text) AS INSERT INTO inetmac(i) VALUES ($1);
PREPARE
postgres=# EXECUTE insinet('10.1.1.1');
INSERT 0 1
postgres=#
... but, rather surprisingly, Pg doesn't implicitly cast from varchar to text in order to use the text casts:
postgres=# PREPARE insinet(varchar) AS INSERT INTO inetmac(i) VALUES ($1);
ERROR: column "i" is of type inet but expression is of type character varying
LINE 1: ...PARE insinet(varchar) AS INSERT INTO inetmac(i) VALUES ($1);
^
HINT: You will need to rewrite or cast the expression.
If you're using varchar input you'll need another set of casts with varchar instead of text input.
Note that this won't happen if you just:
INSERT INTO inetmac(i) VALUES ('10.1.1.1');
directly, because here '10.1.1.1 is of pseudo-type unknown, and gets interpreted as inet since that's what the input requires. It's kind of like a type that's implicitly castable to any input function. By contrast text and varchar are concrete types that Pg must consult the casting rules for, so without creating casts, this won't work:
INSERT INTO inetmac(i) VALUES ('10.1.1.1'::text);
I'm going to raise a fuss about this on the -hackers list, it's just painful how difficult it is for people to use PostgreSQL's extension types from client interfaces. Sure, said client interfaces should expose a way to tell the JDBC driver what the base type is, but we're lucky if most things cope with the extreme basics like indexes and composite keys, let alone details of type handling. Pg really needs to be a bit less nuts about this, or provide a way to specify the 'unknown' pseudo-type in parameter binding.
Fuss raised:
这篇关于Macaddr/Inet类型的postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
