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

Question

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

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

INSERT STATEMENT

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);

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

Thanks in advance !

Answer 1

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?

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?

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.

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.

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.

Extra

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