如何使用nodejs pg-promise库将带有uuid数组的记录插入到pg表中 [英] how to insert a record with a uuid array into a pg table using nodejs pg-promise library
问题描述
我需要在我的数据库中有一个表,该表包含一个单列,该单列是uuid对象的数组(uuid []类型)
i need to have a table in my db which contains a single column which is an array of uuid objects (uuid[] type)
但是当我尝试使用名为pg-promise的nodejs库插入它失败
but when i try to insert into it using a nodejs library named pg-promise it fails
我收到以下错误消息,告诉我我需要重写演员或表达式
i get the following error message telling me i need to rewrite the cast or the 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"}
此是奇怪的
,因为当我尝试向同一精确表中的另一列输入单个uuid时我没有任何问题(这意味着我没有代表uuid的问题,顺便说一句,我将它们创建为来自另一个lib的文本变量,但是它们是普通的旧文本变量)
this is strange since i have absolutely no issues when i try to enter a single uuid to another column on the same exact table (meaning, i have no issue with representing uuid, btw i create them as a text variables from another lib, but they are plain old text variables)
当我尝试向同一对象输入TEXT对象数组时也没有问题列(以防我将表更改为具有TEXT []列而不是UUID []列)
nor do i have an issue when i try to enter an array of TEXT objects to the same column (in case i change the table to have a TEXT[] column instead of UUID[] column)
这是我的代码
////////////////
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);
});
}
推荐答案
插入UUID- s是一种特殊情况,需要显式类型转换,因为您要将UUID-s作为文本字符串数组传递给 uuid []
类型。
Inserting an array of UUID-s is a special case that requires explicit type casting, because you are passing UUID-s into type uuid[]
as an array of text strings.
您需要更改您的 INSERT
查询:将 $ {sobList}
替换为 $ {sobList} :: uuid []
。这将指示PostgeSQL将字符串数组转换为UUID-s数组。
You need to change your INSERT
query: replace ${sobList}
with ${sobList}::uuid[]
. This will instruct PostgeSQL to convert the array of strings into array of UUID-s.
与您的问题无关,您无需使用 Promise仅执行单个请求时,在
。您可以简单地从插入请求中返回结果: db.tx
中的.all
Unrelated to your question, you do not need to use Promise.all
inside db.tx
when executing just a single 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 :)
更新
pg-promise 支持自定义类型格式,因此您可以编写自己的自定义类型以进行查询格式设置,避免显式类型转换。
The latest version of pg-promise supports Custom Type Formatting, so you can write your own custom types for query formatting, avoiding explicit type casting.
在数组内的UUID -s中,您可以实现自己的UUID类型:
For your example of using UUID-s inside an array, you can implement your own UUID type:
const UUID = a => ({rawType = true, toPostgres = () => a.v4()});
对于任何 uuidValue
或单独使用,您可以使用 UUID(uuidValue)
进行自动格式化。
And for any uuidValue
in array or alone, you can use UUID(uuidValue)
for automatic formatting.
这篇关于如何使用nodejs pg-promise库将带有uuid数组的记录插入到pg表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!