Exploring the database

Now that you have created a database cluster and connected to it, you can start exploring the database. In this step, we'll show you how to use psql to connect to the database cluster and run some basic queries.

Assuming that you are using psql as your Postgres client as suggested in the previous step, proceed to connect to the database cluster.

Once you have connected you can run some basic queries. But first you'll want to create a database of your own to work with.

Create a database

1. Create a user with the edb_admin role:

   → WrapCopy

   CREATE USER explorer WITH PASSWORD 'yourpasswordhere';

2. Create a database:

   → WrapCopy

   CREATE DATABASE explore;

3. Grant the user access to the database:

   → WrapCopy

   GRANT explorer to edb_admin;

4. Connect to the database:

   → WrapCopy

   \c explore

Create a table and data

Let's create a table of integers and populate it with some random values.

1. Create a table:

   → WrapCopy

   CREATE TABLE quicktest ( id SERIAL PRIMARY KEY, value INT ); 

2. Populate the table:

   → WrapCopy

   INSERT INTO quicktest (value) SELECT random()*10000
   FROM generate_series(1,10000);

Run some queries

1. Get a sum of the value column (and a count of the rows):

   → WrapCopy

   select COUNT(*),SUM(value) from quicktest;

2. Get the average value:

   → WrapCopy

   select AVG(value) from quicktest;

3. Get the maximum value:

   → WrapCopy

   select MAX(value) from quicktest;

4. Get the minimum value:

   → WrapCopy

   select MIN(value) from quicktest;

5. Get the standard deviation of the values:

   → WrapCopy

   select STDDEV(value) from quicktest;

6. Get the ten lowest values in the table:

   → WrapCopy

   select * from quicktest order by value limit 10;

7. Get the ten highest values in the table:

   → WrapCopy

   select * from quicktest order by value desc limit 10;