Why am I getting an error in this simple INSERT query?

Question

Working with Postgres 14 on Win 10. Running this simple query:

mydata=# SELECT * FROM contact_test;
 cntct_id | fname | lname | email | street | unit | town | state | post_code
----------+-------+-------+-------+--------+------+------+-------+-----------
(0 rows)


mydata=# INSERT INTO contact_test (fname, lname, email) VALUES ('Robert', 'Johnson', 'rjohnsonn@gmail.com');
ERROR:  malformed array literal: "Robert"
LINE 1: ...T INTO contact_test (fname, lname, email) VALUES ('Robert', ...
                                                             ^
DETAIL:  Array value must start with "{" or dimension information.

I looked up the syntax here:

• https://www.postgresql.org/docs/current/dml-insert.html

The sample essentially looks the same as my statement:

INSERT INTO products (product_no, name, price) VALUES
(1, 'Cheese', 9.99);

What am I missing?

Answer 1

Given the table definition you added in a comment, this statement would work:

INSERT INTO contact_test (fname, lname, email) VALUES ('{R}', '{J}', 'r');

fname and lname are array types, so you need to pass array literals (or array constructors). Examples:

• PostgreSQL INSERT into an array of enums
• Error "invalid input syntax for integer" for a non-integer?
• IN vs ANY operator in PostgreSQL

But your table definition is nonsense, of course. You don't want the internal enumeration type "char", which holds a single ASCII letter. You want varchar or text. And I am not convinced you want array types, either. ("char"[] is an array of "char".)
Fix your table definition accordingly! Like:

CREATE TABLE contact_test (
  cntct_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, fname text NOT NULL
, lname text NOT NULL
, email text NOT NULL
);

Then your original INSERT statement works.

See:

• Any downsides of using data type "text" for storing strings?