ERROR: column reference «b_id» is ambiguous
знаю что данная ошибка означает что нужно изменить где-то b_id, но что-то не могу понять где.
Вот запрос:
SELECT "b_id","b_genre","b_author","b_book_name","b_page_count","at_text","bc_name" FROM "book" LEFT JOIN "annotation" ON "b_id"="at_bid" LEFT JOIN "book_cover" ON "bc_book_id"="b_id" LEFT JOIN (SELECT "b_id" FROM "book" ORDER BY "b_id" DESC OFFSET 10 LIMIT 10 ) as t ON t.b_id = "book".b_id
Ошибка которая вываливается:
ERROR: column reference "b_id" is ambiguous LINE 1: SELECT "b_id","b_genre","b_author","b_book_name","b_page_cou... ^
Вот это, я использую как offset, limit так как если использовать прямой offset и limit запрос зависает конкретно, записей много.
LEFT JOIN (SELECT "b_id" FROM "book" ORDER BY "b_id" DESC OFFSET 10 LIMIT 10 )
База данных PostgreSQL 9.3.11
I am using PostgreSQL as my database. And I need to create an entry in the database, and if it’s already exists, just update its fields, but one of the fields should be updated only if it’s not set.
I’ve used info from this question: https://stackoverflow.com/questions/13305878/dont-update-column-if-update-value-is-null, it’s quite related to what I have.
I tried to use this query, but when I run it, it errors with Column reference 'affiliate_code' is ambiguous:
INSERT INTO accounts (id, token, affiliate_code)
VALUES (value1, value2, value3)
ON CONFLICT (id) DO
UPDATE SET token = value2,
affiliate_code = COALESCE(affiliate_code, value3);
(the real values are substituted, of course).
If I replace affiliate_code = COALESCE(affiliate_code, value3) with affiliate_code = value3, everything works, but not in the way I want it to work.
How can I make this work?
Here is how my table is defined:
CREATE TABLE accounts (
id VARCHAR NOT NULL UNIQUE,
token VARCHAR NOT NULL,
affiliate_code VARCHAR
);
Last Modified Date: 07 Apr 2023
Issue
When trying to refresh a data extract containing custom SQL, the following error might occur:
ERROR: column reference «<field name>» is ambiguous; Error while executing the query
Environment
- Tableau Desktop
- Tableau Data Extract
- Custom SQL
Resolution
Verify that the SQL Query completes successfully when run directly against the database. If the query does not complete successfully, work with your database administrator to address issues in the query.
Cause
The error occurs within the data source and is not related to Tableau.
Additional Information
More information on the «Column reference «<name>» is ambiguous error can be found below:
- Identifier Qualifiers at MySQL
- 8 Tips Absolute Beginners can Use to Fix SQL Queries at Vertabelo Academy
The problematic underlying query that postgres was running as part of the alter statement was this:
SELECT
fk."a",
fk."b",
fk."c"
FROM ONLY "public"."b" fk
JOIN ONLY "public"."a" pk
ON ( pk."a" OPERATOR(pg_catalog.=) fk."a"
AND pk."b" OPERATOR(pg_catalog.=) fk."b"
AND pk."c" OPERATOR(pg_catalog.=) fk."c")
WHERE pk."a" IS NULL
AND (fk."a" IS NOT NULL
AND fk."b" IS NOT NULL
AND fk."b" IS NOT NULL);
So I changed the names, but it apparently was important that the name of column «b» in both tables was actually «rowid». After speaking with my DBA for a while, we just guessed that maybe rowid was a reserved word in postgres or at least the bug was related to that. Turns out it was; I renamed the column in both tables to «row_id» and the problem was resolved. Doesn’t work:
SELECT
fk."templateid",
fk."rowid",
fk."colid"
FROM ONLY "public"."b" fk
JOIN ONLY "public"."a" pk
ON ( pk."templateid" OPERATOR(pg_catalog.=) fk."templateid"
AND pk."rowid" OPERATOR(pg_catalog.=) fk."rowid"
AND pk."colid" OPERATOR(pg_catalog.=) fk."colid")
WHERE pk."templateid" IS NULL
AND (fk."templateid" IS NOT NULL
AND fk."rowid" IS NOT NULL
AND fk."colid" IS NOT NULL);
Works :
SELECT
fk."templateid",
fk."row_id",
fk."col_id"
FROM ONLY "public"."b" fk
JOIN ONLY "public"."a" pk
ON ( pk."templateid" OPERATOR(pg_catalog.=) fk."templateid"
AND pk."row_id" OPERATOR(pg_catalog.=) fk."row_id"
AND pk."col_id" OPERATOR(pg_catalog.=) fk."col_id")
WHERE pk."templateid" IS NULL
AND (fk."templateid" IS NOT NULL
AND fk."row_id" IS NOT NULL
AND fk."col_id" IS NOT NULL);
(I just changed colid for naming consistency, it was not contributing to the problem)
Explanation: This error occurs when the referenced column cant be resolved unambiguously. This may occur when you have two tables that have columns with the same name.
How can you fix an ambiguous column reference error?
One of the simplest ways to solve an ambiguous name column error without changing column name is to give the tables you want to join an alias. This sends a clear information to the SQL Machine the columns are different.
What does it mean in SQL when something is ambiguous?
Ambiguous error means that you are calling a certain field in which exist in both Table and the SQL has no idea where to get it.
What is ambiguous error?
Ambiguity errors occur when erasure causes two seemingly distinct generic declarations to resolve to the same erased type, causing a conflict.
What does column reference ambiguous mean?
If you run the above query, you will get this error Ambiguous name column. This means two columns have the same column name that is the Name column. The SQL Machine is confused as to which Name out of the two tables you are referring to. It is ambiguous not clear.
What is reference column in SQL?
Ambiguous error means that you are calling a certain field in which exist in both Table and the SQL has no idea where to get it.
What does ambiguous column name in SQL mean?
Explanation: This error occurs when the referenced column cant be resolved unambiguously. This may occur when you have two tables that have columns with the same name.
What does invalid column name mean?
Ambiguous error means that you are calling a certain field in which exist in both Table and the SQL has no idea where to get it.
What is meant by ambiguous in SQL?
If you run the above query, you will get this error Ambiguous name column. This means two columns have the same column name that is the Name column. The SQL Machine is confused as to which Name out of the two tables you are referring to. It is ambiguous not clear.
What does it mean when a field list is ambiguous?
This error occurs when you are trying to fetch some data from multiple tables with the help of a join query. But if the same field name is present in both tables, and you are not passing the table name as part of the column identifier, the query will be unsuccessful.
Where clause is ambiguous query?
Ambiguity errors occur when erasure causes two seemingly distinct generic declarations to resolve to the same erased type, causing a conflict.
What is ambiguity error?
Ambiguity errors occur when erasure causes two seemingly distinct generic declarations to resolve to the same erased type, causing a conflict.
What is ambiguous error in SQL?
If you run the above query, you will get this error Ambiguous name column. This means two columns have the same column name that is the Name column. The SQL Machine is confused as to which Name out of the two tables you are referring to. It is ambiguous not clear.
What is ambiguous error in C++?
Access to a base class member is ambiguous if you use a name or qualified name that does not refer to a unique function or object. The declaration of a member with an ambiguous name in a derived class is not an error. The ambiguity is only flagged as an error if you use the ambiguous member name.
What is ambiguity in Java?
The ambiguities are those issues that are not defined clearly in the Java language specification. The different results produced by different compilers on several example programs support our observations.
What is a column reference in SQL?
Explanation: This error occurs when the referenced column cant be resolved unambiguously. This may occur when you have two tables that have columns with the same name.
What is a reference table in SQL?
Table references are objects such as tables and views that you can include in an SQL statement. You can add table references at any time while you are working on the statement. You can create an SQL statement with multiple table references by using SELECT, FULLSELECT, and WITH statements.
What is Ref column?
A Ref column always stores the key column value of the referenced row. For example, if the key column value of a Customers row is Ann Adams then the Ref field in the related Orders row will contain the value Ann Adams . A tables key column value uniquely identifies each row in that table.
Why reference is used in SQL?
The references keyword is used to define which table and column is used in a foreign key relationship. This means that a record in the hobby table must have a person_id that exists in the person table or else at the time of insert you will receive an error that the key does not exist.
How does reference work in SQL?
A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table
How do you name a column in SQL?
Ambiguity errors occur when erasure causes two seemingly distinct generic declarations to resolve to the same erased type, causing a conflict.
Can we create two columns with same name in a relational table?
To get the column name of a table we use sp_help with the name of the object or table name. sp_columns returns all the column names of the object. The following query will return the tables column names: sp_columns @table_name News
Can a column name be null?
You cannot create two columns with exactly the same name.
Which is a valid column name?
Column names can contain any valid characters (for example, spaces). If column names contain any characters except letters, numbers, and underscores, the name must be delimited by enclosing it in back quotes (`).
How do you qualify a column name?
A qualifier for a column name can be a table name, a view name, an alias name, a synonym, or a correlation name. Whether a column name can be qualified depends, like its meaning, on its context. In some forms of the COMMENT and LABEL statements, a column name must be qualified