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!
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.
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.
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 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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.