3

Developed by node.js. I am using pg-promise.

There is a problem with inserting the following data.

I want to insert multiple rows of data into the table below.

create table info (
  id varchar(20) not null,
  name varchar(20) not null,
  createdate timestamp with time zone not null
)

I inserted the data below.

let info = [
  { myid: '0001', myname: 'name1' },
  { myid: '0002', myname: 'name2' },
  { myid: '0003', myname: 'name3' },
]

I originally inserted the following.

for (let i = 0; i <info.length; i ++) {
  db.none (`INSERT INTO info (id, name, createdate ') VALUES ($1, $2, CURRENT_TIMESTAMP)`, [info[i].id, info[i].name])
}

I want to fix it correctly using pg-promise.

const dataMulti = info
const cs = new pgp.helpers.ColumnSet(
[
  'id',
  'name',
  'createdate',
], {
  table: 'info',
})
await db.none(pgp.helpers.insert(dataMulti, cs))

Here,

1. Can not insert when the key of the insert data is different from the . columns name of the table?

I know there is pgp.helpers.ColumnSet()

Error: Property 'id' does not exist.

The same error occurs.

I am curious about the relationship between table column names and ColumnSets for column settings.

2. When using the helpers above, how do I insert the current time current_timestamp in the database?

Thank you for your answers to the two questions.

vitaly-t
  • 24,279
  • 15
  • 116
  • 138
bamgae
  • 301
  • 1
  • 2
  • 15

1 Answers1

6

The following ColumnSet corrects all your problems:

const cs = new pgp.helpers.ColumnSet([
    {name: 'id', prop: 'myid'},
    {name: 'name', prop: 'myname'},
    {name: 'createdate', mod: '^', def: 'CURRENT_TIMESTAMP'}
], {
    table: 'info'
});

See Column for what's available to configure individual columns.

the application of CURRENT_TIMESTAMP is difficult to understand...

{name: 'createdate', mod: '^', def: 'CURRENT_TIMESTAMP'}

It is a column named createdate, with default value CURRENT_TIMESTAMP and formatting modifier ^ (same as :raw), which means raw text (no escaping needed). Other than that, Column type explains it in detail.

vitaly-t
  • 24,279
  • 15
  • 116
  • 138
  • Thank you very much. I now know that this is a feature. **However, the application of current_timestamp is difficult to understand.** Can I ask for a little more explanation? – bamgae Sep 17 '17 at 05:25
  • I do not know how to initialize 'rawText'. – bamgae Sep 17 '17 at 05:26
  • @hyeokluv No such thing as `rawText` in my answer ;) You should use the latest, it was simplified ;) – vitaly-t Sep 17 '17 at 05:32
  • *As a more detailed answer to your question, I am able to use it properly.* pg-promise is a really useful module. thank you. – bamgae Sep 17 '17 at 05:53