CODEWITHSUNDEEP
sqlarrayspostgresql
user3814846
user3814846

Reputation:

PostgreSQL:How to insert values in an array into a table

for example,

myelems is my table

create table myelems (id int,element text)

and if I have an array 

ARRAY['A','B','C']

then how to insert the values in the above array into my table myelems?
i.e,

 id|element 
---+-------
 1 |A
 2 |B
 3 |C

Upvotes: 3

Views: 4519

Answers (2)

NullEverything
NullEverything

Reputation: 460

Assuming that ID by default is generated from a sequence, you can use:

INSERT INTO myelms(myelms) 
SELECT UNNEST(ARRAY['A','B','C'])

However if you don't have a sequence and need to specify an id, you can use two unnest to insert your data.

INSERT INTO myelms(id,myelms)
SELECT UNNEST(ARRAY[1,2,3]),UNNEST(ARRAY['A','B','C'])

using the second option requires you to have the same number of elements in both unnest or else you will get the Cartesian product of both which will result in data that you do not want. And of course you need to make sure the ID is not in there already.

IDs should generally be generated from sequence since having to create your own ID can cause problems and duplicates. Since there are no other columns beside elements, I will assume that ID is your primary key, and all keys need to be unique.

Upvotes: 2

Sathish
Sathish

Reputation: 4487

Try like this

insert into myelems select row_number() OVER () AS rn,* from 
(select unnest(Array['A','B','C'])) as t

Upvotes: 1

Related Questions