Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionNext revisionBoth sides next revision | ||
labs:postgresql [2008/05/09 15:47] – admin | labs:postgresql [2016/06/27 16:09] – [Creating Tables] admin | ||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== PostgreSQL Help ====== | ||
+ | ===== SQL in PostgreSQL ===== | ||
+ | ==== Select Statements ==== | ||
+ | |||
+ | Returns the results as a virtual table. | ||
+ | |||
+ | General format: | ||
+ | |||
+ | SELECT c0_name, c1_name, ..., cn_name | ||
+ | FROM table1_name, | ||
+ | [ WHERE condition ]; | ||
+ | |||
+ | You can use * to mean all columns in the table. | ||
+ | |||
+ | === Explanation of Various Joins === | ||
+ | |||
+ | The picture below comes from [[http:// | ||
+ | |||
+ | {{http:// | ||
+ | |||
+ | |||
+ | ==== Insert Statements ==== | ||
+ | |||
+ | Preferred way: | ||
+ | INSERT INTO table_name (c0_name, c1_name, ..., cn_value) | ||
+ | VALUES ( c0_value, c1_value, ... cn_value ); | ||
+ | |||
+ | Column names are not required but depends on order that the columns are in the table: | ||
+ | INSERT INTO table_name VALUES ( c0_value, c1_value, ... cn_value ); | ||
+ | |||
+ | Automatic generation of IDs: | ||
+ | |||
+ | INSERT INTO Students VALUES (nextval(' | ||
+ | |||
+ | If the table is set up appropriately, | ||
+ | |||
+ | INSERT INTO Students (fname) VALUES (' | ||
+ | |||
+ | Update the sequence value after a COPY FROM: | ||
+ | |||
+ | BEGIN; | ||
+ | COPY distributors FROM ' | ||
+ | SELECT setval(' | ||
+ | END; | ||
+ | ==== Update Statements ==== | ||
+ | |||
+ | UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...] | ||
+ | [ FROM fromlist ] | ||
+ | [ WHERE condition ] | ||
+ | |||
+ | |||
+ | ===== Creating Database ===== | ||
+ | |||
+ | createdb < | ||
+ | |||
+ | ===== Dropping Database ===== | ||
+ | |||
+ | dropdb < | ||
+ | |||
+ | ===== Creating Tables ===== | ||
+ | |||
+ | Example: | ||
+ | |||
+ | CREATE TABLE table_name ( | ||
+ | id integer NOT NULL default nextval(' | ||
+ | eagle_id char(9), | ||
+ | floor_to_graffito_height varchar(30), | ||
+ | description text, | ||
+ | comment text, | ||
+ | translation text, | ||
+ | FOREIGN KEY ( eagle_id ) REFERENCES EAGLE_inscriptions(id), | ||
+ | PRIMARY KEY (id) | ||
+ | ); | ||
+ | ===== Creating Sequences ===== | ||
+ | | ||
+ | |||
+ | ===== Viewing Table Information ===== | ||
+ | |||
+ | You may want to look at a table' | ||
+ | |||
+ | \d Users | ||
+ | |||
+ | to view that information. | ||
+ | |||
+ | ===== Administration ===== | ||
+ | |||
+ | |||
+ | |||
+ | ==== List Databases ==== | ||
+ | |||
+ | psql -l | ||
+ | |||
+ | or, inside of psql | ||
+ | |||
+ | \l | ||
+ | |||
+ | ==== Viewing Permissions ==== | ||
+ | \dp | ||
+ | ==== Viewing Users ==== | ||
+ | \du | ||
+ | |||
+ | or | ||
+ | |||
+ | SELECT * FROM pg_roles; | ||
+ | |||
+ | ==== Creating User ==== | ||
+ | |||
+ | CREATE USER username [WITH PASSWORD '' | ||
+ | |||
+ | Or | ||
+ | |||
+ | createuser | ||
+ | |||
+ | ==== Adding user to groups ==== | ||
+ | |||
+ | ALTER GROUP groupname ADD user username [, ... ] | ||
+ | ==== Granting Permissions ==== | ||
+ | |||
+ | GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } | ||
+ | [,...] | ALL [ PRIVILEGES ] } | ||
+ | ON [ TABLE ] tablename [, ...] | ||
+ | TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] | ||
+ | |||
+ | ==== Revoking Permissions ==== | ||
+ | REVOKE [ GRANT OPTION FOR ] | ||
+ | { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } | ||
+ | [,...] | ALL [ PRIVILEGES ] } | ||
+ | ON [ TABLE ] tablename [, ...] | ||
+ | FROM { username | GROUP groupname | PUBLIC } [, ...] | ||
+ | [ CASCADE | RESTRICT ] | ||
+ | |||
+ | ==== Altering Tables ==== | ||
+ | |||
+ | === Add a Column === | ||
+ | |||
+ | ALTER TABLE table_name ADD COLUMN col_name type ... | ||
+ | |||
+ | === Change Column === | ||
+ | |||
+ | ALTER TABLE quizzes ALTER COLUMN name TYPE varchar(30); | ||
+ | |||
+ | ==== Dump Data Base ==== | ||
+ | |||
+ | pg_dump < | ||
+ | |||
+ | ==== Connecting to Another Database ('' | ||
+ | |||
+ | \c dbname |