CODEWITHSUNDEEP
postgresqlmultiple-columnssql-insertbulkinsert
Arjun_TECH
Arjun_TECH

Reputation: 383

PgSql Insert multiple records with multiple regexp_split_to_table

I have a table with columns [FileId, FileName, FileUrl].

I get FileName and FileUrl as comma-separated string (v_FileName, v_FileUrl) in input params of my INSERT stored procedure, and FileId is auto increment.

Records are inserted successfully for FileName if I use regexp_split_to_table a single time as follows:

INSERT INTO MYTABLE
    SELECT 
        nextval('mytable_fileid_seq'),
        regexp_split_to_table,
        'testurl'
    FROM 
        regexp_split_to_table(v_FileName, E',')

This successfully inserts rows for all filenames in the v_FileName param and set 'testurl' for fileurl as in above test script.

My query is, is it possible to set FileUrl as well using regexp_split_to_table for v_FileUrl param also in such single go ?

I tried but I get an error

regexp_split_to_table is used more than once

(Maybe I am not using it properly)

Or suggest me any other way to insert records with such input params.

Thanks in advance.

Upvotes: 0

Views: 1414

Answers (3)

Arjun_TECH
Arjun_TECH

Reputation: 383

Well below solution worked for me per what exactly I was expecting. Giving solution with extending MYTABLE as my actual table contains more columns. Also, it would be more helpful for others with same requirements.

Columns: 

FileId, StoryId, FileName, FileHref, InsertDate

INSERT stored proc input params with sample values:

v_StoryId = 5678,
v_FileName = 'File_1.jpg,File_2.pdf,File_3.jpg,File_4.pdf'
v_FileHref = 'www.abc.com/File_1.jpg,www.abc.com/File_2.pdf,www.abc.com/File_3.jpg,www.abc.com/File_4.pdf'

The code block that works:

INSERT INTO MYTABLE
(SELECT NextVal('mytable_fileid_seq')   
        ,v_StoryId
        ,FileName.regexp_split_to_table         
        ,FileHref.regexp_split_to_table
        ,LOCALTIMESTAMP
        FROM    (   select row_number() over () as idx,
                    i as regexp_split_to_table
                    from UNNEST(STRING_TO_ARRAY(v_FileName,',')) i 
                )as FileName
                JOIN (  select row_number() over () as idx,
                        i as regexp_split_to_table
                        from UNNEST(string_to_array(v_FileHref,',')) i 
                        )as FileHref
                ON FileName.idx = FileHref.idx
                );

Result:

FileId  | StoryId   | FileName  |FileHref       |InsertDate
--------------------------------------------------------------------------------------------
23      | 1234      |File_1.jpg |www.abc.com/File_1.jpg | 2016-12-14 12:11:54.374933
--------------------------------------------------------------------------------------------
24      | 1234      |File_2.pdf |www.abc.com/File_2.pdf | 2016-12-14 12:11:54.374933
--------------------------------------------------------------------------------------------
25      | 1234      |File_3.jpg |www.abc.com/File_3.jpg | 2016-12-14 12:11:54.374933
--------------------------------------------------------------------------------------------
26      | 1234      |File_4.pdf |www.abc.com/File_4.pdf | 2016-12-14 12:11:54.374933

However, currently it feels fishy using regexp_split_to_table() together with unnest() but as in my case, the comma separated values may not increase then 30 so the snippet should be ok. Currently I am new to pgsql, but if anyone have better/neat solution then certainly appreciated.

Thanks @jcaron for showing a direction towards solution.

Upvotes: 0

jcaron
jcaron

Reputation: 17720

One option is to use unnest with regexp_split_to_array:

SELECT * FROM unnest(
    regexp_split_to_array('a,b,c',','),
    regexp_split_to_array('1,2,3',',')
);

Result:

 unnest | unnest 
--------+--------
 a      | 1
 b      | 2
 c      | 3

So in your case:

INSERT INTO mytable (FileName, FileUrl)
SELECT *
FROM unnest(
    regexp_split_to_array(v_FileName,','),
    regexp_split_to_array(v_FileUrl,',')
);

(I'm assuming your FileId has a proper default value using the sequence).

Upvotes: 1

Riya Bansal
Riya Bansal

Reputation: 1311

If you need to store comma separating string in to multiple rows. You can check below code;

Comma sepearated string - "1,2,3,4,5,6"

create table schema_name.table_name(id int);

insert into schema_name.table_name
(
SELECT CAST(regexp_split_to_table('1,2,3,4,5,6', ',') AS INTEGER) AS id
) ;

select  * from schema_name.table_name

Upvotes: 0

Related Questions