PostgreSQL Help

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.

The picture below comes from here.

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 [ 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;

createdb <dbname>

dropdb <dbname>

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)
);

 create sequence "majors_id_seq";

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.

psql -l

or, inside of psql

\l

\dp

\du

or

SELECT * FROM pg_roles;

CREATE USER username [WITH PASSWORD ''] ;

Or

createuser

ALTER GROUP groupname ADD user username [, ... ]

GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
  [,...] | ALL [ PRIVILEGES ] }
  ON [ TABLE ] tablename [, ...]
  TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

REVOKE [ GRANT OPTION FOR ]
  { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
  [,...] | ALL [ PRIVILEGES ] }
  ON [ TABLE ] tablename [, ...]
  FROM { username | GROUP groupname | PUBLIC } [, ...]
  [ CASCADE | RESTRICT ]

ALTER TABLE table_name ADD COLUMN col_name type ...

ALTER TABLE quizzes ALTER COLUMN name TYPE varchar(30);

pg_dump <dbname>

\c dbname