insert string into text [] column

Question

I have the following issue.

I will receive input as a text from a webservice to insert into a certain psql table. assume

create table test ( id serial, myvalues text[])

the recieved input will be: insert into test(myvalues) values ('this,is,an,array');

I want to create a trigger before insert that will be able to convert this string to a text [] and insert it

first Idea that came in my mind was to create a trigger before insert

create function test_convert() returns trigger as $BODY%
 BEGIN
   new.myvalues = string_to_array(new.myvalues,',')
   RETURNS NEW
 END; $BODY$ language plpgsql

but this did not work

score 2 · Answer 1 · answered Aug 19 '15 at 22:26

2

You can use the string_to_array function to convert your string into an string array within your insert query:

INSERT INTO test ( myvalues )
VALUES ( string_to_array( 'this,is,an,array', ',' ) );

answered Aug 19 '15 at 22:26

Gareth Flowers

1,513
12
23

The question was different I meant that the insert command I have from a webservice is an insert as string can I do it from a trigger side? – Taher Galal Aug 21 '15 at 10:37
You can't change the type of a value inside an insert query via a before trigger or any other means. You have to change the initial insert query to pass through the correct type. – Gareth Flowers Aug 21 '15 at 12:15

score 0 · Answer 2 · answered Aug 06 '15 at 09:27

0

Suppose you receive text in the following format this is an array and you want to convert it to this,is,an,array then you can use string_to_array('this is an array', ' ') and it will be converted. However if you are receiving comma separated then you can just used it.

answered Aug 06 '15 at 09:27

zulqarnain

1,695
1
16
33

Try to insert it will throw an error because the insert is expecting an array – Taher Galal Aug 06 '15 at 17:43

score 0 · Answer 3 · answered Dec 10 '22 at 14:40

Creating the Table Schema Like this,

CREATE TABLE expert (
    id VARCHAR(32) NOT NULL,
    name VARCHAR(36),
    twitter_id VARCHAR(40),
    coin_supported text[],
    start_date TIMESTAMP,
    followers BIGINT,
    PRIMARY KEY (id)
    );

Inserting values like this will help you to insert array,

insert into expert(id, name, twitter_id, coin_supported, start_date, followers) values('9ed1cdf2-564c-423e-b8e2-137eg', 'dev1', 'dev1@twitter', '{"btc","eth"}', current_timestamp, 12);

insert string into text [] column

3 Answers3