Search code examples
postgresqluuidsqldatatypes

Equivalent to UNIQUE IDENTIFIER in PostgreSQL

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 ?

Solution

  • 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.