Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

insert values to array column in PostgreSQL

Tags:

sql

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']);
like image 237
user8545255 Avatar asked Jun 20 '18 00:06

user8545255


People also ask

How do you add values to an array?

First get the element to be inserted, say x. Then get the position at which this element is to be inserted, say pos. Then shift the array elements from this position to one position forward(towards right), and do this for all the other elements next to pos.

Can you insert array in PostgreSQL?

PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, composite type, range type, or domain can be created.

How do I add multiple values in PostgreSQL?

PostgreSQL INSERT Multiple Rows First, specify the name of the table that you want to insert data after the INSERT INTO keywords. Second, list the required columns or all columns of the table in parentheses that follow the table name. Third, supply a comma-separated list of rows after the VALUES keyword.

What is [] in PostgreSQL?

Array Type. PostgreSQL gives the opportunity to define a column of a table as a variable length single or multidimensional array. Arrays of any built-in or user-defined base type, enum type, or composite type can be created. We will focus on one data type in particular, the Array of text, text[].


1 Answers

If you want to keep existing values with new ones:

UPDATE details SET names = names || '{john, smith}';
like image 150
Marat Safin Avatar answered Sep 29 '22 14:09

Marat Safin