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.

  • 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 '17 at 9:07
  • What is the value of $2 in the above execution. – Evan Carroll Feb 3 '17 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 :) – Dennis Nolte Feb 3 '17 at 9:59
  • @DennisNolte see my answer if you want a slightly improved way of doing this. – Evan Carroll Feb 3 '17 at 19:52
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
  • first test seems promising, will update in a few hours when i have more time for testing. – Dennis Nolte Feb 3 '17 at 10:31
  • after more testing this worked exactly as described, thank you for adapting Craigs answer, which i did upvote as well. – Dennis Nolte Feb 3 '17 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
  • (I guess this means changing client_code...) – joanolo Feb 3 '17 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. – Dennis Nolte Feb 3 '17 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. – dezso Feb 3 '17 at 9:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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