5

I am creating a materialized view using the following query:

CREATE MATERIALIZED VIEW article_view AS
SELECT
  id,
  alternative_headline,
  article_author_id,
  created_at,
  description,
  headline,
  preview_paragraph_image_id,
  published_at,
  updated_at
FROM article
WHERE
  published_at IS NOT NULL
WITH NO DATA;

CREATE UNIQUE INDEX ON article_view (id);

I want it to be represented in the that the published_at column is not nullable.

The reason for wanting to represent the published_at column as not nullable, is because I am using a scaffolding tool that generates database queries and types based on the database schema. In this particular case, the published_at is being falsely represented as a nullable column triggering strict type checking errors.

The said scaffolding tool is using the following query to describe the database:

SELECT
  pc1.relname AS "tableName",
  pa1.attname AS "columnName",
  pg_catalog.format_type (pa1.atttypid, NULL) "dataType",
  pc1.relkind = 'm' "isMaterializedView",
  NOT(pa1.attnotnull) "isNullable"
FROM
  pg_class pc1
JOIN pg_namespace pn1 ON pn1.oid = pc1.relnamespace
JOIN
  pg_attribute pa1 ON pa1.attrelid = pc1.oid
  AND pa1.attnum > 0
  AND NOT pa1.attisdropped
WHERE
  pn1.nspname = 'public' AND
  pc1.relkind IN ('r', 'm')
Gajus
  • 69,002
  • 70
  • 275
  • 438

1 Answers1

7

The syntax doesn't support it in CREATE or ALTER forms as it stands today. I am guessing, but since the CREATE MATERIALIZED VIEW statement accepts any query, it cannot reliably copy the not null constraints from the referenced table.

You can however UPDATE the pg_catalog.pg_attribute itself to do what you want. 

UPDATE pg_catalog.pg_attribute 
SET attnotnull = true
WHERE attrelid = the_oid_of_the_published_at_column;
Neil Anderson
  • 1,265
  • 12
  • 19
  • Thank you. I do like your suggestion regarding `ALTER TABLE`, though. For others reference: https://www.postgresql.org/message-id/20171112031621.31521.10140%40wrigleys.postgresql.org – Gajus Nov 12 '17 at 12:22
  • You can add the lookup to the OID as indicated in this post: https://stackoverflow.com/questions/10953123/determining-the-oid-of-a-table-in-postgres-9-1#:~:text=To%20get%20a%20table%20OID,an%20exception%20if%20not%20found. – Lars Skaug Aug 02 '20 at 15:23