How to insert record with uuid array into pg table using libraryjs pg-prom library

Question

I need to have a table in my db that contains one column, which is an array of uuid objects (type uuid [])

but when I try to insert into it using the nodejs library called pg-prom, this will not work

I get the following error message telling me that I need to rewrite the cast or expression

{"name":"error","length":206,"severity":"ERROR","code":"42804","hint":"You will need to rewrite or cast the expression.","position":"230","file":"src\\backend\\parse r\\parse_target.c","line":"510","routine":"transformAssignedExpr"} 

This is strange since I have absolutely no problems when I try to enter one uuid in another column in the same exact table (which means that I have no problem representing uuid, btw I create them as text variables from another lib but they are simple text variables)

and there is no problem when I try to enter an array of TEXT objects into the same column (in case I changed the table to the TEXT [] column instead of the UUID [] column)

here is my code

 //////////////// var Promise = require('bluebird'); var pgpLib = require('pg-promise'); var pgp = pgpLib(); var cn = confUtil.pgDbConnectionConfiguration(); var db = pgp(cn); ////////////////// var newEntity={}; newEntity.hash = uuid.v4(); newEntity.location = {X:2394876,Y:2342342}; newEntity.mother = uuid.v4(); newEntity.timestamp = Date.now(); newEntity.content = {content:"blah"}; newEntity.sobList = [uuid.v4(),uuid.v4(),uuid.v4()]; addEntity (newEntity); //////////////////// function addEntity(newEntity) { var insertEntityQueryPrefix='insert into entities ('; var insertEntityQueryMiddle=') values ('; var insertEntityQueryPostfix=""; var insertEntityQuery=""; Object.keys(newEntity).forEach(function(key){ insertEntityQueryPrefix=insertEntityQueryPrefix+'"'+key+'",'; insertEntityQueryPostfix=insertEntityQueryPostfix+'${'+key+'},'; }); insertEntityQueryPrefix=insertEntityQueryPrefix.slice(0,-1); insertEntityQueryPostfix=insertEntityQueryPostfix.slice(0,-1)+")"; insertEntityQuery=insertEntityQueryPrefix+insertEntityQueryMiddle+insertEntityQueryPostfix; //longStoryShort this is how the query template i used looked like /* "insert into entities ("hash","location","mother","timestamp","content","sobList") values (${hash},${location},${mother},${timestamp},${content},${sobList})" */ //and this is the parameters object i fed to the query i ran it when it failed /* { "hash": "912f6d85-8b47-4d44-98a2-0bbef3727bbd", "location": { "X": 2394876, "Y": 2342342 }, "mother": "87312241-3781-4d7c-bf0b-2159fb6f7f74", "timestamp": 1440760511354, "content": { "content": "bla" }, "sobList": [ "6f2417e1-b2a0-4e21-8f1d-31e64dea6358", "417ade4b-d438-4565-abd3-a546713be194", "e4681d92-0c67-4bdf-973f-2c6a900a5fe4" ] } */ return db.tx(function () { var processedInsertEntityQuery = this.any(insertEntityQuery,newEntity); return Promise.all([processedInsertEntityQuery]) }) .then( function (data) { return newEntity; }, function (reason) { throw new Error(reason); }); } 

Answer 1

Inserting an array of UUIDs is a special case that requires explicit casting, since you pass UUIDs to uuid[] as an array of text strings.

You need to change the INSERT query: replace ${sobList} with ${sobList}::uuid[] . This will instruct PostgeSQL to convert the array of strings to an array of UUIDs.

Regardless of your question, you do not need to use Promise.all inside db.tx when executing just one request. You can simply return the result from the insert request:

 return this.none(insertEntityQuery,newEntity); 

although using a transaction to execute a single request is equally pointless :)

UPDATE

The latest pg version promises support for custom type formatting , so you can write your own custom types to format queries, avoiding explicit type conversions.

For your example of using UUIDs inside an array, you can implement your own UUID type:

 const UUID = a => ({rawType = true, toPostgres = () => a.v4()}); 

And for any uuidValue in an array or separately, you can use UUID(uuidValue) for automatic formatting.