No way to pass types with spaces using data Values a

[michalrus]

If I do:

DB.Values ["timestamp with time zone"] (DB.Only <$> timePoints)

The following happens in run-time:

{ "sqlErrorMsg": "type \"timestamp with time zone\" does not exist"
, "sqlErrorHint": ""
, "sqlState": "42704"
, "sqlExecStatus": "FatalError"
, "sqlErrorDetail": ""
}

The same error is achievable in PostgreSQL by running:

select * from
  (values
      ("timestamp with time zone" '2018-03-08 11:41:47.167973+00')
    , ('2018-03-09 11:41:47.198564+00')
    , ('2018-03-10 11:41:47.198564+00')
    ) as v(reference_ts)

which results in:

ERROR:  type "timestamp with time zone" does not exist
LINE 7:       ("timestamp with time zone" '2018-03-08 11:41:47.16797...
               ^

********** Error **********

ERROR: type "timestamp with time zone" does not exist
SQL state: 42704
Character: 70

A version without quotes works correctly:

select * from
  (values
      (timestamp with time zone '2018-03-08 11:41:47.167973+00')
    , ('2018-03-09 11:41:47.198564+00')
    , ('2018-03-10 11:41:47.198564+00')
    ) as v(reference_ts)

A workaround is to use a short form, timestamptz, instead of timestamp with time zone. But this might not be available for other types.

[cimmanon]

This specific case is actually mentioned in the documentation for Values (emphasis mine):

The first argument is a list of postgresql type names. Because this is turned into a properly quoted identifier, the type name is case sensitive and must be as it appears in the pg_type table. Thus, you must write timestamptz instead of timestamp with time zone, int4 instead of integer or serial, _int8 instead of bigint[], etcetera.

https://hackage.haskell.org/package/postgresql-simple-0.5.3.0/docs/Database-PostgreSQL-Simple-Types.html#t:Values

[michalrus]

Why not generate correct SQL instead? =)

[cimmanon]

What's not correct about the SQL being generated? You do understand what the double quotes are for, right?

[michalrus]

It’s not correct for types with spaces, is it?

Are they for SQLi prevention? How would I introduce one, hmmm; I’d have to pass a string from a user in this data Value constructor’s first param. Would you do that? :P That would require pretty advanced users, to be useful. 🤷‍♂️

[BardurArantsson]

The situation around quoting and escaping seems to be a bit messy and ad hoc at the moment, unfortunately :(.