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

Question

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

Answer 1

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>))

Answer 2

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.

Answer 3

Try this:

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

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