Data Compare - Arrays

[rosscoombes]

Bit of background here... I'm using Sequelize to create the models and in there I have one column which is declared as a string array.

@column(DataType.ARRAY({type: DataType.STRING}))
attributes: string[]

This generates a table where the column is defined as...

attributes character varying(255)[]

When I store data in that column, it takes the format...

'{ATTR_1, ATTR_2}'

Inside the __generateSqlFormattedValue function, it tries to handle arrays by doing a value.join as if the data was genuinely an array as opposed to a comma separated value enclosed in curly braces as above. And thus, join is not a function leading to...

TypeError: value.join is not a function
at Object.__generateSqlFormattedValue (C:\Users\rossc\AppData\Local\Yarn\Data\global\node_modules\pg-diff-api\src\sqlScriptGenerator.js:631:35) at Object.generateDeleteTableRecordScript (C:\Users\rossc\AppData\Local\Yarn\Data\global\node_modules\pg-diff-api\src\sqlScriptGenerator.js:593:54)
at C:\Users\rossc\AppData\Local\Yarn\Data\global\node_modules\pg-diff-api\src\api\CompareApi.js:1361:30

Not sure whether somehow my data is stored incorrectly by Postgres or whether its a bug in the code.

Would appreciate your thoughts.

[michaelsogos]

Dear @rosscoombes

Ok, string array (like int array, etc.) are already supported from pg-diff.
Maybe it could be a bug on my side not encountered before.

If i remember well in pgsql you will see curly bracket if it is a comma separated list of values, while you will see a mix of ( or ] when it is a range of values. So it seems to be correct.

If you open your database with a GUI tool able to give you back COLUMN DDL and share it here; i will do my best to reproduce and fix the issue.

[michaelsogos]

Dear @rosscoombes

I tried to implement the column as you suggest to reproduce the problem, and:

1. Any kind of string[], independently from original DDL like varchar[], character varying(255)[], etc.; land into final persisted DDL like _varchar (standard sugar alias from PG 9.x)
2. I tried to give NULL, {A,B}, {ATTR_1, ATTR_2}, {} but in every case i don't encounter your issue
3. Is absolutely to avoid any kind of array as KEY FIELD for data compare, don't you have a PKEY?

I tried any form of test and i can't reproduce your issue, do you fill comfortable to share (privately) your database in order to catch the issue?

[rosscoombes]

Hi Michael,

Sorry for the delay. I believe the issue is that the column is being stored as an enum but it's being interpreted as an array.

Given other similar columns work fine and I can clearly see you've handled enums in the code, I think the problem must lie in my code. Thanks for looking, I'll close it and let you know if I find something relevant for your attention.

[rosscoombes]

Hi Michael,

Finally got round to figuring out the problem here, I believe it's in the interpretation of a string[] versus an enum[].

When it encounters a string array, in the function __generateSqlFormattedValue, the dataTypeCategory is "A" for array and the value correctly joins back into a string.

However, if it encounters an enum[], the dataTypeCategory is also "A" for array but the format of the value is {ITEM_1,ITEM_2} and so the join fails. In this case, you can simply return value unchanged.

There seems to be two solutions here. Either you somehow figure out that the dataTypeCategory should be an "E" and then your existing code will return the correct value.
I wasn't sure how best to do that so I went with the other option of simply checking whether the value is indeed an array before doing the join, otherwise returning the value as is. As far as I can tell, this works.

See below if you want to implement...

switch (dataTypeCategory) {
...
case "A": //ARRAY
return Array.isArray(value) ? '{${value.join()}}' : value;
case "R": //RANGE
return '${value}';
case "B": //BOOL
case "E": //ENUM
case "G": //GEOMETRIC

If you want to replicate, you should just need a column with an enum array e.g. "enum_Example"[]

CREATE TYPE public."enum_Example" AS ENUM (
'ITEM_1',
'ITEM_2',
'ITEM_3'
);

CREATE TABLE public."TestTable" (
id uuid NOT NULL,
attributes public."enum_Example"[],
);

Hope that helps

[michaelsogos]

Dear @rosscoombes

Of course it help a lot.
From point of view of pg, it is an array of enum values, so it will never be E and it must be A.
The point is to introduce custom types, know about its real type and parse it properly.
I will investigate asap.