Обсуждение: insert daterange field value
Hello!
I have a table in PostgreSQL(9.3) with daterange field type.
I can select this field like a String with JDBC, but I cannot Insert it in a table.
What I've tried:
PreparedStatement stm = conn.prepareStatement("insert into mytable (my_daterange_field) values (?)");
stm.setString(1, "[2014-01-02,2014-01-04]");
int i = stm.executeUpdate();and I got:
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: column "my_daterange_field" is of type daterange but expression is of type character varying Hint: You will need to rewrite or cast the expression. Position: 168Does anyone have a solution for inserting daterange? What stm.setXXX should I use? Or maybe I cannot do that because JDBC Driver does not have daterange support... Maybe there is a third solution?
Thank you.
P.S.
My PostgreSQL JDBC Driver:
<dependency> <groupId>postgresql</groupId> <artifactId>postgresql</artifactId> <version>8.4-701.jdbc4</version> </dependency>> Does anyone have a solution for inserting daterange? What stm.setXXX should I use? Or maybe I cannot do that because JDBCDriver does not have daterange support... Maybe there is a third solution? You need to specify the cast in your SQL and then bind the parameter with setObject(...). String value = "[2014-01-02,2014-01-04]"; String sql = "INSERT INTO date_range_test (some_field) VALUES (?::daterange)"; PreparedStatement stmt = conn.prepareStatement(sql); stmt.setObject(1, value); stmt.execute(); > My PostgreSQL JDBC Driver: > > <dependency> > <groupId>postgresql</groupId> > <artifactId>postgresql</artifactId> > <version>8.4-701.jdbc4</version> > </dependency> I haven't tried this with the older 8.4 driver but the code above works fine with the latest 9.3 driver. It might work with the older driver as well but you really should upgrade your JDBC driver to a more recent version. The newer driver supports both old (8.4+) and new versions of PostgreSQL so there's no real reason not to use it. Regards, Sehrope Sarkuni Founder & CEO | JackDB, Inc. | http://www.jackdb.com/
On Thu, Mar 6, 2014 at 12:27 PM, Sehrope Sarkuni <sehrope@jackdb.com> wrote:
>
> > My PostgreSQL JDBC Driver:
> >
> > <dependency>
> > <groupId>postgresql</groupId>
> > <artifactId>postgresql</artifactId>
> > <version>8.4-701.jdbc4</version>
> > </dependency>
Also the Maven co-ordinates changed so it might explain if you haven't
seen a newer version. The latest version is as follows:
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>9.3-1101-jdbc41</version>
</dependency>
Thank you. It worked.
On Thu, Mar 6, 2014 at 4:27 PM, Sehrope Sarkuni <sehrope@jackdb.com> wrote:
> Does anyone have a solution for inserting daterange? What stm.setXXX should I use? Or maybe I cannot do that because JDBC Driver does not have daterange support... Maybe there is a third solution?You need to specify the cast in your SQL and then bind the parameter
with setObject(...).
String value = "[2014-01-02,2014-01-04]";
String sql = "INSERT INTO date_range_test (some_field) VALUES (?::daterange)";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setObject(1, value);
stmt.execute();I haven't tried this with the older 8.4 driver but the code above
> My PostgreSQL JDBC Driver:
>
> <dependency>
> <groupId>postgresql</groupId>
> <artifactId>postgresql</artifactId>
> <version>8.4-701.jdbc4</version>
> </dependency>
works fine with the latest 9.3 driver.
It might work with the older driver as well but you really should
upgrade your JDBC driver to a more recent version.
The newer driver supports both old (8.4+) and new versions of
PostgreSQL so there's no real reason not to use it.
Regards,
Sehrope Sarkuni
Founder & CEO | JackDB, Inc. | http://www.jackdb.com/