Data model

Object-relational Database

Is support for:
• custom objects and their behavior
• including data types
• functions
• operations
• domains
• indexes

Structures and data types

• uuid
• monetary
• enumerable
• geometric
• binary
• network addresses
• bit strings
• text search
• xml
• json
• arrays
• composite types and ranges
• some internal types

Network addresses

• 192.168.100.128/25
• 10.1.2.3/32
• 2001: 4f8: 3: ba: 2e0: 81ff: ​​fe22: d1f1 / 128
• :: ffff: 1.2.3.0/128

Multidimensional arrays

							CREATE TABLE holiday_picnic (
								holiday varchar(50) -- string
								sandwich text[], -- array
								side text[] [], -- multidimensional array
								beverage text ARRAY[4] -- array of 4 elements
							);
							INSERT INTO holiday_picnic VALUES
								('Labor Day',
								'{"roast beef","veggie","turkey"}',
								'{
									{"potato salad","green salad"},
									{"chips","crackers"}
								}',
								'{"soda","juice","beer","water"}'
								);
						

Geometric data

							CREATE TABLE trails ( -- create a table for storing trails
									 trail_name varchar(250),
									 trail_path path
							);
						

							// insert the path into the table,
						 INSERT INTO trails VALUES
									('Dool Trail - Creeping Forest Trail Loop',
									((37.172,-122.22261666667),
									(37.171616666667,-122.22385),
									(37.1735,-122.2236),
									(37.175416666667,-122.223),
									(37.1758,-122.22378333333),
									(37.179466666667,-122.22866666667),
									(37.18395,-122.22675),
									(37.180783333333,-122.22466666667),
									(37.176116666667,-122.2222),
									(37.1753,-122.22293333333),
									(37.173116666667,-122.22281666667)));
						

JSON support

Getting a bit more useful:
• Postgres introduces the ->, ->>, #>, and #>> operators.
• This allows us to actually start querying against our JSON data!

						-- returns the value of doc.property1 as JSON
						SELECT doc -> 'property1' FROM SOME_TABLE;
						-- returns the value of doc.property1 as text
						SELECT doc ->> 'property1' FROM SOME_TABLE;
						-- returns the value of doc.property2.subproperty as JSON
						SELECT doc #> '{property2,subproperty}' FROM SOME_TABLE;
						-- returns the value of doc.property2.subproperty as text
						SELECT doc #>> '{property2,subproperty}' FROM SOME_TABLE;
					

Creating a new type

							-- creating a new composite "wine"
							CREATE TYPE wine AS (
									 wine_vineyard varchar(50),
									 wine_type varchar(50),
									 wine_year int
							);

							 -- create a table that uses a compositetype "wine"
							CREATE TABLE pairings (
									 menu_entree varchar(50),
									 wine_pairing wine
							);
						

							-- insert data into the table using the ROW expression
							INSERT INTO pairings VALUES
								('Lobster Tail',ROW('Stag Leap','Chardonnay', 2012)),
								('Elk Medallions',ROW('Rombauer','Sauvignon',2012));

							/*
								fetch from a table using the column name
								(use parentheses separated by dot from field name
								in a composite type)
							*/
							SELECT (wine_pairing).wine_vineyard, (wine_pairing).wine_type
							FROM pairings
							WHERE menu_entree = 'Elk Medallions';
						

Data integrity

ANSI-SQL:2008

ACID

• Atomicity
• Consistency
• Isolation
• Durability

Valid data

• primary keys
• constraining and cascading foreign keys
• unique constraints
• NOT NULL constraints
• check constraints