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

Dung Do Tien Oct 01 2021 152

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.

Related Q&A May You Like

  1. GitHub remote: Support for password authentication was removed on August 13, 2021
  2. Error remote: Repository not found on GitHub when clone
  3. The SDK 'Microsoft.NET.Sdk.Web' specified could not be found
  4. Unable to prepare context: unable to evaluate symlinks in Dockerfile in Asp.net Core
  5. Connection refused 127.0.0.1:3306 in .Net Core and MySql
  6. The operation failed as details for project could not be loaded in Asp.net Core
  7. Cannot implicitly convert type 'bool' to 'System.Threading.Tasks.Task<bool>' in C# Asp.Net Core
  8. Assets file 'project.assets.json' not found in Asp.net Core
  9. Eror the DbContext of type cannot be pooled in Asp.Net Core
  10. Error field doesn't have a default value in FE Core & MySql
  11. Your startup project doesn't reference Microsoft.EntityFrameworkCore.Design
  12. How to set GIT Username and email global in local computer?
  13. How to undo merge GIT with some conflicts, still not pushed?
  14. Denied: access forbidden in gitlab cicd with .net 6
  15. Git error fatal: cannot do a partial commit during a merge
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.

Most Quizs Popular

Start Test

Javascript - Functions

Start Test

Asp.Net Core - Exam test 01

Start Test

Asp.Net Core - Exam test 02

Start Test

C# - Level Beginner

Popular Tips

This site uses cookies so that we can remember you and understand how you interact with our website. This allows us to improve and customize your browsing experience. See our Cookies Policy.
X Close