postgres

Prevent Postgres from automatically lower casing constraint names

This doesn’t work:

ALTER TABLE company DROP CONSTRAINT UQ_924dc2ee53aa15f1b16b4af12be;

Postgres says that this constraint doesn’t exist.

Solution

The problem is that Postgres will lowercase the constraint name. Use double quotes around the constraint name, like this:

ALTER TABLE company DROP CONSTRAINT "UQ_924dc2ee53aa15f1b16b4af12be";

View

db postgres

Return empty array from aggregate in Prostgres

When you want to avoid NULL for the result of JSONB_AGG and instead you want to get an empty array.

Solution

Use COALESCE and '[]'::JSONB.

SELECT
   COALESCE(
      JSONB_AGG(
        JSONB_BUILD_OBJECT(
          'id', role.id,
          'name', role.name
        )
      ) FILTER (WHERE role.id IS NOT NULL),
      '[]'::JSONB
    ) AS "adminRoles",
FROM ...

View