如何使用Clojure JDBC插入Postgres枚举值？

For example, here is a product table in PostgreSQL with status as an enum:

例如，这是PostgreSQL中的产品表，状态为枚举：

create type product_status as enum ('InStock', 'OutOfStock');

create table product (
    pid            int primary key default nextval('product_pid_seq'),
    sku            text not null unique,
    name           text not null,
    description    text not null,
    quantity       int not null,
    cost           numeric(10,2) not null,
    price          numeric(10,2) not null,
    weight         numeric(10,2),
    status         product_status not null
);

Typical Clojure code to insert a product would be:

插入产品的典型Clojure代码是：

(def prod-12345 {:sku "12345"
                 :name "My Product"
                 :description "yada yada yada"
                 :quantity 100
                 :cost 42.00
                 :price 59.00
                 :weight 0.3
                 :status "InStock"})

(sql/with-connection db-spec
   (sql/insert-record :product prod-12345))

However, status is an enum so you can't insert it as a normal string without casting it to an enum:

但是，status是一个枚举，因此您不能将其作为普通字符串插入而不将其强制转换为枚举：

'InStock'::product_status

I know you can do it with a prepared statement, such as:

我知道你可以用准备好的声明来做，例如：

INSERT INTO product (name, status) VALUES (?, ?::product_status)

But is there a way to do it without using a prepared statement?

但有没有办法在不使用准备好的声明的情况下做到这一点？

3 个解决方案

#1

I got this working today using the stringtype=unspecified ~~hack~~ workaround.

今天我使用stringtype = unspecified hack workaround来解决这个问题。

You can add this parameter to your db-spec as follows:

您可以将此参数添加到db-spec，如下所示：

(def db-spec {:classname "org.postgresql.Driver"
              :subprotocol "postgresql"
              :subname "//myserver:5432/mydatabase"
              :user "myuser"
              :password "mypassword"
              :stringtype "unspecified"}) ; HACK to support enums

Then just use insert! as usual.

然后只需使用插入！照常。

It would be good to have a solution that doesn't weaken type safety so much.

拥有一个不会过分削弱类型安全性的解决方案会很好。

#2

Kris Jurka replied to the discussion Mike Sherrill cited above with a workaround:

Kris Jurka回复了Mike Sherrill上面引用的讨论，并提出了一个解决方法：

use the url parameter stringtype=unspecified [in the JDBC connection URL] to have setString always bind to unknown instead of varchar, which then shouldn't require any code changes.

使用url参数stringtype = unspecified [在JDBC连接URL中]使setString始终绑定到unknown而不是varchar，然后不应该要求任何代码更改。

I tried this in Java, and it seems to work fine.

我在Java中试过这个，它似乎运行正常。

#3

Unless you pass plain SQL to the back end, you'll have to use a cast. (The SQL statement INSERT INTO product (name, status) VALUES ('SomeName', 'InStock'); should work fine.)

除非您将纯SQL传递给后端，否则您将不得不使用强制转换。（SQL语句INSERT INTO产品（名称，状态）VALUES（'SomeName'，'InStock'）;应该可以正常工作。）

Tom Lane addressed this issue on pgsql-hackers a week after you asked your question.

在您提出问题一周后，Tom Lane在pgsql-hackers上解决了这个问题。

AFAIK this is just business as usual with JDBC: setString() implies that the parameter is of a string type. It'll fall over if the type actually required is anything but a string. (I'm no Java expert, but I seem to recall that using setObject instead is the standard workaround.)

AFAIK这与JDBC一样正常：setString（）暗示参数是字符串类型。如果实际需要的类型不是字符串，它将会失效。（我不是Java专家，但我似乎记得使用setObject代替标准的解决方法。）

Enums are not suffering any special hardship here, and I'd be against weakening the type system to give them a special pass.

Enums在这里没有遇到任何特殊困难，我反对弱化类型系统给他们一个特殊的传球。

Our own @CraigRinger participated in that discussion, and might have found something relevant by now.

我们自己的@CraigRinger参与了那次讨论，现在可能已经找到了相关内容。

#1