SQL query error on PostgreSQL 9.5 when try to list Bookmarks

That query seems to be created by Views. (The /bookmark page is a View, and the query is not one that appears in the Flag source code). This error is essentially the same as many others that I found in the issue queues. The one with the most information is probably #2747079: "Undefined function: 7 ERROR: operator does not exist: bigint" error with a PostgreSQL database. There's a hack in there that "fixes" this, but what it does is implement hook_views_query_alter() and put in PostgreSQL-specific code. Not a good solution, but it may be a workaround if you're desperate.

To me this feels more like a Views problem or a problem with the Drupal PostgreSQL driver. Flag's only input is to define the View via the views.view.flag_bookmark.yml file. I ran the configuration inspector and I verified that this file agrees with the schema, so what Flag passes to Views is type-correct.

The Flag module uses the core Drupal Entity API, and Drupal explicitly allows entity IDs to be either integers or strings. There's even a set of core tests written to make sure, among other things, that the code runs correctly if you use string IDs (core/modules/system/tests/modules/entity_test).

This is why I'm saying the problem and the needed fix isn't in the Flag module:
1) Many other modules experience the same problem, and the problem shows up in queries generated by Views - check google.
2) The Entity API declares that an entity ID may be either a string or an int, so Flag is allowed to use the string type for the flagged entity ID. In fact, because entity_id is used to hold the ID of the flagged entity, it *should* be a string - that allows for flagged entities to have either int or string IDs.

One way to address this is to alter your own table or change the Flag module, but that doesn't help anybody but you and it just pushes to the problem to another spot - you will now not be able to Flag entities with a string ID.

The first thing I would do is look at Views and see if there's some way to change the way Views builds that query so that it casts the JOIN id's to the same type to prevent the PostgreSQL error. But seeing the hook_views_query_alter() that I linked to above, that probably has to be done at the PostgreSQL driver level (core/lib/Drupal/Core/Database/Driver/pgsql). If you can develop a fix, that should be addressed in the core Drupal issue queue. In fact if you search you might even find an existing issue for this in the core queue.

Changing the status back to Active. Needs Review is for when you have provided a patch that needs to be reviewed. There's no patch here. A patch to change the BaseFieldDefinition type would also have to provide an update function so that existing sites could update to the new definition, as well as provide an update test to make sure the update works properly. But as I said, then you would be just avoiding this one problem and causing another.

had to do some thing like ...

use Drupal\Core\Database\Database;
use Drupal\views\ViewExecutable;
use Drupal\views\Plugin\views\query\QueryPluginBase;

/**
 * Implements hook_views_query_alter()
 */
function MYMODYLE_user_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  $db = Database::getConnection()->driver();
  if ($db == 'pgsql') {
    if (isset($view->relationship['flag_relationship'])) {
      $query->getTableInfo('flagging_node_field_data')['join']->field .= '::bigint';
    }
  }
}

The solution here:

#2864440: PostgreSQL problems on entity_id of type varchar with flagging and flag_count tables

My comment #12 has the patch included

[duplicate] removed

I completely agree with #5, and have submitted a patch to core to address this.

hi,
#6 made it for me, thanks @taggartj

I got the same error with group and webform modules when you assign one of the element to the form as a group entity.
#6 works for me.
I also applied #9, the one that was posted for the core, but I got duplicated rows in different views, for example, I got duplicated results after filtering users by mail in /admin/people.

This seems to be a duplicate of #2864440: PostgreSQL problems on entity_id of type varchar with flagging and flag_count tables.

I had this problem and was able to solve it with in the patch in #3079534-27: Views JOIN condition fails in PostgreSQL when comparing entity identifiers of different datatypes.

Patch #3079534 works, but there is also ability to do the same with internal module changes. Please test the patch.

Needs reroll to MR

If this is a Views problem and patch from Views solves the issue (it will be committed at one point) the patch can be added via composer, why would you then want to solve the same issue within the contributed module?
I think this issue is not a Flag issue and therefore the issue can be closed.

@deaom, according to #13, it is still kind of a Flag issue too, as far as I understand.

I have made a merge request with the patch in #13.

Claude Code says:

Overview

Fixes a long-standing PostgreSQL compatibility bug in the Flag Views relationship. flagging.entity_id is defined as a string (varchar) in Flagging.php:126, but many entity ID columns (e.g., node.nid) are integers. MySQL silently coerces the types during JOIN, but PostgreSQL raises a type-mismatch error. The fix sets a left_formula on the relationship that casts the base-table's ID column to varchar, producing a JOIN condition like CAST(node_field_data.nid AS varchar) = flagging.entity_id.

Correctness

- Approach is sound. Using Views' left_formula is the standard way to override the left side of a relationship JOIN, and CAST(x AS varchar) is portable across MySQL, PostgreSQL, and SQLite.
- ensureMyTable() is required before reading $this->tableAlias — the call at line 172 is correct.
- No SQL injection risk. $this->tableAlias and $this->realField are Views-internal identifiers, not user input.
- Direction of cast is right. Casting the numeric base ID → varchar avoids CAST('abc' AS int) failures when flagging non-numeric entities (e.g., config entities keyed by string IDs).

Suggestions

- Unconditional cast has a perf cost. CAST(base.id AS varchar) on the left side of a JOIN prevents PostgreSQL from using an index on the base table's ID column. For views that return from large entity tables this can degrade noticeably. Consider casting the right side instead — flagging.entity_id is already varchar, so no cast is needed there — or only apply the formula when the base column type is non-string. A cheaper alternative would be to cast the flagging side once (e.g. CAST(flagging.entity_id AS int)) only when the base entity's ID column is integer, which preserves index use on the larger base table.
- Placement. The mutation happens inside query() before parent::query() at line 211, which is the correct hook. Leaving it as-is is fine.
- No test added. The module has a tests/ directory but this PR ships no regression test. A kernel or functional test exercising FlagViewsRelationship::query() against a node view would catch reintroductions — though PostgreSQL-specific coverage requires a multi-DB CI matrix, so this may be a known limitation of the project's test setup.
- Minor style. CAST($this->tableAlias.$this->realField AS varchar) uses string interpolation with a dot between properties, which works but is slightly easier to misread than {$this->tableAlias}.{$this->realField}. Not a correctness issue.

Risks

- Behavior change on MySQL users. Existing queries that worked fine on MySQL now run through a CAST. MySQL's optimizer generally handles this, but if the site relied on an index on the base ID column for a large flagged view, query plans could shift. Low but non-zero risk.
- Upstream/contrib interaction. Any code that read $this->definition['left_formula'] or expected left_field to be authoritative will see the new formula. Unlikely to be an issue since this is internal to the plugin.

Verdict

Small, targeted, correct fix for a real cross-DB bug. Ship it, ideally with a follow-up to consider the index/performance trade-off and add a regression test.

Tests are failing.

At the time that comment #19 was posted, the merge request also needed to be rebased. Claude gets it wrong again.

@benstallings I've just tested your latest commits on the last MR, as a patch, and I can confirm that the bug is gone now:)