ALTER_VIEW(7pg18)

✨ ALTER VIEW ✨

okay bestie, this is the ALTER VIEW page from the PostgreSQL 18.6 manual, and it's literally all about changing the definition of a view. no cap. 💅

---

🌸 NAME 🌸

ALTER_VIEW — change the definition of a view

---

🎀 SYNOPSIS 🎀

ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name DROP DEFAULT
ALTER VIEW [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER VIEW [ IF EXISTS ] name RENAME [ COLUMN ] column_name TO new_column_name
ALTER VIEW [ IF EXISTS ] name RENAME TO new_name
ALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema
ALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] )
ALTER VIEW [ IF EXISTS ] name RESET ( view_option_name [, ... ] )

---

💖 DESCRIPTION 💖

So ALTER VIEW changes all the auxiliary properties of a view. that's the side-quest stuff, not the main plot. 🫶

heads up: if you actually wanna mess with the view's defining query, that's a whole different vibe — use CREATE OR REPLACE VIEW instead, bestie. ✨

Now. permission check, because of course there's a permission check. 🔥

• You must own the view to use ALTER VIEW. no ownership, no slay.
• Wanna change a view's schema? you also need CREATE privilege on the new schema. both. periodt.
• Wanna change the owner? you need to be able to SET ROLE to the new owning role, and that role needs CREATE privilege on the view's schema.

the reason for all these restrictions: they make sure that altering the owner doesn't let you do anything you couldn't already do by dropping and recreating the view. so it's giving… security. iconic. 👑

tiny loophole though: a superuser can alter ownership of any view anyway. main character energy fr fr. 💀

---

🌈 PARAMETERS 🌈

• name — the name of an existing view. optionally schema-qualified, live your truth.
• column_name — the name of an existing column.
• new_column_name — the new name for an existing column. glow-up moment ✨
• IF EXISTS — do not throw an error if the view doesn't exist. instead, a notice gets issued. gentle rejection, not a crash out. 🦋
• SET/DROP DEFAULT — these forms set or remove the default value for a column. here's the tea: a view column's default value gets substituted into any INSERT or UPDATE command whose target is the view, before applying any rules or triggers for the view. so the view's default will take precedence over any default values from underlying relations. it wins. 👑
• new_owner — the user name of the new owner of the view.
• new_name — the new name for the view.
• new_schema — the new schema for the view.

💅 SET ( view_option_name [= view_option_value] [, ... ] ) / RESET ( view_option_name [, ... ] )

sets or resets a view option. currently supported options are, and yes there are exactly three:

• check_option (enum) — changes the check option of the view. the value must be local or cascaded. those are the only two. don't try anything else. 🙈
• security_barrier (boolean) — changes the security-barrier property of the view. the value must be a Boolean value, such as true or false.
• security_invoker (boolean) — changes the security-invoker property of the view. the value must be a Boolean value, such as true or false.

---

⭐ NOTES ⭐

for historical reasons (iykyk), ALTER TABLE can be used with views too. but the only variants of ALTER TABLE that are allowed with views are equivalent to the ones shown above. that's it. that's the whole list. 🧚

---

🍓 EXAMPLES 🍓

wanna rename the view foo to bar? say less:

ALTER VIEW foo RENAME TO bar;

wanna attach a default column value to an updatable view? okay bestie, watch this:

CREATE TABLE base_table (id int, ts timestamptz);
CREATE VIEW a_view AS SELECT * FROM base_table;
ALTER VIEW a_view ALTER COLUMN ts SET DEFAULT now();
INSERT INTO base_table(id) VALUES(1);  -- ts will receive a NULL
INSERT INTO a_view(id) VALUES(2);  -- ts will receive the current time

see how the two inserts got different results? that's the whole point. 💖

---

🌸 COMPATIBILITY 🌸

ALTER VIEW is a PostgreSQL extension of the SQL standard. it's ours, we made it, we're keeping it. 💜

---

💖 SEE ALSO 💖

• CREATE VIEW (CREATE_VIEW(7))
• DROP VIEW (DROP_VIEW(7))

---

okay that's the whole page, bestie!! you ate. 💅✨

this has been PostgreSQL 18.6, dated 2026 — mwah, go alter some views, ily 🫶🎀