Thread: Do I really have to convert text to varchar and varchar to varchar[] to do an INSERT?
Do I really have to convert text to varchar and varchar to varchar[] to do an INSERT?
From
Mary Anderson
Date:
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.
Re: Do I really have to convert text to varchar and varchar to varchar[] to do an INSERT?
From
Jon Jensen
Date:
> 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