2

I have a table that is a clone of another one and I am trying to cast an InetAddress to an postgres inet type, however I am unable to do this this successfully. I have tried passing in both the InetAddress object itself as well as the string representation of it, but both have the same issues in the end and provide me with the following error:

ERROR: syntax error at or near ":"

pointing to the casting of ?::inet, even though I have no issues with this syntax when running a similar query here: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=fa87a62be58e8bb2f64e1031819c5646

Here is what I have:

EntityTransaction et = entityManager.getTransaction();
et.begin();
for (Foo foo : foos) {
  entityManager.createNativeQuery("INSERT INTO my_table_clone (ip_1, ip_2, country) " 
         + VALUES (?::inet, ?::inet, ?);")
      .setParameter(1, foo.getIp().getIp1().toString()) // have also tried using the InetAddress object itself, instead of string
      .setParameter(2, foo.getIp().getIp2().toString())
      .setParameter(3, foo.getCountry())
      .executeUpdate();
}
entityManager.flush();
et.commit();
entityManager.clear();

The entity:

@lombok.Data
@Entity(name = "my_table_clone")
@Table(name = "my_table_clone")
@Embeddable
public class Foo implements Serializable {

  @EmbeddedId private Ip ip;

  @Column(nullable = false)
  private String country;
}

and

@Data
@Embeddable
public class Ip implements Serializable {

  @Column(nullable = false)
  private InetAddress ip1;

  @Column(nullable = false)
  private InetAddress ip2;
}

Additionally, I have also tried NOT casting the fields in the query, but I get the following errors when trying to pass the InetAddress or String types respectively:

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

How can I store my ip columns as inet correctly?

1 Answer 1

5

Obfuscation layers like Hibernate/JPA get confused by the :: operator. Use the standard SQL cast operator instead:

VALUES (cast(? as inet), cast(? as inet), ?)
Sign up to request clarification or add additional context in comments.

4 Comments

Awesome thanks, that was it! I also had to remove the leading slash to get it work entirely via foo.getIp().getIp1().toString().replaceAll("/", ""), but your answer is correct
@a_horse_with_no_name "Obfuscation layers", precise hit!
@Stefanov.sm: ORM = Obfuscated Relational Model
confused = bad implemented in this case

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.