Select All in SQL query with Input Tool and Postgres

[pturner_010]

Whenever I try to use select * in the Input Tool's SQL editor and connected to a PostgreSQL database, I get the following error:

Error: Input Data (2): Internal Error: Invalid parameter detected in function (null). File: (null) Line: 0 Expression: (null)

Both queries return "successful connection and no errors found when parsing" when clicking the Test Query button on the editor.

This query returns the error:

select * 
from lms_dl."PA_STUDENT" s 
where s."FNAME" = 'Paul'

But this query works fine: 

select s."STUD_ID" 
from lms_dl."PA_STUDENT" s 
where s."FNAME" = 'Paul'

What am I missing? How do you select all the columns in a table with Postgres and Alteryx? The syntax using select * works fine in other db tools connected to the same Postgres instance.

[anjaliaithal]

Have you tried select s.* from lms_dl."PA_STUDENT" s where s."FNAME" = 'Paul'

[messi007]

Dear @pturner_010,

Have you tried to use :

select s.* 
from lms_dl."PA_STUDENT" s 
where s."FNAME" = 'Paul'

Hope that helps,

Regards, 

[pturner_010]

Yes, I have tried using select s.* as well, but the same "invalid pararmeter detected in function" error is returned.

Edit: I did some more digging into the columns and it appears that for columns that contain a mix of NULL and NOT NULL values, Alteryx cannot read them from Postgres. I do not face this issue with the same scenario connected to an Oracle db.

I tried using a CASE WHEN to just insert dummy values into the NULL fields (which again works fine in other Postgres db tools), but Alteryx still throws the invalid parameter error. I'm using the 64-bit Simba PostgreSQL ODBC driver that is recommended by Alteryx.

Is this just an issue with Alteryx and I should reach out to support at this point? 

Here is the statement I tried:

select s."STUD_ID",
(CASE WHEN s."END_DTE" IS NULL THEN '1900-01-01'
      ELSE s."END_DTE" END) as "END_DTE"
from lms_dl."PA_STUDENT" s 
where s."FNAME" = 'Paul'

[ntobon]

@pturner_010 are you using for the Gallery Data Connection? Is the connection string over 210 characters long? 

[pturner_010]

I am not using a Gallery Data Connection. The connection string is definitely less than 210 characters. I am able to successfully connect and query data with explicit select statements, it's only when I attempt to select "all" or a column with a mix of null and not null values that I encounter this error.

[pturner_010 · Accepted solution]

This was resolved with a change in drivers. When I switched from Simba to psqlodbc 09.06.0310 version (Windows MSI available here), the error was fixed. Thank you @ntobon for your help!