PostgreSQL errors, explained
The error messages people hit most, what Postgres actually means, and the fix, with a live editor to try it. Find yours below.
- ERROR: column "customers.active_status" must appear in the GROUP BY clause or be used in an aggregate functionGROUP BY squashes many rows into one row per group. Postgres is asking: this column has several different values inside each group, so which one should it show? Either group by that column too, or tell Postgres how to combine its values.
- ERROR: column "discounted" does not existPostgres looked through every table in your FROM clause and found no column with that name. The name may be misspelled, it may not exist yet at that point in the query, or Postgres may be reading something as a column name that you meant as a value.
- ERROR: relation "customer" does not existRelation is Postgres's word for a table (or a view). The table name in your FROM or JOIN doesn't match any table Postgres can see right now. Either the name is off, it lives in another schema, or it was never created in this database.
- ERROR: column reference "id" is ambiguousYou joined tables that share a column name, such as id or name, and then used that name on its own. Postgres sees two candidates and won't pick one for you. Add the table alias in front of the column and the error goes away.
- ERROR: missing FROM-clause entry for table "c"Your query mentions a table or alias, like c.name, but that table was never brought into the query with FROM or JOIN. Postgres only knows about tables you've listed, so it has nowhere to look up that column.
- ERROR: aggregate functions are not allowed in WHEREWHERE looks at one row at a time, before rows are grouped. SUM(), COUNT() and AVG() need a whole group of rows, so at the WHERE stage there is nothing for them to add up yet. Group filters belong in HAVING.
- ERROR: window functions are not allowed in WHEREWindow functions are worked out near the end of the query, after WHERE has already picked the rows. So WHERE can't use them. The fix is always the same: calculate the window function in an inner query, give it a name, and filter on that name in the outer query.
- ERROR: syntax error at or near "FROM"Postgres read your query word by word and got stuck at the word it shows you. That word is where it noticed the problem, but the actual mistake is often right before it, like an extra comma or a missing one.
- ERROR: syntax error at end of inputPostgres reached the end of your query while it was still waiting for more. Something was opened or started and never finished, so the query is cut off from Postgres's point of view.
- ERROR: operator does not exist: integer = textPostgres is strict about types. It has an = for two integers and an = for two texts, but none for an integer and a text, so it refuses to compare them. Convert one side so both sides have the same type.
- ERROR: invalid input syntax for type numeric: "$1,200.50"Somewhere a text value is being converted to a number or a date, and that value doesn't look like one. Postgres shows you the exact value it choked on in the quotes, like "$1,200.50" or "". Fix or skip that value and the conversion works.
- ERROR: division by zeroOne of the rows in your query divides by 0, and Postgres stops the whole query instead of guessing an answer. Usually it's a ratio where some group has nothing in the bottom part, like a user with no credits or a buyer with no visits.
- ERROR: subquery in FROM must have an aliasA subquery in FROM acts like a temporary table, and older Postgres versions insist that every table has a name. Add a short alias after the closing parenthesis. Postgres 16 and newer (including this site) accept the subquery without one, but naming it is still good practice.
- ERROR: more than one row returned by a subquery used as an expressionYou put a subquery where Postgres expects one single value, like after = or inside the SELECT list. For at least one row, that subquery came back with several rows, and Postgres can't fit several values into one spot.
- ERROR: function round(double precision, integer) does not existROUND(value, 2) exists in Postgres, but only for the numeric type. Your value is a double precision (a float), so Postgres can't find a matching version of ROUND. Convert the value to numeric and it works.
- ERROR: function datediff(date, date) does not existPostgres has no function with that name that accepts those argument types. Most often it's a function from MySQL, SQL Server or SQLite. Postgres can do the same job, it just spells it differently.
- ERROR: COALESCE types text and integer cannot be matchedCOALESCE returns one column, and a column can only have one type. You gave it a text value and a number, so Postgres can't decide what type the result should be. The same rule applies to CASE branches and to the columns of a UNION.
- ERROR: duplicate key value violates unique constraint "orders_pkey"The table has a rule that a column, usually the id, must be unique. Your INSERT or UPDATE would create a second row with a value that's already taken, so Postgres blocks the change. The constraint name in quotes tells you which column it is: orders_pkey is the primary key of orders.
- ERROR: null value in column "signup_date" of relation "customers" violates not-null constraintThe column named in the error is marked NOT NULL, which means every row must have a value there. Your INSERT or UPDATE left it empty, either because you didn't mention the column or because the value you supplied was NULL.
- ERROR: unterminated quoted string at or near "'Canada;"Postgres found an opening single quote and kept reading, looking for the closing one, until the query ran out. Either a closing quote is missing, or an apostrophe inside your text (like in didn't) ended the string early and left a stray quote behind.