Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
labs:postgresql [2008/05/23 20:25] adminlabs: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
CC Attribution-Noncommercial-Share Alike 4.0 International
Driven by DokuWiki Recent changes RSS feed Valid CSS Valid XHTML 1.0