Creating Table

Private: Mastering SQL using Postgresql DML or CRUD Operations 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

Previous Topic

Back to Lesson

Next Topic