Summary
The user is facing an error in the typing and deduping phase of PostgreSQL replication due to the ‘row_number’ column ambiguity. They are considering renaming the column in the source schema to resolve the issue and are seeking advice on potential workarounds and whether this issue is specific to PostgreSQL destination.
Question
Hi; I’m doing a PoC that includes PostgreSQL -> PostgreSQL replication. One of the tables in the source database has a column called row_number, and this is making the sync fail in the typing and deduping phase. I’m seeing the following error in the log, along with a Java stack trace:
org.postgresql.util.PSQLException: ERROR: column reference "row_number" is ambiguous
which I think is being triggered by the query that’s doing (roughly)
delete from {destination_table} where "_airbyte_raw_id" in (select "_airbyte_raw_id" from (select "_airbyte_raw_id", row_number() over (partition by {primery_keys} order by "_airbyte_extracted_at" desc) as "row_number" from {destination_table}) as "airbyte_ids" where "row_number" <> 1)
I can’t see an obvious workaround, other than renaming the column in the source schema. Am I missing something here? Would I run in to the same issue with other destination databases, or is this specific to the PostgreSQL destination? Would it be a good idea for the code to be using a more obscure name for this column alias (e.g. _airbyte_row_number)?
This topic has been created from a Slack thread to give it more visibility.
It will be on Read-Only mode here. Click here if you want
to access the original thread.