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;

Answer 2

Just to add to the accepted response:

Using uuid.uuid1().__str__() or uuid.uuid4().__str__() will give you values like XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX. This is completely valid as a uuid, but a neat way to get rid of the hash is to use the hex attribute:

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

or

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

This will give you values without the hash, so just 32 hexadecimal characters.