![]() Output: Name | Owner | Location | Access privileges | Options | Size | Description Since the ts_primary tablespace has some data, you can view its information using the following command in psql: \db+ ts_primary VALUES( '', 1) Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) INSERT INTO deliveries(order_date, customer_id) The following statement creates a new table called deliveries and inserts a row into the table: CREATE TABLE deliveries (ĭelivery_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, The TABLESPACE clause indicates the tablespace that the logistics database will use to store data. TABLESPACE ts_primary Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) The following statement creates the logistics database that uses the ts_primary tablespace: CREATE DATABASE logistics Ts_primary | postgres | c:\pgdata\primary | | | 0 bytes | Name | Owner | Location | Access privileges | Options | Size | Description The db+ command shows more information such as size and access privileges: List of tablespaces (4 rows) Code language: Shell Session ( shell ) Ts_primary | postgres | c:\pgdata\primary To list all tablespaces in the current PostgreSQL database server, you use the \db command: \db Code language: Shell Session ( shell ) And the c:\pgdata\primary must exist before you execute the command. Notice that this statement used the Unix-style slashes for the directory path. LOCATION 'c:\pgdata\primary' Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) The following statement uses the CREATE TABLESPACE to create a new tablespace called ts_primary with the physical location c:\pgdata\primary. Once a tablespace is created, you can specify it in the CREATE DATABASE, CREATE TABLE and CREATE INDEX statements to store data files of the objects in the tablespace. ![]() PostgreSQL system users must own this directory in order to read and write data into it. The directory_path is the absolute path to an empty directory used for the tablespace. To assign another user as the owner of the tablespace, you specify it after the OWNER keyword. The name of the tablespace should not begin with pg_, because these names are reserved for the system tablespaces.īy default, the user who executes the CREATE TABLESPACE is the owner of the tablespace. LOCATION directory_path Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) To create new tablespaces, you use the CREATE TABLESPACE statement as follows: CREATE TABLESPACE tablespace_name For example, you can place the frequent access indexes or tables on devices that perform very fast e.g., solid-state devices, and put the tables containing archive data which is rarely used on slower devices.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |