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

user1322772 · Accepted Answer · 2015-08-19 22:26:04Z

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, 2015 at 22:26

user1322772

Sign up to request clarification or add additional context in comments.

2 Comments

Taher Galal Over a year ago

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?

user1322772 Over a year ago

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.

zulqarnain · Accepted Answer · 2015-08-06 09:27:25Z

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 6, 2015 at 9:27

zulqarnain

1,7452 gold badges16 silver badges35 bronze badges

1 Comment

Taher Galal Over a year ago

Try to insert it will throw an error because the insert is expecting an array

Paran01D · Accepted Answer · 2022-12-10 14:40:26Z

0

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);

answered Dec 10, 2022 at 14:40

Paran01D

415 bronze badges

Collectives™ on Stack Overflow

insert string into text [] column

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related