42803

At a glance

42803 is grouping_error: a grouped query projects a value that is neither grouped nor aggregated. The case groups category while selecting label.

Meaning

The analyzer must assign one value to each selected expression per group. The fixed message is column "%s.%s" must appear in the GROUP BY clause or be used in an aggregate function. PostgreSQL can allow an otherwise ungrouped column when the inspected relation is proven functionally dependent on the grouping columns; the fixed check_functional_grouping path requires the GROUP BY columns to contain every column of that table's primary key, so this is not a blanket exception for any unique-looking expression. The selected VALUES relation has no such table constraint. Adding label therefore deliberately changes the report grain from one row per category to one row per (category,label), rather than being a semantics-neutral repair. The selected autocommit error leaves IDLE.

Diagnosis

Check every nonaggregate expression in SELECT, HAVING, and relevant ordering expressions. Inspect the relation's constraints before relying on functional dependency, and decide whether the desired cardinality is one row per category or one row per category/label before editing GROUP BY. Ordered-set aggregate direct arguments have a separate grouped-column rule; the DETAIL for that branch is source-backed below, but it is not the selected ordinary query.

Response

Add the intended expression to GROUP BY or aggregate it with a deliberate rule, then check cardinality. The case repairs with GROUP BY category, label and returns two rows because the two labels are distinct groups. If the business result is one row per category, choose an aggregate or an explicit rule for which label to retain instead. In an explicit transaction, the failed statement leaves the transaction in INERROR; roll it back or use an appropriate savepoint before retrying. The selected autocommit path is the case that returns to IDLE.

Observed diagnostics

The fixed parse_agg.c group is explicit ERROR. The selected primary is column "%s.%s" must appear in the GROUP BY clause or be used in an aggregate function. When the ungrouped variable is an ordered-set aggregate's direct argument, the same source branch adds the source-only DETAIL Direct arguments of an ordered-set aggregate must use only grouped columns.; that condition is guarded by context->in_agg_direct_args and was not produced by the selected ordinary grouped query.

Representative case

The registry uses two (category,label) values, triggers with only GROUP BY category, then repairs with GROUP BY category, label ORDER BY ... and asserts (1,a,1) and (1,b,1).

SELECT category, label, count(*) FROM (VALUES (1, 'a'), (1, 'b')) AS grouping_rows(category, label) GROUP BY category;
SELECT category, label, count(*) FROM (VALUES (1, 'a'), (1, 'b')) AS grouping_rows(category, label) GROUP BY category, label ORDER BY category, label;

The selected 18.6 and 10.21 runs passed SQLSTATE, severity, state/recovery, repair, cleanup, and isolated-target stop assertions. See case JSON and authored evidence; private manifest and registry hashes are recorded there.

Versions

The locked catalogue contains this condition from the 7.4 presence bound through the listed snapshots. The selected natural case passed on PostgreSQL 18.6 and 10.21; that bounded result does not infer every intermediate release or every source branch.

Related

• 42804
• 42703

Sources

• src.errcodes.REL_18_6 — fixed definition at 724edf9bde9d356724ad384a2e196edc3c9f80f7; SHA-256 6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba (source).
• src.grouping-error.18.6 — src/backend/parser/parse_agg.c lines 1552–1558 at 724edf9bde9d356724ad384a2e196edc3c9f80f7; blob SHA-256 6e80a805fddbfc9c7e0f3047b348bf992a20c99f16ce7fee89c7067753027f72 (source).
• src.grouping-error.10.23 — src/backend/parser/parse_agg.c lines 1356–1362 at 02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4; blob SHA-256 4c7e12bba2ffd465eb55d22c2adc593afa97e5a5f9a93045a882afab1401cb28 (source).
• src.grouping-functional-dependency.18.6 — src/backend/catalog/pg_constraint.c lines 1728–1778 at 724edf9bde9d356724ad384a2e196edc3c9f80f7; blob SHA-256 0aa324acd355da955f8eb6cec17f1a0138277a8bebd2c6e999cd2a603d3bfa01 (source).
• src.grouping-functional-dependency.10.23 — src/backend/catalog/pg_constraint.c lines 1057–1107 at 02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4; blob SHA-256 c90dff2d1d863e6b43102d762cec6e08b6c4ba40458045b525a14b49865d8a65 (source).
• src.calls.REL_18_6 / src.calls.REL_10_23 — fixed call scans, SHA-256 9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf / 00d16d3eb01b71ccf1b245c8f3102f9d0ec9f36fb02777b8dd1b99fcb263040c.
• manifest.42803 / snippet-registry.42803 — hashes are recorded in evidence/42803.json and each runtime record.

Source evidence

Evidence belongs to the frozen source and runtime versions listed here. It is not a runtime verification of the selected manual version.

• src/backend/utils/errcodes.txt · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• raw/calls/REL_18_6.jsonl · ·
• raw/calls/REL_10_23.jsonl · ·
• src/backend/parser/parse_agg.c · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• src/backend/parser/parse_agg.c · REL_10_23 ·
  02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4
• src/backend/catalog/pg_constraint.c · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• src/backend/catalog/pg_constraint.c · REL_10_23 ·
  02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4
• verify/cases/42803/cases.json · ·
• verify/cases/42803/snippets.json · ·

Message templates

column "%s.%s" must appear in the GROUP BY clause or be used in an aggregate function

Direct arguments of an ordered-set aggregate must use only grouped columns.

Reproduction & repair cases

Recorded runtime evidence

{
  "diagnostic": {
    "text": "column \"grouping_rows.label\" must appear in the GROUP BY clause or be used in an aggregate function\nLINE 1: SELECT category, label, count(*) FROM (VALUES (1, 'a'), (1, ...\n                         ^",
    "context": null,
    "severity": "ERROR",
    "sqlstate": "42803",
    "table_name": null,
    "column_name": null,
    "schema_name": null,
    "source_file": "parse_agg.c",
    "source_line": "1558",
    "message_hint": null,
    "datatype_name": null,
    "exception_type": "GroupingError",
    "internal_query": null,
    "message_detail": null,
    "constraint_name": null,
    "message_primary": "column \"grouping_rows.label\" must appear in the GROUP BY clause or be used in an aggregate function",
    "source_function": "substitute_grouped_columns_mutator",
    "internal_position": null,
    "statement_position": "18",
    "severity_nonlocalized": "ERROR"
  },
  "repair_rows": [
    [
      1,
      "a",
      1
    ],
    [
      1,
      "b",
      1
    ]
  ],
  "failed_status": "IDLE",
  "repair_status": "IDLE",
  "connection_autocommit": true
}

{
  "diagnostic": {
    "text": "column \"grouping_rows.label\" must appear in the GROUP BY clause or be used in an aggregate function\nLINE 1: SELECT category, label, count(*) FROM (VALUES (1, 'a'), (1, ...\n                         ^",
    "context": null,
    "severity": "ERROR",
    "sqlstate": "42803",
    "table_name": null,
    "column_name": null,
    "schema_name": null,
    "source_file": "parse_agg.c",
    "source_line": "1362",
    "message_hint": null,
    "datatype_name": null,
    "exception_type": "GroupingError",
    "internal_query": null,
    "message_detail": null,
    "constraint_name": null,
    "message_primary": "column \"grouping_rows.label\" must appear in the GROUP BY clause or be used in an aggregate function",
    "source_function": "check_ungrouped_columns_walker",
    "internal_position": null,
    "statement_position": "18",
    "severity_nonlocalized": "ERROR"
  },
  "repair_rows": [
    [
      1,
      "a",
      1
    ],
    [
      1,
      "b",
      1
    ]
  ],
  "failed_status": "IDLE",
  "repair_status": "IDLE",
  "connection_autocommit": true
}