1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
|
.. include:: ./snippets/postgres_doc_links.rst
.. include:: ./snippets/manager_model_warning.rst
.. _expressions_page:
Expressions
===========
Selecting an individual HStore key
----------------------------------
Use the :class:`~psqlextra.expressions.HStoreRef` expression to select an indvidiual `hstore`_ key:
.. code-block:: python
from psqlextra.models import PostgresModel
from psqlextra.fields import HStoreField
from psqlextra.expressions import HStoreRef
class MyModel(PostgresModel):
bla = HStoreField()
MyModel.objects.create(bla={'a': '1', 'b': '2'})
# '1'
a = (
MyModel.objects
.annotate(a=HStoreRef('bla', 'a'))
.values_list('a', flat=True)
.first()
)
Selecting a datetime as a UNIX epoch timestamp
----------------------------------------------
Use the :class:`~psqlextra.expressions.DateTimeEpoch` expression to select the value of a :class:`~django:django.db.models.DateTimeField` as a UNIX epoch timestamp.
.. code-block:: python
from psqlextra.models import PostgresModel
from psqlextra.fields import HStoreField
from psqlextra.expressions import DateTimeEpoch
class MyModel(PostgresModel):
datetime = DateTimeField(auto_now_add=True)
MyModel.objects.create()
timestamp = (
MyModel.objects
.annotate(timestamp=DateTimeEpoch('datetime'))
.values_list('timestamp', flat=True)
.first()
)
Multi-field coalesce
--------------------
Use the :class:`~psqlextra.expressions.IsNotNone` expression to perform something similar to a `coalesce`, but with multiple fields. The first non-null value encountered is selected.
.. code-block:: python
from psqlextra.models import PostgresModel
from psqlextra.fields import HStoreField
from psqlextra.expressions import IsNotNone
class MyModel(PostgresModel):
name_1 = models.TextField(null=True)
name_2 = models.TextField(null=True)
name_3 = models.TextField(null=True)
MyModel.objects.create(name_3='test')
# 'test'
name = (
MyModel.objects
.annotate(name=IsNotNone('name_1', 'name_2', 'name_3', default='buh'))
.values_list('name', flat=True)
.first()
)
# 'buh'
name = (
MyModel.objects
.annotate(name=IsNotNone('name_1', 'name_2', default='buh'))
.values_list('name', flat=True)
.first()
)
Excluded column
---------------
Use the :class:`~psqlextra.expressions.ExcludedCol` expression when performing an upsert using `ON CONFLICT`_ to refer to a column/field in the data is about to be upserted.
PostgreSQL keeps that data to be upserted in a special table named `EXCLUDED`. This expression is used to refer to a column in that table.
.. code-block:: python
from django.db.models import Q
from psqlextra.expressions import ExcludedCol
(
MyModel
.objects
.on_conflict(
['name'],
ConflictAction.UPDATE,
# translates to `priority > EXCLUDED.priority`
update_condition=Q(priority__gt=ExcludedCol('priority')),
)
.insert(
name='henk',
priority=1,
)
)
|
