Reading from PostgeSQL truncates <256 Characters

[Verakso]

Hi 

I am having similar problems like many other, e.g. https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Input-Tool-Truncating-Fields-t... but no solutions seems to have been provided.

I can't figure out what is wrong with the driver or how to circumvent it.

I the above thread, the suggestion is to use OLEDB instead of ODBC, but I can't see that the Postgres supports OLEDB, only ODBC

When I look directly at the Metadata from the Input Control, I can see that all the VARCHAR fields are truncated to 255:

And so far my efforts to circumvent that have failed, so I open to any suggestions.

/Thomas

[Verakso]

Okay, after I pushed the Post button, I kind a got an idea.

I simply did a cast on the truncated field in the SQL like this

It ain't pretty but it works for now. I guess if the result supersedes 8190 characters, I will have to go with cast to guid 😂


If anyone comes up with a better solution, then I am still interested.

/Thomas

[apathetichell]

I do not think what you are seeing is what you think you are seeing. I believe there are allocations to columns based upon the max length of data in that columns. For those tables the max length is 256 bytes (in 0 script - 255 characters). In others it's 8192 characters (which for some reason is shown here as 8190) and in others its 4096^2 (16777216 - the max string length in many dbs). in your screenshot type has an allocation of 8190 bytes - while others have 255 - why? because that is the minimum possible memory allocation for that field - ie it does not exceed 255 characters in the current db.

[Raj]

@Verakso have you tried generic OLEDB connection?

[Verakso]

Well, that's not correct @apathetichell 
The driver explicit returns these fields as 255 even though the field clearly exceeds that, because before my "hack" the data was truncated

[Verakso]

Hi @Raj 

Please show me a guide how to use generic OLEDB with Postgress in Alteryx, because I haven't had any success with that.

[apathetichell]

Not sure - but this is my experience:

I'm connecting to Postgres via Greenplum via In-DB. I have a slew of string fields. The ones which do not exceed 255 characters go to canvas at 255. the larger field is going to canvas at 1024. All are V_WString. I am not performing any casts.

My MaxLongVarChar is 8190. My MaxVarChar is 255 in my driver config.

[apathetichell]

internally what is the difference between result & type in your db? if you have result set to as varchar(255) - and try to add data to this field in your in-db tools - yes - it should truncate. If both of these fields hvae the same datatype in your DB - I do not see why one is being set at 8190 and one is being set at 255 if both have values which exceed 255 - and there is no max size for this field set at the db level.

and in the metadata what are type and status (if aren't they varchar as well?)