Working with Databases -Before You Start
Tags: opengauss
Published: 2023-03-13 04:12:09

This section explains how to use databases, including creating databases and tables, inserting data to tables, and querying data in tables.

**Prerequisites**
openGauss is running properly.

**Procedure**
1. Log in as the OS user omm to the primary node of the database.

If you are not sure which server the primary node of the database is deployed on, see Confirming Connection Information.

2. Connect to a database.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c6lp2cb6qo1j5mc26ipq.png)

If the following information is displayed, the connection has been established:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q6o6aybngbahkr7d7573.png)

**postgres** is the database generated by default after openGauss installation is complete. You can connect to this database to create a database. **8000** is the port number of the database primary node, and you can change it as needed. You can obtain the port number by following the instructions provided in Confirming Connection Information.

**Note:**

2.1 You need to use a client program or tool to connect to the database and to deliver SQL statements
2.2 **gsql** is a command-line interface (CLI) tool provided for connecting to a database. For more database connection methods, see Connecting to a Database.

3. Create a database user.

Only administrators that are created during openGauss installation can access the initial database by default. You can also create other database users.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sblmckknfesqg8hqddfa.png)

If the following information is displayed, the user has been created:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6qmvzkva7os1c5g3mciy.png)

In this case, you have created a user named **joe**, and the user password is xxxxxxxxx.

The following command sets the joe user as the system administrator.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ar7txw3to3aveojhlvfv.png)

Use GRANT command to set related permissions. For specific operations, please refer to GRANT.

**Note:** For details about how to create users, see Managing Users and Their Permissions.

4. Create a database.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/09m42banuudktiarjo00.png)

If the following information is displayed, the database has been created:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/umqbldvnhghminhammjq.png)

After creating the **db_tpcc** database, you can run the following command to exit the **postgres** database and log in to the **db_tpcc** database as the user you created for more operations. You can also continue using the default **postgres** database.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w3u3wwshldvx9fejxcnd.png)

Create a schema.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/udxfg78l9r4vu4yqfttp.png)

If the following information is displayed, the schema has been created:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ocifmufujxolyahmzn33.png)

**Note:**

New databases are created in the **pg_default** tablespace by default. To specify another tablespace, run the following statement:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x8tnebpl0agsgtjtk7p4.png)

hr_local indicates the tablespace name. For details about how to create a tablespace, see Creating and Managing Tablespaces.

5. Create a table.

5.1 Create a table named **mytable** that has only one column. The column name is **firstcol** and the column type is integer.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l2vwzyu4dd5nx0k79l9g.png)

5.2 Run the following command to insert data to the table:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zo9ftdsewsiihxthxuo3.png)

If the following information is displayed, the data has been inserted:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/asaeh18jq0wb0iu1513k.png)

5.3 Run the following command to view data in the table:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q2gv8wpc109jsgnhpnmx.png)

**Note:**

By default, new database objects, such as the **mytable** table, are created in the $user schema. For more details about schemas, see Creating and Managing Schemas.

For more details about how to create a table, see Creating and Managing Tables.

In addition to the created tables, a database contains many system catalogs. These system catalogs contain openGauss installation information and information about various queries and processes in openGauss. You can collect information about the database by querying system catalogs. For details, see Querying System Catalogs.

openGauss supports row and column storage, providing high query performance for interaction analysis in complex scenarios. For details about how to select a storage model, see Planning a Storage Model.