PostgreSQL INSERT语句执行失败排查：代码无明显问题却报错

First off, let's get past that generic error message—your code only tells you something went wrong, not what went wrong. That's the first fix we should make, because specific error details are how you'll pinpoint the issue fast.

Step 1: Grab the Exact PostgreSQL Error #

Modify your code to output the detailed error message from PostgreSQL. Since you're using pg_exec(), I assume you're working with PHP's PostgreSQL extension—use pg_result_error() to pull the specifics:

$result = pg_exec($conn,"INSERT INTO people (id, firstname, lastname, phone, email) VALUES(DEFAULT, 'DANNY', 'SMITH', '20245644222', 'danny@hotmail.com')");
if (!$result) {
    echo "An Insert query error occurred: " . pg_result_error($conn) . "\n";
    exit;
}

Once you have that specific message, you'll know exactly what's broken, but here are the most common culprits based on your query:

Common Causes to Check #

• Mismatched Data Types: Your phone value '20245644222' is an 11-digit number. If your phone column is set to integer, that's way too big (PostgreSQL's integer maxes out at 2147483647). Switch the column to bigint or (even better) varchar—phone numbers often need to handle special characters like + or - anyway.
• Invalid DEFAULT for id: Using DEFAULT for id only works if the column is auto-incrementing (like serial or GENERATED AS IDENTITY). If id doesn't have a default value defined, PostgreSQL will throw an error here. Double-check your table schema for the id column's setup.
• Constraint Violations: Maybe your email column has a UNIQUE constraint, and danny@hotmail.com already exists in the table. Or another column has a NOT NULL constraint you're accidentally missing (though your query seems to fill all required fields).
• Case-Sensitive Field Names: PostgreSQL treats unquoted identifiers as lowercase. If your table was created with quoted field names (e.g., "FirstName"), writing firstname in your query will reference a non-existent column.
• Insufficient Permissions: The database user you're connecting with might not have INSERT permissions on the people table.

Start by adding the error message output—this will narrow down the problem immediately. Once you have that specific error, fixing it will be straightforward (adjusting data types, fixing constraints, etc.).

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