3 Insert sample data

The next step after connecting to the cluster is to insert some sample data to PostgreSQL.

Create a schema

Every database in PostgreSQL has a default schema called public. A schema stores database objects like tables, views, indexes and allows organizing them into logical groups.

When you create a table, it ends up in the public schema by default. In recent PostgreSQL versions (starting from PostgreSQL 15), non-database owners cannot access the public schema. Therefore, you need to create a new schema to insert the data.

Use the following statement to create a schema

CREATE SCHEMA demo;

Create a table

After you created a schema, all tables you create end up in this schema if not specified otherwise.

At this step, we will create a sample table Library as follows:

CREATE TABLE LIBRARY(
   ID INTEGER NOT NULL,
   NAME TEXT,
   SHORT_DESCRIPTION TEXT,
   AUTHOR TEXT,
   DESCRIPTION TEXT,
   CONTENT TEXT,
   LAST_UPDATED DATE,
   CREATED DATE
);

Tip

If the schema has not been automatically set to the one you created, set it manually using the following SQL statement:

SET schema 'demo';

Replace the demo schema name with your value if you used another name.

Insert the data

PostgreSQL does not have the built-in support to generate random data. However, it provides the random() function which generates random numbers and generate_series() function which generates the series of rows and populates them with the numbers incremented by 1 (by default).

Combine these functions with a couple of others to populate the table with the data:

INSERT INTO LIBRARY(id, name, short_description, author,
                              description,content, last_updated, created)
SELECT id, 'name', md5(random()::text), 'name2'
      ,md5(random()::text),md5(random()::text)
      ,NOW() - '1 day'::INTERVAL * (RANDOM()::int * 100)
      ,NOW() - '1 day'::INTERVAL * (RANDOM()::int * 100 + 100)
FROM generate_series(1,100) id;

This command does the following:

• Fills in the columns id, name, author with the values id, name and name2 respectively;
• generates the random md5 hash sum as the values for the columns short_description, description and content;
• generates the random number of dates from the current date and time within the last 100 days, and
• inserts 100 rows of this data

Now your cluster has some data in it.

Next steps

Make a backup

Get expert help

If you need assistance, visit the community forum for comprehensive and free database knowledge, or contact our Percona Database Experts for professional support and services. Join K8S Squad to benefit from early access to features and “ask me anything” sessions with the Experts.

Community Forum Get a Percona Expert Join K8S Squad

---

Last update: 2024-11-21