Обсуждение: Do I really have to convert text to varchar and varchar to varchar[] to do an INSERT?

Hi,
    Here is my code:

    INSERT INTO location_types (lo_location_type_id, lo_location_type)
    VALUES (3, 'region');

Somehow, variants of this statement generate strange error messages.

This one gets

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

Another try with a UNION SELECT to persuade it to insert multiple rows
gave me

ERROR: Column lt_location_type is of CHARACTER VARYING[] but expression
is of type text.

So I changed it to
INSERT INTO location_types (lo_location_type_id, lo_location_type)
    VALUES (3, VARCHAR 'region');

Here is the error message

ERROR: column "lt_location_type is of type character varying[] but
expression is of type character varying.

HINT: You will need to rewrite or cast the expression.

Something is going on with pgsql which I truly don't understand.  After
twenty years of working with SQL, I am pretty sure i know how to write a
simple INSERT statement!  Explanations would be greatly appreciated.

> Here is the error message
>
> ERROR: column "lt_location_type is of type character varying[] but
> expression is of type character varying.
>
> HINT: You will need to rewrite or cast the expression.
>
> Something is going on with pgsql which I truly don't understand.
> After twenty years of working with SQL, I am pretty sure i know how to
> write a simple INSERT statement!  Explanations would be greatly
> appreciated.
Looks like your lt_location_type column is defined as a varchar array
(i.e. VARCHAR[]), so it's expecting you to populate it with a varchar
array value (e.g. '{''foo'', ''bar''}') , as opposed to simple
varchar/text values (e.g. 'foo'). A simple alter table should resolve it
for you:

ALTER TABLE location_types ALTER COLUMN lt_location_type TYPE VARCHAR;

Jon