Pgsql commands that are commonly used

Pgsql commonly used commands:
1. Create a database
Create database name with owner username;

2. Create a user
Create role with Createdb;
Create user user_name;

3. Change the password
Alter user user_name with password ' new password ';

Query information for all tables in the current DB:
"SELECT * FROM Pg_tables"--get information about all the tables in the current db (Pg_tables is a system view here)

Modify Table Name:
ALTER TABLE table_name RENAME to new_table_name;

To modify a table field structure:
Alter TABLE Journal Alter Is_retail TYPE character (350);

The method of modifying a field error cannot be automatically converted to type integer HINT: You may need to specify a "USING Is_retail::integer".
ALTER TABLE Goods.goods
Alter COLUMN is_retail SET data TYPE smallint using 0

Modify the field name of a table
ALTER TABLE Config.sql_config RENAME config_id to sql_config_id;
To remove a non-null restriction property for a table field
ALTER TABLE Config.sql_config ALTER note DROP NOT NULL;
To delete a table field:
ALTER TABLE Config.sql_config DROP Null_exit_text

FOREIGN key:
ALTER TABLE score table ADD constraint Fk_studentno foreign key (Studentno) references Student (STUDENTNO)
On UPDATE CASCADE on DELETE CASCADE
Cascade UPDATE, Cascade Delete, so that when the main table student is deleted, all scores for that student in the score table will be deleted.

Exporting the entire database requires a client installation Pg_dump
Pg_dump-h localhost-u postgres (user name) database name (default is the same as user name) >/data/dum.sql
Pg_dump-h 192.168.1.98-u k branch.server > WX. S
Pg_dump-h 192.168.2.152-u k centre.server > Or.ce.s

Import the entire database
Psql-u postgres (user name) database name (default is the same as user name) </data/dum.sql
Psql-u Postgres centre.se < Sql_config.dll.sql

With Psql, this machine does not have to specify a password
-D Specify Database
-U Specify user
-f Specifies the exported file
-H Specify Server
--password instructions need to enter a password
Natively
psql-d yemai-u yemai-f Yemai.sql
psql-d hold-u hold-f. sql
Remote
Psql-h 192.168.2.176-d centre.se-u Postgres sql.sql

Export a table
Pg_dump-h localhost-u postgres (user name) database name (default is the same as user name)-T table (table name) >/data/dum.sql
Pg_dump-h 192.168.2.176-u postgres centre.se-t config.sql_config > C.S.SL

Function
Replace string
Replace

Pg_dump-h localhost-u postgres "centre.se" > Centre.se.sql

Common introduction commands for Psql:
\d
\DN View Table
\q exit
\du View all users, roles

Modification time
Select Now () + interval ' 2 years ';

Pgsql commands that are commonly used