Create and query a Cloud SQL for PostgreSQL database using the Google Cloud console

Create and query a Cloud SQL for PostgreSQL database using the Google Cloud console

MySQL   |  PostgreSQL   |  SQL Server

Learn how to create and query a Cloud SQL for PostgreSQL database using the Google Cloud console. This quickstart is intended for database practitioners seeking a quick introduction to Cloud SQL for PostgreSQL. The quickstart takes you through the steps required to complete the following tasks:

• Create a Cloud SQL for PostgreSQL instance
• Create a database
• Create a schema
• Create a table
• Insert data
• Query the data that you inserted
• Clean up your resources

As you follow along, keep the default values for settings unless otherwise specified.

Before you begin

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

If you're using an existing project for this guide, verify that you have the permissions required to complete this guide. If you created a new project, then you already have the required permissions.

Verify that billing is enabled for your Google Cloud project.

Enable the Cloud SQL, Cloud SQL Admin, and Compute Engine APIs.

Roles required to enable APIs

To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains the serviceusage.services.enable permission. Learn how to grant roles.

Enable the APIs

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

If you're using an existing project for this guide, verify that you have the permissions required to complete this guide. If you created a new project, then you already have the required permissions.

Verify that billing is enabled for your Google Cloud project.

Enable the Cloud SQL, Cloud SQL Admin, and Compute Engine APIs.

Roles required to enable APIs

To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains the serviceusage.services.enable permission. Learn how to grant roles.

Enable the APIs

Required roles

To get the permissions that you need to complete this quickstart, ask your administrator to grant you the Cloud SQL Admin (roles/cloudsql.admin) IAM role on the project. For more information about granting roles, see Manage access to projects, folders, and organizations.

You might also be able to get the required permissions through custom roles or other predefined roles.

Create a Cloud SQL for PostgreSQL instance

Create an instance in the Google Cloud console using the following settings. For all other settings, keep the default.

1. Go to the Cloud SQL Instances page in the Google Cloud console.
   Go to the Cloud SQL Instances page
2. Click Create Instance.
3. Click Choose Cloud SQL for PostgreSQL.
4. For Choose a Cloud SQL edition, choose Enterprise.
5. For Edition preset, select Sandbox.
6. For Instance ID, enter quickstart-instance.
7. Enter a password for the default user account and save it for future use.
8. Choose a region near you.
9. For Zonal availability, select Single zone.
10. Click Create instance, and then wait until the instance initializes and starts. The initialization process can take more than five minutes.

Tip: Check the long-running operation drawer at the bottom of the Google Cloud console to monitor the status of your instance-creation operation.

Create a database

After quickstart-instance has been created, you can create a database in your Cloud SQL for PostgreSQL instance.

1. In the instance navigation menu, click Databases.
2. Click Create a database.
3. In the Database Name text field, enter quickstartdb.
4. Click Create.

Add a user

Before you can read or write to the database, you must create a database user that is different from the root user.

1. In the instance navigation menu, click Users.
2. Click Add user account.
3. In the pane that opens, select Built-in authentication.
4. In the User name field, enter quickstart-user.
5. Enter a password for the new user. Save this password for future use.
6. Click Add.

Create a schema

After you've created your instance and database, you can navigate to Cloud SQL Studio and use the query editor to create a schema.

1. In the instance navigation menu, click Cloud SQL Studio. A dialog is displayed.
2. In the Database drop-down, choose quickstartdb.
3. Select Built-in database authentication.
4. In the User drop-down, select quickstart-user.
5. In the Password field, enter the password that you chose for the user in the Add a user section.
6. Click Authenticate. Cloud SQL Studio opens.
7. Click Untitled Query to open the query editor.

8. Paste the following code into the query editor:

   CREATE SCHEMA IF NOT EXISTS "myschema";
   

9. Optional: to correctly format the SQL statement, click Format.

10. Click Run. The results pane displays a success message.

Create a table

Now create a table using the schema that you created.

1. Click New tab to open a new query editor tab.

2. Paste the following statement into the query editor:

   CREATE TABLE IF NOT EXISTS
     "myschema"."quickstart_table" ( "UserId" SERIAL
     PRIMARY KEY
       ,
       "FirstName" VARCHAR(255),
       "LastInitial" VARCHAR(1),
       "BirthDate" DATE );
   

3. Optional: to correctly format the SQL statement, click Format.

4. Click Run. The results pane displays a success message.

The quickstartdb database now has a table with the columns to store the following data:

• An automatically incrementing user ID column that is configured to be the table's primary key
• First name
• Last initial
• Birthdate

Insert data

To populate the quickstart_table table with some data, take the following steps:

1. Click New tab to open a new query editor tab.

2. Paste the following statement into the query editor:

   INSERT INTO
     "myschema"."quickstart_table" ("FirstName",
       "LastInitial",
       "BirthDate")
   VALUES
     ('Yuri', 'Z', '1999-05-24'),
     ('Cruz', 'S', '1978-11-01'),
     ('Kai', 'D', '1965-12-09'),
     ('Luka', 'L', '2003-04-19'),
     ('Taylor', 'S', '2001-01-31');
   

3. Optional: to format the SQL statement correctly, click Format.

4. Click Run. The results pane displays a success message.

Query the database

Now you can run queries against the data in the table that you created.

Select all records

To retrieve all records in the table, do the following:

1. Click New tab to open a new query editor tab.

2. Paste the following statement into the query editor:

   SELECT
     *
   FROM
     "myschema"."quickstart_table"
   

3. Click Run.

   The query results are similar to the following:

   UserID	FirstName	LastInitial	BirthDate
   1	Yuri	Z	1999-05-24 00:00:00
   2	Cruz	S	1978-11-01 00:00:00
   3	Kai	D	1965-12-09 00:00:00
   4	Luka	L	2003-04-19 00:00:00
   5	Taylor	S	2001-01-31 00:00:00

Run a filtered select statement

To retrieve the user ID and first name of users born on or after January 1, 2000, specify the columns and use a WHERE clause:

1. Click New tab to open a new query editor tab.

2. Paste the following statement into the query editor:

   SELECT
     "UserId",
     "FirstName"
   FROM
     "myschema"."quickstart_table"
   WHERE
     "BirthDate" > '1999-12-31';
   

3. Click Run.

   The query results are similar to the following:

   UserID	FirstName
   4	Luka
   5	Taylor

Clean up

To avoid incurring charges to your Google Cloud account for the resources used on this page, follow these steps.

Disable deletion protection and then delete the quickstart instance:

1. In the instance navigation menu, click Overview.
2. Click Edit.
3. Expand the Data Protection section.
4. In Instance deletion protection, deselect all options.
5. Click Save. Delete is now selectable.
6. Click Delete. A dialog appears.
7. In the Instance ID field, enter quickstart-instance.
8. Click Delete.

What's next

• Configure public IP
• Cloud SQL for PostgreSQL features