如何使用nodejs pg

本文介绍了如何使用nodejs pg-promise库将带有uuid数组的记录插入到pg表中的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要在我的数据库中有一个表，该表包含一个单列，该单列是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.