PostgreSQL 9.2 – Handling Blank (But Not Null) Columns

postgresql

Doing by:

select * from ja_customers where id = 8154501

I receive:

You can see that all the required fields are "blank" with some invisible character, so they're not null, so they're valid (technically)

Question:

How can I see which characters are in there?

Best Answer

Create a function like the one below and check if a column has a value of empty string (this function return null for an empty string) or an invisible character.

    CREATE FUNCTION get_as_byte_array(anyelement) RETURNS INTEGER[] AS $$
      SELECT
        CASE 
          WHEN length($1::VARCHAR) = 0 THEN NULL
          ELSE
            (SELECT array_agg(get_byte($1::bytea, a)) FROM generate_series(0, length($1::bytea) - 1) a)
        END;
    $$ LANGUAGE SQL;

    SELECT get_as_byte_array(name_first), * FROM ja_customers WHERE id = 8154501;

Proper solution

The core of the problem is the data model. In a normalized schema, you wouldn't store name and email redundantly. Could look like this:

CREATE TABLE name (
  name_id          SERIAL PRIMARY KEY,
  name             TEXT NOT NULL,
  email            TEXT NOT NULL,
  verified         BOOLEAN NOT NULL DEFAULT FALSE,
  UNIQUE (name, email)
);

CREATE TABLE address (
  address_id       SERIAL PRIMARY KEY,
  name_id          INT REFERENCES name(name_id)
  ...
);

If not-yet verified names should be allowed to break the UNIQUE constraint, you can replace it with a partial UNIQUE INDEX (much like you had in mind):

CREATE UNIQUE INDEX name_verified_idx ON name(name, email) WHERE verified;

Work with what you have

While stuck with your unfortunate design, the solution you already found yourself fits your requirements perfectly. A FOREIGN KEY with the default MATCH SIMPLE behavior matches the

partial index syntax with a clause like WHERE verified_name_id IS NOT NULL.

Quoting the manual:

MATCH SIMPLE allows any of the foreign key columns to be null; if any of them are null, the row is not required to have a match in the referenced table.

A (somewhat less reliable and more expensive) alternative would be a trigger on INSERT / UPDATE in address and trigger(s) on INSERT / UPDATE / DELETE in verified_name.

Postgresql – What are the consequences of not specifying NOT NULL in PostgreSQL for fields which can’t be null

What happens when a new programmer arrives and has to write an app against that db? They don't know that field x has to be NOT NULL.

Another programme might assume that all field x's are NOT NULL for performing counts say, but some now are NULL because of the new programme, leading to inconsistent and difficult to trace errors.

IMHO it is always best to enforce data integrity rules as near to the data as possible, i.e. in the database. That way, new apps and/or programmers can't mess up your data.

Programmers, applications, languages and frameworks come and go. Data and databases tend to persist. The database is your last line of defence against inconsistent, potentially erroneous data.

Make maximum use of your database's integrity constraint enforcement mechanisms, even at the expense of performance. A slow system that produces correct results is infinitely superior to a fast one that gets things wrong!

Best Answer

Related Solutions

PostgreSQL – Two-Column Foreign Key Constraint Only When Third Column is NOT NULL

Proper solution

Work with what you have

Postgresql – What are the consequences of not specifying NOT NULL in PostgreSQL for fields which can’t be null

Related Question