Try to remove the whitespaces in the table:

UPDATE my_table
       SET zip_ = trim(_zip);

And then find out who enters ZIPs with whitespaces and tell them to stop, or add a trigger to the table that trims the data upon insertion or updating.

Simple version for a start:

    CREATE FUNCTION my_table_normalize_zip()
                    RETURNS TRIGGER
    AS
    $$
    BEGIN
      NEW.zip_ = trim(NEW.zip_);

      RETURN NEW;
    END;
    $$
    LANGUAGE plpgsql;

    CREATE TRIGGER normalize_zip
                   BEFORE INSERT
                           OR UPDATE
                   ON my_table
                   FOR EACH ROW
                   EXECUTE PROCEDURE my_table_normalize_zip();

Possibly add more normalization logic to it as needed.

You might also consider a CHECK CONSTRAINT or rather a CONSTRAINT TRIGGER (because this can get complicated and beyond the scope of a simple CHECK CONSTRAINT) to have some plausibility check on the ZIPs. But a warning: We tried that once in the company I worked for. For some countries it's pretty simple. For others it's just a nightmare and again for others you can search your b**t to even find any rules. In the end we gave up. I was to expensive compared to the benefit. But a "light version", for those countries it's easy for, might be reasonable.

Try to remove the whitespaces in the table:

UPDATE my_table
       SET zip_ = trim(_zip);

And then find out who enters ZIPs with whitespaces and tell them to stop, or add a trigger to the table that trims the data upon insertion or updating.

Simple version for a start:

    CREATE FUNCTION my_table_normalize_zip()
                    RETURNS TRIGGER
    AS
    $$
    BEGIN
      NEW.zip_ = trim(NEW.zip_);

      RETURN NEW;
    END;
    $$
    LANGUAGE plpgsql;

    CREATE TRIGGER normalize_zip
                   BEFORE INSERT
                           OR UPDATE
                   ON my_table
                   FOR EACH ROW
                   EXECUTE PROCEDURE my_table_normalize_zip();

Possibly add more normalization logic to it as needed.

You might also consider a CHECK CONSTRAINT or rather a CONSTRAINT TRIGGER (because this can get complicated and beyond the scope of a simple CHECK CONSTRAINT) to have some plausibility check on the ZIPs. But a warning: We tried that once in the company I worked for. For some countries it's pretty simple. For others it's just a nightmare and again for others you can search your b**t to even find any rules. In the end we gave up. It was to expensive compared to the benefit (most postal services are quite good in finding out where misaddressed mail really belongs to anyway). But a "light version", for those countries it's easy for, might be reasonable.

Try to remove the whitespaces in the table:

UPDATE my_table
       SET zip_ = trim(_zip);

And then find out who enters ZIPs with whitespaces and tell them to stop, or add a trigger to the table that trims the data upon insertion or updating.

Try to remove the whitespaces in the table:

UPDATE my_table
       SET zip_ = trim(_zip);

And then find out who enters ZIPs with whitespaces and tell them to stop, or add a trigger to the table that trims the data upon insertion or updating.

Simple version for a start:

    CREATE FUNCTION my_table_normalize_zip()
                    RETURNS TRIGGER
    AS
    $$
    BEGIN
      NEW.zip_ = trim(NEW.zip_);

      RETURN NEW;
    END;
    $$
    LANGUAGE plpgsql;

    CREATE TRIGGER normalize_zip
                   BEFORE INSERT
                           OR UPDATE
                   ON my_table
                   FOR EACH ROW
                   EXECUTE PROCEDURE my_table_normalize_zip();

Possibly add more normalization logic to it as needed.

You might also consider a CHECK CONSTRAINT or rather a CONSTRAINT TRIGGER (because this can get complicated and beyond the scope of a simple CHECK CONSTRAINT) to have some plausibility check on the ZIPs. But a warning: We tried that once in the company I worked for. For some countries it's pretty simple. For others it's just a nightmare and again for others you can search your b**t to even find any rules. In the end we gave up. I was to expensive compared to the benefit. But a "light version", for those countries it's easy for, might be reasonable.