How To Download/Restore/Load Sample Database In PostgreSQL Using pgAdmin4

Only 3.9% of viewers are subscribing to my channel 😓.

I request you to please give click on Subscribe button.

It really helps me grow 😢.

We will use the DVD rental database for demonstrating the features of PostgreSQL.



The DVD rental database represents the business processes of a DVD rental store. The DVD rental database has many objects including:



15 tables

1 trigger

7 views

8 functions

1 domain

13 sequences



PostgreSQL Sample Database Tables

There are 15 tables in the DVD Rental database:



actor – stores actors data including first name and last name.

film – stores films data such as title, release year, length, rating, etc.

film_actor – stores the relationships between films and actors.

category – stores film’s categories data.

film_category- stores the relationships between films and categories.

store – contains the store data including manager staff and address.

inventory – stores inventory data.

rental – stores rental data.

payment – stores customer’s payments.

staff – stores staff data.

customer – stores customers data.

address – stores address data for staff and customers

city – stores the city names.

country – stores the country names.



You can use the pgAdmin tool to restore the sample database from the downloaded database file using the following steps:





First, launch the pgAdmin tool and connect to the PostgreSQL server.



Next, right-click on the dvdrental database and choose Restore… menu item as shown:



After that, wait for a few seconds to let the restoration process completes.



Finally, open the dvdrental database from object browser panel, you will see the tables in the public schema and other database objects as shown.



We have shown you how to load the dvdrental sample database into the PostgreSQL database server for learning and practicing PostgreSQL.



Let’s start learning PostgreSQL and have fun!

How To Return A Table From A Function In PostgreSQL || PostgreSQL Tips

Only 3.9% of viewers are subscribing to my channel 😓.

I request you to please give click on Subscribe button.

It really helps me grow 😢.

RETURNING A TABLE FROM A FUNCTION IN POSTGRESQL

Sometimes for our needs, we may need to return a table from a user-defined function a table. In this blog, we will see how to accomplish this using the PostgreSQL database.

Before going to function and it’s implementation, let’s create a table and store several dummy records in it.
After insertion, let’s check the table values by executing the below statement.

select * from public.functiontest;

CREATE TABLE public.FunctionTest
(
id numeric,
name character varying
);

ALTER TABLE public.FunctionTest
OWNER to postgres;


We have created a table named FunctionTest having 2 columns only.

Let’s put records in it.

INSERT INTO public.functiontest(
id, name)
VALUES (1, 'Akram');

INSERT INTO public.functiontest(
id, name)
VALUES (2, 'Sohail');

The output is shown in the above snapshot.

Now suppose we want to retrieve all the data available in the table by using a user-defined function.

To do it, let’s create a function.

CREATE OR REPLACE FUNCTION public.get_data(
)
RETURNS TABLE(v_id numeric, v_name character varying)
LANGUAGE 'plpgsql'

COST 100
VOLATILE
ROWS 1000

AS $BODY$
DECLARE

BEGIN
RETURN QUERY
SELECT
id,name
FROM
public.functiontest;
END;
$BODY$;

ALTER FUNCTION public.get_data()
OWNER TO postgres;


We have created a simple function get_data() that will return us all the data present in the table functiontest.

To return a table from the function, we use RETURNS TABLE syntax and specify the columns of the table. Each column is separated by a comma (,).

In the function, we return a query that is a result of a SELECT statement. We can see that the columns in the SELECT statement must match with the columns of the table that we want to return.


To call the function, we use the following statement.

SELECT public.get_data();


As we know, the function is returning a table, so here is the output for the called function.
We can also call the function as below, that gives better tabular form output.

SELECT * from get_data();

The above snapshot are the outcome of the function get_data().
So in this way we return a table from a function in PostgreSQL.

How To Download And Install PostgreSQL 12.3 Latest Release In 64 Bit Windows 10 in 2020 || Knowledge 360

Only 3.9% of viewers are subscribing to my channel 😓.

I request you to please give click on Subscribe button.

It really helps me grow 😢.

Hello Friends, I am Akram and in this blog, I will tell you how to download and install the PostgreSQL 12.3 latest release in your windows 10 PC of 64 bit.

1. Click this link to download the installation file, which is of 190 MBs.

2. Install the file and follow step by step procedure.







3. When the installation is done, click on the finish.








4. Search for PgAdmin.







5. Now Enter the password you had given while installing.







Note:- Please take a screenshot of the configuration summary. If you forgot to, please watch the video again and uninstall the file and again install it.