This is an old revision of the document!


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.

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 ]

Creating Database

createdb <dbname>

Dropping Database

dropdb <dbname>

Creating Tables

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 ;

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>
labs/postgresql.1249579109.txt.gz · Last modified: 2009/08/06 17:18 by admin
CC Attribution-Noncommercial-Share Alike 4.0 International
Driven by DokuWiki Recent changes RSS feed Valid CSS Valid XHTML 1.0