Equivalent to UNIQUE IDENTIFIER in PostgreSQL

user_8379612 0
Viewed 11157

I was tryiong to switch from MSSQL to PostgreSQL and hence trying to convert queries to PostgreSQL equivalent. However running PostgreSQL query is giving an error:

ERROR: type "uniqueidentifier" does not exist LINE 3: ID UNIQUEIDENTIFIER DEFAULT UUID_GENERATE_V4()::VARCHAR NO... ^ SQL state: 42704 Character: 38

MSSQL

CREATE TABLE [dbo].[ISS_AUDIT]
(
  [ID] UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL,
  [GRAPH_ID] [varchar](196)
  PRIMARY KEY(ID)
);

PostgreSQL

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE public.ISS_AUDIT
(
  ID UNIQUEIDENTIFIER DEFAULT UUID_GENERATE_V4()::VARCHAR NOT NULL,
  GRAPH_ID VARCHAR(196),
  PRIMARY KEY(ID)
);

Am I missing something on UNIQUEIDENTIFIER ?

sqldatatypes uuid postgresql
2 Answers

This is the correct script:

CREATE TABLE public.ISS_AUDIT
(
  ID uuid PRIMARY KEY DEFAULT UUID_GENERATE_V4(),
  GRAPH_ID VARCHAR(196)
);

See this link. Extract:

SQL Server calls the type UniqueIdentifier and PostgreSQL calls the type uuid. Both types occupy 16-bytes of storage. For compatibility reasons with other software or databases, many use some stanardized text representation of them particularly for transport rather than using the native type.

user_77463980

We need UUID which can be used as below:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE public.ISS_AUDIT(
  ID UUID DEFAULT UUID_GENERATE_V4()::UUID NOT NULL,
  GRAPH_ID VARCHAR(196),
  PRIMARY KEY(ID)
);
user_83796120
Related