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.

I am not all that familiar with PostgreSQL, but I don't think "contacts" is a data type...Is it a custom data type? If it is, what is the definition of the custom "contacts" data type? — gangreen, Mar 03 '12 at 03:36
Works fine for me. What happens if you specify the columns (which you should **always** do anyway, **always**, no exceptions ever) in your insert: `insert into unit_contacts (id, unit_id, old_contact, ...) values (...)`? — mu is too short, Mar 03 '12 at 03:50
@muistooshort you can build the schema? what is the deal with the contacts data type? — gangreen, Mar 03 '12 at 04:20
@gangreen: PostgreSQL has a rather rich type system that allows you to define all sorts of custom data types: http://www.postgresql.org/docs/current/interactive/datatype-enum.html and http://www.postgresql.org/docs/current/interactive/datatype.html — mu is too short, Mar 03 '12 at 04:24

Erwin Brandstetter · Accepted Answer · 2022-03-04T02:33:56.700

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

Erwin... you should hold a Pg bootcamp, and I will come attend it. Yes, you were correct. I had a table called 'contacts' tucked away in one of my schemas. What a mess. Many thanks. I can now go fix this. — punkish, Mar 03 '12 at 19:05

score 0 · Answer 2 · answered May 17 '23 at 05:21

I also ran into this error in a different situation where I had a composite type that only had 1 property. It worked if I added another property to the composite type. For example:

drop type if exists mything;
drop type if exists mything2;
create type mything as (val varchar(200));
create type mything2 as (val varchar(200), wat int);

-- Works
select val from unnest(array[('ok',null),('doke',null)]::mything2[]);

-- ERROR:  Missing left parenthesis.malformed record literal: "ok"
select value from unnest(array[('ok'),('doke')]::mything[]);

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

2 Answers2