Unable to insert record due to "invalid record literal"

Question

The next problem has me at a standstill

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 cannot understand why I cannot insert a line at all.

Answer 1

Obviously, you are facing a naming conflict.

Error message for missing enum value:

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

Your error message indicates that there is a composite type with the same name, which is most likely associated with a table with the same name . You must avoid using identical names !

For the general public to reproduce, view the following 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 JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace WHERE typname = 'contacts'; -- all good -- DROP TABLE t; 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 the enumeration type and the table (composite type) exist in two different schemes . PostgreSQL would not allow in the same scheme. In your case, the schema with the table schema (composite type) clearly appeared before the enumeration type schema in search_path when creating the table. Or perhaps the type of enumeration did not even exist at that time.

In my example, the temporary table comes first because the pg_temp schema pg_temp to first place in the search path. When I create the contacts table, it is considered a composite type ( pg_temp.contacts ), not an enumeration type ( public.contacts ).

If you must have a table and enumeration with the same name, make sure that schema-qualify type names . In my example:

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