[Solved]-ProgrammingError: column "product" is of type product[] but expression is of type text[] enum postgres

by

10đź‘Ť

âś…

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.

👤e4c5

2đź‘Ť

The definition of product field is incorrect as it specifies that it is array of CharFields but it is array of enums in reality. Django does not support enum type now so you can try this extension to define the type correctly:

class Product(Enum):
  ProductA = 'a'
  ...

class Campaign(models.Model):
  product = ArrayField(EnumField(Product, max_length=<whatever>))
👤Roman-Stop RU aggression in UA

2đź‘Ť

Try this: 

def django2psql(s):
    return '{'+','.join(s) + '}'

campaign = Campaign(id=5, product=django2psql(["car", "truck"]))
👤stovfl

-1đź‘Ť

I think you may have to subclass CharField to get it to report the correct db_type. There may be more problems than this but you can give this a try:

class Product(models.CharField):
    def db_type(self, connection):
        return 'product'

PRODUCT = (
    ('car', 'car'),
    ('truck', 'truck')
)

class Campaign(models.Model):
    product = ArrayField(Product(null=True, choices=PRODUCT))
👤edruid

Leave a comment