ALTER_OPERATOR(7pg17)

💅 ALTER OPERATOR — change the definition of an operator

okay so bestie, welcome to the ALTER OPERATOR page from the PostgreSQL 17.11 documentation 📖✨ (yes, that's 7pg17 energy, you're in the right place, pull up a chair 🪑).

This is the page you come to when you've already got an operator and you're like "hmm, actually, let's tweak it." We do not gatekeep operator glow-ups here. 💖

---

✨ SYNOPSIS ✨

here's the whole menu, copy-paste ready, no cap:

ALTER OPERATOR name ( { left_type | NONE } , right_type )
    OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }

ALTER OPERATOR name ( { left_type | NONE } , right_type )
    SET SCHEMA new_schema

ALTER OPERATOR name ( { left_type | NONE } , right_type )
    SET ( {  RESTRICT = { res_proc | NONE }
           | JOIN = { join_proc | NONE }
           | COMMUTATOR = com_op
           | NEGATOR = neg_op
           | HASHES
           | MERGES
          } [, ... ] )

three whole flavours: hand it to a new owner, yeet it into a new schema, or fiddle with its guts. pick your fighter 🥊

---

🌸 DESCRIPTION 🌸

ALTER OPERATOR changes the definition of an operator. that's literally it, that's the whole vibe. 🦋

but here's the peasant-gate, bestie, listen up: you must own the operator to use ALTER OPERATOR. periodt. 👑

and if you're changing the owner? then you gotta be able to SET ROLE to that new owning role, and that role needs CREATE privilege on the operator's schema. both things. no shortcuts. 🙅‍♀️

why tho? because these restrictions enforce that altering the owner doesn't do anything you couldn't already do by dropping and recreating the operator. it's giving "no sneaky power-ups." 🙈

however — and this is the iconic part — a superuser can alter ownership of any operator anyway. main character energy, we stan. 👑✨

---

🎀 PARAMETERS 🎀

okay now the actual knobs and dials, let's go:

• name — the name of an existing operator, optionally schema-qualified. you know, the whole myschema.myop situation. 🏷️
• left_type — the data type of the operator's left operand. write NONE if the operator has no left operand (yes, unary operators exist, they're valid, they're beautiful 💅).
• right_type — the data type of the operator's right operand.
• new_owner — the new owner of the operator. handing over the crown 👑
• new_schema — the new schema for the operator. moving day 📦
• res_proc — the restriction selectivity estimator function for this operator; write NONE to remove the existing selectivity estimator. 🗑️
• join_proc — the join selectivity estimator function for this operator; write NONE to remove the existing selectivity estimator. also 🗑️
• com_op — the commutator of this operator. can only be changed if the operator does not have an existing commutator. 🚫
• neg_op — the negator of this operator. can only be changed if the operator does not have an existing negator. 🚫
• HASHES — indicates this operator can support a hash join. can only be enabled, not disabled. one-way street, bestie 🛣️
• MERGES — indicates this operator can support a merge join. can only be enabled, not disabled. same deal 🛣️

---

🦋 NOTES 🦋

refer to Section 36.14 and Section 36.15 for further information. (go read them, they're the lore 📚)

okay so this next bit is lowkey important, pay attention:

since commutators come in pairs that are commutators of each other, ALTER OPERATOR SET COMMUTATOR will also set the commutator of the com_op to be the target operator. 🤝

likewise, ALTER OPERATOR SET NEGATOR will also set the negator of the neg_op to be the target operator. 🔄

so like… therefore, you must own the commutator or negator operator as well as the target operator. both of them. no borrowing your friend's operator without permission. 🙅‍♀️💀

---

🔥 EXAMPLES 🔥

change the owner of a custom operator a @@ b for type text:

ALTER OPERATOR @@ (text, text) OWNER TO joe;

change the restriction and join selectivity estimator functions of a custom operator a && b for type int[]:

ALTER OPERATOR && (int[], int[]) SET (RESTRICT = _int_contsel, JOIN = _int_contjoinsel);

mark the && operator as being its own commutator:

ALTER OPERATOR && (int[], int[]) SET (COMMUTATOR = &&);

look at that self-commutating icon. obsessed. 🫶

---

💀 COMPATIBILITY 💀

there is no ALTER OPERATOR statement in the SQL standard. nope. not a thing. this is a PostgreSQL original, we made it ourselves 💅✨

---

👑 SEE ALSO 👑

• CREATE OPERATOR (CREATE_OPERATOR(7)) — for making the operator in the first place 🌱
• DROP OPERATOR (DROP_OPERATOR(7)) — for when it's time to say bye 🗑️👋

---

and that's the whole page, bestie, you ate it up fr fr 💖🌸🦋

cute little sign-off: this was PostgreSQL 17.11, dated 2026, and the page is ALTER OPERATOR(7). go forth and alter responsibly 🎀✨