Shulou(Shulou.com)06/01 Report--

A database is a collection of schemas, which contain tables, functions, and so on. Hierarchy: server, database, schema, table (or some other type of object, such as function). List existing databases: SELECT datname FROM pg_database; or:\ l.1. Create a database: when create database name; initdb initializes a datastore, the first database created by default is postgres. The second database, template1, is also created during database cluster initialization. Whenever a new database is created in a cluster, it is cloned from template1. Any changes you make are propagated from template1 to all subsequently created databases. Create a database for others, which can be configured and managed: CREATE DATABASE dbname OWNER rolename;- 2. Template database: standard system database template1. Make a template for the new database. Template1 contains data specific to coding or locale. The second standard system database, template0, contains the same data as the original content, template1. That is, only the PostgreSQL version of the predefined standard object. After the template0 database cluster is initialized, it should never be changed. You can create an "original" user database by instructing CREATE DATABASE to replicate template0. Create the database template0: CREATE DATABASE dbnameTEMPLATE template0; createdb-T template0 dbname- 3. Database configuration sets database-specific default values: for example: disable GEQO optimizer for a given database: ALTER DATABASE mydb SET geqo TO off; revokes database-specific default values: ALTER DATABASE dbname RESET varname;- 4. 0. Delete database: drop database name; deleting a database deletes all objects contained in the database. -5. Tablespaces: tablespaces in PostgreSQL allow database administrators to define where files that represent database objects can be stored in the file system. Once created, you can reference tablespaces by name when you create database objects. Tablespaces allow administrators to optimize performance using knowledge of the usage patterns of database objects. Tablespaces are also part of a database cluster. Create a tablespace: all objects created by CREATE TABLESPACE fastspace LOCATION'/ ssd1/postgresql/data'; in the tablespace will be stored in files in this directory. Delete empty tablespaces: drop tablespace tablespacename; can assign tables, indexes, and entire databases to specific tablespaces. Users with CREATE given tablespace privileges must pass the tablespace name as an argument to the relevant command. CREATE TABLE foo (I int) TABLESPACE space1; or: SET default_tablespace = space1; CREATE TABLE foo (I int); temp_tablespaces parameter, which is used to determine the location of temporary tables and indexes, and to sort temporary files for large datasets, etc. The tablespace associated with the database is used to store the system directory of the database. In addition, it is the default tablespace for tables, indexes, and temporary files created in the database. Two tablespaces are automatically created when the database cluster is initialized. The pg_global table is used to share the system directory. The pg_ default table is the default tablespace template1 and template0 database. View existing tablespaces: SELECT spcname FROM pg_tablespace; or\ db command to view. -6 Encoding: LC_COLLATE string sort order LC_CTYPE character classification (what are letters? Its capital letters are equivalent? The language of the LC_MESSAGES message LC_MONETARY format currency amount LC_NUMERIC format numeric LC_TIME format date and time check the locale installed on the system: locale-a command view. Check the currently available locale and use the query SELECT * FROM pg_collation or\ dOS+- 7. The character set supports setting the default character set (encoding): the encoding of the initdb-E UTF-8 database is stored in the system directory pg_database. \ l View. Or: automatic character set conversion between SELECT * FROM pg_database; server and client, and the conversion information is stored in the pg_conversion system directory. View conversion information: select * from pg_conversion; enable automatic character set conversion, change client encoding:\ encoding utf-8; set client encoding: SET CLIENT_ENCODING TO' value'; view current client encoding: SHOW client_encoding; reset to default encoding: RESET client_encoding

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.