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
Creating Table Updating Data