# Log in to a postgreSQL database
psql -U username dbname
# Get list of databases.
SELECT datname FROM pg_database;
# or
\l
# or
psql -l
# Get size of tables, sorted in descending order.
SELECT relname, relpages,reltuples FROM pg_class ORDER BY relpages DESC;
# Show tables in a database.
select * from information_schema.tables where table_schema='public' and table_type='BASE TABLE';# or
\dt
# Show structure of a table.
\d tablename
psql Commands
General
\c[onnect] [DBNAME|- [USER]] | connect to new database (currently "dfdata") |
\cd [DIR] | change the current working directory |
\copyright | show PostgreSQL usage and distribution terms |
\encoding [ENCODING] | show or set client encoding |
\h [NAME] | help on syntax of SQL commands, * for all commands |
\q | quit psql |
\set [NAME [VALUE]] | set internal variable, or list all if no parameters |
\timing | toggle timing of commands (currently off) |
\unset NAME | unset (delete) internal variable |
\! [COMMAND] | execute command in shell or start interactive shell |
Query Buffer
\e [FILE] | edit the query buffer (or file) with external editor |
\g [FILE] | send query buffer to server (and results to file or pipe) |
\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
\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||
\d [NAME] | describe table, index, sequence, or view |
\d{t|i|s|v|S} [PATTERN] (add "+" for more detail) | list tables/indexes/sequences/views/system tables |
\da [PATTERN] | list aggregate functions |
\db [PATTERN] | list tablespaces (add "+" for more detail) |
\dc [PATTERN] | list conversions |
\dC | list casts |
\dd [PATTERN] | show comment for object |
\dD [PATTERN] | list domains |
\df [PATTERN] | list functions (add "+" for more detail) |
\dg [PATTERN] | list groups |
\dn [PATTERN] | list schemas (add "+" for more detail) |
\do [NAME] | list operators |
\dl | list large objects, same as \lo_list |
\dp [PATTERN] | list table, view, and sequence access privileges |
\dT [PATTERN] | list data types (add "+" for more detail) |
\du [PATTERN] | list users |
\l | list all databases (add "+" for more detail) |
\z [PATTERN] | list table, view, and sequence access privileges (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 | show only rows (currently off) | ||||||||||||
\T [STRING] | set HTML
Copy, Large Object
|
No comments:
Post a Comment