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 here.

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
labs/postgresql.txt · Last modified: 2017/07/25 16:22 by admin
CC Attribution-Noncommercial-Share Alike 4.0 International
Driven by DokuWiki Recent changes RSS feed Valid CSS Valid XHTML 1.0