Inserting data with SELECT into new column I have a table of data built from a file inventory. I then want to add a categorical column so that I may easily perform GROUP BY.

How things start:

SELECT COUNT(fullpath) FROM data;

12395


I use the column fullpath a lot so I create an index:

CREATE INDEX "index" ON data (fullpath);


I then add the column:

ALTER TABLE data ADD COLUMN 'Sub' VARCHAR(20);


Insert values into the Sub column based on the fullpath:

insert into data(Sub)
select case
    when fullpath like '%subA%' then 'subA'
    when fullpath like '%subB%' then 'subB'
    else 'neither' end
from data;


Check the number of rows again:

SELECT COUNT(fullpath) FROM data;

24790


I don't understand the syntax and the operations. Do I need to join or something to respect the index?
You already solved your main problem!

But I want to suggest you to create a new table for sub and maybe also for fullpath, because you store the same values again and again in this row.

Something like this: 

CREATE TABLE path (id integer, name varchar(100));


Then populate it with a SELECT and add the ID to the main table!