Error in PROC SQL query

[Aexor]

Dears,

I need help in understanding a error 

ERROR: CLI execute error: ERROR: operator does not exist: uuid = character varying;Error while executing the query

 proc sql ;
connect to POSTGRES (<server-password details>);
execute ( update X.tableX set plan_lock_flg = true, process_status_no=41,
modified_by_user_id = 'tst', modified_dttm = current_timestamp where id in (select pp_id from
X.tableY) ) by POSTGRES;
ERROR: CLI execute error: ERROR: operator does not exist: uuid = character varying;Error while executing the query

But the same query is working in when I give value in the inner query in below format 

eaxmple : 

 proc sql ;
connect to POSTGRES (<server-password details>);
execute ( update X.tableX set plan_lock_flg = true, process_status_no=41,
modified_by_user_id = 'tst', modified_dttm = current_timestamp where id in ('00b3553b-408d-41b2-8ed3-cee324187d6e' , '018b781e-cd65-460a-8faa-5b4e57dbff49') ) by POSTGRES;

value of TableY is as below

id

'00b3553b-408d-41b2-8ed3-cee324187d6e'
'018b781e-cd65-460a-8faa-5b4e57dbff49'
'08394f63-b3fb-43fd-b0d1-0f780a2891c2'
'41a4a2f1-cf16-4659-9e0d-b62021406f94'
'5f5fffb2-8a61-4d79-bf65-cf0574383abf'
'669941d4-0b16-46fc-a2e6-562239355bbd'
'70eb1ad5-b6a8-43b5-9245-a6ae62b0a892'

How can I resolve this error. Please help.

Thanks!

[Quentin]

I'm not a postgres person, but I googled a bit and I'm guessing the problem is your ID column is type varchar but your PP_ID column is type UUID.  Don't know if you can simply CAST your UUID column as varchar, but it's worth a shot.  This stack exchange question seems similar, don't think it's quite an answer for you:

https://dba.stackexchange.com/questions/177733/postgresql-custom-operator-uuid-to-varchar

Note that the error message is coming from postgres, not SAS.  So if you google:

postgres "ERROR: operator does not exist: uuid = character varying"

you may find a solution.

Or try posting on stack overflow with a postgres tag.  I wouldn't mention SAS if you go that route, you can just post the postgres query.

[Patrick · Accepted solution]

I normally develop explicit pass-through SQL directly via a database client and only integrate into a SAS process once that's fully working. 

What @Quentin proposes will work. See PG test script below developed using the PG client PGAdmin.

1. Create and populate a temporary PG table for testing.

--create temporary table with a varchar and a uuid column
drop table if exists test;
create temporary table if not exists test 
	(
		 col_uuid uuid
		,col_varchar character varying(40)
	)
	;
	
--populate columns
insert into test values('00b3553b-408d-41b2-8ed3-cee324187d6e','00b3553b-408d-41b2-8ed3-cee324187d6e');	

2. Where clause without type casting to replicate the error you observe.

--test for where clause with different data types
select * from test where col_uuid = col_varchar;

...and here what Postgres returns:

ERROR:  operator does not exist: uuid = character varying
LINE 14: select * from test where col_uuid = col_varchar;
                                           ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 403

3. Where clause with type casting - two syntax options tested.

--test for where clause casting varchar to uuid
select * from test where col_uuid = cast(col_varchar as uuid);

select * from test where col_uuid = col_varchar::uuid;

...and this works.

Above tests done with Postgres 15 but I would be rather surprised if such syntax isn't supported in older versions.

And as a related topic:

Try to avoid explicit passing of credentials (even if the password is SAS encoded). Ideally use an authentication domain or if there is already a pre-assigned SAS library then you can also use this libref as documented here.

[Aexor]

Sorry. I need one more info. Cas we have CAST kind of functionality outside pass through query. To use CAST function in later step . I have to load the dataset in PG schema and then I am using CAST . Can we remove the LOADING overhead and use CAST like functional in PROC SQL itself . Thank You!

[Patrick]

---

@Aexor wrote:
Sorry. I need one more info. Cas we have CAST kind of functionality outside pass through query. To use CAST function in later step . I have to load the dataset in PG schema and then I am using CAST . Can we remove the LOADING overhead and use CAST like functional in PROC SQL itself . Thank You!

---

Are you talking about loading the data first into SAS and then do the join within SAS?

If so then SAS 9.4 got only two data types (character and numeric). A UUID column will get converted to a character type column same as a Varchar so you wouldn't need any type casting anymore.

https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/p1prjok4ko5rf4n1h2rf5ok8d2ct.htm 

With SAS type casting is done via put/input functions.