Sling support of array data type

[matanper]

I try to copy between 2 postgres databases a table with array of uuid, and I get warning WRN using string since type '_uuid' not mapped for col 'foo': ""
It still copies the data as varchar column, however if the array is too large I get pq: value too long for type character varying(255)

code to reproduce:

create table test_table  (
	"id" uuid,
	"foo" _uuid
);

INSERT INTO test_table (id, foo) VALUES (gen_random_uuid(), ARRAY[gen_random_uuid(), gen_random_uuid()]);

source: LOCAL_PG
target: LOCAL_PG

defaults:
  mode: full-refresh 
  object: 'test.{stream_table}'

streams:
  public.test_table:

[flarco]

Thanks, Fixed in #179

[matanper]

Thanks 🙏
so it isn't possible to sync between 2 databases of the same type and keep the original type of all the columns?

[flarco]

Ah I see. Sling uses JSON for any semi-structure/nested data. At the moment, it doesn't support native arrays (each database has it's own syntax for arrays). What I'm thinking is to have sling auto-convert the native array form source when selecting and writing into target as json (see #179). But writing into a native array on target is a bigger lift. The work-around is, on target side, you could use a target.options.post_sql, to convert the json values into native array.

[matanper]

Thanks, got it working with:

streams:
  public.test_table:
    source_options:
      columns:
        foo: text
    target_options:
      post_sql:
        "alter table test.test_table alter column foo type _uuid using foo::_uuid"

[flarco]

Don't forget, you can use object patterns in post-sql:

post_sql: "alter table {object_schema}.{object_table} alter column foo type _uuid using foo::_uuid"