20

I am trying to use node-pg-migrate and run migrations to create tables in my node project. When I run migrations I get function uuid_generate_v4() does not exist.

I did check in my extensions and uuid-ossp is available.

extname  | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
-----------+----------+--------------+----------------+------------+-----------+--------------
 plpgsql   |       10 |           11 | f              | 1.0        |           | 
 uuid-ossp |    16384 |         2200 | t              | 1.1        |           | 
(2 rows)

I expect my migrations to run but it fails. I am using Postgres 11 on Mac. Postgres installed from here - https://postgresapp.com/

Rockr
  • 319
  • 1
  • 2
  • 11
  • I hope you have not encountered the same problem as this: https://github.com/salsita/node-pg-migrate/issues/401#issuecomment-466891346 – Shinigami Jun 01 '19 at 23:28

3 Answers3

36
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
Purushottam Sadh
  • 1,087
  • 10
  • 5
  • you also need to have `uuid-ossp.control` extension to be available provided by `postgresql-contrib` package – mkungla May 06 '22 at 19:11
1

Unfortunately, I do not have much information about what your pg-migrate files look like, but you must specify the following:

pgm.createExtension('uuid-ossp', { ifNotExists: true });
// and
pgm.createTable('my_table', {
    id: { type: 'uuid', primaryKey: true, default: new PgLiteral('gen_random_uuid()') },
    // ...
Shinigami
  • 646
  • 1
  • 7
  • 21
  • Hey, I tried that and it still says function not found. I must be doing something really silly. Somewhere I found its not in right schema, I even put that and it still gave the same error. – Rockr Jun 04 '19 at 14:32
0

The reason is that the create extension must be in a different transaction. Practically it means to have one migration for creating the extension. Only in later migration create the table with the uuid_generate_v4 function.

Yaakov Aglamaz
  • 17
  • 2