INSERT statement gives column reference is ambiguous error when used in Postgres function

Question

I have the following function in Postgres for inserting values to a table employee_leaves and the table has the following structure

id[PK]	emp_id[varchar]	lt_id[int]	from_dt[date]	from_session_id[int]	to_dt[Date]	to_session_id[int]	reasons[varchar]	contact_details[varchar]	status[int]	notify_users[jsonb]	pending_with[int]	other_info[jsonb]	created_by[int]	created_on[date]	last_updated_by[int]	last_updated_on[date]

The function is as follows:

-- FUNCTION: company.lib.org.create_edit_employee_leave(json)

-- DROP FUNCTION IF EXISTS "company.lib.org".create_edit_employee_leave(json);

CREATE OR REPLACE FUNCTION "company.lib.org".create_edit_employee_leave(
    params json)
    RETURNS json
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
--select"company.lib.org".create_edit_employee_leave('{"emp_id":292, "lt_id":1, "from_dt":"2022-01-10", "from_session_id":1,"to_dt":"2022-01-11" ,"to_session_id":"2", "reasons":"Leave Reason", "contact_details":"8884514965", "status":1, "notify_users":[1,2,3] ,"pending_with":3, "other_info":"{}"," created_by":"123", "last_updated_by":13, "last_updated_on":"2022-01-20 00:00:00"}');

declare
        emp_id character varying(50);
        query text;
        cond text;  
        response json;
        message character varying(50);

        lt_id smallint;
        from_dt date;
        from_session_id smallint;
        to_dt date;
        to_session_id smallint;
        reasons character varying(1024);
        contact_details character varying(1024);
        status smallint;
        notify_users json;
        pending_with character varying(50);
        other_info json;
        created_by integer;
        created_on timestamp with time zone;
        last_updated_by integer;
        last_updated_on timestamp with time zone;
        
        begin
        emp_id = (params->>'emp_id');
        lt_id = (params->>'lt_id');
        from_dt = (params->>'from_dt');
        from_session_id = (params->>'from_session_id');
        to_dt =  (params->>'to_dt');
        to_session_id = (params->>'to_session_id')::smallint;
        reasons = (params->>'reasons');
        contact_details = (params->>'contact_details');
        status = (params->>'status')::smallint;
        notify_users = COALESCE((params ->> 'notify_users')::jsonb,'{}'::jsonb);
        pending_with=(params->>'pending_with');
        other_info = COALESCE((params ->> 'other_info')::jsonb,'{}'::jsonb);
        created_by = (params->>'created_by')::integer;
        created_on = (params->>'created_on')::date;
        last_updated_by = (params->>'last_updated_by')::integer;
        last_updated_on = (params->>'last_updated_on')::date;
        message = '';
        raise info 'leave_application_id %',emp_id;

        if (params->>'action') = 'C' then
        INSERT INTO "company.lib.org".employee_leaves(
        emp_id, lt_id, from_dt, from_session_id, to_dt, to_session_id, reasons, contact_details, status, notify_users, pending_with, other_info, created_by, created_on, last_updated_by, last_updated_on)
        VALUES (
            emp_id, lt_id, from_dt, from_session_id, to_dt, to_session_id, reasons, contact_details, status, notify_users, pending_with, other_info, created_by, created_on, last_updated_by, last_updated_on
        ) returning emp_id into emp_id;
        end if;
        message = 'New Leave application created successfully';
        
        response = ('{"status": 200, "leave_application_id": "' || emp_id || '", "message":"' || message ||'"}')::json;
    
       return response;
    
    EXCEPTION
        WHEN unique_violation THEN
            response = '{"status": 500, "data": "Unique key violation"}';
        WHEN check_violation THEN
            response = '{"status": 500, "data": "Check violation"}';
        WHEN no_data_found THEN
            response = '{"status": 500, "data": "Check"}';
        WHEN others THEN
            RAISE INFO 'Error Name:%', SQLERRM;
            RAISE INFO 'Error State:%', SQLSTATE;
            response = '{"status": 500, "data": "Unknown exception '|| SQLERRM || '"}'; 
    return response;

    END;
            
$BODY$;

ALTER FUNCTION "company.lib.org".create_edit_employee_leave(json)
    OWNER TO postgres;

Now when I use the following INSERT directly, it is working fine

INSERT INTO "company.lib.org".employee_leaves(
        emp_id, lt_id, from_dt, from_session_id, to_dt, to_session_id, reasons, contact_details, status, notify_users, pending_with, other_info, created_by, created_on, last_updated_by, last_updated_on)
        VALUES (
            '25506', 1, '2020-01-11', 1, '2020-01-12', 2, 'Leave Reasons', '8884514965', 1, '[25509,25336]', 25509, '{}', 25506, '2022-02-06T06:01:31.025Z', 25506, '2022-02-06T06:01:31.025Z'
        )

But when I call function like this,

select "company.lib.org".create_edit_employee_leave(
    (
        '{"action":"C","emp_id":"25506","lt_id":1,"from_dt":"2020-01-11","from_session_id":1,"to_dt":"2020-01-12","to_session_id":2,"reasons":"LeaveReasons","contact_details":"8884514965","status":1,"notify_users":["25509","25336"],"pending_with":25509,"other_info":{},"created_by":"25506","created_on":"2022-02-06T06:08:11.531Z","last_updated_by":"25506","last_updated_on":"2022-02-06T06:08:11.531Z"}'
    )::json

)

It is giving an error like

INFO:  Error Name:column reference "emp_id" is ambiguous
INFO:  Error State:42702

As I have shown in the table structure, the column exists in the table and there are no other columns with the same name. However, I have other tables with the same column name and there is a foreign key constraint that refers to the employee table and the emp_id in that table. Also, this is an INSERT statement. So I am not sure what went wrong here. If someone can tell me what is the error here, that would be helpful.

Does this answer your question? Postgresql column reference "id" is ambiguous — TylerH
– TylerH, Commented Oct 20, 2022 at 13:37

M-Raw · Accepted Answer · 2022-02-06 06:57:15Z

1

ambiguous is caused by having two tables with identical column names like users.active and profile.active it is clear when your using users or profiles table alone, but if you join them then you have two active columns that belong to different tables, so you need to clarify for the query what you are trying to use: is it users.active or profiles.active explicitly

answered Feb 6, 2022 at 6:57

M-Raw

8395 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user18098820 · Accepted Answer · 2022-02-06 07:03:48Z

0

I think that it is the line

returning emp_id into emp_id;

which is causing the problem. You could change the name the local variable in the procedure, insert_emp_id for example.

answered Feb 6, 2022 at 7:03

user18098820

Comments

jian · Accepted Answer · 2022-02-06 15:49:55Z

By default, PL/pgSQL will report an error if a name in an SQL statement could refer to either a variable or a table column. You can fix such a problem by renaming the variable or column, or by qualifying the ambiguous reference, or by telling PL/pgSQL which interpretation to prefer.

https://www.postgresql.org/docs/current/plpgsql-implementation.html

from here> https://www.postgresql.org/docs/12/functions-json.html. ->> return text type. But you declare emp_id character varying(50);. I am not so sure. I think it's fine. but maybe future will be a issue. For example:

CREATE OR REPLACE FUNCTION test_txt2()
RETURNS void
LANGUAGE 'plpgsql'
AS $BODY$
declare a varchar(1); 
begin
    RAISE INFO 'Alas we are at the end of our journey';
    a = 'test'::text;
    RAISE INFO 'Alas we are at the end of our journey111';
end;
$BODY$;

It will fail.

INFO:  Alas we are at the end of our journey
ERROR:  value too long for type character varying(1)
CONTEXT:  PL/pgSQL assignment "a = 'test'::text"
PL/pgSQL function test_txt2() line 5 at assignment

If the expression's result data type doesn't match the variable's data type, the value will be coerced as though by an assignment cast (see Section 10.4). If no assignment cast is known for the pair of data types involved, the PL/pgSQL interpreter will attempt to convert the result value textually, that is by applying the result type's output function followed by the variable type's input function.

further info.https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT

Update: I deep dived a little bit. From here https://www.postgresql.org/docs/12/errcodes-appendix.html, you get the error code. Then I searched the postgresql source code. Then I located the final explanation page: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob_plain;f=src/backend/parser/README;hb=HEAD

Collectives™ on Stack Overflow

INSERT statement gives column reference is ambiguous error when used in Postgres function

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related