How do I create a column in postgresql to count different values in the same cells of a column that-CodePudding

How do I count different values in a cell of a column and put that count in a new column?

for example :

 car/bike/truck/pickup/trailer/jeep

I want to be able to create a column like 'count of vehicle' with a corresponding value of 6. This is Postgres by the way

CodePudding user response：

You can count different values by SELECT COUNT(DISTINCT vehicle) FROM table; and set some value to column by UPDATE table SET different = val.

CodePudding user response：

You can capture the names between slashes as a set using a regular expression. Then, you can count the size of the set.

For example:

select count(*) from (
  select regexp_matches('car/bike/truck/pickup/trailer/jeep',
                        '([^/] )', 'g')
) x

Result:

count
-----
6

See running examplt at db<>fiddle.

CodePudding user response：

You can turn that value into an array, then count the number of array elements:

select cardinality(string_to_array(the_column, '/')) as vehicle_count
from the_table