I need to convert character varying(255)[] to character varying(255)-CodePudding

I have a table that has "projects" that has this field workspace which takes an array of strings. So I have converted this to store a single character

CREATE TABLE projects (
    id BIGSERIAL PRIMARY KEY,
    name character varying(255) NOT NULL,
    workspace character varying(255)[]
);

To store a single character

CREATE TABLE projects (
    id BIGSERIAL PRIMARY KEY,
    name character varying(255) NOT NULL,
    workspace character varying(255)
);

But in the database, I have a lot of data that I need to update to store a single string . So I need to write a query that can go through every data.

Example:-

Suppose this is stored in the table

id | 1, name | "daily", workspace  | {workspace1, workspace2}
id | 2, name | "daily2", workspace | {workspace2, workspace1}
id | 3, name | "daily3", workspace | {workspace3, workspace4}

So I need to write a query that can go through every data and check if the workspace has workspace1 first and update that field to only store workspace1. In case, it doesn't find workspace1 then it should look for workspace2 and update that field to only store workspace2. If it doesn't find both then it should ignore.

CodePudding user response：

you can use a CASE expression in the USING part of an ALTER TABLE statement:

alter table discounts
   alter workspace type varchar(255)
      using case 
             when 'workspace1' = any(workspace) then 'workspace1'
             when 'workspace2' = any(workspace) then 'workspace2'
             else  null
            end;

Online example

Note that the limit 255 has no magic performance benefit compared to e.g. 253 or 257.

If you only want to change the content, but keep the data type, use an UPDATE:

update discounts
  set workspace =  case 
                     when 'workspace1' = any(workspace) then '{workspace1}'
                     when 'workspace2' = any(workspace) then '{workspace2}'
                     else  workspace
                    end
where workspace && array['workspace1', 'workspace2']::varchar[];

The workspace avoid useless updates with the same value.

Online example