PostgreSQL Basic Command operation

PostgreSQL Basic Command operation:

Login database:

[Email protected] ~]$ psql-utestwjw-h 127.0.0.1-dpostgres-p 36985

Password for user testwjw:

Psql.bin (9.5.9)

Type ' help ' for help.

Postgres=>

To switch databases:

Postgres=> \c TESTDB1

Connected to Database "TESTDB1" as User "TESTWJW".

To view all databases:

testdb1=> \l

Testdb1=> \list

List of databases

Name | Owner |   Encoding |    Collate |   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

TESTDB1 | TESTWJW | UTF8 | en_US. UTF-8 | en_US. UTF-8 | =TC/TESTWJW +

|          |             |             | | Testwjw=ctc/testwjw

TESTDB2 | TESTWJW | UTF8 | en_US. UTF-8 | en_US. UTF-8 |

(5 rows)

View all the tables:

Testdb1=> \dt

List of relations

Schema | Name |  Type | Owner

--------+-------+-------+---------

Public | T | Table | Testwjw

Public | T1 | Table | Testwjw

Public | TLB01 | Table | Testwjw

(3 rows)

Testdb1=>

To create a database:

[Email protected] ~]$ psql-p 36985

Psql.bin (9.5.9)

Type ' help ' for help.

postgres=# CREATE database testdb3 with encoding= ' UTF8 ' owner=testwjw;

CREATE DATABASE

[Email protected] ~]$ createdb testdb5-p 36985

[Email protected] ~]$ createdb testdb6-p 36985

To view the database that was created:

[Email protected] ~]$ psql-p 36985-c ' \list ' |egrep "TESTDB4|TESTDB5"

TESTDB4 | Postgres | UTF8 | en_US. UTF-8 | en_US. UTF-8 |

TESTDB5 | Postgres | UTF8 | en_US. UTF-8 | en_US. UTF-8 |

To delete the created database:

#以testwjw的身份连接服务器, delete the TESTDB1 database.

[Email protected] ~]$ dropdb-utestwjw-p 36985-e testdb1

DROP DATABASE testdb1;

[Email protected] ~]$ psql-p 36985-c ' \list ' |grep "TESTDB1"

Verify that the database has been deleted by viewing the system table:

[[email protected] ~]$ psql-p 36985-c "SELECT COUNT (*) from pg_database WHERE datname = ' testdb1 '"

Count

-------

0

(1 row)

Prove that this database is indeed deleted.

View all the tables and the single table structure in the database:

testdb2=# \dt

List of relations

Schema | Name |  Type | Owner

--------+------+-------+---------

Public | TLB2 | Table | Testwjw

(1 row)

testdb2=# \d TLB2

Table "PUBLIC.TLB2"

Column | Type | Modifiers

--------+-----------------------+-----------

ID | Integer |

Pay | Character varying (20) |

name | Character varying (6) |

Indexes:

"Uniq" UNIQUE CONSTRAINT, Btree (ID)

testdb2=#

To view the index details:

testdb2=# \d Uniq;

Index "Public.uniq"

Column | Type | Definition

--------+---------+------------

ID | Integer | Id

Unique, btree, for table "PUBLIC.TLB2"

\d+ command: Will display more detailed information than the \d command, in addition to those described earlier, it will also display any comments related to the table column, as well as the OID that appears in the table.

testdb2=# \d+

List of relations

Schema | Name |  Type |  Owner | Size | Description

--------+------+-------+---------+---------+-------------

Public | TLB2 | Table | TESTWJW | 0 bytes |

(1 row)

testdb2=# \d

List of relations

Schema | Name |  Type | Owner

--------+------+-------+---------

Public | TLB2 | Table | Testwjw

(1 row)

testdb2=#

List all the schemas:

testdb2=# \DN

List of schemas

Name | Owner

--------+----------

Public | Postgres

(1 row)

Create Schema:

testdb2=# CREATE schema sa;

CREATE SCHEMA

testdb2=# \DN

List of schemas

Name | Owner

--------+----------

Public | Postgres

SA | Postgres

(2 rows)

testdb2=#

Displays the time that SQL executes, which can be used with the \timing parameter:

testdb2=# \timing

Timing is on.

testdb2=# select * from TLB2;

ID | Pay | Name

----+-----+------

(0 rows)

time:0.177 ms

testdb2=#

If you want to list all roles or users in a database, you can use \du \DG, which is equivalent because users and roles in Postgressql are not differentiated.

testdb2=# \du

List of roles

Role name | Attributes | Member of

-----------+------------------------------------------------------------+-----------

Postgres | Superuser, create role, create DB, Replication, Bypass RLS | {}

TESTWJW | | {}

testdb2=# \DG

List of roles

Role name | Attributes | Member of

-----------+------------------------------------------------------------+-----------

Postgres | Superuser, create role, create DB, Replication, Bypass RLS | {}

TESTWJW | | {}

testdb2=#

To view a table field:

testdb2=# SELECT a.attname from Pg_class c,pg_attribute a,pg_type t where c.relname= ' tlb2 ' and a.attnum>0 and A.attreli D=c.oid and A.atttypid=t.oid;

AttName

---------

Id

Pay

Name

(3 rows)

time:0.586 ms

testdb2=# \dnp+

List of schemas

Name |  Owner |      Access Privileges | Description

--------+----------+----------------------+------------------------

Public | Postgres | postgres=uc/postgres+| Standard Public schema

| | =uc/postgres |

SA |                      Postgres | |

(2 rows)

testdb2=# \DN

List of schemas

Name | Owner

--------+----------

Public | Postgres

SA | Postgres

(2 rows)

testdb2=#

This article is from the "10931853" blog, please be sure to keep this source http://wujianwei.blog.51cto.com/10931853/1970402

PostgreSQL Basic Command operation