Postgres - uuid_generate_v4() does not exists

 

We might encounter this issue, when we try to create a Materialized View in Postgresql. For example In this case we have an Oracle MV with SYS_GUID() and I am trying to create an equivalent Postgres MV.

 Oracle:

CREATE MATERIALIZED VIEW "PROD"."MY_MVIEWS" ("ID")

AS

    SELECT TO_NUMBER (SYS_GUID (), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')    AS ID

      FROM MY_TABLE

     WHERE A = B;

PostgreSQL:

CREATE MATERIALIZED VIEW "PROD"."MY_MVIEWS" 

AS

   SELECT TO_NUMBER(uuid_generate_v4(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') AS ID

 FROM MY_TABLE

     WHERE A = B;

Error:

ERROR:  function to_number(uuid, unknown) does not exist
LINE 2: SELECT TO_NUMBER(uuid_generate_v4(),'XXXXXXXXXXXXXXXXXXXXXXX...
               ^
HINT:  No function matches the given name and argument types. 

You might need to add explicit type casts.
SQL state: 42883
Character: 60

Solution: 

1. Try to create "uuid-ossp" extention:

   CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

2. In my case, it did not work and I was asked to install the extension. 

So I  am going to install the extension first.

[root@dbserver]# yum install postgresql15-contrib

:

Installed:
  postgresql15-contrib.x86_64 0:15.2-1PGDG.rhel7

Complete!

3. Now, create the extension as the owner of the database, in my case postgres.

bash-4.2$ psql pkprod -U postgres
psql (15.2)
Type "help" for help.

prod1=#
prod1=# select current_user;
 current_user
--------------
 postgres
(1 row)

prod1=# CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION
prod1=#

->> Grant privileges for the user schema to access it. 

Now, test if the function works....

select uuid_generate_v4();

uuid

88ea95a2-b289-440e-94fd-547555665287

Here it outputs with characters. But what we need is numeric data type. 

So perform the below type cast.

select TO_NUMBER(cast (uuid_generate_v4() as text),'99999999999999999999'); 

numeric:
164387628487