BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Aexor
Lapis Lazuli | Level 10

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

 

 

How can I resolve this error. Please help.

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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.

Patrick_0-1676773679938.png

 

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:

Patrick_0-1676776028889.png

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.

Patrick_1-1676776228632.png

 

 

 

 

View solution in original post

4 REPLIES 4
Quentin
Super User

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.

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in March 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
Patrick
Opal | Level 21

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.

Patrick_0-1676773679938.png

 

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:

Patrick_0-1676776028889.png

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.

Patrick_1-1676776228632.png

 

 

 

 

Aexor
Lapis Lazuli | Level 10
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
Opal | Level 21

@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 

Patrick_0-1677192959106.png

 

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

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 2124 views
  • 4 likes
  • 3 in conversation