How to change a default setting temporarily in PostgreSQL?

Question

In my psql script all foreign key references include the clause on delete cascade on update cascade, because the default is on delete no action on update no action:

create table x (
  y bigint
    references schema1.table1(column1) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE
);

This construct is repeated hundreds of times.

So I would rather like to write:

create table x (
  y bigint 
    references schema1.table1(column1)
);

How to change the default in Postgres temporarily?

You can also run a plpgsql loop over information_schema that dynamically constructs and executes an adequate alter table..alter column on all non-system objects using a given setting, default or otherwise. — Zegarek
– Zegarek, Commented Oct 27, 2024 at 13:03

Laurenz Albe · Accepted Answer · 2024-10-27 06:52:49Z

2

No, you will have to repeat the text every time.

I understand your desire; I often wish that NOT NULL were the default for columns. But such a feature would be dangerous. Any configuration that alters the semantics of SQL is problematic, because it leads to confusion. Imagine there were such a setting, and you posted your SQL code in — say — a Stackoverflow question: we'd always have to think of clarifying how you configured the database before we could even understand your code.

You don't have to write your table definitions every day. Use a good text editor and define a macro.

answered Oct 27, 2024 at 6:52

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Code4R7 Over a year ago

Fair enough answer. Thanks.

Collectives™ on Stack Overflow

How to change a default setting temporarily in PostgreSQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related