Adding or Modifying Columns

Private: Mastering SQL using Postgresql Creating Tables and Indexes Adding or Modifying Columns

Let us understand details about adding or modifying columns using ALTER TABLE command.

New columns can be added to the existing table. However, if you want to add a column which cannot have null value then you need to follow these steps.
- Add column to the table.
- Update data in the column with some value.
- Alter table to enforce not null constraint for the newly added column.
Existing columns can be dropped from the table, but it is not advisable to do so. If at all we have to drop the column, then there should be extra caution as some or the other application functionality can be broken.
We can modify the existing columns for defining it as not null or to change the data type.
Once the application is in production, all the operations related to modifying or dropping columns should be avoided. We can consider adding columns.
Let us perform these tasks to understand more about adding or modifying or dropping table columns.
- Change the data type of user_id as SERIAL (we have to first create the sequence and then set the sequence generated value as default).
- Define default value for user_email_validated and is_active to FALSE.
- Change the data type of user_role to CHAR(1), set default value to ‘U’.
- Add new column last_updated_ts with data type timestamp and also set default value to current timestamp.

%load_ext sql

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

%sql DROP SEQUENCE IF EXISTS users_user_id_seq

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

[]

%sql CREATE SEQUENCE users_user_id_seq

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

[]

%sql ALTER TABLE users ALTER COLUMN user_id SET DEFAULT nextval('users_user_id_seq')

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

[]

%%sql 

SELECT table_catalog, 
    table_name,
    column_name,
    data_type,
    character_maximum_length,
    column_default,
    is_nullable,
    ordinal_position
FROM information_schema.columns 
WHERE table_name = 'users'
ORDER BY ordinal_position

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
9 rows affected.

table_catalog	table_name	column_name	data_type	character_maximum_length	column_default	is_nullable	ordinal_position
itversity_retail_db	users	user_id	integer	None	nextval('users_user_id_seq'::regclass)	YES	1
itversity_retail_db	users	user_first_name	character varying	30	None	NO	2
itversity_retail_db	users	user_last_name	character varying	30	None	NO	3
itversity_retail_db	users	user_email_id	character varying	50	None	NO	4
itversity_retail_db	users	user_email_validated	boolean	None	None	YES	5
itversity_retail_db	users	user_password	character varying	200	None	YES	6
itversity_retail_db	users	user_role	character varying	1	None	YES	7
itversity_retail_db	users	is_active	boolean	None	None	YES	8
itversity_retail_db	users	created_dt	date	None	CURRENT_DATE	YES	9

%%sql

ALTER TABLE users
    ALTER COLUMN user_email_validated SET DEFAULT FALSE,
    ALTER COLUMN is_active SET DEFAULT FALSE

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

[]

%%sql

ALTER TABLE users
    ALTER COLUMN user_role SET DATA TYPE CHAR(1),
    ALTER COLUMN user_role SET DEFAULT 'U'

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

[]

%%sql

ALTER TABLE users
    ADD COLUMN last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

[]

Note

We can perform multiple column level operations using one ALTER TABLE command. Let us see an example here.

%%sql

DROP TABLE IF EXISTS users

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

[]

%%sql

CREATE TABLE users (
  user_id INT,
  user_first_name VARCHAR(30) NOT NULL,
  user_last_name VARCHAR(30) NOT NULL,
  user_email_id VARCHAR(50) NOT NULL,
  user_email_validated BOOLEAN,
  user_password VARCHAR(200),
  user_role VARCHAR(1),
  is_active BOOLEAN,
  created_dt DATE DEFAULT CURRENT_DATE
)

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

[]

%sql DROP SEQUENCE IF EXISTS users_user_id_seq

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

[]

%sql CREATE SEQUENCE users_user_id_seq

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

[]

%%sql

ALTER TABLE users
    ALTER COLUMN user_id SET DEFAULT nextval('users_user_id_seq'),
    ALTER COLUMN user_email_validated SET DEFAULT FALSE,
    ALTER COLUMN is_active SET DEFAULT FALSE,
    ALTER COLUMN user_role SET DATA TYPE CHAR(1),
    ALTER COLUMN user_role SET DEFAULT 'U',
    ADD COLUMN last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

[]

%%sql 

SELECT table_catalog, 
    table_name,
    column_name,
    data_type,
    character_maximum_length,
    column_default,
    is_nullable,
    ordinal_position
FROM information_schema.columns 
WHERE table_name = 'users'
ORDER BY ordinal_position

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

table_catalog	table_name	column_name	data_type	character_maximum_length	column_default	is_nullable	ordinal_position
itversity_retail_db	users	user_id	integer	None	nextval('users_user_id_seq'::regclass)	YES	1
itversity_retail_db	users	user_first_name	character varying	30	None	NO	2
itversity_retail_db	users	user_last_name	character varying	30	None	NO	3
itversity_retail_db	users	user_email_id	character varying	50	None	NO	4
itversity_retail_db	users	user_email_validated	boolean	None	false	YES	5
itversity_retail_db	users	user_password	character varying	200	None	YES	6
itversity_retail_db	users	user_role	character	1	'U'::bpchar	YES	7
itversity_retail_db	users	is_active	boolean	None	false	YES	8
itversity_retail_db	users	created_dt	date	None	CURRENT_DATE	YES	9
itversity_retail_db	users	last_updated_ts	timestamp without time zone	None	CURRENT_TIMESTAMP	YES	10

%%sql 

SELECT * FROM information_schema.sequences 
WHERE sequence_name ~ 'users'

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

sequence_catalog	sequence_schema	sequence_name	data_type	numeric_precision	numeric_precision_radix	numeric_scale	start_value	minimum_value	maximum_value	increment	cycle_option
itversity_retail_db	public	users_user_id_seq	bigint	64	2	0	1	1	9223372036854775807	1	NO

Previous Topic

Back to Lesson

Next Topic