如何用npm pg-promise包编写指定PostgreSQL更新查询？

To translate your PostgreSQL update query into pg-promise while maintaining safety and proper formatting, you have a couple of clean approaches. Both methods ensure parameterized values (to avoid SQL injection) and handle the CAST for time values correctly.

Approach 1: Direct Tuple Formatting (Simple & Concise) #

This method uses pg-promise's as.format to build parameterized tuples for your multi-column IN clause:

const pgp = require('pg-promise')();
const db = pgp('your-database-connection-string');

// Define your parameters
const studentId = 'a1ef71bc6d02124977d4';
const teacherId = '6b33092f503a3ddcc34';
const scheduleTimes = [
  ['M', '17:00:00'],
  ['T', '19:00:00']
];

// Create parameterized tuples with time casting
const timeTuples = scheduleTimes.map(pair => 
  pgp.as.format('($1, CAST($2 AS TIME))', pair)
);

// Format the full update query
const updateQuery = pgp.as.format(`
  UPDATE schedule
  SET student_id = $1
  WHERE teacher_id = $2
  AND (start_day_of_week, start_time) IN ($3:list)
`, [studentId, teacherId, timeTuples]);

// Execute the query
db.none(updateQuery)
  .then(() => console.log('Schedule updated successfully'))
  .catch(error => console.error('Update failed:', error));

How it works: #

• pgp.as.format safely escapes each value in the tuples, preventing SQL injection.
• The $3:list modifier joins the formatted tuples into a comma-separated list for the IN clause.
• The CAST($2 AS TIME) ensures your time string is converted to PostgreSQL's time type.

Approach 2: Using ColumnSet (Dynamic/Larger Datasets) #

For more dynamic scenarios (e.g., variable numbers of schedule entries), use pg-promise's helpers.ColumnSet to generate a parameterized VALUES clause:

const pgp = require('pg-promise')();
const db = pgp('your-database-connection-string');

// Define parameters
const studentId = 'a1ef71bc6d02124977d4';
const teacherId = '6b33092f503a3ddcc34';
const scheduleTimes = [
  ['M', '17:00:00'],
  ['T', '19:00:00']
];

// Create a ColumnSet to define the tuple structure and casting
const cs = new pgp.helpers.ColumnSet(
  [
    { name: 'day', prop: 0 }, // Maps to first element of each array entry
    { name: 'time', prop: 1, cast: 'time' } // Maps to second element, casts to time
  ],
  { table: null } // No table name needed for standalone VALUES
);

// Generate the parameterized VALUES clause
const valuesClause = pgp.helpers.values(scheduleTimes, cs);

// Build the full query
const updateQuery = `
  UPDATE schedule
  SET student_id = $1
  WHERE teacher_id = $2
  AND (start_day_of_week, start_time) IN (${valuesClause})
`;

// Combine all parameters (flatten the scheduleTimes array)
const params = [studentId, teacherId, ...scheduleTimes.flat()];

// Execute the query
db.none(updateQuery, params)
  .then(() => console.log('Successfully updated schedule'))
  .catch(error => console.error('Error:', error));

How it works: #

• ColumnSet defines the structure of each tuple, including the cast for the time value.
• pgp.helpers.values generates a fully parameterized VALUES clause, which we insert into the IN clause.
• Flattening scheduleTimes ensures all values are passed as parameters, keeping the query safe.

Both approaches are valid—choose the one that fits your use case best.

内容的提问来源于stack exchange，提问作者Jacob Myers