Inserting Data

Private: Mastering SQL using Postgresql DML or CRUD Operations Inserting Data

Let us see how to insert the data into the table.

We need to use INSERT clause to insert the data. Here is the sample syntax.

INSERT INTO <table_name> (col1, col2, col3)
VALUES (val1, val2, val3)

If we don’t pass columns after table name then we need to specify values for all the columns. It is not good practice to insert records with out specifying column names.
If we do not specify value for SERIAL field, a sequence generated number will be used.
It is not mandatory to pass the values for those fields where DEFAULT is specified. Values specified in DEFAULT clause will be used.
It is mandatory to specify columns and corresponding values for all columns where NOT NULL is specified.

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%env DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db

env: DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db

%sql TRUNCATE TABLE users

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.

[]

%%sql

INSERT INTO users (user_first_name, user_last_name, user_email_id)
VALUES ('Scott', 'Tiger', 'scott@tiger.com')

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.

[]

%sql SELECT * FROM users

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.

user_id	user_first_name	user_last_name	user_email_id	user_email_validated	user_password	user_role	is_active	create_ts	last_updated_ts
1	Scott	Tiger	scott@tiger.com	False	None	U	False	2020-11-14 15:35:11.813351	2020-11-14 15:35:11.813351

%%sql

INSERT INTO users (user_first_name, user_last_name, user_email_id)
VALUES ('Donald', 'Duck', 'donald@duck.com')

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.

[]

%sql SELECT * FROM users

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
2 rows affected.

user_id	user_first_name	user_last_name	user_email_id	user_email_validated	user_password	user_role	is_active	create_ts	last_updated_ts
1	Scott	Tiger	scott@tiger.com	False	None	U	False	2020-11-14 15:35:11.813351	2020-11-14 15:35:11.813351
2	Donald	Duck	donald@duck.com	False	None	U	False	2020-11-14 15:35:14.495991	2020-11-14 15:35:14.495991

%%sql

INSERT INTO users (user_first_name, user_last_name, user_email_id, user_role, is_active)
VALUES ('Mickey', 'Mouse', 'mickey@mouse.com', 'U', true)

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.

[]

%sql SELECT * FROM users

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
3 rows affected.

user_id	user_first_name	user_last_name	user_email_id	user_email_validated	user_password	user_role	is_active	create_ts	last_updated_ts
1	Scott	Tiger	scott@tiger.com	False	None	U	False	2020-11-14 15:35:11.813351	2020-11-14 15:35:11.813351
2	Donald	Duck	donald@duck.com	False	None	U	False	2020-11-14 15:35:14.495991	2020-11-14 15:35:14.495991
3	Mickey	Mouse	mickey@mouse.com	False	None	U	True	2020-11-14 15:35:15.881686	2020-11-14 15:35:15.881686

%%sql

INSERT INTO users 
    (user_first_name, user_last_name, user_email_id, user_password, user_role, is_active) 
VALUES 
    ('Gordan', 'Bradock', 'gbradock0@barnesandnoble.com', 'h9LAz7p7ub', 'U', true),
    ('Tobe', 'Lyness', 'tlyness1@paginegialle.it', 'oEofndp', 'U', true),
    ('Addie', 'Mesias', 'amesias2@twitpic.com', 'ih7Y69u56', 'U', true)

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
3 rows affected.

[]

%sql SELECT * FROM users

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
6 rows affected.

user_id	user_first_name	user_last_name	user_email_id	user_email_validated	user_password	user_role	is_active	create_ts	last_updated_ts
1	Scott	Tiger	scott@tiger.com	False	None	U	False	2020-11-14 15:35:11.813351	2020-11-14 15:35:11.813351
2	Donald	Duck	donald@duck.com	False	None	U	False	2020-11-14 15:35:14.495991	2020-11-14 15:35:14.495991
3	Mickey	Mouse	mickey@mouse.com	False	None	U	True	2020-11-14 15:35:15.881686	2020-11-14 15:35:15.881686
4	Gordan	Bradock	gbradock0@barnesandnoble.com	False	h9LAz7p7ub	U	True	2020-11-14 15:35:20.938583	2020-11-14 15:35:20.938583
5	Tobe	Lyness	tlyness1@paginegialle.it	False	oEofndp	U	True	2020-11-14 15:35:20.938583	2020-11-14 15:35:20.938583
6	Addie	Mesias	amesias2@twitpic.com	False	ih7Y69u56	U	True	2020-11-14 15:35:20.938583	2020-11-14 15:35:20.938583

Previous Topic

Back to Lesson

Next Topic