Macaddr/Inet type of postgres in slick

问题:

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

回答1:

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:

• http://www.postgresql.org/message-id/[e-mail]

• http://www.postgresql.org/message-id/[e-mail]