current community

your communities

more stack exchange communities

Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.
up vote 6 down vote favorite
2

The following is a snippet of a table called "containers".

       Column       |            Type             |            Modifiers            
--------------------+-----------------------------+---------------------------------
 id                 | uuid                        | not null
 name               | character varying(255)      | 
 products           | character varying           | default '{}'::character varying

How can I alter the products column to "character varying[]" and the corresponding modifiers to default '{}'::character varying[] ? Essentially, I want to convert a string to a string array. Note the products column has no limit on the number of characters.

alter table "containers" alter "products" type character varying[];

throws the following error - ERROR: column "products" cannot be cast to type character varying[]

share|improve this question

1 Answer 1

up vote 6 down vote accepted

You should define a function to convert type, eg.

create or replace function text_to_text_array(value text)
returns text[] language sql as $$
    select array[$1]
$$;

alter table "containers" alter "products" type text[] using text_to_text_array(products);

Edit:

Function is not necessary:

alter table "containers" alter "products" drop default;
alter table "containers" alter "products" type text[] using array[products];
alter table "containers" alter "products" set default '{}';
share|improve this answer
    
Thanks. Getting this cast error though - ERROR: default for column "products" cannot be cast to type text[] –  papdel Feb 8 '13 at 22:32
    
create or replace function string_to_string_array(value character varying) returns character varying[] language sql as $$ select array[$1] $$; and then alter table "containers" alter "products" type character varying[] using string_to_string_array(products); threw the same error as well. –  papdel Feb 8 '13 at 22:37
    
How do I change the existing default value as well? –  papdel Feb 8 '13 at 22:40
    
Sorry, I have not noticed default... Answer edited. –  klin Feb 8 '13 at 22:47
2  
I'm lazy. The two are synonyms. –  klin Feb 8 '13 at 22:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.