How to Change a Pg Column to Nullable True

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.

ALTER TABLE, set null in not null column, PostgreSQL 9.1

ALTER TABLE person ALTER COLUMN phone DROP NOT NULL;

More details in the manual: http://www.postgresql.org/docs/9.1/static/sql-altertable.html

Altering a column to be nullable

Assuming SQL Server (based on your previous questions):

ALTER TABLE Merchant_Pending_Functions ALTER COLUMN NumberOfLocations INT NULL

Replace INT with your actual datatype.

Change column type and set not null

This should be correct:

ALTER TABLE mytable
    ALTER COLUMN col TYPE character varying(15),
    ALTER COLUMN col SET NOT NULL

Updating integer column with null values in postgres

This should be,

UPDATE table1 
SET column_a = NULL
WHERE column_b = 'XXX';

How to set not null constraint to columns in postgres

You can't provide a list of column in parentheses, you need to use multiple ALTER COLUMN options separated by a comma:

ALTER TABLE the_table
    ALTER COLUMN test_id set not null, 
    ALTER COLUMN type SET NOT NULL;

PostgreSQL ADD COLUMN DEFAULT NULL locks and performance

https://www.postgresql.org/docs/current/static/sql-altertable.html

1. Yes - same ACCESS EXCLUSIVE, no exceptions for DEFAULT NULL or no DEFAULT mentionned (statistics, "options", constraints, cluster would require less strict I think, but not add column)

Note that the lock level required may differ for each subform. An
ACCESS EXCLUSIVE lock is held unless explicitly noted. When multiple
subcommands are listed, the lock held will be the strictest one
required from any subcommand.

2. No - it will rather append NULL to result on select

When a column is added with ADD COLUMN, all existing rows in the table
are initialized with the column's default value (NULL if no DEFAULT
clause is specified). If there is no DEFAULT clause, this is merely a
metadata change and does not require any immediate update of the
table's data; the added NULL values are supplied on readout, instead.

3. No - no difference AFAIK. Just metadata change in both cases (as I believe it is one case expressed with different semantics)

Edit - Demo:

db=# create table so(i int);
CREATE TABLE
Time: 9.498 ms
db=# insert into so select generate_series(1,10*1000*1000);
INSERT 0 10000000
Time: 13899.190 ms
db=# alter table so add column nd BOOLEAN;
ALTER TABLE
Time: 1025.178 ms
db=# alter table so add column dn BOOLEAN default null;
ALTER TABLE
Time: 13.849 ms
db=# alter table so add column dnn BOOLEAN default true;
ALTER TABLE
Time: 14988.450 ms
db=# select version();
                                                    version
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.1 on x86_64-apple-darwin15.6.0, compiled by Apple LLVM version 8.0.0 (clang-800.0.42.1), 64-bit
(1 row)

lastly to avoid speculations it is data type specific:

db=# alter table so add column t text;
ALTER TABLE
Time: 25.831 ms
db=# alter table so add column tn text default null;
ALTER TABLE
Time: 13.798 ms
db=# alter table so add column tnn text default 'null';
ALTER TABLE
Time: 15440.318 ms

Postgres: Update Boolean column with false if column contains null

You must use IS NULL to check for the presence of a NULL value:

UPDATE test
SET utilized = false
WHERE utilized IS NULL;

In SQL, unlike other places such as Java or C#, NULL is a special value meaning something like "not known." Therefore, comparing a column to NULL using the = operator is also unknown, because it might or might not be true. Instead, use IS NULL or IS NOT NULL to directly compare a column to NULL.

---

Related Topics