Movatterモバイル変換

[0]ホーム
URL:

You don’t need a vector database - just use Postgres for everything. Read the case study on switching from Pinecone to Neon
PostgreSQL Tutorial
PostgreSQL Tutorial

PostgreSQL CREATE VIEW

Summary: in this tutorial, you will learn how to use the PostgreSQLCREATE VIEW statement to create a new view in your database.

PostgreSQL CREATE VIEW statement

In PostgreSQL, a view is a named query stored in the database server. To create a new view, you can use theCREATE VIEW statement.

Here's the basic syntax of theCREATE VIEW statement:

CREATE VIEW view_nameAS  query;

In this syntax:

  • First, specify the name of the view after theCREATE VIEW keywords.
  • Second, specify aSELECT statement (query) that defines the view. The query is often referred to as thedefining query of the view.

PostgreSQL CREATE VIEW statement examples

Let's take some examples of using theCREATE VIEW statement.

We'll use thecustomer table from thesample database:

customer table

1) Basic CREATE VIEW statement example

The following example uses theCREATE VIEW statement to create a view based on thecustomer table:

CREATE VIEW contact ASSELECT  first_name,  last_name,  emailFROM  customer;

Output:

CREATE VIEW

The following query data from thecontact view:

SELECT * FROM contact;

Output:

first_name  |  last_name   |                  email-------------+--------------+------------------------------------------ Jared       | Ely          | jared.ely@example.com Mary        | Smith        | mary.smith@example.com Patricia    | Johnson      | patricia.johnson@example.com...

2) Using the CREATE VIEW statement to create a view based on a complex query

The following example creates a view based on the tablescustomer,address,city, andcountry:

CREATE VIEW customer_info ASSELECT  first_name,  last_name,  email,  phone,  city,  postal_code,  countryFROM  customer  INNER JOIN address USING (address_id)  INNER JOIN city USING (city_id)  INNER JOIN country USING (country_id);

The following query retrieves data from thecustomer_info view:

SELECT * FROM customer_info;

Output:

first_name  |  last_name   |                  email                   |    phone     |            city            | postal_code |                country-------------+--------------+------------------------------------------+--------------+----------------------------+-------------+--------------------------------------- Jared       | Ely          | jared.ely@example.com             | 35533115997  | Purwakarta                 | 25972       | Indonesia Mary        | Smith        | mary.smith@example.com            | 28303384290  | Sasebo                     | 35200       | Japan Patricia    | Johnson      | patricia.johnson@example.com      | 838635286649 | San Bernardino             | 17886       | United States...

3) Creating a view based on another view

The following statement creates a view calledcustomer_usa based on thecustomer_info view. Thecustomer_usa returns the customers who are in theUnited States:

CREATE VIEW customer_usaASSELECT  *FROM  customer_infoWHERE  country = 'United States';

Here's the query that retrieves data from the customer_usa view:

SELECT * FROM customer_usa;

Output:

first_name | last_name  |                email                 |    phone     |          city           | postal_code |    country------------+------------+--------------------------------------+--------------+-------------------------+-------------+--------------- Zachary    | Hite       | zachary.hite@example.com      | 191958435142 | Akron                   | 88749       | United States Richard    | Mccrary    | richard.mccrary@example.com   | 262088367001 | Arlington               | 42141       | United States Diana      | Alexander  | diana.alexander@example.com   | 6171054059   | Augusta-Richmond County | 30695       | United States...

Replacing a view

To change the defining query of a view, you use theCREATE OR REPLACE VIEW statement:

CREATE OR REPLACE VIEW view_nameAS  query;

In this syntax, you add theOR REPLACE between theCREATE andVIEW keywords. If the view already exists, the statement replaces the existing view; otherwise, it creates a new view.

For example, the following statement changes the defining query of thecontact view to include thephone information from theaddress table:

CREATE OR REPLACE VIEW contact ASSELECT  first_name,  last_name,  email,  phoneFROM  customerINNER JOIN address USING (address_id);

Display a view on psql

To display a view onpsql, you follow these steps:

First, open the Command Prompt on Windows or Terminal on Unix-like systems and connect to the PostgreSQL server:

psql -U postgres

Second, change the current database todvdrental:

\c dvdrental

Third, display the view information using the\d+ view_name command. For example, the following shows thecontact view:

\d+ contact

Output:

View "public.contact"   Column   |         Type          | Collation | Nullable | Default | Storage  | Description------------+-----------------------+-----------+----------+---------+----------+------------- first_name | character varying(45)|           |          |         | extended | last_name  | character varying(45)|           |          |         | extended | email      | character varying(50)|           |          |         | extended | phone      | character varying(20)|           |          |         | extended |View definition: SELECT customer.first_name,    customer.last_name,    customer.email,    address.phone   FROM customer     JOIN address USING (address_id);

Summary

  • Use the PostgreSQLCREATE VIEW statement to create a new view in your database.
  • Use the\d+ command in psql to display the information of a view.

Last updated on

Was this page helpful?
Thank you for your feedback!
[8]ページ先頭
©2009-2025 Movatter.jp