CODEWITHSUNDEEP
sqlpostgresql
user8545255
user8545255

Reputation: 839

insert values to array column in PostgreSQL

I have table "details" with the below structure

id     status1      status2    names
 1    approved     rejected     NULL
 2    approved     rejected     NULL
 3    approved     rejected     NULL
 4    rejected     rejected     NULL

I want to insert values to array column "names" with default values {john,smith}

example :I need

  id     status1      status2    names
   1    approved     rejected   {john,smith}
   2    approved     rejected   {john,smith}
   3    approved     rejected   {john,smith}
   4    rejected     rejected   {john,smith}

It fails when i wrote 

INSERT INTO details (names) VALUES(ARRAY['john', 'smith']);

Upvotes: 10

Views: 17609

Answers (3)

Marat Safin
Marat Safin

Reputation: 1909

If you want to keep existing values with new ones:

UPDATE details SET names = names || '{john, smith}';

Upvotes: 14

Barbaros Özhan
Barbaros Özhan

Reputation: 65158

There's no problem with your INSERT statement provided your version is at least 9.3, and names columns is of type text[] or varchar[], i.e. variable-length character string :

create table details( id int, status1 varchar(25), status2 varchar(25), names varchar[]);
insert into details(names) values( ARRAY['john', 'smith']);
select * from details;

id     status1  status2   names
(null)  (null)  (null)  john,smith

SQL Fiddle Demo

Due to your last comments, you need an update statement which sets only names column as @a_horse_with_no_name proposed, but not insert where (null) values are produced for the rest of the columns.

Upvotes: 1

user330315
user330315

Reputation:

INSERT creates completely new rows - but you want to change existing rows, so you need to use UPDATE:

update details 
  set names  = ARRAY['john', 'smith'];

Upvotes: 3

Related Questions