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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

%%sql 

SELECT * FROM information_schema.tables 
WHERE table_catalog = 'itversity_sms_db' AND table_schema = 'public'
LIMIT 10

Copy to clipboard

2 rows affected.

Copy to clipboard

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;

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

%%sql 

SELECT * FROM information_schema.tables 
WHERE table_catalog = 'itversity_sms_db' AND table_schema = 'public'
LIMIT 10

Copy to clipboard

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

Copy to clipboard

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
)

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

  • 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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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