Unavailabe update row with data of user-defined type

[Roman Merzlyakov]

Hello!

I use SQL Workbench with PostgreSQL and several times I face the same problem, but could not find solutions:

In database exist data with user-defined type, for example time_interval

CREATE TYPE time_interval AS

(

  t1  time,

  t2  time

);

When i change data of that type anywhere and try to save the changes, SQL Worbench attempts to perform next SQL UPDATE command:

UPDATE filial

    SET su = (08:30:00, 19:00:00)

WHERE osb = 8558

AND   filial = 3;

and of course i see the error (taken from the log-file):

2013-10-29 14:45 ERROR Error executing statement UPDATE filial SET su = ? WHERE osb = ? AND filial = ? ERROR: column "su" is of type time_interval but expression is of type character varying

  Hint: You will need to rewrite or cast the expression.

  Position: 24 [SQL State=42804] 

org.postgresql.util.PSQLException: ERROR: column "su" is of type time_interval but expression is of type character varying

  Hint: You will need to rewrite or cast the expression.

  Position: 24

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)

at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:363)

at workbench.storage.DmlStatement.execute(DmlStatement.java:175)

at workbench.storage.DataStore.executeGuarded(DataStore.java:1636)

at workbench.storage.DataStore.updateDb(DataStore.java:1744)

at workbench.gui.sql.DwPanel.saveChanges(DwPanel.java:468)

at workbench.gui.sql.SqlPanel.updateDb(SqlPanel.java:1154)

at workbench.gui.sql.SqlPanel$4.run(SqlPanel.java:1133)

The obvious solution - to specify a value in single quotes:

UPDATE filial

    SET su = '(08:30:00, 19:00:00)'

WHERE osb = 8558

AND   filial = 3;

This work even without typecast (at least for PostgreSQL), when entered manually, but is there a way to do this automatically?

Version of SQL Workbench - last Build 115 (2013-09-01) 

JDBC4 Postgresql Driver, Version 9.2-1003 

Did anyone have the same problem and is there any solution?

[Thomas Kellerer]

Hi,

this is unfortunately a limit of the JDBC interface (or the way I currently use it to generate the UPDATE or INSERT statements).

For the time being columns like that cano not be changed by editing the result directly

Regards
Thomas

Roman Merzlyakov wrote on 29.10.2013 10:21:
> Hello!
> I use SQL Workbench with PostgreSQL and several times I face the same problem, but could not find solutions:
>

> In database exist data with user-defined type, for example *time_interval*
>
> CREATE TYPE *time_interval* AS

> (
> t1 time,
> t2 time
> );
>
> When i change data of that type anywhere and try to save the changes, SQL Worbench attempts to perform next SQL UPDATE command:
>
> UPDATE filial
> SET su = (08:30:00, 19:00:00)
> WHERE osb = 8558
> AND filial = 3;
>
> and of course i see the error (taken from the log-file):
>

> 2013-10-29 14:45 ERROR Error executing statement UPDATE filial SET su = ? WHERE osb = ? AND filial = ? ERROR: column "su" is of type *time_interval* but expression is of type character varying

> Hint: You will need to rewrite or cast the expression.
> Position: 24 [SQL State=42804]

> org.postgresql.util.PSQLException: ERROR: column "su" is of type *time_interval* but expression is of type character varying

> Hint: You will need to rewrite or cast the expression.
> Position: 24
> at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
> at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
> at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:363)
> at workbench.storage.DmlStatement.execute(DmlStatement.java:175)
> at workbench.storage.DataStore.executeGuarded(DataStore.java:1636)
> at workbench.storage.DataStore.updateDb(DataStore.java:1744)
> at workbench.gui.sql.DwPanel.saveChanges(DwPanel.java:468)
> at workbench.gui.sql.SqlPanel.updateDb(SqlPanel.java:1154)
> at workbench.gui.sql.SqlPanel$4.run(SqlPanel.java:1133)
>
> The obvious solution - to specify a value in single quotes:
>
> UPDATE filial

> SET su = *'*(08:30:00, 19:00:00)*'*

> WHERE osb = 8558
> AND filial = 3;
>
> This work even without typecast (at least for PostgreSQL), when entered manually, but is there a way to do this automatically?
>
> Version of SQL Workbench - last Build 115 (2013-09-01)
> JDBC4 Postgresql Driver, Version 9.2-1003
>
> Did anyone have the same problem and is there any solution?
>
>
>
>
>
>
>

> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.

[Roman Merzlyakov]

Thank you for taking the time to me!

I hope in the next version will support this feature.

In addition to user-defined types have the same problem exists with arrays of standard data types.

[Thomas Kellerer]

There is actually a way to get this working.

It is possible to define expressions to be used for specific datatypes when a DML statement is generated.

The feature is primarily intended for "standard" datatypes that are not supported directly by the JDBC API (e.g. Postgres' INET type). But it can just as easily be used for user defined types. The drawback is that you need to do this for every type you have.

Taking your example, the following would register an expression that should make editing the result possible:

WbSetConfig workbench.db.postgresql.dmlexpression.time_interval=cast(? as time_interval)


SQL Workbench/J will internally generate the following UPDATE statement:

UPDATE filial
SET su = cast(? as time_interval)
WHERE osb = ?
AND filial = ?;

And set the values through the JDBC API when sending the statement to the database.

In that case it is assumed that the input value entered by the user allows such a cast.

You can't use that for an array though and if you have many types like that it might be a bit tedious, but at least you should be able to edit the values directly.

I will see if I can make that somehow more generic.

Regards
Thomas

> > To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <javascript:>.
> > For more options, visit https://groups.google.com/groups/opt_out <https://groups.google.com/groups/opt_out>.