2

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.

4
  • 1
    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.
    – joanolo
    Feb 3, 2017 at 9:07
  • What is the value of $2 in the above execution. Feb 3, 2017 at 9:19
  • 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 :) Feb 3, 2017 at 9:59
  • @DennisNolte see my answer if you want a slightly improved way of doing this. Feb 3, 2017 at 19:52

3 Answers 3

6

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
2
  • first test seems promising, will update in a few hours when i have more time for testing. Feb 3, 2017 at 10:31
  • after more testing this worked exactly as described, thank you for adapting Craigs answer, which i did upvote as well. Feb 3, 2017 at 11:39
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
1

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
3
  • (I guess this means changing client_code...)
    – joanolo
    Feb 3, 2017 at 9:05
  • 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. Feb 3, 2017 at 9:19
  • 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. Feb 3, 2017 at 9:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.