CODEWITHSUNDEEP
sqlpostgresqlsql-insert
Raushan Setthi
Raushan Setthi

Reputation: 79

Insert row inside sql table based on its column value

i am having table file which contains multiple columns , one of them is val which was combination of 3 attributes version,name and availability all three are combined with ; and stored inside val . Now i have to insert 3 rows with coping all data and value i.e 

id    file_id   file_version_id   val                  type

162   190234       259           1.2;DESC;AVAIL        desc
============================================================

id   file_id   file_version_id      val                type
162   190234       259             1.2                version

id   file_id   file_version_id      val                type
162   190234       259             DESC                name

 id   file_id   file_version_id     val                 type
162   190234       259              AVAIL           availability

As you can see from the example , i have to insert 3 records with its data from parent row. The order of data is version,name and availability in case for some records name is not available i.e 3.4;NOT_AVAIL then have to insert only two records with type Version and availability

Upvotes: 0

Views: 66

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Presumably, it is the positions of the values in the string that determine the type, not their values. I would suggest unnest() with the position and a case expression:

insert into file (id, file_id, file_version_id, val, type)
    select f.id, f.file_id, f.file_version_id, u.val,
           (case u.n 
                when 1 then 'version'
                when 2 then 'name'
                when 3 then 'availability'
            end)
    from file f cross join lateral
         unnest(string_to_array(f.val, ';')) with ordinality u(val, n);

If you want to be sure that all values are present, you might want where f.val like '%;%;%'.

Here is a db<>fiddle.

Upvotes: 0

forpas
forpas

Reputation: 164089

You can use UNNEST() to split the column val and a CASE expression to update the column type:

INSERT INTO tablename (id, file_id, file_version_id, val, type)
WITH cte AS (
  SELECT *, UNNEST(STRING_TO_ARRAY(val, ';')) str
  FROM tablename                              
)
SELECT id, file_id, file_version_id, str,
  CASE
    WHEN str ~ '^[0-9\.]+$' THEN 'version'
    WHEN str IN ('AVAIL', 'NOT AVAIL') THEN 'availability'
    ELSE 'name'                   
  END
FROM cte;

See a simplified demo.

Upvotes: 1

Related Questions