Creating Table¶
Before getting into action with respect to basic DML and queries or CRUD operations, we need to prepare tables.
At this time we have not covered DDL yet. All database operations related to managing tables come under DDL.
For now, let’s just create the table by copy pasting below CREATE TABLE statement. We will get into concepts as part of the subsequent sections.
Connect to the database.
Create the table.
%load_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
SELECT * FROM information_schema.tables
WHERE table_catalog = 'itversity_sms_db' AND table_schema = 'public'
LIMIT 10
2 rows affected.
| table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action |
|---|---|---|---|---|---|---|---|---|---|---|---|
| itversity_sms_db | public | courses | BASE TABLE | None | None | None | None | None | YES | NO | None |
| itversity_sms_db | public | users | BASE TABLE | None | None | None | None | None | YES | NO | None |
%%sql
DROP TABLE IF EXISTS users;
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
[]
%%sql
SELECT * FROM information_schema.tables
WHERE table_catalog = 'itversity_sms_db' AND table_schema = 'public'
LIMIT 10
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.
| table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action |
|---|---|---|---|---|---|---|---|---|---|---|---|
| itversity_sms_db | public | courses | BASE TABLE | None | None | None | None | None | YES | NO | None |
%%sql
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
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 DEFAULT FALSE,
user_password VARCHAR(200),
user_role VARCHAR(1) NOT NULL DEFAULT 'U', --U and A
is_active BOOLEAN DEFAULT FALSE,
create_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
[]
Let us validate the objects that are created in the underlying database. We can either run query against information_schema or use Database Explorer in SQL Workbench or even
psql.
%%sql
SELECT * FROM information_schema.tables
WHERE table_catalog = 'itversity_sms_db' AND table_schema = 'public'
LIMIT 10
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.
| table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action |
|---|---|---|---|---|---|---|---|---|---|---|---|
| itversity_sms_db | public | users | BASE TABLE | None | None | None | None | None | YES | NO | None |
%%sql
SELECT * FROM information_schema.columns
WHERE table_name = 'users'
LIMIT 10
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
10 rows affected.
| table_catalog | table_schema | table_name | column_name | ordinal_position | column_default | is_nullable | data_type | character_maximum_length | character_octet_length | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | character_set_catalog | character_set_schema | character_set_name | collation_catalog | collation_schema | collation_name | domain_catalog | domain_schema | domain_name | udt_catalog | udt_schema | udt_name | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier | is_self_referencing | is_identity | identity_generation | identity_start | identity_increment | identity_maximum | identity_minimum | identity_cycle | is_generated | generation_expression | is_updatable |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| itversity_sms_db | public | users | user_id | 1 | nextval('users_user_id_seq'::regclass) | NO | integer | None | None | 32 | 2 | 0 | None | None | None | None | None | None | None | None | None | None | None | None | itversity_sms_db | pg_catalog | int4 | None | None | None | None | 1 | NO | NO | None | None | None | None | None | NO | NEVER | None | YES |
| itversity_sms_db | public | users | user_first_name | 2 | None | NO | character varying | 30 | 120 | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_sms_db | pg_catalog | varchar | None | None | None | None | 2 | NO | NO | None | None | None | None | None | NO | NEVER | None | YES |
| itversity_sms_db | public | users | user_last_name | 3 | None | NO | character varying | 30 | 120 | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_sms_db | pg_catalog | varchar | None | None | None | None | 3 | NO | NO | None | None | None | None | None | NO | NEVER | None | YES |
| itversity_sms_db | public | users | user_email_id | 4 | None | NO | character varying | 50 | 200 | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_sms_db | pg_catalog | varchar | None | None | None | None | 4 | NO | NO | None | None | None | None | None | NO | NEVER | None | YES |
| itversity_sms_db | public | users | user_email_validated | 5 | false | YES | boolean | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_sms_db | pg_catalog | bool | None | None | None | None | 5 | NO | NO | None | None | None | None | None | NO | NEVER | None | YES |
| itversity_sms_db | public | users | user_password | 6 | None | YES | character varying | 200 | 800 | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_sms_db | pg_catalog | varchar | None | None | None | None | 6 | NO | NO | None | None | None | None | None | NO | NEVER | None | YES |
| itversity_sms_db | public | users | user_role | 7 | 'U'::character varying | NO | character varying | 1 | 4 | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_sms_db | pg_catalog | varchar | None | None | None | None | 7 | NO | NO | None | None | None | None | None | NO | NEVER | None | YES |
| itversity_sms_db | public | users | is_active | 8 | false | YES | boolean | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_sms_db | pg_catalog | bool | None | None | None | None | 8 | NO | NO | None | None | None | None | None | NO | NEVER | None | YES |
| itversity_sms_db | public | users | create_ts | 9 | CURRENT_TIMESTAMP | YES | timestamp without time zone | None | None | None | None | None | 6 | None | None | None | None | None | None | None | None | None | None | None | itversity_sms_db | pg_catalog | timestamp | None | None | None | None | 9 | NO | NO | None | None | None | None | None | NO | NEVER | None | YES |
| itversity_sms_db | public | users | last_updated_ts | 10 | CURRENT_TIMESTAMP | YES | timestamp without time zone | None | None | None | None | None | 6 | None | None | None | None | None | None | None | None | None | None | None | itversity_sms_db | pg_catalog | timestamp | None | None | None | None | 10 | NO | NO | None | None | None | None | None | NO | NEVER | None | YES |
%sql SELECT * FROM users
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
0 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 |
|---|