Skip to content

Add UUID support #1

@roblinksdata

Description

@roblinksdata

Hi, I stumbled across this (very new) project and decided to try a couple of things out which didn't quite work. I thought I'd highlight them...

Starting with the following postgres SQL definition of a table and some data

CREATE TABLE public.patients (
	patient_id uuid DEFAULT gen_random_uuid() NOT NULL,
	week_of_birth date NOT NULL,
	CONSTRAINT patients_pk PRIMARY KEY (patient_id)
);

INSERT INTO public.patients (patient_id,week_of_birth) VALUES
	 ('8e4808ee-c1f3-4deb-a60a-71b7b8425b3b'::uuid,'2020-01-01'),
	 ('bd829f47-cd1e-4135-99b9-f24bd19a6934'::uuid,'2001-02-01');

I tried converting it to the DB2 dialect and got the following:

CREATE TABLE public.patients (
  patient_id UUID DEFAULT UUID() NOT NULL,
  week_of_birth DATE NOT NULL,
  CONSTRAINT patients_pk PRIMARY KEY (patient_id)
); INSERT INTO public.patients (
  patient_id,
  week_of_birth
)
VALUES
  (CAST('8e4808ee-c1f3-4deb-a60a-71b7b8425b3b' AS UUID), '2020-01-01'),
  (CAST('bd829f47-cd1e-4135-99b9-f24bd19a6934' AS UUID), '2001-02-01')

I think it needs to look more like the following:

CREATE TABLE public.patients (
  patient_id CHAR(36) NOT NULL DEFAULT '0',
  week_of_birth DATE NOT NULL,
  CONSTRAINT patients_pk PRIMARY KEY (patient_id)
);

INSERT INTO public.patients (
  patient_id,
  week_of_birth
)
VALUES
  ('8e4808ee-c1f3-4deb-a60a-71b7b8425b3b', '2020-01-01'),
  ('bd829f47-cd1e-4135-99b9-f24bd19a6934', '2001-02-01')

Also, I'm not sure how you'd want to deal with inserting default values from functions like the UUID in patient_id into the tables - I left that out entirely as I couldn't find a quick way to do it in DB2; presumably it needs a trigger.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type
    No fields configured for issues without a type.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions