How do I cast varchar to macaddr without changing client code

Question

I have a dedicated DB Server with Debian Jessie and Postgresql 9.4

Client: A is a Tomcat Java app which use torque with a JDBC driver connecting to the DB Server.

Issue: on Updating a row with a new value the update fails because the app transfers the wrong datatype.

Error message:

column "device_macaddr" is of type macaddr but expression is of type character varying at character 159

Update Statement generated by Torque:

UPDATE device SET device_last_change = $1, device_macaddr = $2 WHERE device_ID = $3

Definition of Datatypes in postgresql :

last_change is timestamp (0) without time zone NOT NULL
device_macaddr is macaddr NOT NULL
device_id is bigint NOT NULL

We recently did update both the DB and the jdbc driver from 8.4 to 9.4

I was able to pinpoint the change to the jdbc driver but was unable to find the changelog which exactly showed this change of behaviour.

Since then the above statement seems to not work anymore, while it previously worked despite being possible the wrong datatype back then as well.

Is there a way to "lessen" the strict detection from the Postgres 9.4 DB Server for that specific case?

I did read the enhancement of the jdbc driver to include those additional datatypes, however this hasn't worked for me yet so I try to get a workaround on the other end of the options i have.

Answer 1

Shamelessly stolen from Craig's answer over SO.

You need to create a cast to make the varchar -> macaddr coercion work automatically. It is slightly tricky, as there is no function that does exactly this, so we have to wrap an internal function into something we can use:

CREATE TABLE mac (addr macaddr);

INSERT INTO mac VALUES ('11:11:11:11:11:11'::varchar);
=> ERROR:  column "addr" is of type macaddr but expression is of type character varying

CREATE OR REPLACE FUNCTION macaddr_invarchar(varchar) 
RETURNS macaddr LANGUAGE SQL AS $$
    SELECT macaddr_in($1::cstring);
$$ IMMUTABLE;

CREATE CAST (varchar AS macaddr) WITH FUNCTION macaddr_invarchar(varchar) AS IMPLICIT;

INSERT INTO mac VALUES ('11:11:11:11:11:11'::varchar);
=> INSERT 0 1

Answer 2

There is an easier to to do this which doesn't involve creating a function by using WITH INOUT

CREATE TEMP TABLE mac (addr macaddr);

INSERT INTO mac VALUES ('11:11:11:11:11:11'::varchar);
=> ERROR:  column "addr" is of type macaddr but expression is of type character varying

CREATE CAST (varchar AS macaddr)
  WITH INOUT
  AS ASSIGNMENT;
INSERT INTO mac VALUES ('11:11:11:11:11:11'::varchar);
INSERT 0 1

Answer 3

By default PostgreSQL consider the value as varchar. You must cast it:

UPDATE device SET device_last_change = $1, device_macaddr = $2::macaddr WHERE device_ID = $3