PostgreSQL Chinese Learning Manual (Database Management)

PostgreSQL Chinese Learning Manual (Database Management). When establishing a connection with the database server, this connection can only be associated with one database and cannot be performed in one session.

PostgreSQL Chinese Learning Manual (Database Management). When establishing a connection with the database server, this connection can only be associated with one database and cannot be performed in one session.

I. Overview:

A database can be considered as a name set of an SQL object (database object). Generally, each database object (table, function, etc.) belongs to only one database. However, for some system tables, such as pg_database, it belongs to the entire cluster. More accurately, a database is a set of patterns, and a schema contains SQL objects such as tables and functions. Therefore, the complete object hierarchy should be like this: Server, database, schema, table, or other types of objects.
When establishing a connection with the database server, the connection can only be associated with one database, and access to multiple databases in one session is not allowed. For example, if you log on with postgres, the default database that this user can access is postgres. After you log on, if you execute the following SQL statement, you will receive an error message from PostgreSQL.
S = # SELECT * FROM MyTest. "MyUser". testtables;
ERROR: cross-database references are not implemented: "otherdb. otheruser. sometable"
LINE 1: select * from otherdb. otheruser. sometable
In PostgreSQL, databases are physically isolated from each other, and their access control is also performed at the session level. However, the mode is only a logical object management structure. whether an object in a mode can be accessed is controlled by the permission system.
Execute the following system table-based query statement to list some database sets.
SELECT datname FROM pg_database;
Note:PsqlApplication\ LMeta commands and-LThe command line option can also be used to list existing databases on the current server.

2. Create a database:

Run the following SQL statement on the PostgreSQL server to create a database.
Create database db_name;
After the database is successfully created, the current logon role automatically becomes the owner of the new database. This user privilege is also required when deleting the database. If you want the owner of the currently created database to be another role, you can execute the following SQL statement.
Create database db_name OWNER role_name;

3. modify Database Configuration:

The PostgreSQL server provides a large number of runtime configuration variables. You can specify a special value for a configuration variable in a database based on your actual situation, you can run the following SQL command to set a configuration of the database to a specified value instead of the default value.
Alter database db_name SET varname TO new_value;
In this way, the modified configuration value takes effect in subsequent sessions based on the database. If you want to cancel this setting and restore it to the original default value, you can execute the following SQL command.
Alter database dbname RESET varname;