sql - 使用Haskell的postgres-simple库执行多行插入时如何修复 "operator does not exist: text = uuid"？

Question

我正在使用 postgres-simple 库插入到 eligible_class_passes 表中。它本质上是一个代表多对多关系的连接表。

我正在使用executeMany postgres-simple 中的函数执行多行插入。

updateEligibleClassPasses :: Connection -> Text -> Text -> [Text] -> IO Int64
updateEligibleClassPasses conn tenantId classTypeId classPassTypeIds =
  withTransaction conn $ do
    executeMany
      simpleConn
      [sql| 
      INSERT INTO eligible_class_passes (class_type_id, class_pass_type_id)
      SELECT upd.class_type_id::uuid, upd.class_pass_type_id::uuid
      FROM (VALUES (?, ?, ?)) as upd(class_type_id, class_pass_type_id, tenant_id)
      INNER JOIN class_types AS ct
      ON upd.class_type_id::uuid = ct.id
      INNER JOIN subscription_types AS st
      ON class_pass_type_id::uuid = st.id
      WHERE ct.tenant_id = upd.tenant_id::uuid AND st.tenant_id = upd.tenant_id::uuid
      |]
      params
 where
  addParams classPassTypeId = (classTypeId, classPassTypeId, tenantId)
  params = addParams <$> classPassTypeIds

当应用正确的参数执行此函数时，我收到以下运行时错误

SqlError {sqlState = "42883", sqlExecStatus = FatalError, sqlErrorMsg = "operator does not exist: text = uuid", sqlErrorDetail = "", sqlErrorHint = "No operator matches the given name and argument type(s). You might need to add explicit type casts."}

但是，当转换为不带参数替换 (?) 的 SQL 时，查询在 psql 中执行时可以正常工作。

INSERT INTO eligible_class_passes (class_type_id, class_pass_type_id)
SELECT upd.class_type_id::uuid, upd.class_pass_type_id::uuid
FROM (VALUES ('863cb5ea-7a68-41d5-ab9f-5344605de500', 'e9195660-fd48-4fa2-9847-65a0ad323bd5', '597e6d7a-092a-49be-a2ea-11e8d85d8f82')) as upd(class_type_id, class_pass_type_id, tenant_id)
INNER JOIN class_types AS ct
ON upd.class_type_id::uuid = ct.id
INNER JOIN subscription_types AS st
ON class_pass_type_id::uuid = st.id
WHERE ct.tenant_id = upd.tenant_id::uuid AND st.tenant_id = upd.tenant_id::uuid;        

我的架构如下

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE tenants (
  id UUID NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY, 
  name text NOT NULL UNIQUE, 
  email text NOT NULL UNIQUE, 
  created_at timestamp with time zone NOT NULL default now(), 
  updated_at timestamp with time zone NOT NULL default now()
);

CREATE TABLE class_types (
  id UUID NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY, 
  FOREIGN KEY (tenant_id) REFERENCES tenants (id), 
  created_at timestamp with time zone NOT NULL default now(), 
  updated_at timestamp with time zone NOT NULL default now()
);

CREATE TABLE class_pass_types (
  id UUID NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY, 
  name TEXT NOT NULL, 
  tenant_id UUID NOT NULL, 
  price Int NOT NULL, 
  created_at timestamp with time zone NOT NULL default now(), 
  updated_at timestamp with time zone NOT NULL default now(), 
  FOREIGN KEY (tenant_id) REFERENCES tenants (id)
);

-- Many to many join through table.
-- Expresses class pass type redeemability against class types.
CREATE TABLE eligible_class_passes (
  class_type_id UUID, 
  class_pass_type_id UUID, 
  created_at timestamp with time zone NOT NULL default now(), 
  updated_at timestamp with time zone NOT NULL default now(), 
  FOREIGN KEY (class_type_id) REFERENCES class_types (id) ON DELETE CASCADE, 
  FOREIGN KEY (class_pass_type_id) REFERENCES class_pass_types (id) ON DELETE CASCADE, 
  PRIMARY KEY (
    class_type_id, class_pass_type_id
  )
);

Answer 1

为了帮助调试您的问题，请使用 formatQuery函数，然后您可以看到 postgresql-simple 发送到服务器的最终查询类型。

此外，我建议使用 UUID从 uuid-types 包中输入，而不是 uuid 的 Text 。使用 Text 很可能会向您隐藏一些问题(您希望通过使用 formatQuery 看到这些问题。