Execute initialization of SQL scripts through the command line is a common requirement. You can execute the following operations on the command line:
If the naming performed is just to create the user, edit the user, and create the database, you can not specify the -d parameter.
psql -U username -d myDataBase -a -f
If it is a remote database, add the -h parameter to specify the host address.
psql -h host -U username -d myDataBase -a -f
Supplementary: PostgreSQL operation-psql basic command
1. Establish a database connection
Access to PostgreSQL database: psql -h IP address -p port -U database name
The database password will be requested to be entered later
2. Access the database
1. List the database: \l
2. Select the database:\c Database name
3. View all tables in this library: \dt
4. Switch the database:\c interface
5. Check a table structure in a library: \d table name
6. Check the records of a table in a certain library: select * from apps limit 1;
7. Display character set: \encoding
8. Exit psgl:\q
List all tables in the current database
\dtList table names
SELECT tablename FROM pg_tables; WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%' ORDER BY tablename;
List database name
\lor
SELECT datname FROM pg_database;
Switch database
\c Database name
1. Query via the command line
\d Database - Get the names of all tables
\d Table name - Get the table structure
2. Query through SQL statements
"select * from pg_tables"
——Get information about all tables in the current db (here pg_tables is the system view)
"select tablename from pg_tables where schemaname='public'"
——Get the names of all user-defined tables (here the "tablename" field is the name of the table, and "schemaname" is the name of the schema. If the user-defined tables are not processed, they are placed under the schema named public by default)
General
\copyright show PostgreSQL usage and distribution terms
\g [FILE] or ; execute query (and send results to file or |pipe)
\h [NAME] help on syntax of SQL commands, * for all commands
\q quit psql
Query Buffer
\e [FILE] [LINE] edit the query buffer (or file) with external editor
\ef [FUNCNAME [LINE]] edit function definition with external editor
\p show the contents of the query buffer
\r reset (clear) the query buffer
\s [FILE] display history or save it to file
\w FILE write query buffer to file
Input/Output
\copy ... perform SQL COPY with data stream to the client host
\echo [STRING] write string to standard output
\i FILE execute commands from file
\o [FILE] send all query results to file or |pipe
\qecho [STRING] write string to query output stream (see \o)
Informational
(options: S = show system objects, + = additional detail)
\d[S+] list tables, views, and sequences
\d[S+] NAME describe table, view, sequence, or index
\da[S] [PATTERN] list aggregates
\db[+] [PATTERN] list tablespaces
\dc[S] [PATTERN] list conversions
\dC [PATTERN] list casts
\dd[S] [PATTERN] show comments on objects
\ddp [PATTERN] list default privileges
\dD[S] [PATTERN] list domains
\det[+] [PATTERN] list foreign tables
\des[+] [PATTERN] list foreign servers
\deu[+] [PATTERN] list user mappings
\dew[+] [PATTERN] list foreign-data wrappers
\df[antw][S+] [PATRN] list [only agg/normal/trigger/window] functions
\dF[+] [PATTERN] list text search configurations
\dFd[+] [PATTERN] list text search dictionaries
\dFp[+] [PATTERN] list text search parsers
\dFt[+] [PATTERN] list text search templates
\dg[+] [PATTERN] list roles
\di[S+] [PATTERN] list indexes
\dl list large objects, same as \lo_list
\dL[S+] [PATTERN] list procedural languages
\dn[S+] [PATTERN] list schemas
\do[S] [PATTERN] list operators
\dO[S+] [PATTERN] list collations
\dp [PATTERN] list table, view, and sequence access privileges
\drds [PATRN1 [PATRN2]] list per-database role settings
\ds[S+] [PATTERN] list sequences
\dt[S+] [PATTERN] list tables
\dT[S+] [PATTERN] list data types
\du[+] [PATTERN] list roles
\dv[S+] [PATTERN] list views
\dE[S+] [PATTERN] list foreign tables
\dx[+] [PATTERN] list extensions
\l[+] list all databases
\sf[+] FUNCNAME show a function's definition
\z [PATTERN] same as \dp
Formatting
\a toggle between unaligned and aligned output mode
\C [STRING] set table title, or unset if none
\f [STRING] show or set field separator for unaligned query output
\H toggle HTML output mode (currently off)
\pset NAME [VALUE] set table output option
(NAME := {format|border|expanded|fieldsep|footer|null|
numericlocale|recordsep|tuples_only|title|tableattr|pager})
\t [on|off] show only rows (currently off)
\T [STRING] set HTML <table> tag attributes, or unset if none
\x [on|off] toggle expanded output (currently off)
Connection
\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
connect to new database (currently "postgres")
\encoding [ENCODING] show or set client encoding
\password [USERNAME] securely change the password for a user
\conninfo display information about current connection
Operating System
\cd [DIR] change the current working directory
\timing [on|off] toggle timing of commands (currently off)
\! [COMMAND] execute command in shell or start interactive shell
Variables
\prompt [TEXT] NAME prompt user to set internal variable
\set [NAME [VALUE]] set internal variable, or list all if no parameters
\unset NAME unset (delete) internal variable
Large Objects
\lo_export LOBOID FILE
\lo_import FILE [COMMENT]
\lo_list
\lo_unlink LOBOID large object operations
There are two ways to use commands in the postgresql data management system:
1. Internal command, start with a backslash \, such as: \l to display all databases
2. Standard SQL commands end with semicolons; or \g, multiple lines can be used
Key operations of databases:
1. Start the service 2. Log in 3. Create a database 4. Create a table 5. Insert a record into the table
6. Update/delete/query/modify operations 7. Exit 8. Stop service
Postgresql installed in Windows 7 uses GBK character set by default, and it is often impossible to use data tables that display Chinese. Solution:
Note: GBK is used when writing operations in postgresql under Windows 7, and UTF8 is used when reading operations;
Just set the character set to utf-8.
postgres=# \encoding utf-8 // Set the client's character setpostgres=# \encoding // Show the client's character setpostgres=# show client_encoding; // Show the client's character setpostgres=# show server_encoding; // Show the server's character set
Start the service:
net start postgresql-9.5
Stop service:
net stop postgresql-9.5
Get command help:
c:\> psql --help
Log in (Note: postgres is the default user, that is, the administrator):
path psql -h server -U username -d database -p Port address // -U is capitalizedC:\> psql -h localhost -U postgres -p 5432 // The postgres database is opened by defaultC:\> psql -h 127.0.0.1 -U postgres -d fengdos -p 5432 // Open fengdos databaseC:\> psql -U postgres // Quick login (all using default settings)// When using some users with passwords, they will be prompted to enter their password.user postgres Password: ILoveYou // No characters are displayed when entering// Show after success:psql (9.5.3) enter "help" Get help information. // Enterpostgresqldatabase系统提示符状态, ******=#Chinese=#Take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition and take the acquisition�postgres=# help // Get system help, as shown below:
You are using psql, which is a command line interface for accessing PostgreSQL
Type:
\copyright Show the Issuance Terms
\h Display instructions for SQL commands
\? Display the instructions for the pgsql command (pgsql internal command)
\gOr end with a semicolon (;) to execute the query
\q Exit Note: Database names are case sensitive.
postgres=# \help // Get help from SQL commands, same as \hpostgres=# \quit // Exit, same as \qpostgres=# \password dlf // Reset the password of the user dlf, and then \q is required to exit before it takes effectc:\>psql exampledb < // Import the file into the exampled databasepostgres=# \h select // Finely display the use of select commands in SQL commandspostgres=# \l // Show all databasespostgres=# \dt // Show all tables in the current databasepostgres=# \d [table_name] // Display the table structure of the specified table of the current databasepostgres=# \c [database_name] // Switch to the specified database, equivalent to usepostgres=# \du // Show all userspostgres=# \conninfo // Display the current database and connection informationpostgres=# \e // Enter the notepad SQL script editing status (closing it will be automatically executed on the command line after entering a batch command)postgres=# \di // View the index (to create association)postgres=# \prompt [Text] Name // Prompt the user to set internal variablespostgres=# \encoding [Feature encoding name] // Display or set the user-side character encoding* can write stored procedures in text files and then in psql state:postgres=# \i // will be imported (to the current database)postgres=# \df // View all stored procedures (functions)postgres=# \df+ name // View a stored procedurepostgres=# select version(); // Get version informationpostgres=# select usename from pg_user; // Get system user informationpostgres=# drop User username // Delete users
Other SQL commands are common, such as (standardized SQL statements):
*Create a database:
create database [Database name];
*Delete the database:
drop database [Database name];
*Create table:
create table ([Field name1] [type1] ;,[Field name2] [type2],......<,primary key (Field namem,Field namen,...)>;);
*Insert data in the table:
insert into Table name ([Field namem],[Field namen],......) values ([ListmValue of],[ListnValue of],......);
*Show table content:
select * from student;
*Rename a table:
alter table [Table nameA] rename to [Table nameB];
*Delete a table:
drop table [Table name];
*Add fields in existing tables:
alter table [Table name] add column [Field name] [type];
*Delete fields in the table:
alter table [Table name] drop column [Field name];
*Rename a field:
alter table [Table name] rename column [Field nameA] to [Field nameB];
*Set default values for a field:
alter table [Table name] alter column [Field name] set default [New default value];
*Remove default values:
alter table [Table name] alter column [Field name] drop default;
*Modify the data of a row and a column in the table:
update [Table name] set [Target field name]=[Target value] where [The row features];
*Delete a row of data in the table:
delete from [Table name] where [The row features]; delete from [Table name]; // Cut out the entire table
* Can be done using pg_dump and pg_dumpall. For example, back up sales database:
pg_dump drupal>/opt/Postgresql/backup/
1. List all table names query statements
SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%' ORDER BY tablename;
2. List all data in the table
SELECT * FROM someTable;
3. Execute external scripts
#/opt/PostgreSQL/8. 3/bin/psql - Upostgres; Log in to the database console interfacepostgres= # \i /root/db. sql \i The command is used to execute an externalsqlScript File。
4. Export the database as an external script
#/opt/PostgreSQL/8. 3/bin/ pg_dump - Upostgres - C - fdb. sql database -C create -f It is the exported file name
Insert hexadecimal number
INSERT INTO tableAAA VALUES( x'0001f' : : integer, 'Authorization' , 'Authority' )
6. Use TG_RELNAME to report an error ERROR: syntax error at or near "$1" at character
[lead]/Uwe//postgresql/2051/TG-RELNAME-problem Perhaps you will get some idea if you read the document: 37. 6. 4. Executing Dynamic Commands
Change: Execute dynamic statements
EXECUTE 'INSERT INTO TG_RELNAME VALUES (NEW.start_time , , NEW.end_time)';
7. Commonly used psql commands
a. \c tesdb1 - - Convert the currently connectedtestdbThe database changed to testdb1 。
b . \q - - Disconnected withPostgresServer connection
c . \l List all database names
\l+ List all database names以及字符集编码
d. \d [ name] Description table, index, sequence, Or view
List the table/index/sequence/view/System table
\d{t| i| s| v| S} [ model] ( add "+" Get more information)
- - List the table/index/sequence/view/System table
\d tablename - - View the structure of the table
\dt - - List all tables in the database
8. How to delete duplicate records in PostgreSQL
Deleting duplicate records in PostgreSQL is actually very simple. No matter how many rows are repeated, just add a column of rownum fields to the table where you want to delete duplicate records (id is the primary key in the table table), set the type to serial type, and then execute SQL
delete from deltest where rownum not in( select max(rownum) from deltest );
Finally, delete the column rownum
text:
Connect to database operations:
psql is a connection database shell command provided by postgresql database, format psql 【option】 dbname
Enter psql in the terminal and connect to the local database in the default way. The user name used is the user name used to log in to the Linux system.
psql -U username -W pass and psql -U username -W pass databasenaem can both implement the function of connecting to the database. The first method is to use the username password to pass to connect to the default database (the specific database link has not been clarified yet), and the second method is to use the username password to pass to connect to the username database. If the login is successful, a similar message will be displayed
Welcome to psql 8.0.6, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit
After the connection is successful, all commands use the "\"+ characters or word to complete the corresponding functions. Several commonly used trains
\l List all databases
\dt List all tables in the connected database
\diList all indexes in the connected database
\dvList all views in the connected database
\h sql command help
\?\ All command help
\qExit the connection
\d tablenameList the table structure of the specified tablename
You can try to execute the following two sentences sql
SELECT current_date SELECT version()
Is nothing happened? This is because the postgresql database requires it to be used; otherwise it will not be executed at the end, and the result will be seen afterwards.
What if we want to create a database?
We know that createdb and dropdb can create and delete databases, but what problems will happen if we execute at this time? You can give it a try, the prompt is an error.
Why?
Createdb and dropdb are shell scripts, so now there are two ways to execute
(1). Exit the connection and enter the terminal, enter createdb test —U user -W pass Wait for a while to prompt that the database is created successfully
dropdb test —U user -W pass hintdropsuccess
(2). Use \! createdb test —U user -W pass Wait for prompting that the database is successfully created
\! dropdb test —U user -W pass hintdropsuccess
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.