How to Add a Column That Doesn't Allow Nulls in a Postgresql Database

How can I add a column that doesn't allow nulls in a Postgresql database?

You have to set a default value.

ALTER TABLE mytable ADD COLUMN mycolumn character varying(50) NOT NULL DEFAULT 'foo';

... some work (set real values as you want)...

ALTER TABLE mytable ALTER COLUMN mycolumn DROP DEFAULT;

PostgreSQL add new not null column and fill with ids from insert statement

demo:db<>fiddle

According to the answers presented here: How can I add a column that doesn't allow nulls in a Postgresql database?, there are several ways of adding a new NOT NULL column and fill this directly.

Basicly there are 3 steps. Choose the best fitting (with or without transaction, setting a default value first and remove after, leave the NOT NULL contraint first and add afterwards, ...)

---

Step 1: Adding new column (without NOT NULL constraint, because the values of the new column values are not available at this point)

ALTER TABLE data ADD COLUMN content_id integer;

Step 2: Inserting the data into both tables in a row:

WITH inserted AS (                -- 1
    INSERT INTO content
    SELECT
        generate_series(
            (SELECT MAX(id) + 1 FROM content),
            (SELECT MAX(id) FROM content) + (SELECT COUNT(*) FROM data)
        ),
        'dummy text'
    RETURNING id
), matched AS (                   -- 2
   SELECT
       d.id AS data_id,
       i.id AS content_id
   FROM (
       SELECT
           id,
           row_number() OVER ()
       FROM data
   ) d 
   JOIN (
      SELECT
          id,
          row_number() OVER ()
      FROM inserted
   ) i ON i.row_number = d.row_number
)                                 -- 3
UPDATE data d
SET content_id = s.content_id
FROM (
    SELECT * FROM matched
) s
WHERE d.id = s.data_id;

Executing several statements one after another by using the results of the previous one can be achieved using WITH clauses (CTEs):

1. Insert data into content table: This generates an integer series starting at the MAX() + 1 value of the current content's id values and has as many records as the data table. Afterwards the new ids are returned
2. Now we need to match the current records of the data table with the new ids. So for both sides, we use row_number() window function to generate a consecutive row count for each records. Because both, the insert result and the actual data table have the same number of records, this can be used as join criterion. So we can match the id column of the data table with the new content's id values
3. This matched data can used in the final update of the new content_id column

Step 3: Add the NOT NULL constraint

ALTER TABLE data ALTER COLUMN content_id SET NOT NULL;

How to alter new not null column in Postgresql?

To avoid your error, two solutions come at once:

BEGIN;
  alter table poll_management.DASHLETS add column name varchar(255);
  update poll_management.DASHLETS as dashlet set name = report.name 
from poll_management.REPORTS as report;
  --mind I removed where, cos you need to update ALL rows to have some avlue
  alter table poll_management.DASHLETS alter column "name" set not null;
END;

and the other:

  alter table poll_management.DASHLETS add column name varchar(255) NOT NULL default 'not set';

how to add not null constraint for a column after dropping it if the table contains data

As described in Pg docs you can do something like this

update table_name set columnname='fill null data' where columnname is null;
ALTER TABLE table_name ALTER COLUMN columnname SET NOT NULL;

ALTER TABLE table_name ALTER COLUMN columnname SET default ' ';

PostgreSQL - Any way I can define a column that will cast empty string to null on insert or update?

No, as @a_horse_with_no_name noted, a check constraint will not help. Here is a trigger definition that should fix your case.

create or replace function emailfix_tf()
returns trigger language plpgsql as
$$
begin
  new.email := nullif(trim(new.email), '');
  return new;
end;
$$;

create trigger users_emailfix_t
before insert or update on users
for each row execute procedure emailfix_tf();

Anyway it's a pity that you have to define a trigger in order to fix data that has been malformed and spoiled by your ORM.

Add a new column to PostgreSQL database, set initial value and set NOT NULL all in one go

Did you try this;

alter table my_table add column my_field1 boolean default false NOT NULL, 
                     add column my_field2 boolean default false NOT NULL;

How to change a PG column to NULLABLE TRUE?

From the fine manual:

ALTER TABLE mytable ALTER COLUMN mycolumn DROP NOT NULL;

There's no need to specify the type when you're just changing the nullability.

insert a NOT NULL column to an existing table

As an option you can initially create Null-able column, then update your table column with valid not null values and finally ALTER column to set NOT NULL constraint:

ALTER TABLE MY_TABLE ADD STAGE INT NULL
GO
UPDATE MY_TABLE SET <a valid not null values for your column>
GO
ALTER TABLE MY_TABLE ALTER COLUMN STAGE INT NOT NULL
GO

Another option is to specify correct default value for your column:

ALTER TABLE MY_TABLE ADD STAGE INT NOT NULL DEFAULT '0'

---

UPD: Please note that answer above contains GO which is a must when you run this code on Microsoft SQL server. If you want to perform the same operation on Oracle or MySQL you need to use semicolon ; like that:

ALTER TABLE MY_TABLE ADD STAGE INT NULL;
UPDATE MY_TABLE SET <a valid not null values for your column>;
ALTER TABLE MY_TABLE ALTER COLUMN STAGE INT NOT NULL;

---

Related Topics