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.

• joanolo: If you cannot change client code but can change table definitions, you can change your device_macaddr to text (or character(xx))... Obviously this might have lots of unwanted side effects, depending on whether this is just to "store" values, or to perform some operations on them.
• Evan Carroll: What is the value of 2ドル in the above execution.
• Dennis Nolte: from the app perspective it is f.e. "40:83:de:XX:XX:XX" (obviously not XX but a valid lowercase MAC) however i was yet unable to get the actual output in postgresql log, and the debug levels of the app only showed that there is a valid mac in there, not the exact query with which values it is inserted. If there is a postgresql config param i can set to "expand" those 1ドル values to actual input, please tell me which, i was unsucessfull in searching for it. (i have a test setup where i can change anything i want until it works :)
• Evan Carroll: @DennisNolte see my answer if you want a slightly improved way of doing this.

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

• Dennis Nolte: first test seems promising, will update in a few hours when i have more time for testing.
• Dennis Nolte: after more testing this worked exactly as described, thank you for adapting Craigs answer, which i did upvote as well.

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ドル

• joanolo: (I guess this means changing client_code...)
• Dennis Nolte: yep, which is exactly the issue, for now as torque generates the query and not the app directly this will take some time to get fixed.
• András Váczi: To be precise, Postgres will think unspecified text literals are unknown, not varchar. I think it is JDBC that produces a varchar there, and then Postgres complains.