From 2358fae57505021818e2119e3d705da14e6cb502 Mon Sep 17 00:00:00 2001 From: Daniele Varrazzo Date: Sun, 22 May 2022 02:10:22 +0200 Subject: [PATCH] fix: fix ambiguous query to fetch enum details The previous query happened to work, but was relying on a joined table to maintain the order, which is not a documented behaviour. --- psycopg/psycopg/_typeinfo.py | 21 +++++++++++---------- 1 file changed, 11 insertions(+), 10 deletions(-) diff --git a/psycopg/psycopg/_typeinfo.py b/psycopg/psycopg/_typeinfo.py index 173691332..9987ee5b2 100644 --- a/psycopg/psycopg/_typeinfo.py +++ b/psycopg/psycopg/_typeinfo.py @@ -323,17 +323,18 @@ class EnumInfo(TypeInfo): cls, conn: "Union[Connection[Any], AsyncConnection[Any]]" ) -> str: return """\ -SELECT - t.typname AS name, t.oid AS oid, t.typarray AS array_oid, - array_agg(x.enumlabel) AS labels -FROM pg_type t -LEFT JOIN ( - SELECT e.enumtypid, e.enumlabel - FROM pg_enum e +SELECT name, oid, array_oid, array_agg(label) AS labels +FROM ( + SELECT + t.typname AS name, t.oid AS oid, t.typarray AS array_oid, + e.enumlabel AS label + FROM pg_type t + LEFT JOIN pg_enum e + ON e.enumtypid = t.oid + WHERE t.oid = %(name)s::regtype ORDER BY e.enumsortorder -) x ON x.enumtypid = t.oid -WHERE t.oid = %(name)s::regtype -GROUP BY t.typname, t.oid, t.typarray +) x +GROUP BY name, oid, array_oid """ -- 2.47.2