Differences

This shows you the differences between two versions of the page.

--- labs:postgresql [2008/05/14 20:37] – admin
+++ labs:postgresql [2017/07/25 16:22] (current) – [Update Statements] 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, 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