VIEWs are a nice way to store a subset of a table in a way that can itself be queried, or perhaps slightly or radically changing the shape of your table. It has a fairly simple syntax:
CREATE VIEW "foo" AS SELECT "bar", "baz", "qux" FROM "corge" WHERE "grault" IS NULL;
You may use any valid
SELECT query as the source of a
VIEW, including one that contains
UNION ALL. You can use this form to create a view that takes two similarly formatted tables and combines them into one logical table. Note that for a
UNION to work, the columns (and column types) must be identical between the two parts of the query. A
UNION will do extra work to ensure all rows are unique:
UNION ALL may perform better, especially if you know your rows will be unique (or you need duplicates).
By default, a Postgres
VIEW is dynamic, and read-only. With the use of the
CREATE MATERIALIZED VIEW form, it’s possible to have a cached copy stored on disk, which requires an
UPDATE MATERIALIZED VIEW "viewname" in order to cause an update.
It’s also possible to create a writeable
VIEW, but I’m not going to discuss those now.
There is a feature of Django that makes in really simple to use a
VIEW as the read-only source of a Django
managed = False.
VIEW defined above, we can write a
Model that will allow us to query it:
from django.db import models class Foo(models.Model): bar = models.CharField() baz = models.CharField() qux = models.CharField() class Meta: managed = False
Psycopg2 also has the ability to automatically convert values as it fetches them, so you don’t even really need to set the fields as the correct type: but you will probably want to where possible, as an aid to code readability.
In my case, I was returning a two-dimensional
TIMESTAMPTZ, but didn’t want to have to include the full code for an
ArrayField. So, I just defined it as a
psycopg2 just gave me the type of object I actually wanted anyway.
There is one little catch, and the code above will not quite work.
Django requires a primary key, even though in this case it makes no sense. You could define any field as a primary key, include a relevant key field from the parent model, or even a dummy value that is the same on every row. Relying on the same
psycopg2 trick as above, you could use
<tablename>-<id> so as to ensure uniqueness, even though that is not normally a valid value for a Django
You probably need to be a little careful here, as if you are doing comparisons, Django will test
pk for testing equality, so you could hurt yourself if you aren’t careful.
You may also want to prevent write access at the Django level. Overriding
delete() on the
Model class would be a good start, as well as writing a custom
QuerySet that does the same. You could raise an exception that makes sense, like
NotImplemented, or just leave it as a database error.