 |
|
|
User:goeko > Database/SQL > Postgres
PostgresTable of contentsHere are some notes about using Postgres
Changeing the postgres data dirTo /home/postgres Give postgres 'kill -HUP' it will reread the config files.
Help with CommandWelcome to psql 7.4.19, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
freeside=> \?
General
\c[onnect] [DBNAME|- [USER]]
connect to new database (currently "freeside")
\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
\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)
\dn [PATTERN] list schemas
\do [NAME] list operators
\dl list large objects, same as \lo_list
\dp [PATTERN] list table 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 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|
recordsep|tuples_only|title|tableattr|pager})
\t show only rows (currently off)
\T [STRING] set HTML <table> tag attributes, or unset if none
\x toggle expanded output (currently off)
Copy, Large Object
\copy ... perform SQL COPY with data stream to the client host
\lo_export
\lo_import
\lo_list
\lo_unlink large object operations
That info is available from the command line with '\?'
How to get the nextval from a sequence on the psql command linesselect nextval( 'sequence_name');
Example of creating a tableCREATE TABLE info ( iid INT2 PRIMARY KEY, nid INT2 NOT NULL, info TEXT, creation DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP );
Working with UsersPostgres relies on the Unix system to manager users, thus commands need to be done as the postgres at the command line. su - postgres # This will prompt for a user password createuser -d -P sql-ledger #createuser adbuser #pgsql template1 #alter user adbuser password 'foubar1'; #\q createdb -U adbuser the_db exit
To list usersselect * from pg_user; or \du
To set a users passwordalter user freeside password 'InsertPasswordHere!';
Database BackupPostgres documentation on backups http://www.postgresql.org/docs/7/sta...ge-ag17965.htm
Some example dump command lines pg_dumpall -h localhost > db_backup_20050206.pgsql #used localhost for auth reason. pg_dump -c -C -f outputfile.psql database
To reload from the backup file (Notes: This will do a "fresh load" [delete and than then load the backup]. Use at your own risk!) dropdb -U dbuser thedatabase createdb -U dbuser thedatabase cat thedatabase_dbdump_20070601.text | psql -U dbuser thedatabase
Data Types (from Postgres Version 7.4.7)List of data types Schema | Name | Description ------------+-----------------------------+------------------------------------------------------------------- pg_catalog | abstime | absolute, limited-range date and time (Unix system time) pg_catalog | aclitem | access control list pg_catalog | "any" | pg_catalog | anyarray | pg_catalog | anyelement | pg_catalog | bigint | ~18 digit integer, 8-byte storage pg_catalog | bit | fixed-length bit string pg_catalog | bit varying | variable-length bit string pg_catalog | boolean | boolean, 'true'/'false' pg_catalog | box | geometric box '(lower left,upper right)' pg_catalog | bytea | variable-length string, binary values escaped pg_catalog | "char" | single character pg_catalog | character | char(length), blank-padded string, fixed storage length pg_catalog | character varying | varchar(length), non-blank-padded string, variable storage length pg_catalog | cid | command identifier type, sequence in transaction id pg_catalog | cidr | network IP address/netmask, network address pg_catalog | circle | geometric circle '(center,radius)' pg_catalog | cstring | pg_catalog | date | ANSI SQL date pg_catalog | double precision | double-precision floating point number, 8-byte storage pg_catalog | inet | IP address/netmask, host address, netmask optional pg_catalog | int2vector | array of 32 int2 integers, used in system tables pg_catalog | integer | -2 billion to 2 billion integer, 4-byte storage pg_catalog | internal | pg_catalog | interval | @ <number> <units>, time interval pg_catalog | language_handler | pg_catalog | line | geometric line (not implemented)' pg_catalog | lseg | geometric line segment '(pt1,pt2)' pg_catalog | macaddr | XX:XX:XX:XX:XX:XX, MAC address pg_catalog | money | monetary amounts, $d,ddd.cc pg_catalog | name | 63-character type for storing system identifiers pg_catalog | numeric | numeric(precision, decimal), arbitrary precision number pg_catalog | oid | object identifier(oid), maximum 4 billion pg_catalog | oid | object identifier(oid), maximum 4 billion pg_catalog | oidvector | array of 32 oids, used in system tables pg_catalog | opaque | pg_catalog | "path" | geometric path '(pt1,...)' pg_catalog | point | geometric point '(x, y)' pg_catalog | polygon | geometric polygon '(pt1,...)' pg_catalog | real | single-precision floating point number, 4-byte storage pg_catalog | record | pg_catalog | refcursor | reference cursor (portal name) pg_catalog | regclass | registered class pg_catalog | regoper | registered operator pg_catalog | regoperator | registered operator (with args) pg_catalog | regproc | registered procedure pg_catalog | regprocedure | registered procedure (with args) pg_catalog | regprocedure | registered procedure (with args) pg_catalog | regtype | registered type pg_catalog | reltime | relative, limited-range time interval (Unix delta time) pg_catalog | "SET" | set of tuples pg_catalog | smallint | -32 thousand to 32 thousand, 2-byte storage pg_catalog | smgr | storage manager pg_catalog | text | variable-length string, no limit specified pg_catalog | tid | (Block, offset), physical location of tuple pg_catalog | timestamp without time zone | date and time pg_catalog | timestamp with time zone | date and time with time zone pg_catalog | time without time zone | hh:mm:ss, ANSI SQL time pg_catalog | time with time zone | hh:mm:ss, ANSI SQL time pg_catalog | tinterval | (abstime,abstime), time interval pg_catalog | "trigger" | pg_catalog | "unknown" | pg_catalog | void | pg_catalog | xid | transaction id (62 rows)
|