ERROR: column "xxx" is of type inet but expression is of type character varying

[tanjaj]

Hi,

I have a simple job, which should just load data from csv file to the db table (Postgres 10).

I keep receiving this error:

Batch entry 0 INSERT INTO "schema"."table" ("id","email_address","code","ip_address","ga_json","created_at") VALUES (1,'test@mail','test','11.111.11.111',NULL,'2019-11-04 16:16:16') was aborted: ERROR: column "ip_address" is of type inet but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 128 Call getNextException to see other errors in the batch.
caused by: ERROR: column "ip_address" is of type inet but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 128

But when i run this command in pgAdmin, i have no problems, it inserts data correctly:

INSERT INTO "schema"."table" ("id","email_address","code","ip_address","ga_json","created_at") VALUES (1,'test@mail','test','11.111.11.111',NULL,'2019-11-04 16:16:16')

Also, there is capture of my schema.

Why should I cast it if db allows this record?

Best,

Tanja

[Anonymous]

I don't know if this will work. It is a guess based on some reading. I haven't been able to test this. So I would appreciate it if you would try this and let me know.

Assuming your IP data is being received as a String in this example. If your IP data is in a column called "IP". Add a tMap and output all of your columns as normal, but set your IP column output for the tMap to be an Object.  Then in your tMap output column for "IP", set the following code (assuming the input row to your tMap is "row1")....

((Object)java.net.InetAddress.getByName(row1.IP))

What this is doing is converting your String IP to a InetAddress object. It is then casting that to an Object. Your db component should consume that initially as an Object, but it will hopefully be implicitly cast by the Postgres libraries.

I don't have a Postgres DB, so this is just what I would try.

[tanjaj]

Thank you for such quick response.

I have tried it, but it didn't work. I got this error:

Exception in component tDBOutput_1
org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.net.Inet4Address. Use setObject() with an explicit Types value to specify the type to use.

[Anonymous]

OK, this is a little more complicated and is another guesstimate I'm afraid. What I have done is created a Routine called PostgreTools. This is shown below. I needed to download a Jar for this, I got it from here: http://www.java2s.com/Code/Jar/o/Downloadorgpostgresqlnet91901jdbc41rc9jar.htm

I associated the Jar with my Routine by right clicking "Edit Routine Libraries".

package routines;
import java.sql.SQLException;
import org.postgresql.net.PGinet;


/*
 * user specification: the function's comment should contain keys as follows: 1. write about the function's comment.but
 * it must be before the "{talendTypes}" key.
 * 
 * 2. {talendTypes} 's value must be talend Type, it is required . its value should be one of: String, char | Character,
 * long | Long, int | Integer, boolean | Boolean, byte | Byte, Date, double | Double, float | Float, Object, short |
 * Short
 * 
 * 3. {Category} define a category for the Function. it is required. its value is user-defined .
 * 
 * 4. {param} 's format is: {param} <type>[(<default value or closed list values>)] <name>[ : <comment>]
 * 
 * <type> 's value should be one of: string, int, list, double, object, boolean, long, char, date. <name>'s value is the
 * Function's parameter name. the {param} is optional. so if you the Function without the parameters. the {param} don't
 * added. you can have many parameters for the Function.
 * 
 * 5. {example} gives a example for the Function. it is optional.
 */
public class PostgreTools {

    public static Object setPGinetAsObject(String value) {
    	
    	PGinet inet = new PGinet();
    	try {
			inet.setValue(value);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	
    	return (Object)inet; 
    }
}

Try using this routine with the following code in your tMap (or wherever you want to make the conversion)....

routines.PostgreTools.setPGinetAsObject(row1.IP)

I suspect that this might work for you.

[tanjaj · Accepted solution]

Thanks for the effort, but this didn't work eater.

But i finally got it resolved. What I did is, I read DB table schema from the Repository, instead of Build in.

(Metadata/ Db Connection/ Table schemas)

Best,

Tanja