编程错误：列“产品”是产品类型[]，但表达式是文本类型[]枚举postgres [英] ProgrammingError: column "product" is of type product[] but expression is of type text[] enum postgres

问题描述

我想保存枚举数组。

我有以下内容：

  CREATE TABLE public.campaign 
（
 id integer NOT NULL，
 product product [] 
）
  / pre> 
 
 
产品是一个枚举。
 
 
 
在Django我定义如下：
  PRODUCT =（
（'car'，'car'），
（'truck'，'truck'）
）
 class Campaign（models.Model）：
 product = ArrayField（models.CharField（null = True，choices = PRODUCT）） 
  
但是，当我写下列内容时：
  campaign = Campaign（id = 5，product = [car，truck]）
 campaign.save（）
  
我收到以下错误：
  ProgrammingError ：列产品类型为产品[]，但表达式为文本类型[] 
 LINE 1：...product= ARRAY ['car ... 
  
 注意 
我看到这个答案，但我不使用sqlalchemy，并会
 
 
 
  EDITED  
我以下这样尝试了@Roman Konoval的建议：
  class PRODUCT（Enum）：
 CAR ='car'
 TRUCK ='truck'
 
 class Campaign（models.Model）：
 product = ArrayField（EnumField（PRODUCT，max_length = 10））
  
和：
  campaign = Campaign（id = 5，product = [CAR，TRUCK]）
 campaign.save（）
  
但是，我仍然收到相同的错误，
 
 
 
我看到django将其转换为字符串列表。 
如果我直接写下psql控制台：
  INSERT INTO campaign（product）VALUES（' {car，truck}':: product []）
  
它工作得很好
解决方案
这里有两个基本问题。
 
 
 
不要使用枚举
 
 
 
如果您继续使用枚举，您在Stackoverflow上的下一个问题将是如何向枚举添加新条目？。 Django不支持枚举类型（谢天谢地）。所以你必须使用第三方库。您的里程将随图书馆的完整程度而有所不同。
 
 
枚举值占用磁盘上的四个字节。枚举
值的文本标签的长度受到编译为
的NAMEDATALEN设置限制到PostgreSQL中;在标准版本中，这意味着最多63个字节。
 
 
如果您正在考虑通过使用枚举来节省磁盘空间，则上述来自手册的引用表明这是一个错觉。 
 
 
 
看到这个 
 
 
不要使用数组
 
 
 
 
 
提示：数组不是集合;搜索特定的数组元素可以是
数据库设计的标志。考虑使用一个单独的表与
行为每个项目将是一个数组元素。这将更容易
进行搜索，并且可能会对大量的
元素进行更好的扩展。
 
 
 
 
  https://www.postgresql.org/docs/9.6/static /arrays.html  
 
 
 
如果您要搜索涉及汽车或卡车的广告系列，您将需要做很多努力。数据库也是如此。
 
 
 
正确的设计
 
 
 
正确的设计是postgresql数组文档中建议的设计页。创建一个相关的表。这是标准的django方式。
  class Campaign（models.Model）：
 name = models.CharField （max_length = 20）
 
 
 class Product（Models.model）：
 name = models.CharField（max_length = 20）
 campaign = models.ForeignKey ）
  
这使您的代码更简单。不需要任何额外的存储空间。不需要第三方库。最重要的是 django相关模型的广泛api >变得可用。 
 
I would like to save array of enums.

I have the following:
CREATE TABLE public.campaign
(
  id integer NOT NULL,
  product product[]
)
product is an enum.

In Django I defined it like this:
PRODUCT = (
    ('car', 'car'),
    ('truck', 'truck')
)
class Campaign(models.Model):
    product = ArrayField(models.CharField(null=True, choices=PRODUCT))
However, when I write the following:
campaign = Campaign(id=5, product=["car", "truck"])
campaign.save()
I get the following error:
ProgrammingError: column "product" is of type product[] but expression is of type text[]
LINE 1: ..."product" = ARRAY['car...
Note
I saw this answer, but I don't use sqlalchemy and would rather not use it if not needed.

EDITED
I tried @Roman Konoval suggestion below like this:
class PRODUCT(Enum):
    CAR = 'car'
    TRUCK = 'truck'

class Campaign(models.Model):
        product = ArrayField(EnumField(PRODUCT, max_length=10))
and with:
campaign = Campaign(id=5, product=[CAR, TRUCK])
campaign.save()
However, I still get the same error,

I see that django is translating it to list of strings.
if I write the following directly the the psql console: 
INSERT INTO campaign ("product") VALUES ('{car,truck}'::product[]) 
it works just fine
解决方案
There are two fundamental problems here.

Don't use Enums

If you continue to use enum, your next question here on Stackoverflow will be "how do I add a new entry to an enum?". Django does not support enum type out of the box (thank heavens). So you have to use third party libraries for this. Your mileage will vary with how complete the library is.

  
An enum value occupies four bytes on disk. The length of an enum
  value's textual label is limited by the NAMEDATALEN setting compiled
  into PostgreSQL; in standard builds this means at most 63 bytes.
If you are thinking that you are saving space on disk by using enum, the above quote from the manual shows that it's an illusion. 

See this Q&A for more on advantages and disadvantages of enum. But generally the disadvantages outweigh the advantages.

Don't use Arrays


  
Tip: Arrays are not sets; searching for specific array elements can be
  a sign of database misdesign. Consider using a separate table with a
  row for each item that would be an array element. This will be easier
  to search, and is likely to scale better for a large number of
  elements.
Source: https://www.postgresql.org/docs/9.6/static/arrays.html

If you are going to search for a campaign that deals with Cars or Trucks you are going to have to do a lot of hard work. So will the database.

The correct design

The correct design is the one suggested in the postgresql arrays documentation page. Create a related table. This is the standard django way as well.
class Campaign(models.Model):
    name = models.CharField(max_length=20)


class Product(Models.model):
    name = models.CharField(max_length=20)
    campaign = models.ForeignKey(Campaign)
This makes your code simpler. Doesn't require any extra storage. Doesn't require third party libraries. And best of all the vast api of the django related models becomes available to you. 

                        
这篇关于编程错误：列“产品”是产品类型[]，但表达式是文本类型[]枚举postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！