How to generate 12 digit unique number in redshift?

Question

I have 3 columns in a table i.e. email_id, rid, final_id.

Rules for rid and final_id:

1. If the email_id has a corresponding rid, use rid as the final_id.
2. If the email_id does not have a corresponding rid(i.e.rid is null), generate a unique 12 digit number and insert into final_id field.

How to generate 12 digit unique number in redshift?

Answer 1

From Creating a UUID function in Redshift:

By default there is no UUID function in AWS Redshift. However with the Python User-Defined Function you can easily create a UUID function in Redshift.

If you want random UUID:

CREATE OR REPLACE FUNCTION public.fn_uuid()
RETURNS character varying AS
' import uuid
 return uuid.uuid4().__str__()
 '
LANGUAGE plpythonu VOLATILE;

If you want sequential UUID :

CREATE OR REPLACE FUNCTION public.fn_uuid()
RETURNS character varying AS
' import uuid
 return uuid.uuid1().__str__()
 '
LANGUAGE plpythonu VOLATILE;