#23 Bugs when exporting timestamps and JSON

[Niels Kristian]

Hi, when I export data from Postgresql 9.3 and use the "UPSERT" option, I get invalid SQL in two cases.

First issue: When exporting a column with the data type is TIMESTAMP.
here is an example:

CREATE TABLE da.invoices
(
  id serial NOT NULL,
  invoiceable_id integer,
  invoiceable_type character varying,
  start_date date,
  end_date date,
  created_at timestamp without time zone NOT NULL,
  CONSTRAINT invoices_pkey PRIMARY KEY (id)
)

When exporting I get results like: Update da.invoices set invoiceable_id=47, invoiceable_type='Customer', start_date=timestamp '2017-08-01'::date, end_date=timestamp '2017-08-31'::date, created_at=timestamp to_timestamp('2017-09-04 07.16.55.055328', 'YYYY-MM-DD HH24.MI.SS.US') Where id=396;

The problem is, created_at=timestamp to_timestamp( is invalid syntax.

Second issue: When exporting data type JSON using UPSERT e.g.

CREATE TABLE da.data_collecting_tracking_configs
(
  id serial NOT NULL,
  config json,
  created_at timestamp without time zone NOT NULL,
  CONSTRAINT data_collecting_tracking_configs_pkey PRIMARY KEY (id)
)

When exporting I get: Insert into da.data_collecting_tracking_configs(id, config, created_at) Select * From (values (1, '{"detail_page_source_id_regexp":"www.url.com","name":"the name","detail_page_source_id_regexp_position":1}', to_timestamp('2017-01-20 10.02.45.528', 'YYYY-MM-DD HH24.MI.SS.US'))) as Q(id, config, created_at) Where not exists (Select * from da.data_collecting_tracking_configs T Where T.id=Q.id);

This is invalid SQL. There is a to_json missing and when running the SQL I will get ERROR: column "config" is of type json but expression is of type text

[Ralf Wisser]

Hi,
thank you for reporting the issue!

Please replace "jailer.jar" with the attached file (based on version 7.5.10) and let me know if the fix works.

[Niels Kristian]

WOW! That was unexpected fast! You are amazing. Yes it solves the issue, however now I see the same problem as with JSON but with HSTORE column types.

An example:

CREATE TABLE da.bin_data
(
  id serial NOT NULL,
  data extensions.hstore,
  created_at timestamp without time zone NOT NULL,
  CONSTRAINT bin_data_pkey PRIMARY KEY (id)
)

Will produce:
Insert into da.bin_data(id, data, created_at) Select * From (values (278116, '"weight"=>"800", "rating"=>"0"', to_timestamp('2013-11-05 18.08.27.802369', 'YYYY-MM-DD HH24.MI.SS.US')) as Q(id, data, created_at) Where not exists (Select * from da.bin_data T Where T.id=Q.id);

It's missing the ::hstore conversion of the string.

[Niels Kristian]

One more. I can see the same applies to arrays. I have a column of

CREATE TABLE da.customers
(
id serial NOT NULL,
created_at timestamp without time zone,
items character varying[] DEFAULT '{}'::character varying[]
CONSTRAINT customers_pkey PRIMARY KEY (id)
)

The produced SQL looks like Insert into da.customers(id, created_at, items) Select * From (values (920, to_timestamp('2017-10-05 12.48.39.865186', 'YYYY-MM-DD HH24.MI.SS.US')'{first_item}')) as Q(id, created_at, items) Where not exists (Select * from da.customers T Where T.id=Q.id); missing the ::character varying[]

[Ralf Wisser]

I'm not very familiar with Postgresql.
The hstore-example works for me with the DDL:

CREATE TABLE da.bin_data
(
  id serial NOT NULL,
  data hstore,
  created_at timestamp without time zone NOT NULL,
  CONSTRAINT bilviden_data_pkey PRIMARY KEY (id)
)

but not with "extensions.hstore" (the CREATE TABLE fails).

What is the type of "data" in the Jailer's data model?
(You can see it in the data model editor or in the file "columns.csv" in the data model folder)

[Niels Kristian]

The data type is hstore. The extensions.hstore is just because the hstore data type is added through a so called extension which needs to be installed into a specifc schema. You can add extensions to Postgresql for all kinds of functionality. In our contrete setup, the hstore-extension is installed in another schema we called extensions. This is a schema just like all other schemas. e.g. the shcema the table itself is created in which is called da. So in other words. don't mind the extensions.hstore part. As long as the ::hsotre data type is appended to the string (just like the ::json) everything is fine!

[Ralf Wisser]

The ::hstore will be appended, if the type name of a column is "hstore" (as reported by the JDBC-driver). I guess that it's not the case for the column "data extensions.hstore"
It would be helpful to know exacly the type name. The columns types are stored in "columns.csv" in the data model folder.

[Niels Kristian]

Ahh I see! here you go: data "extensions"."hstore" null; created_at timestamp; Hmm yeah, that makes it tricky. I guess your code expects hstore to always be located in the same schema as the table, In that case, it will just say hstore instead. So I guess there is a need of supporting when hstore is installed into a different schema. To my knowledge, this is often the case.

[Ralf Wisser]

Thank you. I didn't know that.
If hstore is not located in the same schema as the table, how would the cast look like?
::hstore
::"extensions"."hstore"

or

::extensions.hstore

[Niels Kristian]

Hi again.

Well all of them works. However the first option would require the user to himself make sure that the apropriate schemas are in the search_path of the current database session. So since the rest of the statements are selfdescribing including schemas, I guess the first is out of the question. The following two works both of them, however I think the second one ::"extensions"."hstore" is the more correct one, and it's also how the data type is listed in the columns.csv

Running this works:
select '"some_name"=>"2760"'::"extensions"."hstore"

[Ralf Wisser]

Hi,
hstore and json objects will now be casted according to the type the jdbc driver reports. I did the same with arrays of any type. Please try the attached file.

Are there more extensions which should supported this way?

[Niels Kristian]

Thanks! It's all running perfectly now. I do not know of any other data types on top of my head. However I'm quite sure some exists. I did a bit of research but didn't find any for now. Thanks again for your swift response :-)