REATE DATABASE

Unix Command prompt:

-bash-3.00$createdb testdb;

Database command prompt

postgres=#create database testdb;

DROP DATABASE

Unix Command prompt:

-bash-3.00$dropdb testdb;

Database command prompt

postgres=#drop database testdb;

CREATE GROUP/ROLE


postgres=#CREATE GROUP group1;

DROP GROUP/ROLE

postgres=#drop group group1;

CREATE USER

postgres=#create user scott password 'tiger';

postgres=#grant all privileges on database testdb to scott;

postgres=#alter user scott CREATEUSER CREATEDB;

postgres=#alter user scott with password 'new_password';

Alter user

ALTER USER name [ [ WITH ] option [ ... ] ]

where option can be:

CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| VALID UNTIL 'abstime'

ALTER USER name RENAME TO newname

ALTER USER name SET parameter { TO | = } { value | DEFAULT }

ALTER USER name RESET parameter

ALTER USER guest with password 'Passwd'

CREATE GROUP/ROLE (AFTER CREATE USER)

postgres=#CREATE GROUP group1 WITH USER scott;


ALTER SEQUENCE


ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
[ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
ALTER SEQUENCE name SET SCHEMA new_schema

ALTER SEQUENCE serial RESTART WITH 105;


CREATE FUNCTION

CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS '
....
end;
' LANGUAGE plpgsql;


Handling of Quotation Marks
Since the code of a PL/pgSQL function is specified in CREATE FUNCTION as a string literal, single quotes inside the function body must be escaped by doubling them. This can lead to rather complicated code at times, especially if you are writing a function that generates other functions, as in the example in Section 37.6.4. This chart may be useful as a summary of the needed numbers of quotation marks in various situations.



CREATE SCHEMA

postgres=#create schema myschema;

CREATE TABLECREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( [
{ column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] }
[, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]
where column_constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL |
NULL |
UNIQUE [ USING INDEX TABLESPACE tablespace ] |
PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] |
CHECK (expression) |
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
and table_constraint is:
[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
PRIMARY KEY ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
CHECK ( expression ) |
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]



ALTER TABLE




ALTER TABLE [ ONLY ] name [ * ]
action [, ... ]
ALTER TABLE [ ONLY ] name [ * ]
RENAME [ COLUMN ] column TO new_column
ALTER TABLE name
RENAME TO new_name



where action is one of:



ADD [ COLUMN ] column type [ column_constraint [ ... ] ]
DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] column TYPE type [ USING expression ]
ALTER [ COLUMN ] column SET DEFAULT expression
ALTER [ COLUMN ] column DROP DEFAULT
ALTER [ COLUMN ] column { SET | DROP } NOT NULL
ALTER [ COLUMN ] column SET STATISTICS integer
ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD table_constraint
DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET WITHOUT OIDS
OWNER TO new_owner
SET TABLESPACE tablespace_name



DROP TABLE



drop table <table_name> [cascade]



CREATE INDEX



CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [, ...] )
[ TABLESPACE tablespace ]
[ WHERE predicate ]



where

method
The name of the method to be used for the index. Choices are btree, hash, rtree, and gist. The default method is btree.
expression
An expression based on one or more columns of the table. The expression usually must be written with surrounding parentheses, as shown in the syntax. However, the parentheses may be omitted if the expression has the form of a function call.
opclass
The name of an operator class. See below for details.
predicate
The constraint expression for a partial index.


DROP INDEX

drop index <index_name>

 

 

CREATE SEQUENCE
CREATE [ TEMPORARY | TEMP ] SEQUENCE name

[ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

Create an ascending sequence called serial, starting at 101:

CREATE SEQUENCE serial START 101;
Select the next number from this sequence:

SELECT nextval('serial');

nextval
---------
114
Use this sequence in an INSERT command:

INSERT INTO distributors VALUES (nextval('serial'), 'nothing');
Update the sequence value after a COPY FROM:

BEGIN;
COPY distributors FROM 'input_file';
SELECT setval('serial', max(id)) FROM distributors;


CREATE FUNCTION foo() RETURNS integer AS '...'
LANGUAGE plpgsql;Anywhere within the function body, quotation marks must appear in pairs.

2 quotation marks
For string literals inside the function body, for example:

a_output := ''Blah'';
SELECT * FROM users WHERE f_name=''foobar'';The second line is seen by PL/pgSQL as

SELECT * FROM users WHERE f_name='foobar';
4 quotation marks
When you need a single quotation mark in a string constant inside the function body, for example:

a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''The value actually appended to a_output would be: AND name LIKE 'foobar' AND xyz.

6 quotation marks
When a single quotation mark in a string inside the function body is adjacent to the end of that string constant, for example:

a_output := a_output || '' AND name LIKE ''''foobar''''''The value appended to a_output would then be: AND name LIKE 'foobar'.

10 quotation marks
When you want two single quotation marks in a string constant (which accounts for 8 quotation marks) and this is adjacent to the end of that string constant (2 more). You will probably only need that if you are writing a function that generates other functions. For example:



a_output := a_output || '' if v_'' ||
referrer_keys.kind || '' like ''''''''''
|| referrer_keys.key_string || ''''''''''
then return '''''' || referrer_keys.referrer_type
|| ''''''; end if;''; The value of a_output would then be:



if v_... like ''...'' then return ''...''; end if;
A different approach is to escape quotation marks in the function body with a backslash rather than by doubling them. With this method you'll find yourself writing things like \'\' instead of ''''. Some find this easier to keep track of, some do not.

DROP FUNCTIONDROP FUNCTION name ( [ type [, ...] ] ) [ CASCADE | RESTRICT ]

CREATE TRIGGER

CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE funcname ( arguments )

DROP TRIGGER

DROP TRIGGER name ON table [ CASCADE | RESTRICT ]

CREATE VIEW

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW name [ ( column_name [, ...] ) ]
AS query



DROP VIEW



DROP VIEW name [, ...] [ CASCADE | RESTRICT ]



GRANTGRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] tablename [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE dbname [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTION funcname ([type, ...]) [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE langname [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schemaname [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespacename [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]




Copying large table data (selected table data)



A) Backup the employee table

BEGIN;

CREATE TEMP TABLE tab_temp1 AS

SELECT *
FROM employee;


COPY tab_employee TO '/var/lib/pgsql/data/backup/tab_employee.copy';

ROLLBACK;
B) Recover the employee table

COPY employee FROM '/var/lib/pgsql/data/backup/tab_employee.copy';