Rapid POSTGRESQL learning, PART-2 By: ali masudianpour [email protected] Rapid POSTGRESQL learning. 1 

Creating Table In psql maximum size of table is 32 terabytes. In order to Create Table in psql we will follow the following syntax – CREATE TABLE [table name] ( [Column name] [Column Data type], … ); ● Example: CREATE TABLE sample_table ( id INTEGER, fname VARCHAR(20), lnameVARCHAR(25) ); ● Now if we write \d sample_table we will see the table details: Rapid POSTGRESQL learning. 2 

PRIMARY KEY CONSTRAINT A primary key constraint is simply a combination of an UNIQUE constraint and a NOT NULL constraint. – How to define: ● First way – CREATE TABLE test( t1 INTEGER, t2 INTEGER, PRIMARY KEY (t1) ); ● Second way – CREATE TABLE test( t1 INTEGER PRIMARY KEY, t2 INTEGER ); Rapid POSTGRESQL learning. 3 

FOREIGN KEY A foreign key is a column or a group of columns that points to the primary key or another table. – HOW TO DEFINE: CREATE TABLE referenceTable( id INTEGER PRIMARY KEY, name VARCHAR(10) ); CREATE TABLE testable( id INTEGER PRIMARY KEY, ref_id INTEGER REFERENCES referenceTable(id) ); If we have a table with two or more foreign keys it would be something like bellow: ● CREATE TABLE referenceTable( id INTEGER, id1 INTEGER , PRIMARY KEY(id,id1)); ● CREATE TABLE testable(id integer PRIMARY KEY, ref_id INTEGER, ref_id1 INTEGER, FOREIGN KEY(ref_id,red_id1) REFERENCES referenceTable(id,id1)); Rapid POSTGRESQL learning. 4 

Check Constraint We can use CHECK constraint when we need to check some values. – HOW TO DEFINE CHECK CONSTRAINT CREATE TABLE item( id INTEGER PRIMARY KEY, name VARCHAR(15), price NUMERIC CHECK(price>0) ); Rapid POSTGRESQL learning. 5 

NOT NULL Constraint NOT NULL constraint is used to force a column that should not accept null value. – HOW TO DEFINE: ● CREATE TABLE test( M1 INTEGER NOT NULL, M2 INTEGER CHECK (M2 IS NOT NULL) ); Rapid POSTGRESQL learning. 6 

UNIQUE Constraint If we need to have a column with unique values we have to use UNIQUE Constraint. – How to Define ● CREATE TABLE test( id INTEGER PRIMARY KEY, email VARCHAR(52) UNIQUE, nationalCode INTEGER(10), CONSTRAINT national_code_unq_const UNIQUE(nationalCode) ); – Above example uses 2 ways of defining UNIQUE Constraint ● Pay attention to the red color and blue color and you can find out which type of difference those have with each other. Rapid POSTGRESQL learning. 7 

DEFAULT VALUES We can specify a column to holds a default value if it had not a value while insert operation. – How to define: ● CREATE TABLE test( id INTEGER PRIMARY KEY, test1 CHAR(5) DEFAULT ‘MASUD’, test2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); – In above example we indicated that the default value of test1 column is and the default value of test2 which it’s data type is TIMESTAMP would be CURRENT_TIMESTAMP ● Note that CURRENT_TIMESTAMP is a predefined constant in postgresql that shows current time stamp Rapid POSTGRESQL learning. 8 

CASCADE Have you ever tried to drop a table that has a dependency to another table? You may checked that, but if not, I’ll tell you what would happen. If you do that you will face an error. That error informs us that you are trying to drop a table that has a dependency on another table. So what is the solution? – Solution would be CASCADE – If we use CASCADE keyword after a command, it avoid dependencies and run the command on related and depended tables or columns. – How to use: ● DROP TABLE [table_name] CASCADE; – Example: DROP TABLE testable CASCADE; Rapid POSTGRESQL learning. 9 

CRUD Crud stands for Create, read, update and delete. In continue we will see how we can crud with Postgresql. Rapid POSTGRESQL learning. 10 

INSERT We can insert into table columns with the following syntax: – INSERT INTO [TABLE NAME] ([COLUMNS]) VALUES ([values]); – For instance we have the following table: ● CREATE TABLE ttst( id SERIAL NOT NULL PRIMARY KEY fname VARCHAR(10), lname VARCHAR(30) ); ● INSERT INTO ttst (fname,lname) VALUES(‘Ali’,’MasudianPour’); ● INSERT INTO ttst (fname,lname) VALUES(‘Reza’,’Nejati’); ● INSERT INTO ttst (fname,lname) VALUES(‘Reza’,’Asi’); – Now if we try to: SELECT * FROM ttst; The result would like the image: Rapid POSTGRESQL learning. 11 

READ In order to see table entries we use SELECT keyword, look at the example – SELECT * FROM [TABLE NAME] ; ● This means that select all entries of table that we mentioned its name into square brackets. – SELECT [COLUMN NAME], [COLUMN NAME], … FROM [TABLE NAME] ● This means that we can indicate which column we want to check and see related values. – Example: ● SELECT id, fname FROM ttst; Rapid POSTGRESQL learning. 12 

UPDATE We can update columns with the following syntax: – UPDATE [TABLE NAME] SET [COLUMN NAME] = [NEW VALUE] – For example: ● UPDATE ttst SET fname=‘Masud’ WHERE id=5; ● After above command the select result would be similar to image Rapid POSTGRESQL learning. 13 

DELETE On order to delete a row we use DELETE Keyword. – DELETE FROM [TABLE NAME] WHERE [CONDITION] ● For instance: – DELETE FROM ttst WHERE id=6; – As we talked about later, If we had some dependencies we use CASCADE keyword. ● For instance – DELETE FROM ttst WHERE id=6 CASCADE; Rapid POSTGRESQL learning. 14 

TRUNCATE In order to empty all rows in a table we use TRUNCATE KEYWORD – TRUNCATE TABLE [Table Name]; ● For example: – TRUNCATE TABLE ttst(); Rapid POSTGRESQL learning. 15 

Eliminate Duplications In order to eliminate duplicate rows we use DISTINCT keyword. – DISTINCT ● We can use DISTINCT keyword to eliminate duplications ● SELECT DISTINCT [*/COLUMN NAME] FROM [TABLE NAME]; – Example: ● SELECT DISTINCT * FROM ttst(); Rapid POSTGRESQL learning. 16 

Portion of a row We can select data more accurate with writing conditions in WHERE clause – For instance: ● SELECT * FROM ttst WHERE id=6 ● SELECT * FROM ttst WHERE id>=10 ● SELECT * FROM ttst WHERE fname=‘Ali’ – AND / OR ● SELECT * FROM ttst WHERE fname=‘Ali’ AND lname=‘MasudianPour’ ● SELECT * FROM ttst WHERE fname =‘Reza’ OR flane=‘Ali’ Rapid POSTGRESQL learning. 17 

SORTING We can sort the query result by using ORDER BY – Take a look at below example ● SELECT * FROM ttst ORDER BY fname – The result will select all values and sort them by fname and finally shows the output. – ORDER BY has some switches ● DESC – ORDER BY [column name] DESC ● Descending sort ● ASC – ORDER BY [column name] ASC ● Ascending Sort ● NULLS FIRST – ORDER BY [column name] DESC NULLS FIRST ● NULLS LAST – ORDER BY [column name] DESC NULLS LAST Rapid POSTGRESQL learning. 18 

ALIAS Optionally, aliases can be declared for a column – Example: ● WE USE AS KEYWORD TO USE ALIAS ● SELECT * FROM ttst AS e; – Selects everything from ttst table and result will be known as e Alias ● SELECT fname fn, lname ln FROM ttst ORDER BY fn – This example selects fname as fn and lname as ln and as you can see we used alias in ORDER BY Rapid POSTGRESQL learning. 19 

LIMIT Take a look at the below example – SELECT * FROM ttst LIMIT 2 ● The output will be limited to only 2 rows – SELECT * FROM ttst LIMIT 10 ● The output will be limited to only 10 rows Rapid POSTGRESQL learning. 20 

OFFSET AS we can limit our result with LIMIT clause, we can also set a range with OFFSET clause – SELECT * FROM ttst OFFSET 4 ● Above example selects all rows from ttst table and shows result with 4 offset. To be more clear it will avoid showing 4 first rows. – SELECT * FROM ttst LIMIT 3 OFFSET 4 ● Just like the previous example and following LIMIT Rapid POSTGRESQL learning. 21 

END OF PART 2 End of Part 2 – In part 3 we will discuss about: ● COMBINING QUERIES with UNIOPN, INTERSECT and … ● Aggregation Functions ● GROUP BY and HAVING ● JOINS ● SUBQUERY EXPRESSIONS ● And … Rapid POSTGRESQL learning. 22