Can't insert a record because of "malformed record literal"

Question

The following problem has me stumped

SELECT string_agg(e.enumlabel, '|') as enum_value
FROM pg_type t 
   JOIN pg_enum e on t.oid = e.enumtypid  
   JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace 
WHERE typname = 'contacts'

above|below|lateral|lateral-bottom|lateral-top|within

CREATE TABLE unit_contacts
(
  id integer NOT NULL DEFAULT nextval('unit_contacts_id_seq1'::regclass),
  unit_id integer NOT NULL,
  old_contact contacts NOT NULL,
  contact contacts NOT NULL,
  old_with_unit integer NOT NULL,
  with_unit integer NOT NULL,
  CONSTRAINT unit_contacts__pkey PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);

mm=> INSERT INTO unit_contacts VALUES (15, 1, 'below', 'below', 8112, 2);
ERROR:  malformed record literal: "below"
LINE 1: ...SERT INTO unit_contacts VALUES (15, 1, 'below', '...

I can't figure out why I am unable to insert the row at all.

Answer 1

Obviously a naming conflict.

The error message for a missing enum value would be:

ERROR:  invalid input value for enum rainbow: "below"
LINE 1: INSERT INTO t VALUES (1, 'below');

Your error message reveals that a composite type of the same name exists, most likely from a table of the same name. Avoid using identical names!

To reproduce, consider this demo:

CREATE TYPE contacts  AS ENUM ('above', 'below', 'lateral');
SELECT 'above'::contacts;  -- all good, before the next step

CREATE TEMP TABLE contacts (id int, x text); -- !the crucial part

SELECT string_agg(e.enumlabel, '|') as enum_value
FROM   pg_type t 
JOIN   pg_enum e on t.oid = e.enumtypid  
WHERE  t.typname = 'contacts'; -- all good

CREATE TEMP TABLE t (id int, r contacts);
INSERT INTO t VALUES (1, 'above');  -- ERROR
SELECT 'above'::contacts;  -- same ERROR

This can only happen if enum type and table (the row type) exist in two different schemas. PostgreSQL would not allow both in the same schema. In your case, the table's schema was listed before the enum's schema in the search_path when you created the table. Or maybe the enum type did not even exist at the time. See:

• How does the search_path influence identifier resolution and the "current schema"

In my example the temporary table comes first because the schema pg_temp comes first in the search path by default. When I create the table, "contacts" is resolved to the row type (pg_temp.contacts), not the enum type (public.contacts).

If you must have a table and an enum of the same name, be sure to schema-qualify the type-names in use. In my example:

pg_temp.contacts -- the composite type
public.contacts  -- the enum type