Inserting array into postgresql

Question

I want to insert an array of id's to a file, but while creating a table I used int, and it has the reference of another table now how can I store array value to the specific field? I understand that the columns have to be the same type and I also saw that some tried to tackle this foreign key on array issue already: also i tried ELEMENT according to this doc but no success. please someone help me here https://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/

db.query('CREATE TABLE IF NOT EXISTS departments_users(_id SERIAL NOT NULL UNIQUE PRIMARY KEY,user_id int REFERENCES companyUser ON DELETE CASCADE,department_id int REFERENCES departments ON DELETE CASCADE)');

exports.addCompanyUser = function(data, callback) {
    db.query('INSERT INTO departments_users(user_id,department_id) VALUES($1,$2) RETURNING *', [companyuseraccess.rows[0]._id, data.department]).then(function(departments_users) {      
    })
}

stackoverflow.com/q/43776122/5315974

Vao Tsun
– Vao Tsun

2017-05-04 07:37:08 +00:00
Commented May 4, 2017 at 7:37 — Vao Tsun
– Vao Tsun, Commented May 4, 2017 at 7:37

Hannan · Accepted Answer · 2017-05-04 05:36:58Z

6

You dont have any field with ARRAY dataType so essentially you cannot insert array into the field. If you wish to put in array in a field change its data type to ARRAY first and then you can

INSERT INTO "some_table" ("id","array_field","some_text_field") 
VALUES (1,ARRAY['value1','value2']::TEXT[],'active') RETURNING *

Hope this helps. Let me know if i can help further

answered May 4, 2017 at 5:36

Hannan

5142 silver badges13 bronze badges

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

6 Comments

Schüler Over a year ago

but @hannan we already created 5 tables which has references from one another i need to store multiple id's only to departments so do you have any idea on Foreign key constraint for array-field?please

Hannan Over a year ago

I dont think the ELEMENT REFERENCES you are talking about exists in postgres yet. I tried to create a table using ELEMENT REFERENCES and it did'nt work

Schüler Over a year ago

and for your answer i got this error { "name": "error", "length": 144, "severity": "ERROR", "code": "42P08", "detail": "text versus integer", "position": "61", "file": "parse_param.c", "line": "221", "routine": "variable_coerce_param_hook" }

Hannan Over a year ago

I have written ::TEXT[] just for example purpose. You will have to put respective data type for that column. i.e. what type of data your array contains. For example if it contains array of UUID's then it should be ::UUID[]

Schüler Over a year ago

sorry my bad i changed it to integer even though it says You will need to rewrite or cast the expression :sad:

|

Collectives™ on Stack Overflow

Inserting array into postgresql

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related