PostgreSQL Common Commands

1.createdb database name
Generate Database
2.DROPDB database name
Deleting a database

3.CREATE User Name
Create user
4.drop User Name
Delete User

5.SELECT usename from Pg_user;
View System User Information
\du

7.SELECT version ();
View version Information

8.psql database name
Open the Psql Interactive tool

9.mydb=> \i Basics.sql
The \i command reads the command from the specified file.

10.COPY weather from '/home/user/weather.txt ';
Bulk importing content from a text file into a wether table

11.SHOW Search_path;
Show Search Path

12. Create a user
CREATE user username with PASSWORD ' password '

13. Create a pattern
CREATE SCHEMA MySchema;

14. Delete Mode
DROP SCHEMA MySchema;

15. View the search mode
SHOW Search_path;

16. Set the search mode
SET Search_path to Myschema,public;

17. Create a table space
Create tablespace table space Name location ' file path ';

18. Display Default Tablespace
Show Default_tablespace;

19. Set the default table space
Set default_tablespace= table space name;

20. Specify User Login
Psql Mtps-u

21. Displays the current system time,
Now ()

22. Configuring the Plpgsql language
CREATE LANGUAGE ' plpgsql ' HANDLER plpgsql_call_handler

23. Delete a rule

DROP RULE name on relation [CASCADE | RESTRICT]

Input
Name
The existing rule to delete.

Relation
The name of the relationship that the rule applies to (can have an outline decoration).

CASCADE
Automatically deletes objects that depend on this rule.

RESTRICT
If there are any dependent objects, the deletion of this rule is denied. This is the default.

24. Date Format function
Select ' P ' | | To_char (current_date, ' YYYYMMDD ') | | ' 01 '

25. Generating groups
Create Group Name

26. Modify the User Attribution group
Alter Group name Add user name

26. Assigning permissions to a group

Grant action on table name to group group name:

27. Create a role
Create role Name

28. Deleting a role
Drop role Name

29. Get the current version of PostgreSQL
SELECT version ();

30. Perform scheduled tasks in Linux
Execute with Crontab
Su root-c "psql-p 5433-u developer mtps-c ' Select test () '"
The developer user's password is stored in the environment variable Pgpassword.

31. Whether the query table exists
SELECT * from pg_statio_user_tables where relname= ' your table name ';

32. Copy Schema permissions for users
Grant all on SCHEMA scope name to user name

33. Entire Database Export
PG_DUMPALL-D-P Port number-H server ip-u postgres (username) >/home/xiaop/all.bak

34. Database Backup Recovery
Psql-h 192.168.0.48-p 5433-u Postgres

35. Current Date function
Current_date

36. Return to the 5 records beginning with article tenth
SELECT * FROM TabName limit 5 offset 10;

37. Assigning mode permissions to Users
Grant on schema developer to Udatahouse

38. Convert Characters to date time
Select To_timestamp (' 2010-10-21 12:31:22 ', ' yyyy-mm-dd hh24:mi:ss ')

39. Database backup
Pg_dumpall-h 192.168.0.4-p 5433-u Postgres >/databack/postgresql2010012201.dmp
Enter the password multiple times after 8.1

40.\dn
View Schema

41. Delete Schema
Drop schema _clustertest cascade;

42. Export the table

./pg_dump-p port number-u user-T table name-F backup file location database;

43. String manipulation functions
SELECT DISTINCT (Split_part (IP, '. ', 1) | | '. ' | | Split_part (IP, '. ', 2)) from T_t_userip Order by (Split_part (IP, '. ', 1) | | '. ' | | Split_part (IP, '. ', 2));

44. Delete Table PRIMARY key
ALTER TABLE name drop CONSTRAINT primary Key name;

45. Create a table space
Create tablespace space Name Location ' path '

46. View Table Structure
SELECT * FROM Information_schema.columns

./postgres-d/usr/local/src/data
Or
./pg_ctl-d/usr/local/src/data-l LogFile Start

47. View the database size
SELECT Pg_size_pretty (pg_database_size (' MTPs ')) as fulldbsize;

48. View the database table size
SELECT Pg_size_pretty (pg_total_relation_size (' Developer.t_l_collectfile ')) as Fulltblsize,
Pg_size_pretty (pg_relation_size (' Developer.t_l_collectfile ')) as Justthetblsize

49. Set the output of SQL statements that execute more than a specified number of seconds to the log
Log_min_duration_statement = 3

50. Over a certain number of seconds SQL automates execution plan
shared_preload_libraries = ' Auto_explain '
custom_variable_classes = ' Auto_explain '
Auto_explain.log_min_duration = 4s

51. Database backup
Select Pg_start_backup (' Backup baseline ');
Select Pg_stop_backup ();

Recovery.conf
restore_command= ' cp/opt/buxlog/%f%p '

52. Rebuilding the Index
REINDEX {INDEX | TABLE | DATABASE | SYSTEM} name [Force]
INDEX
Re-establish the declared index.

TABLE
All indexes of the declared table are re-established. If the table has a subordinate "TOAST" table, the table is also re-indexed.

DATABASE
Rebuilds all indexes in the current database. Indexes on shared system tables are ignored except in standalone run mode (see below).

SYSTEM
Rebuilds the indexes on all system tables on the current database. The index on the user table is not processed. In addition, shared system tables are ignored (see below) except in single-master mode.

Name
The name of the index, table, or database in which the index needs to be rebuilt. Table and index names can have schema adornments. Currently, REINDEX database and REINDEX SYSTEM can only reconstruct the indexes of the current databases, so its parameters must match the name of the current database.

Force
This is an obsolete option, and if declared, it will be ignored.

54. Data dictionary View table structure
SELECT column_name, data_type from information_schema.columns where table_name = ' blog_sina_content_train ';

52. View Locked Table
SELECT pg_class.relname as table, pg_database.datname as database, PID, mode, granted
From Pg_locks, Pg_class, pg_database
WHERE pg_locks.relation = pg_class.oid
and pg_locks.database = pg_database.oid;

53. View Client Connection Status
SELECT client_addr, client_port,waiting,query_start,current_query from Pg_stat_activity;

54. Read the database frequently. conf configuration
Show All

55. Modifying database postgresql.conf Parameters
Modify postgresql.conf Content
Pg_ctl Reload

56. Rollback Log Forced recovery
Pg_resetxlog-f Database file path

Idvalue | Remark
----------+--------
33953557 | Inser

57. The current date falls within the first week of the year
Select EXTRACT (Week from TIMESTAMP ' 2010-10-22 ');

58. Show recently executed commands
\s

I. SQL command
abort-exiting the current transaction
Alter aggregate-Modify the definition of a clustered function
Alter conversion-Modify the definition of an encoding transformation
Alter database-Modify a database
Alter domain-change the definition of a field
Alter function-Modify the definition of a function
Alter group-Modify a user group
Alter index-change the definition of an index
Alter language-to modify the definition of a process language
Alter operator-change the definition of an operator
Alter OPERATOR class-Modify the definition of an operation
Alter role-modifies a database role
Alter schema-to modify the definition of a pattern
Alter sequence-change the definition of a sequence builder
Alter table-to modify the definition of a table
Alter tablespace-change the definition of a table space
Alter trigger-change the definition of a trigger
Alter type-to change the definition of a type
Alter user-change database user account
analyze-collection of statistics related to the database
begin-Start a transaction block
checkpoint-forcing a transaction log checkpoint
close-Closing a cursor
cluster-a table-set cluster based on an index
comment-define or alter the commentary of an object
commit-committing the current transaction
Commit prepared-commits a previously prepared transaction for two-phase commit
copy-copying data between tables and files
CREATE aggregate-defines a new aggregation function
CREATE cast-defines a user-defined transformation
CREATE CONSTRAINT trigger-define a new constraint trigger
CREATE conversion-defines a new encoding conversion
Create database-creating a new database
CREATE domain-define a new domain
CREATE function-defines a new function
CREATE group-Define a new user group
CREATE index-define a new index
CREATE language-defines a new process language
CREATE operator-defines a new operator
CREATE OPERATOR class-Define a new operator descriptor
CREATE role-define a new database ROLE
CREATE rule-defines a new rewrite rule
CREATE schema-define a new pattern
Create sequence-creates a new sequence generator
CREATE table-define a new table
CREATE table as-Define a new table from the results of a query
CREATE tablespace-define a new table space
CREATE trigger-define a new trigger
CREATE type-defines a new data type
Create user-creates a new database user account
CREATE view-Define a view
deallocate-to delete a prepared query
Declare-Defining a cursor
delete-Delete rows from a table
Drop aggregate-Delete a user-defined aggregate function
Drop cast-Delete a user-defined type conversion
Drop conversion-Delete a user-defined encoding conversion
Drop database-Delete a database
Drop domain-Delete a user-defined domain
Drop function-Delete a function
Drop group-Delete a user group
Drop index-Delete an index
Drop language-Delete a process language
Drop operator-Delete an operator
Drop OPERATOR class-Delete an action descriptor
Drop role-Delete a database role
Drop rule-Delete a rewrite rule
Drop schema-Delete a pattern
Drop sequence-Delete a sequence
Drop table-Delete a table
Drop tablespace-Delete a table space
Drop trigger-Delete a trigger definition
Drop type-Delete a user-defined data type
Drop user-Delete a database user account
Drop view-Delete a view
End-Commit the current transaction
execute-execute a prepared query
explain-Show statement Execution planning
fetch-fetching rows from a query with cursors
Grant-Defining access rights
insert-creating a new row in a table
listen-Monitoring a notification
load-loading or overloading a shared library file
lock-explicitly locks a table
move-reposition a cursor
notify-Generate a notification
prepare-Create a prepared query
PREPARE transaction-Prepare two-phase commits for the current transaction
reindex-Rebuilding an index
RELEASE savepoint-Delete a previously defined save point
reset-restores a run-time parameter value to its default value
revoke-Delete access rights
rollback-exiting the current transaction
ROLLBACK prepared-Cancel a previously prepared transaction for two-phase commit
ROLLBACK to-rollback to a save point
savepoint-define a new savepoint in the current transaction
select-fetching several rows from a table or view
SELECT into-defines a new table from the results of a query
set-changing run-time parameters
Set constraints-sets the constraint check mode for the current transaction
SET Role-set The current user identifier of the current session
Set session authorization-user identifier and current user identifier for the current session
Set transaction-Setting the properties of the current transaction
show-displaying values for run-time parameters
Start transaction-starting a transaction block
truncate-empty one or a bunch of tables
unlisten-Stop Monitoring Notification information
update-updating rows in a table
vacuum-garbage collection and optionally analyze a database
Ii. Client-side applications
clusterdb-to build a PostgreSQL database
createdb-creating a new PostgreSQL database
createlang-definition of a new PostgreSQL process language
createuser-define a new PostgreSQL user account
dropdb-Deleting an existing PostgreSQL database
droplang-Deleting a PostgreSQL process language
dropuser-Deleting a PostgreSQL user account
ecpg-Embedded SQL C Preprocessor
pg_config-retrieving information for an installed version of PostgreSQL
pg_dump-to extract a PostgreSQL database into a script file or other archive file
pg_dumpall-extracting a PostgreSQL db cluster into a script file
pg_restore-Restore the PostgreSQL database from a backup file created by Pg_dump.
Psql-postgresql Interactive Terminal
vacuumdb-collect garbage and analyze a PostgreSQL database
Iii. PostgreSQL Server Application
initdb-creating a new PostgreSQL database cluster
ipcclean-Remove shared memory and semaphores from the failed PostgreSQL server
pg_controldata-Display control information for a PostgreSQL cluster
pg_ctl-start, stop and restart PostgreSQL
pg_resetxlog-resetting the pre-write log and other control content for a PostgreSQL db cluster
postgres-running a PostgreSQL server in single-user mode
Postmaster-postgresql Multi-user database server

59. Export Database Roles

/data/pgsql/bin/pg_dumpall-p 5432-u postgres-r >/tmp/postgres_8.3_role.bak

60. Modify the sequence owner

Grant all on sequence name to owner;

61. Modify the sequence initial value

Alter SEQUENCE sequencename START value;

62. View sequence Current values

SELECT currval (' sequencename ');

63. View sequence Next value

SELECT nextval (' sequencename ');

64. Set sequence Current value

Alter SEQUENCE sequencename restart with startvalue;

SELECT nextval (' sequencename ');

65. Query Table Structure

SELECT A.attnum,a.attname as field,t.typname as type,a.attlen as Length,a

. Atttypmod as lengthvar,a.attnotnull as Notnull

From Pg_class C,pg_attribute A,pg_type t

WHERE c.relname= table name and A.attnum > 0 and A.attrelid = c.oid and a

. Atttypid = T.oid

66. Output the query results directly to a file

In the Psql

\o file path

Select Datname,rolname from Pg_database a left outer joins Pg_roles B on a.datdba=b.oid;

\o

67. Querying the database for all

Select Datname,rolname from Pg_database a left outer joins Pg_roles B on a.datdba=b.oid;

68. Ending a transaction that is executing

SELECT * from Pg_stat_activity;

Select Pg_cancel_backend (' Procpid ');

60. End Session

SELECT * from Pg_stat_activity;

Select pg_terminate_backend(' procpid ');

61.postgresql de-escaping character function

Set the standard_conforming_strings in the postgresql.conf file to On

62. Query executing SQL

SELECT
Procpid,
Start
Now ()-Start as lap,
Current_query
From
(SELECT
Backendid,
Pg_stat_get_backend_pid (S.backendid) as Procpid,
Pg_stat_get_backend_activity_start (S.backendid) as Start,
Pg_stat_get_backend_activity (S.backendid) as Current_query
From
(SELECT Pg_stat_get_backend_idset () as Backendid) As S
) as S
WHERE
Current_query <> "
ORDER by
Lap DESC;

PostgreSQL Common Commands