Error Drupal SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type bigint

General
1

I have an error after migrating Drupal database from MySQL to PostgresSQL. All works fine, but when I want to add a node (all types), an error fires:

Drupal\Core\Entity\EntityStorageException: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type bigint: “” LINE 2: WHERE “nid” = ‘’ ^: DELETE FROM “node_field_data” WHERE “nid” = :db_condition_placeholder_0; Array ( [:db_condition_placeholder_0] => ) in Drupal\Core\Entity\Sql\SqlContentEntityStorage->save() (line 817 of core/lib/Drupal/Core/Entity/Sql/SqlContentEntityStorage.php).

This error appears after pushing the “create” button in new node page.

I’m going crazy and can’t figure it out.

Drupal version is 10.3.2

Any help is welcome. Thanks in advance.

2

The error message indicates that you’re encountering an issue with the nid (node ID) field in your PostgreSQL database. Specifically, the system is attempting to delete a node with an empty (“” ) nid , which is invalid for a bigint data type.

Here are some potential causes and solutions:

1. Incorrect Data Type:

  • Check nid Field: Ensure that the nid field in your PostgreSQL database is defined as a bigint data type. If it’s a different type (e.g., text , varchar ), you’ll need to modify the schema to match the expected type.
  • Query the Database: Run a direct SQL query to inspect the nid values in the node_field_data table. Look for any empty or non-numeric values that might be causing the issue.

2. Data Integrity Issues:

  • Corrupted Data: Check if there are any corrupted or inconsistent data entries in the node_field_data table. You might need to manually correct or delete any invalid records.
  • Referential Constraints: If you have foreign key constraints referencing the nid field in other tables, ensure that those constraints are defined correctly and that there are no orphaned or dangling references.

3. Custom Modules or Themes:

  • Third-Party Code: Review any custom modules or themes you’re using for potential conflicts or issues related to node creation or data handling. Try temporarily disabling them to see if the problem persists.

4. Database Migration Issues:

  • Migration Script: If you used a custom migration script, double-check its logic to ensure that it’s handling nid values correctly and not introducing any inconsistencies.
  • Migration Tools: If you used a migration tool like Migrate Drupal, review its logs and configuration to identify any potential errors or warnings related to the nid field.

5. Drupal Core Bug:

  • Check for Known Issues: Although less likely, there might be a known bug in Drupal 10.3.2 related to node creation and PostgreSQL. Check the Drupal issue tracker or community forums for any reported issues that match your symptoms.

Troubleshooting Steps:

  1. Verify Data Type: Confirm that the nid field in your PostgreSQL database is defined as bigint .
  2. Inspect Data: Query the node_field_data table to check for any invalid or empty nid values.
  3. Review Custom Code: Disable custom modules or themes to isolate the issue.
  4. Check Migration Scripts: If applicable, verify the correctness of your migration scripts or tools.
  5. Search for Known Issues: Look for reported bugs or issues related to node creation and PostgreSQL in Drupal 10.3.2.

By following these steps and carefully investigating the potential causes, you should be able to identify and resolve the issue preventing node creation in your Drupal installation.