Error 42702: column reference "id" is ambiguous in PostgreSQL

Dung Do Tien Oct 01 2021 4

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:

 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;

And when I try to execute in code c# by using Dapper I got an exception throw 42702: column reference "id" is ambiguous.

 42702: column reference "id" is ambiguous
at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Threading.Tasks.ValueTask`1.get_Result()
at Npgsql.NpgsqlDataReader.<NextResult>d__46.MoveNext()
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.NpgsqlDataReader.NextResult()
at Npgsql.NpgsqlCommand.<ExecuteDbDataReader>d__100.MoveNext()
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at Npgsql.NpgsqlCommand.ExecuteReader()
at Project.Data.RacingGameTicket.RacingGameDal.GetTicketByMember(Int32 memberId, DateTime fromDate, DateTime toDate) in E:\Project\Philip\Main\Source_New\Admin\Source\game\frontend\AutoCore\Project.Data\RacingGameTicket\RacingGameDal.cs:line 53
at Project.BLL.RacingGameTicket.RacingGameBLL.GetTicketByMember(Int32 memberId, DateTime fromDate, DateTime toDate) in E:\Project\Philip\Main\Source_New\Admin\Source\game\frontend\AutoCore\Project.BLL\RacingGameTicket\RacingGameBLL.cs:line 27
at AutoPhilippine.Controllers.RacingGameController.GetTicketByMember() in E:\Project\Philip\Main\Source_New\Admin\Source\game\frontend\AutoPhilippine\AutoPhilippine\Controllers\RacingGameController.cs:line 89 

This is my racinggameticket table

  --- racinggameticket
     -- id integer
     -- memberid integer
     -- createddate timestamp
     -- score integer

My select command is very simple and the table does not have two id columns. So I really not understand why them ambiguous.

Thank you for any suggestions.

Have 2 answer(s) found.
  • D

    Diego Ganchozo Oct 01 2021

    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:

     SELECT r.id, r.memberid, r.createddate, r.score
        FROM racinggameticket r
        WHERE r.memberid = _memberid AND r.createddate between _fromdate AND _todate;

    I hope this answer is all you need.

  • R

    Ruchi Gupta Oct 01 2021

    Select command in Postgre database is not same with Mssql server database.  In Mssql you do not need to define an alias for a table but Postgres is required. So you can add more alias as below:

     SELECT game.id, game.memberid, game.createddate, game.score
        FROM racinggameticket game
        WHERE game.memberid = _memberid AND game.createddate between _fromdate AND _todate;

    And it worked for you. 

    Note: This issue is not related to Asp.net Core or Dapper.

Leave An Answer
* NOTE: You need Login before leave an answer

* Type maximum 2000 characters.

* All comments have to wait approved before display.

* Please polite comment and respect questions and answers of others.

Popular Tips

X Close