PostgreSQL Wrapper Design – No Operator Matches Given Name and Argument Type

awsjavajdbcpostgresqlpostgresql-9.4

I'm building a wrapper for DB access using AWS' Lambda functions Java, the issue I'm having is that Lambda parameters are JSON, thus typeless, so all incoming params are de-JSON'd into Strings, and when adding them to a PreparedStatement using the setObject(param, value) method they all get treated as if you were using setString(param, value), this is fine for Strings and numbers, but when it comes to a parameter that is a timestamp or date, we get a parameter type mismatch like this:

org.postgresql.util.PSQLException: operator does not exist: timestamp with time zone >= character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

I'm aware that passing parameter types as an extra field for the JSON might help, but I'm worried about both the extra overhead and, the need of manually adding the parameter type to the JSON, as it's currently being built "automagically" from the underlying DTO using GSON.

Can anyone figure out a different approach, perhaps on passing the parameters to the PreparedStatement.?

Best Answer

You'll need to explicitly convert your datetime strings to appropriate data types. Since you chose not to include the SQL statement in question, I can only guess what it's like, but if it looks anything like

...WHERE timestamp_column > ?...

you'll need to replace it with

...WHERE timestamp_column > TO_TIMESTAMP(?, 'YYYY-MM-DD HH24:MI:SS')...

using, of course, an appropriate format string.

Related Solutions

Understanding Parameters in SQL Server Query Plan

Yes, you are likely seeing implicit conversions due to the guesses SQL Server has to make. It is creating a plan that will work not just for the parameter value you're passing now, but other potential values, too. With strings it (or perhaps JDBC? not sure) chooses an arbitrary default of 4000 and Unicode. I believe there are ways in JDBC to not send Unicode (Unicode vs. non-Unicode implicit conversions are among the most tedious), but you're still going to deal with the length issue (which may or may not be an issue in your specific case; again, not sure).

To avoid the implicit conversions and guesses, and ultimately the scans and high CPU conditions, you need to make SQL Server understand the data type that is being used in the parameter through explicit / strong typing.

Here are my suggestions, in order of preference:

Switch to stored procedures.
Switch to stored procedures.

Declare your variables explicitly, e.g.:

DECLARE @p1 VARCHAR(36) = ?; UPDATE ... WHERE col = @p1;

Keep using CAST/CONVERT.

PostgreSQL – How to Combine Concatenate Operator on JSON Values with AS

Not exactly sure what you are asking, but in Postgres 9.3 or later you can use json_agg() to translate a whole table into a JSON array of records. Prepare the rows in a single subquery and feed whole rows to json_agg() using the table alias:

Multiple layers of nested subselects like you display seem uncalled for.

Simple example:

CREATE TABLE tbl (
  tbl_id serial PRIMARY KEY,
  foo    text
);

INSERT INTO tbl VALUES
  (1, 'First' )
, (2, 'Second');

Query:

SELECT json_agg(sub)
FROM  (
   SELECT tbl_id        AS some_id
        , foo           AS bar
        , tbl_id || foo AS some_derived_column
   FROM  tbl
   ) sub;

Result:

[{"some_id":1,"bar":"First","some_derived_column":"1First"}, 
 {"some_id":2,"bar":"Second","some_derived_column":"2Second"}]

SQL Fiddle.

Best Answer

Related Solutions

Understanding Parameters in SQL Server Query Plan

PostgreSQL – How to Combine Concatenate Operator on JSON Values with AS

Related Question