This is the start of a series about partitioning in PostgreSQL. For this and the following posts I will use PostgreSQL 12 (which currently is in beta) so some stuff might not work if you are on PostgreSQL 11 or even on PostgreSQL 10 when declarative partitioning was introduced. This very first post is only about preparing the data set and I decided to separate this one from the next post because it shows various things around loading data in PostgreSQL and therefore might be useful on its one.<\/p>\n
<\/p>\n
When you are looking for free data sets “The home of the U.S. Government\u2019s open data”<\/a> is a great source. It provides free data sets for research, development or just data you can play with for whatever reason. For the scope of this and the following posts we will use the Traffic violations<\/a> data set. It contains more the 1.5 millions of rows and is a good starting point for diving into PostgreSQL partitioning.<\/p>\n The initial import goes into a table which only contains text columns and from there we can load our partitions later on. The structure of the table is: <\/p>\n For loading the table the easiest way is to use PostgreSQL’s copy<\/a> command:<\/p>\n So we have around 1.5 millions of rows with real data. As this table contains only text columns we will create a materialized view<\/a> with data types that fit the data. Looking at one row of the table:<\/p>\n The materialized view becomes:<\/p>\n The statement contains some “case”<\/a> expressions to test for empty strings. If you do not do this you get issues like this when you cast into a specific data type:<\/p>\n The beauty of a materialized view is, that you can refresh whenever the underlying data set changed, e.g.:<\/p>\n Note that this will block access to the materialized view for the time of the refresh. You could avoid that with refreshing concurrently<\/a> but that only works when you have a unique index on the materialized view. With this data set we can not create a unique index because some of the rows are duplicates, e.g.:<\/p>\n So now we have the data set we can use for the partitioning examples in the next posts. To summarize, what did we do:<\/p>\n In the next post we will look at the first partitioning strategy: Range partitioning.<\/p>\n","protected":false},"excerpt":{"rendered":" This is the start of a series about partitioning in PostgreSQL. For this and the following posts I will use PostgreSQL 12 (which currently is in beta) so some stuff might not work if you are on PostgreSQL 11 or even on PostgreSQL 10 when declarative partitioning was introduced. This very first post is only […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-12480","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\n\ncreate table traffic_violations\n( seqid text\n, date_of_stop text\n, time_of_stop text\n, agency text\n, subagency text\n, description text\n, location text\n, latitude text\n, longitude text \n, accident text\n, belts text\n, personal_injury text\n, property_damage text\n, fatal text\n, commercial_license text\n, hazmat text\n, commercial_vehicle text\n, alcohol text\n, workzone text\n, state text\n, vehicletype text\n, year text\n, make text\n, model text\n, color text\n, violation_type text\n, charge text\n, article text\n, contributed_to_accident text\n, race text\n, gender text\n, driver_city text\n, driver_state text\n, dl_state text\n, arrest_type text\n, geolocation text\n, council_districts text\n, councils text\n, communities text\n, zip_codes text\n, municipalities text\n);\n<\/pre>\n
\npostgres=# ! ls -l Traffic_Violations.csv\n-rw-rw-r--. 1 postgres postgres 634161687 May 28 11:28 Traffic_Violations.csv\npostgres=# copy traffic_violations from '\/home\/postgres\/Traffic_Violations.csv' with ( format csv, header true, null 'null', delimiter ',');\nCOPY 1528078\n<\/pre>\n
\npostgres=# x\nExpanded display is on.\npostgres=# select * from traffic_violations limit 1;\n-[ RECORD 1 ]-----------+-------------------------------------------------------------------------\nseqid | e13d2082-55a7-4a93-8836-173be19d2648\ndate_of_stop | 06\/01\/2019\ntime_of_stop | 13:38:00\nagency | MCP\nsubagency | 2nd District, Bethesda\ndescription | DRIVER FAILURE TO YIELD RIGHT-OF-WAY TO VEH. UPON EMERGING FROM DRIVEWAY\nlocation | RANDOLPH RD \/ NEW HAMPSHIRE\nlatitude | 39.07592\nlongitude | -77.0011316666667\naccident | Yes\nbelts | No\npersonal_injury | No\nproperty_damage | Yes\nfatal | No\ncommercial_license | No\nhazmat | No\ncommercial_vehicle | No\nalcohol | No\nworkzone | No\nstate | MD\nvehicletype | 02 - Automobile\nyear | 2014\nmake | HYUN\nmodel | TK\ncolor | SILVER\nviolation_type | Warning\ncharge | 21-705(c)\narticle | Transportation Article\ncontributed_to_accident | false\nrace | WHITE\ngender | F\ndriver_city | SILVER SPRING\ndriver_state | MD\ndl_state | MD\narrest_type | A - Marked Patrol\ngeolocation | (39.07592, -77.0011316666667)\ncouncil_districts | 5\ncouncils | 5\ncommunities | 26\nzip_codes | 12\nmunicipalities | 1\n<\/pre>\n
\ncreate materialized view mv_traffic_violations\n( seqid \n, date_of_stop \n, time_of_stop \n, agency \n, subagency \n, description \n, location \n, latitude \n, longitude \n, accident \n, belts \n, personal_injury \n, property_damage \n, fatal \n, commercial_license \n, hazmat \n, commercial_vehicle \n, alcohol \n, workzone \n, state \n, vehicletype \n, year \n, make \n, model \n, color \n, violation_type \n, charge \n, article \n, contributed_to_accident \n, race \n, gender \n, driver_city \n, driver_state \n, dl_state \n, arrest_type \n, geolocation \n, council_districts \n, councils \n, communities \n, zip_codes \n, municipalities \n)\nas\nselect seqid\n , to_date(date_of_stop,'MM\/DD\/YYYY')\n , time_of_stop::time\n , agency\n , subagency \n , description\n , location\n , latitude::numeric\n , longitude::numeric\n , accident\n , belts::boolean\n , personal_injury::boolean\n , property_damage::boolean\n , fatal::boolean\n , commercial_license::boolean\n , hazmat::boolean\n , commercial_vehicle::boolean\n , alcohol::boolean\n , workzone::boolean\n , state\n , vehicletype\n , case year\n when '' then null\n else year::smallint\n end\n , make\n , model\n , color\n , violation_type\n , charge\n , article\n , contributed_to_accident::boolean\n , race\n , gender\n , driver_city\n , driver_state\n , dl_state\n , arrest_type\n , geolocation::point\n , case council_districts\n when '' then null\n else council_districts::smallint\n end\n , case councils\n when '' then null\n else councils::smallint\n end\n , case communities\n when '' then null\n else communities::smallint\n end\n , case zip_codes\n when '' then null\n else zip_codes::smallint\n end\n , case municipalities\n when '' then null\n else municipalities::smallint\n end\n from traffic_violations;\n<\/pre>\n
\npsql: ERROR: invalid input syntax for type smallint: \"\"\n<\/pre>\n
\npostgres=# refresh materialized view mv_traffic_violations WITH data;\nREFRESH MATERIALIZED VIEW\n<\/pre>\n
\npostgres=# select * from mv_traffic_violations where seqid='b87c908c-ce2d-4c10-89fa-ca48735af485' and date_of_stop = '2012-11-07' and time_of_stop = '05:07:00' and description = 'FAILURE OF VEH. DRIVER IN ACCIDENT TO LOCATE AND NOTIFY OWNER OF UNATTENDED VEH. OF DAMAGE' and charge = '20-105(b)' and violation_type = 'Citation';\n-[ RECORD 1 ]-----------+-------------------------------------------------------------------------------------------\nseqid | b87c908c-ce2d-4c10-89fa-ca48735af485\ndate_of_stop | 2012-11-07\ntime_of_stop | 05:07:00\nagency | MCP\nsubagency | 2nd District, Bethesda\ndescription | FAILURE OF VEH. DRIVER IN ACCIDENT TO LOCATE AND NOTIFY OWNER OF UNATTENDED VEH. OF DAMAGE\nlocation | IFO 2531 ROSS RD\nlatitude | 39.0222578333333\nlongitude | -77.04575825\naccident | Yes\nbelts | f\npersonal_injury | f\nproperty_damage | t\nfatal | f\ncommercial_license | f\nhazmat | f\ncommercial_vehicle | f\nalcohol | f\nworkzone | f\nstate | MD\nvehicletype | 02 - Automobile\nyear | 2002\nmake | SUBARU\nmodel | FORESTER\ncolor | SILVER\nviolation_type | Citation\ncharge | 20-105(b)\narticle | Transportation Article\ncontributed_to_accident | f\nrace | HISPANIC\ngender | M\ndriver_city | SILVER SPRING\ndriver_state | MD\ndl_state | MD\narrest_type | A - Marked Patrol\ngeolocation | (39.022257833333,-77.04575825)\ncouncil_districts | 5\ncouncils | 5\ncommunities | 10\nzip_codes | 17\nmunicipalities | 1\n-[ RECORD 2 ]-----------+-------------------------------------------------------------------------------------------\nseqid | b87c908c-ce2d-4c10-89fa-ca48735af485\ndate_of_stop | 2012-11-07\ntime_of_stop | 05:07:00\nagency | MCP\nsubagency | 2nd District, Bethesda\ndescription | FAILURE OF VEH. DRIVER IN ACCIDENT TO LOCATE AND NOTIFY OWNER OF UNATTENDED VEH. OF DAMAGE\nlocation | IFO 2531 ROSS RD\nlatitude | 39.0222578333333\nlongitude | -77.04575825\naccident | Yes\nbelts | f\npersonal_injury | f\nproperty_damage | t\nfatal | f\ncommercial_license | f\nhazmat | f\ncommercial_vehicle | f\nalcohol | f\nworkzone | f\nstate | MD\nvehicletype | 02 - Automobile\nyear | 2002\nmake | SUBARU\nmodel | FORESTER\ncolor | SILVER\nviolation_type | Citation\ncharge | 20-105(b)\narticle | Transportation Article\ncontributed_to_accident | f\nrace | HISPANIC\ngender | M\ndriver_city | SILVER SPRING\ndriver_state | MD\ndl_state | MD\narrest_type | A - Marked Patrol\ngeolocation | (39.022257833333,-77.04575825)\ncouncil_districts | 5\ncouncils | 5\ncommunities | 10\nzip_codes | 17\nmunicipalities | 1\n<\/pre>\n
\n