Tetraloops: pgAdmin with PostgreSQL database Tutorial for beginners

In prevailing busy circumstances no one have much time to read out very lengthy stuff, therefore, I decided the shortest way to learn PostgreSQL.

Create schema in PostgreSQL using pgAdmin 4.

Step - 1

In pgAdmin right click on schema, select create and then sechema.

Step - 2

Now enter schema name and select owner and click Security.

Step - 3

Next click on Security tab then select grantee and privileges as you want to give this schema and click Default Privileges.

Step - 4

Next click on default privileges tab and select Grantee, Privileges and Grantor accordingly under sub tab of tables and then click Sequences.

Step - 5

Select Sequences tab to grant privileges to schema if needed and then click Functions.

Step - 6

Select Function tab and grant privileges to schema if needed and then click Types.

Step - 7

Select Types tab and grant privileges to the schema if needed.

Finally press Save button to the changes.

Create Tables/Objects in PostgreSQL using pgAdmin 4.

Step - 1

Right click on Table in pgAdmin then select Create and then Table.

Step - 2

In General Tab, enter name of table as per your own convention and select tablespace from drop down menu. You may also enter comments regarding this table for yours and other's convenient and then click Columns.

Step - 3

Define column name, datatype, length, not null and primary key as below and click on Constraints.

Step - 4

As shown below the primary key will automatically be created as in above screen we selected Yes now click on Foreign Key tab.

Step - 5

Press + sign on right corner of Foreign Key tab.

Step - 6

Press Edit sign on left of Name field.

Step - 7

Enter name of Foreign key in General tab below like start from table name _ column name _ fkey and click Column tab forward to Definition tab.

Step - 8

Select column of this table for which foreign key is being created, select Reference means the table name having primary key of such column and select Referencing means the column name on which primary key has been defined. Now click on below + sign it will generate foreign key and you may observe changes above under column field.

Finally press Save button so that the changes may be saved.

If you further explore constraints like what is primary key, foreign key, check constraint, Unique key etc you may study constraints in detail.

The last one is to normalize the database which is Indexing.

Create index on a column of the table so that the query may response quickly. Right click on table you have created and select Create and then select Index.

Enter name of index as i have entered fnd_application_application_id and click Definition.

Click on the field forward to Unique to change No to Yes, select below column name and the again column name. But when you are creating index on foreign key then do not change Unique to yes as foreign key usually is repeating one in the column.

Finally press Save button to save changes.

Create Sequence to auto generate Primary Key.

Right click on Sequences then create and then click Sequence.

Enter Sequence name with what convention you want.

Enter Increment, Start value, minimum value and maximum value.

Finally press Save button to save changes.

Take Backup of schema

In my case the schema is 'has' now first configure the Binary Path in pgAdmin Preferences.

Click on three dots on left side opposite on required version and provide path as in below screen.

Backup of Schema

Select schema name click on Tools menu then Backup.

Enter backup file name or click three dots to provide complete path where you want to save this backup file and press Backup Button.

After completing backup you may view the back file as below screen.

Create trigger to insert auto increment value in primary key column by using sequences already created above.

CREATE OR REPLACE FUNCTION application_id_f()
RETURNS "trigger" AS
$BODY$
BEGIN
New.application_id:=nextval('application_id_s');
Return NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER application_id_t
BEFORE INSERT
ON fnd_applications
FOR EACH ROW
EXECUTE PROCEDURE application_id_f();

Create Views

Right click on Views, select Create and then View.