pg-promise助手:插入和多次更新中的可选字段 [英] pg-promise helpers : optionnal fields in insert and multiple-update

问题描述

pg-promise助手:插入和多次更新中的可选字段

pg-promise helpers : optionnal fields in insert and multiple-update

我对插入和更新语句的非必填字段有疑问

I have some question about non-required fields for insert and update statement

通过此帖子，我定义了 ColumnSet ，其中包含可选字段( stype ， sspeed ， disup ).它可以工作，但我只想知道一些细节:您可以看到，如果对象没有属性，则 ColumnSet 将 state 的值定义为"false".在数据库中，默认情况下将字段"disup"定义为"false"，所以我真的需要在此处将值定义为 false 还是有其他方法来定义可选列?想象一下，我 ALTER TABLE 将默认值更改为 TRUE ，我将不得不更改代码.跳过参数不适用于插入，我不知道不了解如何使用部分(我想我不能在这里使用)

With this post I defined a ColumnSet that contains optionals fields (stype, sspeed, disup). It works but I just want to know a little detail: You can see that the ColumnSet define the value of state as "false" if the object don't have the property. In the database the field "disup" is defined to "false" as default, so do I really need to define here the value as false or is there an other way to define optional columns ? Imagine that I ALTER TABLE to change the default value toTRUE I will have to change the code. skip parameter don't works with insert I don't understand how to use partial (I think I can not use it here)

cs.insert = new pgp.helpers.ColumnSet([
    /* hidden for brevity */
    { name: 'stype', prop: 'type', def:  { _rawType: true, toPostgres: () => null } },
    { name: 'sspeed', prop: 'speed', def:  { _rawType: true, toPostgres: () => null } },
    { name: 'disup', prop: 'state', def:  { _rawType: true, toPostgres: () => false } }
  ], {
    table: 'interfaces'
});

const objInsert = [
  { /* hidden for brevity */, state: false },
  { /* hidden for brevity */, speed: 2000, type: "Wired" }
];

pgp.helpers.insert(objInsert, cs.insert);

更新声明

我需要在另一个 ColumnSet 中定义可选列，但是这次是UPDATE语句，我想使用数组作为输入.跳过不适用于数组，如何使多个更新"查询与可选"字段一起使用?答案是任务还是批处理"?(我不太了解批处理的内容)例如使用

UPDATE STATEMENT

I need to define optional columns in an other ColumnSet but for UPDATE statement this time and I would like to use an array as input. skip don't works with array, how to make "multiple update" query works with "optional" fields ? Is the answer "with a task or a batch" ? (I don't really understand what a batch does) For example using

cs.update = new pgp.helpers.ColumnSet([
    { name: 'interfaceid', prop: 'id', cnd: true },                          
    { name: 'updatedat', mod:'^', init: () => 'CURRENT_TIMESTAMP(0)' },                     
    { name: 'siface', prop: 'iface', skip: col => !col.exists },
    { name: 'sipv4', prop: 'ipv4', cast: 'inet', skip: col => !col.exists },
    { name: 'sipv6', prop: 'ipv6', cast: 'inet', skip: col => !col.exists },
    { name: 'smac', prop: 'mac', cast: 'macaddr', skip: col => !col.exists },
    { name: 'stype', prop: 'type', skip: col => !col.exists },
    { name: 'sspeed', prop: 'speed', skip: col => !col.exists },
    { name: 'disup', prop: 'state', skip: col => !col.exists }
  ], {
    table: 'interfaces'
});

const objs =  [
    { id: 1, iface: "new value", state: false   },
    { id: 37, ipv4: "192.168.254.1" }
];

pgp.helpers.update(objs, cs.update); // throw "Property 'ipv4' doesn't exist." because objs is an array

提前谢谢！

推荐答案

在数据库中，默认情况下将字段 disup 定义为 false ，所以我真的需要在此处将值定义为 false 还是还有另一种定义可选列的方法吗?

In the database the field disup is defined to false as default, so do I really need to define here the value as false or is there an other way to define optional columns?

这取决于您要实现的目标.例如，如果您想在缺少该属性时使用 false ，则可以使用 skip:col =>代替.！col.exists ，则可以使用 def:false .

It depends on what you are trying to achieve. For example, if you want to use false when the property is missing, then instead of skip: col => !col.exists, you can use def: false.

如何使多个更新"查询与可选"字段一起使用?

how to make "multiple update" query works with "optional" fields?

不可能.PostgreSQL用于多行更新的语法不允许这种情况.它是文档中的 - skip 逻辑仅适用于单行更新.对于多行更新，您将必须在其中提供默认值，如 def 或 init .

It is not possible. PostgreSQL syntax for multi-row updates doesn't allow such thing. It is in documentation - skip logic works only for single-row updates. And for multi-row updates you will have to provide default values there, as either def or init.

还请注意，您正在对原始类型使用过时的 _rawType 属性.不久前它已更改为 rawType .还是您正在使用图书馆的古老版本?那也不是很好，您应该升级.并且所有在线文档均指最新版本.

Also note that you are using a long-obsolete _rawType property for raw types. It was changed to rawType awhile ago. Or are you using an ancient version of the library? That wouldn't be good either, you should upgrade. And all online documentation refers to the latest release.

它对您有用的原因是因为 def:{rawType:true，toPostgres:()=>false} 可以简化为 def:false .除非您的函数返回预格式化的文本，否则您无需使用原始文本.

And the reason it works for you is because def: { rawType: true, toPostgres: () => false } can be reduced to just def: false. You do not need to use raw text, unless your function returns pre-formatted text.

额外

如果您的 INSERT的 ColumnSet 对象和 UPDATE 非常相似，您可以使用方法合并;)

If your ColumnSet objects for INSERT and UPDATE are very similar, you can reduce the re-declaration, by using methods extend and merge ;)

这篇关于pg-promise助手:插入和多次更新中的可选字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！