CODEWITHSUNDEEP
arraysdatabasepostgresqltypessql-insert
Noob Doob
Noob Doob

Reputation: 1907

Cannot INSERT: ERROR: array value must start with "{" or dimension information

INSERT INTO user_data.user_data (username,randomint)
VALUES ('mahman',1);

ERROR:  array value must start with "{" or dimension information
LINE 1: ... user_data.user_data (username,randomint) VALUES ('mahman...

INSERT INTO user_data.user_data (username,randomint)
VALUES {'mahman',1};

ERROR:  syntax error at or near "{"
LINE 1: ...O user_data.user_data (username,randomint) VALUES {'mahman',...

INSERT INTO user_data.user_data (username,randomint)
VALUES (2,{'mahman',1});

ERROR:  syntax error at or near "{"
LINE 1: ...ser_data.user_data (username,randomint) VALUES (2,{'mahman',...

The above varying INSERT statements fail in PostgreSQL. What is it that I am doing wrong?

My schema:

journeypost=# \dt user_data.*
           List of relations
  Schema   |   Name    | Type  | Owner 
-----------+-----------+-------+-------
 user_data | user_data | table | user

My table:

journeypost=# \d user_data.user_data
           Table "user_data.user_data"
  Column   |          Type           | Modifiers 
-----------+-------------------------+-----------
 username  | character varying(50)[] | not null
 randomint | integer                 | 
Indexes:
    "UsernameUnique" PRIMARY KEY, btree (username)

Upvotes: 48

Views: 109110

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656734

Your column username seems to be an array type, so the literal 'mahman' is not valid input for it.

It would have to be '{mahman}':

INSERT INTO user_data.user_data (username,randomint)
VALUES ('{mahman}',1);

(Or alter the data type to plain varchar or text instead.)

Update confirms it: character varying(50)[] is an array of varchar(50).

About array literal / array constructor:

Upvotes: 61

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

I called generate_subscripts() with ARRAY[a,b,c] as shown below:

                         -- ↓↓↓↓↓↓↓↓↓↓↓↓
SELECT generate_subscripts('ARRAY[a,b,c]'::VARCHAR[]);

Then, I got the same error below:

ERROR:  malformed array literal: "ARRAY[a,b,c]"
LINE 1: SELECT generate_subscripts('ARRAY[a,b,c]'::VARCHAR[]);
                                   ^
DETAIL:  Array value must start with "{" or dimension information.

So, I used {a,b,c} as shown below, then the error was solved:

                         -- ↓↓↓↓↓↓↓
SELECT generate_subscripts('{a,b,c}'::VARCHAR[], 1);

Upvotes: 0

Related Questions