====== 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, table2_name, ..., table_name
    [ WHERE condition ];

You can use * to mean all columns in the table.  Likely, you will want to specify only the columns you want to appear in your results.

=== Explanation of Various Joins ===

The picture below comes from [[http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-join|here]].

{{http://i.stack.imgur.com/1UKp7.png}}


==== 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('student_id_sequence'), 'firstname', '...");

If the table is set up appropriately, if you don't assign a value to the id, it will be automatically generated for you:

  INSERT INTO Students (fname) VALUES ('Joe');

Update the sequence value after a COPY FROM:

  BEGIN;
  COPY distributors FROM 'input_file';
  SELECT setval('serial', max(id)) FROM distributors;
  END;
==== Update Statements ====

  UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...]
    [ FROM fromlist ]
    [ WHERE condition ]


To update a table with a value in another table:
  
  update table2 set t2field = t1.field from table1 t1 where table2.keyfield2 = t1.keyfield1;
===== Creating Database =====

  createdb <dbname>

===== Dropping Database =====

  dropdb <dbname>

===== Creating Tables =====

Example: 

  CREATE TABLE table_name (
	id integer NOT NULL default nextval('inscription_id_seq'),
	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 =====
   create sequence "majors_id_seq";

===== Viewing Table Information =====

You may want to look at a table's attributes and their types.  In a PostgreSQL client, you can use the command ''\d tablename'', e.g.,

  \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 <dbname>

==== Connecting to Another Database (''use'' in MySQL) ====

  \c dbname