0

I am new to Postgres and wondering if it is possible to create a named default-value-constraint in Postgresql. I found something similar in SqlServer Can I create a named default constraint in an add column statement in SQL Server? but couldn't figure the same for Postgresql.

To give some context, I am trying to add a column to a table and while adding I am trying to add this named constraint to the column.

Any help would be appreciated.

Thanks in advance

Rahul
  • 91
  • 1
  • 11
  • Postgres (and essentially every other database) has default _values_. There is no such thing as a "default constraint" in (standard SQL). So, no you can't give a default _value_ a name. –  Jun 15 '21 at 19:15

1 Answers1

0

Like this:

\d foo 
                 Table "public.foo"
  Column  |  Type   | Collation | Nullable | Default 
----------+---------+-----------+----------+---------
 fooid    | integer |           |          | 
 foosubid | integer |           |          | 
 fooname  | text    |           |          | 

alter  table foo add column bar varchar constraint u2_idx UNIQUE;
ALTER TABLE

 \d foo
                      Table "public.foo"
  Column  |       Type        | Collation | Nullable | Default 
----------+-------------------+-----------+----------+---------
 fooid    | integer           |           |          | 
 foosubid | integer           |           |          | 
 fooname  | text              |           |          | 
 bar      | character varying |           |          | 
Indexes:
    "u2_idx" UNIQUE CONSTRAINT, btree (bar)

UPDATE. To get closer to what the post you linked to shows:


alter  table foo add column baz varchar default ''  constraint nn_constraint NOT NULL;

\d foo
                             Table "public.foo"
  Column  |       Type        | Collation | Nullable |        Default        
----------+-------------------+-----------+----------+-----------------------
 fooid    | integer           |           |          | 
 foosubid | integer           |           |          | 
 fooname  | text              |           |          | 
 bar      | character varying |           |          | 
 baz      | character varying |           | not null | ''::character varying
Indexes:
    "u2_idx" UNIQUE CONSTRAINT, btree (bar)

Though the NOT NULL constraint does not actually get named.

Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
  • 2
    What SQL Server calls a "default constraint" is simply a "default _value_" in every other database. –  Jun 15 '21 at 19:16
  • @Adrian Klaver I think it is in line with what a_horse_with_no_name has put in comment. It is just default value. It can't be given a name unlike sqlserver. Correct me if I am wrong. – Rahul Jun 16 '21 at 06:50
  • If you are talking about a `DEFAULT` value, then that cannot be named. If as in your question, 'add this named constraint to the column' then yes that can be done. It comes down to what you are defining as a constraint. – Adrian Klaver Jun 16 '21 at 14:17