Question 89533 in PostgreSQL Question posted in
The official documentation can be found here.

Difference between the 2 postgres statement – Postgresql

ALTER TABLE rejected_events.rejected_events
    ADD COLUMN tenant_id smallint NOT NULL DEFAULT 0;

ALTER TABLE rejected_events.rejected_events
    ALTER COLUMN tenant_id DROP DEFAULT;

In my product, tenant_id is set to 0 and then removed in next command as well.
I wonder what would be the intention of this strange stuff.

Tags:

2

Answers


  1. 0

    Is this in a database migration/upgrade script?

    The column is tenant_id so if previously there was only one user/tenant in the database it gives that tenant an id of 0.

    In the future though you want any queries to specify a tenant. So, you keep the NOT NULL but drop the default.

    Login or Signup to reply.
  2. 0

    This is done because:

    alter table varchar_test add column t_fld integer NOT NULL;
ERROR:  column "t_fld" of relation "varchar_test" contains null values

    A DEFAULT is always specified for a column, by default that value is NULL per docs CREATE TABLE

    DEFAULT default_expr

    The DEFAULT clause assigns a default data value for the column whose column definition it appears within. The value is any variable-free expression (in particular, cross-references to other columns in the current table are not allowed). Subqueries are not allowed either. The data type of the default expression must match the data type of the column.

    The default expression will be used in any insert operation that does not specify a value for the column. If there is no default for a column, then the default is null.

    If you ran:

    ALTER TABLE rejected_events.rejected_events
    ADD COLUMN tenant_id smallint NOT NULL

    you would get the error I showed above. Adding the DEFAULT 0 satisfies the NOT NULL constraint by adding 0 to the field for each existing row in the table you are altering. Then ALTER COLUMN tenant_id DROP DEFAULT; removes the DEFAULT for subsequent INSERTs. At that point you have to make sure a NON-NULL value is specified for the column.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search