RavenDB Sql Replication and Postgres uuid

Question

I have set up Sql Replication using Postgres/Npgsql.

We are using Guids for ids in Ravendb. Everything is working fine as long as my id column in Postgres is of type varchar, but if I set it to uuid, which should be the correct type to match Guid, it fails.

It also fails for other columns than id.

Postgres log gives me:

operator does not exist: uuid = text at character 34 HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Postgres schema looks like this:

CREATE TABLE public.materiels
(
  id uuid NOT NULL,
  type character varying(50),
  nummer integer,
  ...
  CONSTRAINT materiels_pkey PRIMARY KEY (id)
)

Replacing first line with

id character varying(50) NOT NULL

will make it work.

My replication setup looks like this:

If I set the replication up to use MSSql it works using MSSql's uniqueidentifier data type.

Łukasz Kamiński Łukasz Kamiński · Accepted Answer · 2017-04-11T12:11:58

If you want to compare UUID with TEXT, then you need to create operators for that. The one solving your error would look like this:

CREATE FUNCTION uuid_equal_text(uuid, text) 
 RETURNS boolean 
 LANGUAGE SQL IMMUTABLE 
AS 
$body$ 
   SELECT $1 = $2::uuid 
$body$;

CREATE OPERATOR =(
  PROCEDURE = uuid_equal_text,
  LEFTARG = uuid,
  RIGHTARG = text);

EDIT: Alternate solution suggested by author of this question himself:

CREATE CAST (text AS uuid)
WITH INOUT
AS IMPLICIT;

RavenDB Sql Replication and Postgres uuid

1 Answers