I. INTRODUCTION to psql is a PostgreSQL command line interactive client tool, similar to the command line tool sqlplus: 1 in Oracle allows you to interactively type SQL or commands, then they are sent to the PostgreSQL server, and then the SQL or command results are displayed; 2 input
I. INTRODUCTION to psql
Psql is a PostgreSQL command line interactive client tool, similar to the command line tool sqlplus in Oracle:
1. allow you to interactively type SQL or commands, then send them to the PostgreSQL server, and then display the SQL or command results;
2. the input content can also come from a file;
3. it also provides some meta commands and a variety of shell-like features to write scripts, as well as to automate the work of bulk tasks;
II. simple and practical psql
Follow the previous steps to switch the su-postgres user and use the psql tool to connect to the database.
1. view the databases
postgres=# \l List of databases Name | Owner | Encoding | Collation | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postgres (3 rows)
A. After installation, a database named postgres will be created by default. There are also two template databases template0 and template1;
B. When you recreate a database, the template database template1 is cloned by default;
C. template0 is the most simplified template library. when creating a database, if you specify to integrate the database, a simplified database will be created;
2. create an osdba database
postgres=# CREATE DATABASE osdba; CREATE DATABASE postgres-# \l List of databases Name | Owner | Encoding | Collation | Ctype | Access privileges-----------+----------+----------+-------------+-------------+----------------------- osdba | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postgres (4 rows)
3. access the osdba database
-bash-4.1$ psql osdba psql (8.4.20) Type "help" for help.
4. create table t in database osdba
osdba=# create table t(id int primary key,name varchar(40)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t" CREATE TABLE
5. View tables in the osdb database
osdba=# \d List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | t | table | postgres (1 row)
6. create a database named testdb and connect to the database named testdb.
osdba=# CREATE DATABASE testdb; CREATE DATABASE osdba=# \c testdb psql (8.4.20) You are now connected to database "testdb". testdb=#
III. common psql commands
1. \ d command-View all tables in the current database
osdba-# \d List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | t | table | postgres (1 row)
2. \ d command-view the structure definition of the table with a table life
osdba-# \d t Table "public.t" Column | Type | Modifiers --------+-----------------------+----------- id | integer | not null name | character varying(40) | Indexes: "t_pkey" PRIMARY KEY, btree (id)
3. \ d command-you can view the index information of table t.
osdba-# \d t_pkey Index "public.t_pkey" Column | Type --------+--------- id | integer primary key, btree, for table "public.t"
4. \ d command-and wildcards such as * OR?
osdba-# \d t* Table "public.t" Column | Type | Modifiers --------+-----------------------+----------- id | integer | not null name | character varying(40) | Indexes: "t_pkey" PRIMARY KEY, btree (id) Index "public.t_pkey" Column | Type --------+--------- id | integer primary key, btree, for table "public.t"
5. the \ d + command displays more detailed information than the \ d command, and displays comments associated with the table column
osdba-# \d+ List of relations Schema | Name | Type | Owner | Size | Description --------+------+-------+----------+---------+------------- public | t | table | postgres | 0 bytes | (1 row)
6. \ d commands that match different object types, such as \ dt-only displaying the matching table; \ di-only displaying the index; \ ds-only displaying the sequence; \ dv-only displaying the view; \ df-only display functions...
osdba-# \dt t* List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | t | table | postgres
7. \ dn command-list all schemas
osdba-# \dn List of schemas Name | Owner --------------------+---------- information_schema | postgres pg_catalog | postgres pg_toast_temp_1 | postgres public | postgres (5 rows)
8. \ db command-display all tablespaces
osdba-# \db List of tablespaces Name | Owner | Location ------------+----------+---------- pg_default | postgres | pg_global | postgres | (2 rows)
9. \ dg-list all roles and users in the database
osdba-# \dg List of roles Role name | Attributes | Member of -----------+-------------+----------- postgres | Superuser | {} : Create role : Create DB
10. \ dp-displays the table permission allocation
osdba-# \dp Access privileges Schema | Name | Type | Access privileges | Column access privileges --------+------+-------+-------------------+-------------------------- public | t | table | | (1 row)
4. commands for compiling the specified character set
1. \ encoding gbk-set the client's character encoding to gbk
V. \ pset command
1. \ pset command-used to set the output format, \ pset border 0/1/2: The output content has no border, only the internal border, and both the internal and external border have border
osdba-# \pset border 0 Border style is 0. osdba-# \dp Access privileges Schema Name Type Access privileges Column access privileges ------ ---- ----- ----------------- ------------------------ public t table (1 row) osdba-# \pset border 1 Border style is 1. osdba-# \dp Access privileges Schema | Name | Type | Access privileges | Column access privileges --------+------+-------+-------------------+-------------------------- public | t | table | | (1 row) osdba-# \pset border 2 Border style is 2. osdba-# \dp Access privileges +--------+------+-------+-------------------+--------------------------+ | Schema | Name | Type | Access privileges | Column access privileges | +--------+------+-------+-------------------+--------------------------+ | public | t | table | | | +--------+------+-------+-------------------+--------------------------+ (1 row)
2. \ x command-you can split each column of data in each row of the table into a single row for display. if there is too many lines of data to be split, you can use this command
osdba-# \x Expanded display is on. osdba-# \dp Access privileges +-[ RECORD 1 ]-------------+--------+ | Schema | public | | Name | t | | Type | table | | Access privileges | | | Column access privileges | | +--------------------------+--------+ osdba-# \x Expanded display is off. osdba-# \dp Access privileges +--------+------+-------+-------------------+--------------------------+ | Schema | Name | Type | Access privileges | Column access privileges | +--------+------+-------+-------------------+--------------------------+ | public | t | table | | | +--------+------+-------+-------------------+--------------------------+ (1 row)
5. psql usage skills
1. use the up/down key to call out previously used commands or SQL statements. two consecutive tab keys indicate completion or prompt input.
osdba-# \d \d \dc \dD \dew \dFd \dg \dn \ds \dT \da \dC \des \df \dFp \di \do \dS \du \db \dd \deu \dF \dFt \dl \dp \dt \dv
2. with the-E parameter added to psql, you can print out the actual SQL statements executed by various commands starting with "\" in psql.
-bash-4.1$ psql -E postgres psql (8.4.20) Type "help" for help. postgres=# \d ********* QUERY ********** SELECT n.nspname as "Schema", c.relname as "Name", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence'WHEN 's' THEN 'special' END as "Type", pg_catalog.pg_get_userbyid(c.relowner) as "Owner" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r','v','S','') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 1,2; ************************** No relations found
If you want to disable it immediately after use
postgres=# \set ECHO_HIDDEN off postgres=# \d No relations found.
The above is the PostgreSQL Quick start: the use of psql tool _ MySQL content, for more information, please follow the PHP Chinese network (www.php1.cn )!