View in Postgre SQL University of Tehran ECE

View in Postgre. SQL University of Tehran ECE Department Database Lab. Mehdi Emadi

View A view is a subset of a table. You can use it to retrieve and update data or even delete rows. You create a view from attributes/tuples of other tables and from there you can do almost everything that you can do with a table.

Views An expression that describes a table without creating it. View definition form is: CREATE VIEW <name> AS <query>;

Optimize Query 1. 2. Push selections down tree. Eliminate unnecessary projections.

CREATE VIEW CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, . . . ] ) ] [ WITH ( view_option_name [= view_option_value] [, . . . ] ) ] AS query CREATE VIEW S_R AS SELECT * FROM ("Sailors" NATURAL JOIN "Reserves") W ;

CREATE MATERIALIZED VIEW view_name [ (column_name [, . . . ] ) ] [ WITH ( storage_parameter [= value] [, . . . ] ) ] [ TABLESPACE tablespace_name ] AS query [WITH [ NO ] DATA ]

MATERIALIZED VIEW CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM mytab; materialized view cannot subsequently be directly updated REFRESH MATERIALIZED VIEW mymatview;

Binary Large Object BLOB

BLOB in Postgre. SQL using OID CREATE TABLE image ( id integer, name text, picture oid );

INSERT INTO image (id, name, picture) VALUES (1, 'beautiful image', lo_import('G: \1. jpg')); INSERT INTO image (id, name, picture) VALUES (2, 'test. jpg', lo_import('G: \1. jpg')); same as above, but specify OID to use. ◦ INSERT INTO image (id, name, picture) VALUES (1, 'beautiful image', lo_import('G: \1. jpg', 68583));

SELECT lo_export(picture, 'G: \2. jpg') FROM image WHERE name = 'beautiful image'; -need superuser permission. SELECT lo_export(picture, ('G: \' || name)) FROM image WHERE name = 'test. jpg';

String Function in Postgre. SQL

Return Description Type string || string text String concatenation char_length(string) int Number of characters lower(string) text Convert to lower case Function octet_length(string) int overlay(string placing string from text int [for int]) position(substring int in string) substring(string text [from int] [for int]) trim([leading | trailing | both] [characters] from string) text Example Result 'Post' || 'gre. SQL' char_length('jose') lower('TOM') Postgre. SQL 4 tom Number of bytes octet_length('jose') 4 Replace substring overlay('Txxxxas' placing 'hom' from 2 for 4) Thomas Location of specified substring Extract substring position('om' in 'Thomas') substring('Thomas' from 2 for 3) Remove the longest string containing only the characters (a space by trim(both 'x' from default) from the 'x. Tomxx') start/end/both ends of the string 3 hom Tom http: //www. postgresql. org/docs/9. 3/static/functions-string. html

BULK DATA - COPY

COPY table_name [ ( column_name [, . . . ] ) ] FROM { 'filename' | STDIN } [ [ WITH ] ( option [, . . . ] ) ] COPY { table_name [ ( column_name [, . . . ] ) ] | ( query ) } TO { 'filename' | STDOUT } [ [ WITH ] ( option [, . . . ] ) ] where option can be one of: FORMAT format_name OIDS [ boolean ] DELIMITER 'delimiter_character' NULL 'null_string' HEADER [ boolean ] QUOTE 'quote_character' ESCAPE 'escape_character' FORCE_QUOTE { ( column_name [, . . . ] ) | * } FORCE_NOT_NULL ( column_name [, . . . ] ) ENCODING 'encoding_name'

COPY "Sailors" TO 'G: test. csv' WITH CSV; CREATE TABLE "Sailors_test“ ( sid integer NOT NULL, sname character varying(30), age integer, rating integer, PRIMARY KEY (sid ) ) COPY "Sailors_test" FROM 'G: test. csv' WITH CSV DELIMITER ', ';