Re: Inserting into time stamp columns

I am sorry, my mistake, created the table as a different user. Thank you,
Dave, for the hint!

And lo!, the connection parameter trick works. Thank you, for the tip!

Peter

On Wed, Jun 26, 2013 at 5:27 PM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:

> Peter,
>
> Strange; can you insert it in psql using the same credentials ?
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
> On Wed, Jun 26, 2013 at 11:18 AM, Péter Kovács <
> peter(dot)dunay(dot)kovacs(at)gmail(dot)com> wrote:
>
>> Thank you, Dave!
>>
>> If I set stringtype to unspecified, I get the following error:
>>
>> Exception in thread "main" org.postgresql.util.PSQLException: ERROR:
>> permission denied for relation tstest
>>
>> The code:
>>
>> Class.forName("org.postgresql.Driver");
>> Properties props = new Properties();
>> props.setProperty("user", "jcbtest");
>> props.setProperty("password", "ciril");
>> *props.setProperty("stringtype", "unspecified");*
>> Connection connection =
>> DriverManager.getConnection("jdbc:postgresql://localhost:5432/jcbtest",
>> props);
>> PreparedStatement pstmt = connection.prepareStatement("insert
>> into tstest values(?)");
>> pstmt.setObject(1, "1998-06-04 00:00:00+09");
>> pstmt.execute();
>> connection.close();
>>
>> After removing the line where the said property is set, I can connect
>> again.
>>
>> Any thoughts?
>>
>> Thanks,
>>
>> Peter
>>
>>
>> On Wed, Jun 26, 2013 at 4:51 PM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
>>
>>> This is more an artifact of the way bound parameters are handled by the
>>> backend. Normally we would bind this to a text type as it is a string. What
>>> you can do is try setting stringtype = unspecified as a connection
>>> parameter in which case it will let the server determine the type.
>>>
>>> The docs here have more of an explanation
>>> http://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters
>>>
>>>
>>> Dave Cramer
>>>
>>> dave.cramer(at)credativ(dot)ca
>>> http://www.credativ.ca
>>>
>>>
>>> On Wed, Jun 26, 2013 at 10:39 AM, Péter Kovács <
>>> peter(dot)dunay(dot)kovacs(at)gmail(dot)com> wrote:
>>>
>>>> Hi,
>>>>
>>>> Consider the following simple table:
>>>>
>>>>
>>>>
>>>> create table tstest (ts timestamp);
>>>>
>>>> The following code snippet
>>>>
>>>> PreparedStatement pstmt = connection.prepareStatement("insert
>>>> into tstest values(?)");
>>>> pstmt.setObject(1, "1998-06-04 00:00:00+09");
>>>> pstmt.execute();
>>>>
>>>> results in the following error:
>>>>
>>>> Exception in thread "main" org.postgresql.util.PSQLException: ERROR:
>>>> column "ts" is of type timestamp without time zone but expression is of
>>>> type character varying
>>>>
>>>> Hint: You will need to rewrite or cast the expression.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> Do I understand it correctly that it is a limitation of the JDBC driver not to implement the String -> Timestamp implicit conversion listed in Table 8.5 of this document:
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> http://docs.oracle.com/javase/1.5.0/docs/guide/jdbc/getstart/mapping.html#1034737 ? Or is something missing in my code to get the friendly behavior of plain SQL which works with time stamp strings such as '1998-06-04 00:00:00+09 without explicit conversion?
>>>>
>>>>
>>>>
>>>> Many thanks
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> Peter
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>
>