Tell the server that you want to change a column attribute

We can change a lot of things about a table. The most common are:

Data Type

ALTER TABLE <schema_name>.<table_name>
ALTER COLUMN <column_name> SET DATA TYPE  <data_type1>;

Default value

ALTER TABLE <schema_name>.<table_name>
ALTER COLUMN <column_name> SET DEFAULT <default_value> | DROP DEFAULT;

Nullability of a column

ALTER TABLE <schema_name>.<table_name>
ALTER COLUMN <column_name> SET NOT NULL | DROP NOT NULL;

Example table

To practice, let's recreate the aaa_Dummy_Table on our sequel-mart-schema:

CREATE TABLE "sequel-mart-schema"."aaa_Dummy_Table" (
	column_001 INT,
	column_002 VARCHAR(10),
	column_003 TIMESTAMP,
	column_004 NUMERIC(9,2)
);

Let's change the data type of column_001 from an INT to a SMALLINT

ALTER TABLE "sequel-mart-schema"."aaa_Dummy_Table"
ALTER COLUMN column_001 SET DATA TYPE SMALLINT;

Now let's force column_002 and column_004 to not accept NULL as an input

ALTER TABLE "sequel-mart-schema"."aaa_Dummy_Table"
ALTER COLUMN column_002 SET NOT NULL;

ALTER TABLE "sequel-mart-schema"."aaa_Dummy_Table"
ALTER COLUMN column_004 SET NOT NULL;

Finally let's set a default value for column_002 of 'Unknown'

ALTER TABLE "sequel-mart-schema"."aaa_Dummy_Table"
ALTER COLUMN column_002 SET DEFAULT 'Unknown';

Testing our new constraints

If we insert the following record (notice we're not including column_002 or column_003):

INSERT INTO "sequel-mart-schema"."aaa_Dummy_Table"
	(column_001, column_004)
VALUES (10, 89.56);

...and query our table:

SELECT *
FROM "sequel-mart-schema"."aaa_Dummy_Table";

We notice that (screenshot 6):

• column_001 has changed datatype and is now SMALLINT
• column_002 has used our default value of 'Unknown'
• column_003 has no default value but can accept NULL so it uses that

Now let's try to violate the constraints.

If we try to run:

INSERT INTO "sequel-mart-schema"."aaa_Dummy_Table"
	(column_001, column_003)
VALUES (10, '2021-09-01 15:02:55');

We get the following helpful message:

ERROR:  null value in column "column_004" of relation "aaa_Dummy_Table" violates not-null constraint

This tells us that column_004 has a NOT NULL constraint but no DEFAULT value

We have to specify a value every time not to get an error

Setting up constraints is a way of:

• Adding an extra layer of quality control to the data we add to our tables
• Helping Postgresql to process queries faster because we're telling it what it doesn't have to look out for (NULL values in this case)