如何使用Knex与PostgreSQL插入ARRAY类型数据？急求帮助

Alright, let's get your data inserted into that PostgreSQL table quickly. I'll cover two practical methods depending on whether you want to use raw SQL directly or work with JavaScript (since your data is in a JS object).

Raw SQL Insertion Method #

First, since your table has case-sensitive column names (Label and Results are capitalized), you'll need to wrap those in double quotes in your SQL query to avoid errors. Here's the parameterized INSERT statement (always use parameterization to prevent SQL injection):

INSERT INTO analyses (userID, choice, "Label", "Results", description)
VALUES (
  $1, -- Replace with your actual user.id value (e.g., 123)
  'SWOT',
  ARRAY['Strengths','Weaknesses','Opportunities','Threats'],
  ARRAY[45,5,20,30],
  'My first Strategic Analysis'
);

• If you're running this in a PostgreSQL client (like psql), replace $1 with the actual numeric value of user.id.
• The ARRAY[] syntax is how PostgreSQL accepts array values directly in SQL.

Node.js Implementation (using pg library) #

Since your data is already in a JavaScript object, using the popular pg library is a seamless approach. Here's a complete, ready-to-use example:

1. First install the library:

npm install pg

2. Then write the insertion code:

const { Pool } = require('pg');
// Your existing data object
const data = { id: user.id, choice:'SWOT', label:['Strengths','Weaknesses','Opportunities','Threats'], results:[45,5,20,30], description:'My first Strategic Analysis' };

// Configure your database connection (fill in your credentials)
const pool = new Pool({
  user: 'your_db_username',
  host: 'your_db_host',
  database: 'your_db_name',
  password: 'your_db_password',
  port: 5432, // Default PostgreSQL port
});

async function insertAnalysis() {
  try {
    const insertQuery = `
      INSERT INTO analyses (userID, choice, "Label", "Results", description)
      VALUES ($1, $2, $3, $4, $5)
      RETURNING *; -- Optional: returns the inserted row if you need to verify it
    `;
    // Map your JS object properties to the query parameters
    const queryValues = [
      data.id,
      data.choice,
      data.label,
      data.results,
      data.description
    ];
    const result = await pool.query(insertQuery, queryValues);
    console.log('Data inserted successfully:', result.rows[0]);
  } catch (error) {
    console.error('Error inserting data:', error.message);
  } finally {
    await pool.end(); // Clean up the database connection pool
  }
}

// Run the insertion function
insertAnalysis();

Key Notes to Avoid Issues #

• Foreign Key Constraint: Make sure the user.id value you're inserting exists in the users table—PostgreSQL will throw an error if it doesn't match any existing user ID.
• Case Sensitivity: Remember to wrap Label and Results in double quotes in your queries, since your table defines them with capitalized names.
• SQL Injection: Never concatenate user input directly into SQL strings—always use parameterized queries like the examples above.

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