PostgreSQL type conversion should default to NATIONAL CHARACTER

[daniel-skovenborg]

PostgreSQL type conversion in PostgreSQLJDBCDatatypeImporter imports varchar as CHARACTER VARYING and text as CHARACTER LARGE OBJECT. However, because SQL:1999 distincts between CHARACTER and NATIONAL CHARACTER, and PostgreSQL does not, the conversion should default to NATIONAL CHARACTER.
I haven't tried, but I believe this could break migration of SIARD archives from PostgreSQL databases to databases that distinct between VARCHAR and NVARCHAR if cells contains non-ASCII characters..

Of course, NATIONAL CHARACTER is not always what you'll want, e.g. if the database encoding is not a Unicode type or the column is just an enum.
I suggest that the type conversion methods should take the schema, table, and column as arguments, and that the PostgreSQL importer should have an option to make the following query to determine if a text column holds national characters:

select exists(select from SCHEMA_NAME.TABLE_NAME where COLUMN_NAME::text ~ '[^\x01-\x7F]');

This will of course very much slow down the import and should probably be an opt in.

[dosubot]

Hi, @daniel-skovenborg. I'm Dosu (https://dosu.dev), and I'm helping the keeps/dbptk-developer team manage their backlog and am marking this issue as stale.

Issue Summary:

• You suggested updating the PostgreSQL type conversion in PostgreSQLJDBCDatatypeImporter to default to NATIONAL CHARACTER instead of CHARACTER VARYING or CHARACTER LARGE OBJECT to better align with SQL:1999 standards.
• You proposed an optional feature to detect non-ASCII characters in text columns via a query, which could slow down imports but improve accuracy in distinguishing VARCHAR and NVARCHAR.
• There has been no further activity or comments on this issue since your initial suggestion.
• The issue remains unresolved and open for consideration.

Next Steps:

• Please let me know if this issue is still relevant to the latest version of keeps/dbptk-developer by commenting here to keep the discussion open.
• If I do not hear back within 7 days, I will automatically close this issue to help keep the backlog manageable.

Thank you for your understanding and contribution!

[daniel-skovenborg]

I believe this is still relevant. NATIONAL CHARACTER would be the safe default.