Download E-books PostgreSQL: Up and Running: A Practical Introduction to the Advanced Open Source Database PDF

In instance 7-15, we show this with a temporal model of generate_series. we are going to use a desk we build with the subsequent: CREATE desk interval_periods(i_type interval); INSERT INTO interval_periods(i_type) VALUES ('5 months'), ('132 days'), ('4862 hours'); instance 7-15. Set returning functionality in choose decide upon i_type , generate_series('2012-01-01'::date,'2012-12-31'::date,i_type) As dt FROM interval_periods; i_type | dt -----------+-----------------------5 months | 2012-01-01 00:00:00-05 five months | 2012-06-01 00:00:00-04 five months | 2012-11-01 00:00:00-04 132 days | 2012-01-01 00:00:00-05 132 days | 2012-05-12 00:00:00-04 132 days | 2012-09-21 00:00:00-04 4862 hours | 2012-01-01 00:00:00-05 4862 hours | 2012-07-21 15:00:00-04 Selective DELETE, replace, and choose from Inherited Tables if you question from a desk that has baby tables, the question drills down, unionizing the entire baby documents pleasurable the question situation. DELETE and replace paintings a similar approach, drilling down the hierarchy for sufferers. occasionally this isn't fascinating and also you wish info to just come from the desk you exact with out the youngsters tagging alongside. this can be the place the one key-phrase turns out to be useful. We observed an instance of its use in instance 7-11, the place we purely desired to delete documents from the y2011 desk that weren’t already migrated to the logs_2011_01_02 desk. with out the single modifier, we’d turn out deleting documents from the kid desk that will were moved formerly. buildings distinctive to PostgreSQL | ninety five RETURNING replaced documents The RETURNING clause is supported via ANSI-SQL criteria, yet no longer present in many databases. We observed an instance of it in instance 7-11, the place we back the documents deleted. RETURNING is additionally used for INSERT and replace. For inserts into tables with serial keys, it really is rather convenient because it returns you the most important worth of the recent row(s). notwithstanding RETURNING is usually followed by way of * for all fields, you could restrict the fields as we do in instance 7-16. instance 7-16. RETURNING replaced documents of an replace replace census. lu_fact_types AS f SET short_name = Replace(Replace(Lower(f. fact_subcats[4]),' ','_'),':','') the place f. fact_subcats[3] = 'Hispanic or Latino:' AND f. fact_subcats[4] > '' RETURNING fact_type_id, short_name; fact_type_id | short_name --------------+------------------------------------------------96 | white_alone ninety seven | black_or_african_american_alone ninety eight | american_indian_and_alaska_native_alone ninety nine | asian_alone a hundred | native_hawaiian_and_other_pacific_islander_alone a hundred and one | some_other_race_alone 102 | two_or_more_races Composite forms in Queries Composites supply loads of flexibility to PostgreSQL. the 1st time you spot a question with composites, you're shocked. in reality, chances are you'll stumble upon their versatility by chance while creating a typo in an SQL assertion. test the subsequent question: decide upon X FROM census. lu_fact_types As X restrict 2; first and foremost look, you could imagine that we disregarded a . * by chance, yet try out the end result: x -----------------------------------------------------------------(86,Population,"{D001,Total:}",d001) (87,Population,"{D002,Total:,""Not Hispanic or Latino:""}",d002) keep in mind from an prior part “All Tables Are customized” on web page seventy one the place we established that PostgreSQL immediately create composite kinds of all tables in PostgreSQL.