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.

Candless

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? – Droppings

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 (...)? – Parodic

@muistooshort you can build the schema? what is the deal with the contacts data type? – Droppings

@gangreen: PostgreSQL has a rather rich type system that allows you to define all sorts of custom data types: postgresql.org/docs/current/interactive/datatype-enum.html and postgresql.org/docs/current/interactive/datatype.html – Parodic

What version of postgresql are you using? – Chinkapin

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

Oven

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. – Candless

Answer 2

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[]);

Ento

Answer 3

I am adding an answer because I faced a similar issue, as mentioned by @JohnnyFun. I had a composite type with a single property and got the "malformed record literal" error when trying to run the following select statement:

select array[('my value 1'), ('my value 2')]::mytype[];

By reading the official documents, I found this article explaining different ways to write a composite type. We have the literal constant way, in which we use single quotes:

select array['("my value 1")','("my value 2")']::mytype[];

And we have the row expression way, in which we use the "row" keyword before the parenthesis:

select array[row('my value 1'), row('my value 2')]::mytype[];

However the "row" keyword it's optional when the composite type has more than one field, like:

select array[('my value 1', 10), ('my value 2', 50)]::mytype2[];

In summary, the malformed error mentioned by @JohnnyFun happened because when you use the row expression syntax to write a composite type, you need to explictly add the "row" keyword (row('my value')) when the composite type has one field. Otherwise, it's optional when it has more than one field.

I hope it may be helpful to someone else.

Ingemar