I have a postgresql 9 installation on windows, which doesn't have built in uuid generator. There is OSSD package, which can be bound to postgresql as uuid generator, but it's for *nix only (I think).
How can I generate uuid in windows postgresql?
3 Answers
The one-click installer from EnterpriseDB does have it. The DLL is called "uuid-ossp.dll" and resides in "(Postgres' installation directory)\lib" and the installation SQL script is called "uuid-ossp.sql" and resides in "(Postgres' installation directory)\share\contrib". You have to execute the last one in the DB of your choice.
3 Comments
Endy Tjahjono
Thanks! To add to the answer, I have to login to the db that needs to generate uuid (not postgres db) as administrator / postgres user (not normal user).
Milen A. Radev
Yes, the "Additional Supplied Modules" section of the manual mentions that.
Cory R. King
on postgresql 9.1, you simply execute CREATE EXTENSION "uuid-ossp"
The uuid-ossp-module in the contrib, does work on Windows XP 32 bits. The 64 bits version is not available yet, the OSSP UUID library project has to fix this problem.
Comments
On windows, I did the following to generate UUIDs:
Log into the server using this command on Command Prompt:
psql -U postgres
This is basically accessing PostgreSQL as root.
Then select the database you want this function to be available in:
postgres=# \c <name of database>
Then execute the following:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
Then test by immediately executing:
SELECT uuid_generate_v4();
I recommend you review the uuid-ossp documentation on the available UUID functions.
uuidv7&uuidv4functions built-in. You’ll want Version 7 UUIDs for indexing. See my Answer on related Question.