Postgres is django’s official database recommendation. Why postgres is used and what are the advantages and disadvantages of mysql and Postgres are not the focus of this article. If you are interested, please refer to the following articles:

What are pros and cons of PostgreSQL and MySQL? With respect to reliability, speed, scalability, and features
PostgreSQL vs MySQL
PostgreSQL Vs. MySQL: Differences In Performance, Syntax, And Features
Why I Choose PostgreSQL Over MySQL/MariaDB
What is PostgreSQL’s advantage over MySQL?

Django’s Model has a few fields that are specific to Postgres. This article will briefly cover these PG specific fields. It then traces the pG-to-PG feature (since all of this is backed up by pg’s powerful features).

The examples in this article are all from the official Django documentation.

Field type overview:

ArrayField
JSONField
HStoreField
Range Field

ArrayField

define

class ArrayField(base_field, size=None, **options)
Copy the code

`base_field`parameter

There is a mandatory base_field argument, which makes sense: an Array must specify the element type. So you can pass in an IntegerField, a CharField, and a TextField, but not a ForeignKey, OneToOneField, or ManyToManyField. ArrayField also supports nested lists! Consider the following example:

from django.contrib.postgres.fields import ArrayField
from django.db import models

class ChessBoard(models.Model):
    board = ArrayField(
        ArrayField(
            models.CharField(max_length=10, blank=True),
            size=8,
        ),
        size=8,
    )
Copy the code

This will generate a column of type CHARACTER VARYING (10)[] in the database:

You can insert data like this:

c = ChessBoard()
c.board = [["a"."b"."c"], ["d"."e"."f"]]
c.save()
Copy the code

It is important to note that the nested list passed in must be of the same length, otherwise an exception will be raised:

django.db.utils.DataError: multidimensional arrays must have array expressions with matching dimensions
Copy the code

This is because PG itself has this limitation on rendering array type data:

The figure above is from 8.15. Arrays

`size`parameter

Optional: Specifies the maximum Array length. If you insert a list that is longer than size, no error will be reported and the execution will still be successful:

c = ChessBoard()
c.board = [
    ["a"."b"."c"],
    ["d"."e"."f"],
    ["d"."e"."f"],
    ["d"."e"."f"],
    ["d"."e"."f"],
    ["d"."e"."f"],
    ["d"."e"."f"],
    ["d"."e"."f"],
    ["d"."e"."f"],
    ["d"."e"."f"],
    ["d"."e"."f"],
    ["d"."e"."f"],
    ["d"."e"."f"],
    ["d"."e"."f"],
    ["d"."e"."f"],
    ["d"."e"."f"],
    ["d"."e"."f"],
    ["d"."e"."f"],
    ["d"."e"."f"],
]
c.save()
Copy the code

Select * from

Query query

Take this model for example:

from django.contrib.postgres.fields import ArrayField
from django.db import models

class Post(models.Model):
    name = models.CharField(max_length=200)
    tags = ArrayField(models.CharField(max_length=200), blank=True)

    def __str__(self):
        return self.name
Copy the code

The generated table is:

`contains`

Insert three pieces of data:

Post.objects.create(name='First post', tags=['thoughts'.'django'])
Post.objects.create(name='Second post', tags=['thoughts'])
Post.objects.create(name='Third post', tags=['tutorial'.'django'])
Copy the code

Filter tags contain data for a tag:

Tags__contains is passed in a list

>>> Post.objects.filter(tags__contains=['thoughts'])
<QuerySet [<Post: First post>, <Post: Second post>]>

>>> Post.objects.filter(tags__contains=['django'])
<QuerySet [<Post: First post>, <Post: Third post>]>

>>> Post.objects.filter(tags__contains=['django'.'thoughts'])
<QuerySet [<Post: First post>]>
Copy the code

`contained_by`

In contrast to contains, this query is a subset of incoming data by tags.

>>> Post.objects.filter(tags__contained_by=['thoughts'.'django'])
<QuerySet [<Post: First post>, <Post: Second post>]>

>>> Post.objects.filter(tags__contained_by=['thoughts'.'django'.'tutorial'])
<QuerySet [<Post: First post>, <Post: Second post>, <Post: Third post>]>
Copy the code

`overlap`

Just include one of them, which means that the wider the list you pass in, the more likely you are to find the data. And the longer the list of contains passed in, the less data you might get.

>>> Post.objects.filter(tags__overlap=['thoughts'])
<QuerySet [<Post: First post>, <Post: Second post>]>

>>> Post.objects.filter(tags__overlap=['thoughts'.'tutorial'])
<QuerySet [<Post: First post>, <Post: Second post>, <Post: Third post>]>
Copy the code

`len`

Query based on the length of the ArrayField.

>>> Post.objects.create(name='First post', tags=['thoughts'.'django'])
>>> Post.objects.create(name='Second post', tags=['thoughts'])

>>> Post.objects.filter(tags__len=1)
<QuerySet [<Post: Second post>]>
Copy the code

`Index transforms`

Query for a particular element of the list (pg is a little strong ~), anything that is not negative will do, and no error will be reported if it exceeds size.

>>> Post.objects.filter(tags__0='thoughts')
<QuerySet [<Post: First post>, <Post: Second post>]>

>>> Post.objects.filter(tags__1__iexact='Django')
<QuerySet [<Post: First post>]>

>>> Post.objects.filter(tags__276='javascript')
<QuerySet []>
Copy the code

`Slice transforms`

Similar to Index transforms, but instead of an element, a slice:

>>> Post.objects.create(name='First post', tags=['thoughts'.'django'])
>>> Post.objects.create(name='Second post', tags=['thoughts'])
>>> Post.objects.create(name='Third post', tags=['django'.'python'.'thoughts'])

>>> Post.objects.filter(tags__0_1=['thoughts'])
<QuerySet [<Post: First post>, <Post: Second post>]>

>>> Post.objects.filter(tags__0_2__contains=['thoughts'])
<QuerySet [<Post: First post>, <Post: Second post>]>
Copy the code

JSONField

define

class JSONField(encoder=None, **options)
Copy the code

These Python native formats can be used: Dictionaries, Lists, Strings, Numbers, Booleans, and None.

The following example uses this model:

class Dog(models.Model):
    name = models.CharField(max_length=200)
    data = JSONField()

    def __str__(self):
        return self.name
Copy the code

The data field is a JSONB type data:

Example of inserting data:

Dog.objects.create(name='Rufus', data={
    'breed': 'labrador'.'owner': {
        'name': 'Bob'.'other_pets': [{
            'name': 'Fishy',}].}})Copy the code

`encoder`parameter

Optional. When does it work? When your data is not of Python native type, such as UUID, datetime, etc. You can use DjangoJSONEncoder or any json.JSONEncoder subclass that meets your requirements.

Datetime.datetime.now () : datetime.datetime.now() : datetime.datetime.now() : datetime.datetime.now() :

TypeError: Object of type datetime is not JSON serializable
Copy the code

If you want to insert datetime data, you can use DjangoJSONEncoder (the official documentation is here), which is:

from django.core.serializers.json import DjangoJSONEncoder

class Dog(models.Model):
    name = models.CharField(max_length=200)
    data = JSONField(encoder=DjangoJSONEncoder)
Copy the code

Then execute the following insert statement without error:

Dog.objects.create(name='Rufus', data={
    'breed': 'labrador'.'owner': {
        'name': 'Bob'.'other_pets': [{
            'name': 'Fishy',}],},'birthday': datetime.datetime.now()
})
Copy the code

Note that pg is still stored as a string, and is not automatically returned to datetime as a string when retrieved.

dog = Dog.objects.filter(name='Rufus') [1]print(type(dog.data['birthday']))
Copy the code

<class 'str'>
Copy the code

I think the reason Django doesn’t automatically convert for you is that it may not be what you want to convert because it is a string that happens to be in datetime format. And you need to know better than Django what type of data it is, and when it needs to be converted and when it doesn’t.

Query data

Insert test data:

Dog.objects.create(name='Rufus', data={
    'breed': 'labrador'.'owner': {
        'name': 'Bob'.'other_pets': [{
            'name': 'Fishy',}]}})Copy the code

Key, index, and path lookups

Dog.objects.filter(data__owner=None)
Dog.objects.filter(data__breed='collie')
Dog.objects.filter(data__owner__name='Bob')
Dog.objects.filter(data__owner__other_pets__0__name='Fishy')

# select missing key from isnull
>>> Dog.objects.create(name='Shep', data={'breed': 'collie'})
>>> Dog.objects.filter(data__owner__isnull=True)
<QuerySet [<Dog: Shep>]>

Copy the code

Is it as powerful as Mongo’s support for JSON-type data?

other

As with HStoreField, there are the following query methods:

contains
contained_by
has_key
has_any_keys
has_keys

HStoreField

define

class HStoreField(**options)
Copy the code

Used to store key-value pair data. The Python data type is dict, but the key must be a string and the value must be a string or null.

If you want to use this field, you need to do two additional things:

willdjango.contrib.postgresAdded to theINSTALLED_APPS.
Open the HStore Extension of PG

The second step is to modify a migrations file:

For example, a model like this:

class Dog(models.Model):
    name = models.CharField(max_length=200)
    data = HStoreField()

    def __str__(self):
        return self.name
Copy the code

The original Migrations document looked like this:

import django.contrib.postgres.fields.hstore
from django.db import migrations, models


class Migration(migrations.Migration):
    dependencies = [
        ('goods'.'0004_auto_20190514_1502'),
    ]

    operations = [
        migrations.CreateModel(
            name='Dog',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('name', models.CharField(max_length=200)),
                ('data', django.contrib.postgres.fields.hstore.HStoreField()),
            ],
        ),
    ]
Copy the code

We need to make a little change: add an HStoreExtension() at the top of operations.

from django.contrib.postgres.operations import HStoreExtension

class Migration(migrations.Migration):
    ...

    operations = [
        HStoreExtension(),
        ...
    ]
Copy the code

The final Migrations document looks like this:

import django.contrib.postgres.fields.hstore
from django.db import migrations, models
from django.contrib.postgres.operations import HStoreExtension


class Migration(migrations.Migration):
    dependencies = [
        ('goods'.'0004_auto_20190514_1502'),
    ]

    operations = [
        HStoreExtension(),
        migrations.CreateModel(
            name='Dog',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('name', models.CharField(max_length=200)),
                ('data', django.contrib.postgres.fields.hstore.HStoreField()),
            ],
        ),
    ]
Copy the code

See the official documentation on adding the database plug-in to Migrations.

This second step cannot be omitted. Otherwise, the following error will be reported:

can't adapt type 'dict' if you skip the first step, or type "hstore" does not exist
Copy the code

The query

`Key lookups`

Select * from key;

>>> Dog.objects.create(name='Rufus', data={'breed': 'labrador'})
>>> Dog.objects.create(name='Meg', data={'breed': 'collie'})

>>> Dog.objects.filter(data__breed='collie')
<QuerySet [<Dog: Meg>]>
Copy the code

Other query methods can also be chained:

>>> Dog.objects.filter(data__breed__contains='l')
<QuerySet [<Dog: Rufus>, <Dog: Meg>]>
Copy the code

`contains`

>>> Dog.objects.create(name='Rufus', data={'breed': 'labrador'.'owner': 'Bob'})
>>> Dog.objects.create(name='Meg', data={'breed': 'collie'.'owner': 'Bob'})
>>> Dog.objects.create(name='Fred', data={})

>>> Dog.objects.filter(data__contains={'owner': 'Bob'})
<QuerySet [<Dog: Rufus>, <Dog: Meg>]>

>>> Dog.objects.filter(data__contains={'breed': 'collie'})
<QuerySet [<Dog: Meg>]>
Copy the code

`contained_by`

>>> Dog.objects.create(name='Rufus', data={'breed': 'labrador'.'owner': 'Bob'})
>>> Dog.objects.create(name='Meg', data={'breed': 'collie'.'owner': 'Bob'})
>>> Dog.objects.create(name='Fred', data={})

>>> Dog.objects.filter(data__contained_by={'breed': 'collie'.'owner': 'Bob'})
<QuerySet [<Dog: Meg>, <Dog: Fred>]>

>>> Dog.objects.filter(data__contained_by={'breed': 'collie'})
<QuerySet [<Dog: Fred>]>
Copy the code

`has_key`

Based on whether a key is included as a query condition.

>>> Dog.objects.create(name='Rufus', data={'breed': 'labrador'})
>>> Dog.objects.create(name='Meg', data={'breed': 'collie'.'owner': 'Bob'})

>>> Dog.objects.filter(data__has_key='owner')
<QuerySet [<Dog: Meg>]>
Copy the code

`has_any_keys`

>>> Dog.objects.create(name='Rufus', data={'breed': 'labrador'})
>>> Dog.objects.create(name='Meg', data={'owner': 'Bob'})
>>> Dog.objects.create(name='Fred', data={})

>>> Dog.objects.filter(data__has_any_keys=['owner'.'breed'])
<QuerySet [<Dog: Rufus>, <Dog: Meg>]>
Copy the code

`has_keys`

>>> Dog.objects.create(name='Rufus', data={})
>>> Dog.objects.create(name='Meg', data={'breed': 'collie'.'owner': 'Bob'})

>>> Dog.objects.filter(data__has_keys=['breed'.'owner'])
<QuerySet [<Dog: Meg>]>
Copy the code

`keys`

>>> Dog.objects.create(name='Rufus', data={'toy': 'bone'})
>>> Dog.objects.create(name='Meg', data={'breed': 'collie'.'owner': 'Bob'})

>>> Dog.objects.filter(data__keys__overlap=['breed'.'toy'])
<QuerySet [<Dog: Rufus>, <Dog: Meg>]>
Copy the code

values

>>> Dog.objects.create(name='Rufus', data={'breed': 'labrador'})
>>> Dog.objects.create(name='Meg', data={'breed': 'collie'.'owner': 'Bob'})

>>> Dog.objects.filter(data__values__contains=['collie'])
<QuerySet [<Dog: Meg>]>
Copy the code

Range Fields

Pg also supports range-type data. For example, IntegerRangeField BigIntegerRangeField DecimalRangeField etc., the space is limited, don’t speak here. Read the official documentation if you are interested.

To summarize

Pg is powerful, very powerful, and can do a lot more than just a relational database. In particular, the built-in data types are extremely rich.

Pg also has full text search, you don’t even need to use ElasticSearch; Pg makes index optimization for JSON data, which can realize the function of non-relational database such as Mongo. No wonder Django’s first official database is pg.

If you love computer science and basic logic like me, welcome to follow my wechat official account:

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

[Django]Django model for PG, if you still don’t use PG, I lose

ArrayField

define

`base_field`parameter

`size`parameter

Query query

`contains`

`contained_by`

`overlap`

`len`

`Index transforms`

`Slice transforms`

JSONField

define

`encoder`parameter

Query data

Key, index, and path lookups

other

HStoreField

define

The query

`Key lookups`

`contains`

`contained_by`

`has_key`

`has_any_keys`

`has_keys`

`keys`

values

Range Fields

To summarize

[Django]Django model for PG, if you still don’t use PG, I lose

ArrayField

define

base_fieldparameter

sizeparameter

Query query

contains

contained_by

overlap

len

Index transforms

Slice transforms

JSONField

define

encoderparameter

Query data

Key, index, and path lookups

other

HStoreField

define

The query

Key lookups

contains

contained_by

has_key

has_any_keys

has_keys

keys

values

Range Fields

To summarize

Related Posts

Leetcode 2215. Find the Difference of Two Arrays (Python)

C++ function related knowledge point analysis

Summary of Java Advanced Interview Series

`base_field`parameter

`size`parameter

`contains`

`contained_by`

`overlap`

`len`

`Index transforms`

`Slice transforms`

`encoder`parameter

`Key lookups`

`contains`

`contained_by`

`has_key`

`has_any_keys`

`has_keys`

`keys`