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
SERIALfield, a sequence generated number will be used. - It is not mandatory to pass the values for those fields where
DEFAULTis specified. Values specified inDEFAULTclause will be used. - It is mandatory to specify columns and corresponding values for all columns where
NOT NULLis specified.
In [1]:
%load_ext sql
In [4]:
%env DATABASE_URL=postgresql://itversity_sms_user:itversity@pg.itversity.com:5432/itversity_sms_db
env: DATABASE_URL=postgresql://itversity_sms_user:itversity@pg.itversity.com:5432/itversity_sms_db
In [5]:
%sql TRUNCATE TABLE users
Done.
Out[5]:
[]
In [6]:
%%sql
INSERT INTO users (user_first_name, user_last_name, user_email_id)
VALUES ('Scott', 'Tiger', 'scott@tiger.com')
* postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db 1 rows affected.
Out[6]:
[]
In [7]:
%sql SELECT * FROM users
* postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db 1 rows affected.
Out[7]:
| 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 | 2022-02-17 09:14:03.139186 | 2022-02-17 09:14:03.139186 |
In [8]:
%%sql
INSERT INTO users (user_first_name, user_last_name, user_email_id)
VALUES ('Donald', 'Duck', 'donald@duck.com')
* postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db 1 rows affected.
Out[8]:
[]
In [9]:
%sql SELECT * FROM users
* postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db 2 rows affected.
Out[9]:
| 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 | 2022-02-17 09:14:03.139186 | 2022-02-17 09:14:03.139186 |
| 2 | Donald | Duck | donald@duck.com | False | None | U | False | 2022-02-17 09:14:07.905932 | 2022-02-17 09:14:07.905932 |
In [10]:
%%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:***@pg.itversity.com:5432/itversity_sms_db 1 rows affected.
Out[10]:
[]
In [11]:
%sql SELECT * FROM users
* postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db 3 rows affected.
Out[11]:
| 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 | 2022-02-17 09:14:03.139186 | 2022-02-17 09:14:03.139186 |
| 2 | Donald | Duck | donald@duck.com | False | None | U | False | 2022-02-17 09:14:07.905932 | 2022-02-17 09:14:07.905932 |
| 3 | Mickey | Mouse | mickey@mouse.com | False | None | U | True | 2022-02-17 09:14:12.355915 | 2022-02-17 09:14:12.355915 |
In [12]:
%%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:***@pg.itversity.com:5432/itversity_sms_db 3 rows affected.
Out[12]:
[]
In [13]:
%sql SELECT * FROM users
* postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db 6 rows affected.
Out[13]:
| 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 | 2022-02-17 09:14:03.139186 | 2022-02-17 09:14:03.139186 |
| 2 | Donald | Duck | donald@duck.com | False | None | U | False | 2022-02-17 09:14:07.905932 | 2022-02-17 09:14:07.905932 |
| 3 | Mickey | Mouse | mickey@mouse.com | False | None | U | True | 2022-02-17 09:14:12.355915 | 2022-02-17 09:14:12.355915 |
| 4 | Gordan | Bradock | gbradock0@barnesandnoble.com | False | h9LAz7p7ub | U | True | 2022-02-17 09:14:16.774064 | 2022-02-17 09:14:16.774064 |
| 5 | Tobe | Lyness | tlyness1@paginegialle.it | False | oEofndp | U | True | 2022-02-17 09:14:16.774064 | 2022-02-17 09:14:16.774064 |
| 6 | Addie | Mesias | amesias2@twitpic.com | False | ih7Y69u56 | U | True | 2022-02-17 09:14:16.774064 | 2022-02-17 09:14:16.774064 |