Why can I not set this unique constraint in PostgreSQL?

Question

I keep getting:

SQL error: ERROR: could not create unique index "service_import_checksum_key" DETAIL: Key (checksum)=() is duplicated.

In statement:

ALTER TABLE "public"."service_import" ADD CONSTRAINT "service_import_checksum_key" UNIQUE ("checksum")

But this constraint ISN'T a duplicate. There is no other constraint like this anywhere in the entire database and I have no idea why on earth it keeps insisting it's a duplicate. I'm assuming this is some weird nuance of postgres that I'm missing here.

What am I doing wrong?

Table dump:

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: service_import; Type: TABLE; Schema: public; Owner: cvs_tar; Tablespace: 
--

CREATE TABLE service_import (
    id integer NOT NULL,
    name character varying(32) NOT NULL,
    importfile character varying(64) NOT NULL,
    reportfile character varying(64) NOT NULL,
    percent smallint NOT NULL,
    message text NOT NULL,
    stamp timestamp without time zone DEFAULT now() NOT NULL,
    complete smallint DEFAULT 0 NOT NULL,
    checksum character varying(40) NOT NULL
);


ALTER TABLE public.service_import OWNER TO cvs_tar;

--
-- Name: service_imports_id_seq; Type: SEQUENCE; Schema: public; Owner: cvs_tar
--

CREATE SEQUENCE service_imports_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.service_imports_id_seq OWNER TO cvs_tar;

--
-- Name: service_imports_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: cvs_tar
--

ALTER SEQUENCE service_imports_id_seq OWNED BY service_import.id;


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: cvs_tar
--

ALTER TABLE service_import ALTER COLUMN id SET DEFAULT nextval('service_imports_id_seq'::regclass);


--
-- Name: service_import_name_key; Type: CONSTRAINT; Schema: public; Owner: cvs_tar; Tablespace: 
--

ALTER TABLE ONLY service_import
    ADD CONSTRAINT service_import_name_key UNIQUE (name);


--
-- Name: service_import_pkey; Type: CONSTRAINT; Schema: public; Owner: cvs_tar; Tablespace: 
--

ALTER TABLE ONLY service_import
    ADD CONSTRAINT service_import_pkey PRIMARY KEY (id);


--
-- Name: service_import_complete_idx; Type: INDEX; Schema: public; Owner: cvs_tar; Tablespace: 
--

CREATE INDEX service_import_complete_idx ON service_import USING btree (complete);


--
-- Name: service_import_stamp_idx; Type: INDEX; Schema: public; Owner: cvs_tar; Tablespace: 
--

CREATE INDEX service_import_stamp_idx ON service_import USING btree (stamp);


--
-- PostgreSQL database dump complete
--

Compossible

Can you show us the complete CREATE TABLE, or provide another description of the table? Also, does CREATE UNIQUE INDEX work instead? – Introduction

Answer 1

Read the error message again:

SQL error: ERROR: could not create unique index "service_import_checksum_key" DETAIL: Key (checksum)=() is duplicated.

Looks like it is telling you that there are duplicate values in the checksum column and you're trying to enforce uniqueness on that column with your constraint. The constraint isn't duplicated, the data has duplicates.

Furthermore, the "()" part indicates that you have multiple empty strings in the checksum column. Unique constraints allow multiple NULL values (since NULL = NULL is NULL which is not true) but empty strings are not NULL.

An example to clarify what's going on:

=> CREATE TABLE x (s VARCHAR NULL);
CREATE TABLE
=> INSERT INTO x (s) VALUES (''), ('a'), ('');
INSERT 0 3
=> ALTER TABLE x ADD CONSTRAINT ux UNIQUE(s);
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "ux" for table "x"
ERROR:  could not create unique index "ux"
DETAIL:  Key (s)=() is duplicated.
=> delete from x where s='';
DELETE 2
=> INSERT INTO x (s) VALUES ('a');
INSERT 0 1
=> ALTER TABLE x ADD CONSTRAINT ux UNIQUE(s);
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "ux" for table "x"
ERROR:  could not create unique index "ux"
DETAIL:  Key (s)=(a) is duplicated.

In particular, note what the ERROR and DETAIL are saying and compare that to the INSERTs.

Respirator

This is a prime example of the need for an additional check constraint to stop developers from cheating. The field is NOT NULL but is an unchecked string, so they're putting the empty string to circumvent the NOT NULL constraint. My kingdom for an addition to the SQL standard a string type that disallows "empty" strings (of any length). – Bomar

@Matthew Wood: I'd use an explicit CHECK constraint to ensure that the checksum has exactly the desired length (no more, no less) and only contains valid characters for the checksum; the checksum is probably a fixed size hex value and the developers can zero-pad it if necessary. We'll leave the whole "empty string is NULL because !empty_string is true and my ORM is too dumb to tell the difference" foolishness for another day :) I'm no DBA but I do know that '' and NULL are different things even outside the database. – Respirator

Regarding: "Unique constraints allow multiple NULL values (since NULL = NULL is false) but empty strings are not NULL." DING! Nailed it. All fixed. The stupid thing was autofilling in empty strings when I created the column and then from there adding the index was impossible. – Compossible

@Joshua Pech: ORM trying to make things "easy"? Ha! You might want to add a CHECK constraint (postgresql.org/docs/current/static/ddl-constraints.html#AEN2385) to checksum to keep this from happening in the future, a simple LENGTH(checksum) > 0 should do the trick. I tend to constraint my data as much as possible: broken code can be fixed, broken data is forever. – Respirator

Really that string should never be anything other than exactly 40 characters long as it represents a sha1 sum so I probably will do that. Also this wasn't an ORM setting that field up, it was phpPgAdmin. – Compossible