How to fix Ambiguous column in PostgreSQL

Review your query to find all tables involved in the JOIN. Check if any column names appear in multiple tables. Use PostgreSQL documentation or run a query like:

SELECT column_name FROM information_schema.columns WHERE table_name IN ('table1', 'table2') ORDER BY column_name;

This shows which columns are duplicated across your tables.

Modify your query to use short aliases for each table. This makes qualification clearer:

SELECT * FROM users AS u
JOIN orders AS o ON u.id = o.user_id;

Using single letters (u, o, p) or meaningful abbreviations (usr, ord) both work well.

Update your SELECT, WHERE, GROUP BY, ORDER BY, and HAVING clauses to prefix columns with their table alias:

Before (causes error):

SELECT id, name, status FROM users
JOIN orders ON users.id = orders.user_id
WHERE status = 'active';

After (fixed):

SELECT u.id, u.name, o.status FROM users AS u
JOIN orders AS o ON u.id = o.user_id
WHERE o.status = 'active';

Note how every column reference now includes its table alias prefix.

If you're writing a function where parameter names conflict with column names, use one of these approaches:

Option 1: Rename the parameter

CREATE FUNCTION update_user(p_id INT, p_name TEXT) AS $$
BEGIN
  UPDATE users SET name = p_name WHERE id = p_id;
END;
$$ LANGUAGE plpgsql;

Option 2: Qualify the column reference with the table name

CREATE FUNCTION update_user(id INT, name TEXT) AS $$
BEGIN
  UPDATE users u SET name = update_user.name WHERE u.id = update_user.id;
END;
$$ LANGUAGE plpgsql;

Option 3: Use block labels

CREATE FUNCTION update_user(id INT, name TEXT) AS $$
DECLARE
BEGIN <<outer_block>>
  UPDATE users SET name = outer_block.name WHERE id = outer_block.id;
END outer_block;
$$ LANGUAGE plpgsql;

Run your updated query in psql or your database client to verify it executes without errors:

SELECT u.id, u.name, o.id, o.total 
FROM users AS u
JOIN orders AS o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.id, o.id;

If the query runs successfully, the ambiguous column error is resolved. Check that the results match your expectations.