Column reference is ambiguous ошибка

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
);

Answer by Arian Rosas

That means when you declare the function with RETURNS TABLE(jobnr character varying…, jobnr is an out parameter. Thus SELECT jobnr … is ambiguous.,Try declaring the function with aliases for the tables in your select:,with a parameter declaration. ,Try it with others names in the RETURNS clausule

Try declaring the function with aliases for the tables in your select:

CREATE OR REPLACE FUNCTION userdata.test3()
 RETURNS TABLE(jobnr character varying, ordernr character varying)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    SELECT  w.jobnr, w.ordernr
    FROM
        (SELECT wip.jobnr, wip.ordernr FROM userdata.WIP_Data as wip WHERE Year=2015 AND period=1) AS W

        LEFT OUTER JOIN
        (SELECT wip.jobnr, wip.ordernr FROM userdata.WIP_Data as wip WHERE Year=2015 AND period=2) AS P 
        ON W.Jobnr=P.Jobnr;
END; 
$function$

Answer by Emilio Briggs

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.
,

Strong teams are more than just connected, they are communities

,Making statements based on opinion; back them up with references or personal experience.,Connect and share knowledge within a single location that is structured and easy to search.

The planner cannot understand which my_date you are mentioning there. Either the one in parameters or the one in the table1 (or table2). The correct syntax should be like the one below;

CREATE OR REPLACE FUNCTION test ()
RETURNS TABLE (
  my_date date, gates smallint, area_type character (1), 
  area_sizes float[], area_canvases float[]
)
AS $$
BEGIN
    RETURN query
    SELECT
        coalesce(s.my_date, r.my_date) AS my_date,
        coalesce(s.gates, r.gates) AS gates,
        coalesce(s.area_type, r.area_type) AS area_type,
        s.area_sizes,
        r.area_canvases
    FROM (
        SELECT
            t1.my_date,
            t1.gates,
            t1.area_type,
            t1.area_sizes
        FROM
            table1 t1
        WHERE
            t1.area_type = 'X') s
    FULL JOIN (
        SELECT
            t2.my_date, 
            t2.gates, 
            t2.area_type, 
            t2.area_canvases
        FROM
            table2 t2
        WHERE
            t2.area_type = 'Y') r ON r.my_date = s.my_date;
END
$$
LANGUAGE plpgsql;

Answer by Addison Glover

errorsfunctionsplpgsqlpostgresqlpostgresql-10,Postgresql – RETURN NEXT in Postgres Function,Postgresql – Postgres function return table with extra column,Postgresql – How to insert a record returned by a function into a table

This works:

CREATE OR REPLACE FUNCTION sql_fun()
RETURNS TABLE (id UUID) AS $$
  INSERT INTO foo DEFAULT VALUES
  RETURNING id
$$ LANGUAGE SQL;

SELECT *
FROM sql_fun();

This doesn’t:

CREATE OR REPLACE FUNCTION plpgsql_fun()
RETURNS TABLE (id UUID) AS $$
  BEGIN
    RETURN QUERY
    INSERT INTO foo DEFAULT VALUES
    RETURNING id;
  END
$$ LANGUAGE PLpgSQL;

SELECT *
FROM plpgsql_fun();

Answer by Idris Rhodes

Minimal SQL file that can be loaded into a clean database:,You can use the function name to disambiguate the field in postgres, e.g.:,Not related but if it can be asked here; I’m using uuid’s, and the following :, question (Please suggest another title, it could be wrong, i based it on the intuition i have about the issue)

create function sc.authenticate(_email text) returns sc.jwt_token as $$
    declare
      account sc.users;
    BEGIN
      IF EXISTS (select 1 from schema.table where sc.users.email = _email) THEN
        select a.* into account
        from sc.users as a
        where a.email = $1;
        return ('postgraphql_user_public', account.id)::sc.jwt_token;
      ELSE
        insert into sc.users(email)
        values ( _email)
        returning * into account;
        return ('postgraphql_user_public', account.id)::sc.jwt_token;
      END IF;
    END;
    $$ language plpgsql strict security definer;

Answer by Coen Lamb

I have a small project with Asp.Net Core 3.1 and using PostgreSQL database. Now I want to get all ticket of a member so I created a function database as below:,Asp.net core error: Unable to create an object of type ‘ApplicationDBContext’.,* Please polite comment and respect questions and answers of others.,In the PostgreSQL database, you have to define an alias for the table and using that alias to determine the columns of the table. You can change your database function as below:

 CREATE OR REPLACE FUNCTION public.func_ticket_getbymemberid (
  _memberid integer,
  _fromdate timestamp,
  _todate timestamp
)
RETURNS TABLE (
  id integer,
  memberid integer,
  createddate timestamp,
  score integer
) AS
$body$
BEGIN
    RETURN QUERY
    SELECT id, memberid, createddate, score
    FROM racinggameticket
    WHERE memberid = _memberid AND createddate between _fromdate AND _todate;
END
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;

Answer by Nyla Blair

Example Postgres Log Output:,This error occurs when the referenced column can’t be resolved unambiguously. This
may occur when you have two tables that have columns with the same name.,Fix your query to include the specific table or view that the column belongs to.,In the example above, you would resolve this by adding an explicit table name
like this:

ERROR: column reference "z" is ambiguous at character 8
STATEMENT: SELECT z FROM x, y