[Bug]: PGVectorStore incorrectly handles string metadata that resembles numbers #15962

Activity

1. nicolas-werner

   added

   bugSomething isn't workingSomething isn't working

   triageIssue needs to be triaged/prioritizedIssue needs to be triaged/prioritized

   on Sep 11, 2024
2. dosubot commented on Sep 11, 2024

   

   dosubotBot

   on Sep 11, 2024 – with DosuBot

   More actions

   Hello @nicolas-werner! I'm here to help you with any bugs, questions, or contributions you may have. Let's work together to solve the issue you've encountered.

   To fix the PGVectorStore to correctly handle string metadata that resembles numbers without causing a DataError: invalid input syntax for type double precision when filtering, you need to ensure that the metadata is treated as text in the SQL queries. This can be done by explicitly casting the metadata fields to text in the filter conditions.

   Here is a modified version of the query method to handle this:

   def query(self, query: VectorStoreQuery, **kwargs: Any) -> VectorStoreQueryResult:
       def cast_to_text_filter(filters):
           return {key: f"CAST({value} AS TEXT)" for key, value in filters.items()}
   
       results = self._client.search(
           embedding=query.query_embedding,
           top_k=query.similarity_top_k,
           filter=(
               meta_contains(
                   cast_to_text_filter({pair.key: pair.value for pair in query.filters.legacy_filters()})
               )
               if query.filters is not None
               else None
           ),
       )
   
       nodes = [
           metadata_dict_to_node(record.meta, text=record.text)
           for record, _ in results
       ]
   
       return VectorStoreQueryResult(
           nodes=nodes,
           similarities=[score for _, score in results],
           ids=[str(record.id) for record, _ in results],
       )

   In this code, the cast_to_text_filter function ensures that all metadata values are cast to text, preventing the DataError caused by invalid input syntax for type double precision ^[1].

   To continue talking to Dosu, mention @dosu.
3. CharbelAD commented on Sep 15, 2024

   

   CharbelAD

   on Sep 15, 2024

   Contributor

   More actions

   Couldn't this issue be fixed by catching DataError in the except?
4. dosubot commented on Apr 10, 2025

   

   dosubotBot

   on Apr 10, 2025 – with DosuBot

   More actions

   Hi, @nicolas-werner. I'm Dosu, and I'm helping the LlamaIndex team manage their backlog. I'm marking this issue as stale.

   Issue Summary

   • The PGVectorStore was incorrectly handling string metadata resembling numbers, causing a DataError during filtering.
   • A solution was suggested to modify the query method to cast metadata fields to text in SQL queries.
   • An alternative solution was proposed to catch the DataError in an exception block.
   • The issue appears to be resolved with the modification of the query method.

   Next Steps

   • Please confirm if this issue is still relevant to the latest version of the LlamaIndex repository. If so, you can keep the discussion open by commenting on the issue.
   • Otherwise, the issue will be automatically closed in 7 days.

   Thank you for your understanding and contribution!
5. dosubot

   added

   staleIssue has not had recent activity or appears to be solved. Stale issues will be automatically closedIssue has not had recent activity or appears to be solved. Stale issues will be automatically closed

   on Apr 10, 2025
6. logan-markewich

   added

   P2

   and removed

   triageIssue needs to be triaged/prioritizedIssue needs to be triaged/prioritized

   staleIssue has not had recent activity or appears to be solved. Stale issues will be automatically closedIssue has not had recent activity or appears to be solved. Stale issues will be automatically closed

   on Apr 11, 2025
7. logan-markewich

   added this to Frameworkon Apr 11, 2025
8. ErenAta16 commented on Jul 22, 2026

   

   ErenAta16

   on Jul 22, 2026

   More actions

   Still present, unchanged, on main: postgres/base.py#L737-L751. Neither of the suggestions in this thread will fix it, so it is worth naming the actual mechanism.

   The try: float(...) is being used as a test for "can Postgres cast this to float8", and the two grammars are not the same. Python's float() accepts PEP 515 digit separators; Postgres does not:

   float('2024_123'            ) -> 2024123.0
   float('2024_0717_150625'    ) -> 20240717150625.0
   float('nan'                 ) -> nan
   float('  12  '              ) -> 12.0
   float('1_0e1_0'             ) -> 100000000000.0
   float('١٢٣'                 ) -> 123.0
   float('report.pdf'          ) -> ValueError
   

   That is exactly what your traceback shows. Running the real _build_filter_clause (source lifted from main, stub MetadataFilter) with FilterOperator.EQ:

   value='2024_0717_150625'     -> WHERE (metadata_->>'file_name')::float = 20240717150625.0
   value='2024_123'             -> WHERE (metadata_->>'file_name')::float = 2024123.0
   value='report.pdf'           -> WHERE metadata_->>'file_name' = 'report.pdf'
   value="O'Brien"              -> WHERE metadata_->>'author' = 'O'Brien'
   value=2024                   -> WHERE (metadata_->>'year')::float = 2024.0
   

   The first line reproduces the = 20240717150625.0 in your posted SQL literally.

   There is a second half to your error that is easy to miss and matters for the fix. The value Postgres rejected was "2024_123", but the value in the predicate was 20240717150625.0. So "2024_123" is not your filter value, it is some other row's stored file_name. (metadata_->>'file_name')::float is evaluated per row, so once any row in the table has a non-castable value under that key, the query fails no matter what you filter for. A single filename like report.pdf in the collection breaks every numeric-looking file_name filter.

   That is also why @CharbelAD's suggestion of catching DataError cannot work: the exception surfaces from the server during execute(), after the full statement (usually an AND/OR tree of many clauses) has been built and sent. There is no clause left to retry, and no way to know which one to rebuild. Any fix has to happen at clause construction time.

   The construction is also unsafe, which the O'Brien line above shows: filter_.value is interpolated straight into text(). A quote in a metadata value produces a syntax error, and anything else in that value is executed as SQL. In a RAG service where filters carry tenant ids, filenames, or anything else derived from user input, that is worth fixing on its own. The IN/NIN, ANY/ALL, CONTAINS and TEXT_MATCH branches interpolate the same way, and filter_.key is interpolated everywhere too.

   Both problems go away with the same change: decide numeric versus text from the Python type of the value rather than by attempting to parse it, and bind instead of interpolate.

   op = self._to_postgres_operator(filter_.operator)
   if isinstance(filter_.value, (int, float)) and not isinstance(filter_.value, bool):
       return text(f"(metadata_ ->> :fkey)::float {op} :fval").bindparams(
           fkey=filter_.key, fval=float(filter_.value)
       )
   return text(f"metadata_ ->> :fkey {op} :fval").bindparams(
       fkey=filter_.key, fval=str(filter_.value)
   )

   One behavior change to be deliberate about: MetadataFilter(key="year", value="2024", operator=GT) currently compares numerically and would start comparing as text (lexicographic). That is arguably the correct reading of a str value, and value=2024 still does the numeric thing, but it is a break for anyone who has been passing numbers as strings, so it belongs in a changelog entry rather than a silent patch.

   ---

   Disclosure: I used Claude Code to help investigate and draft this comment. The float() table and the generated SQL above are real output; the SQL was produced by executing the current _build_filter_clause and _to_postgres_operator source from main against stub filter objects.
9. pctablet505

   added a commit that references this issue on Aug 21, 2026

   fix(pgvector): use type check for numeric filter values

   ...

   This issue will close once commit 948b271 is merged into the 'main' branch.

   948b271
10. kumarpriyanshu09 commented on Sep 29, 2026

    

    kumarpriyanshu09

    on Sep 29, 2026

    More actions

    Taking a crack at this — the float() / ::float path on string metadata that looks numeric (e.g. "2024_123") is still present on main, and I'm planning a small fix in PGVectorStore._build_filter_clause with unit tests + a postgres integration version bump.

    Noting draft #22367 already sketches the isinstance approach; it's been idle as a draft, so I'll open a ready-for-review PR in the same spirit. Happy to defer if that one is about to land.