[{"_1":2,"_1667":-5,"_1668":-5},"loaderData",{"_3":4,"_22":23},"root",{"_5":6},"current",{"_7":8,"_9":10,"_21":8},"development",false,"env",{"_11":12,"_13":14,"_15":16,"_17":18,"_19":20},"COMMIT_SHA","032c64b08f7dd5f7f1f6f3923a1c6d1445f4fb68","IMAGE_CDN","https://planetscale-images.imgix.net","IMAGE_CDN_ENABLED","true","INTERNAL_API","https://api.planetscale.com","SENTRY_DSN","https://bd81903b44804e22a06bdc0c1a91b303@o499952.ingest.us.sentry.io/4504531942572032","userSignedIn","routes/blog.author.$slug",{"_24":25,"_30":31,"_1657":1658,"_3":1666},"author",{"_26":27,"_28":29},"slug","adnan","name","Adnan Kukic","blogs",[32],{"_26":33,"_34":35,"_1603":1604,"_1605":-7,"_1606":1607,"_1612":1638,"_1639":1640,"_1643":1644,"_1646":1647,"_1648":1649,"_1650":1651},"migrating-from-postgres-to-mysql","body",[36,62,67,72,77,82,410,415,428,433,451,462,485,495,504,509,597,614,641,656,673,681,686,691,776,781,797,814,819,824,924,929,934,939,944,949,1008,1019,1024,1081,1097,1105,1110,1120,1199,1216,1244,1255,1310,1315,1326,1331,1342,1347,1352,1363,1368,1385,1405,1420,1436,1468,1483,1494,1499,1510,1522,1539,1544,1560,1571,1576,1593,1598],["SingleFetchClassInstance",37],{"_38":39,"_28":40,"_41":42,"_45":46},"$$mdtype","Tag","Callout","attributes",{"_43":44},"type","note","children",[47],["SingleFetchClassInstance",48],{"_38":39,"_28":49,"_41":50,"_45":51},"p",{},[52,53,61],"PlanetScale now supports ",["SingleFetchClassInstance",54],{"_38":39,"_28":55,"_41":56,"_45":59},"a",{"_57":58},"href","/blog/planetscale-for-postgres-is-generally-available",[60],"PostgreSQL",".",["SingleFetchClassInstance",63],{"_38":39,"_28":49,"_41":64,"_45":65},{},[66],"Choosing a data store is one of the most important decisions you'll make when building software applications. The good and bad news though, is that there is an abundance of options. Depending on the type of application you're building, you may opt for a relational database like MySQL or Postgres, a non-relational database like MongoDB or CouchDB, a graph database like Neo4j, or one of the many other alternatives that each bring their own benefits and drawbacks.",["SingleFetchClassInstance",68],{"_38":39,"_28":49,"_41":69,"_45":70},{},[71],"It is a difficult choice and sometimes you realize that the database you initially went with no longer serves the needs of your application and you have to make the choice to migrate.",["SingleFetchClassInstance",73],{"_38":39,"_28":49,"_41":74,"_45":75},{},[76],"In this blog post, we'll take a look at how you can migrate your database schema from PostgreSQL to MySQL. MySQL and PostgreSQL or Postgres are both relational databases that have a lot of similarities, but also have a fair amount of differences that can make migration a challenge.",["SingleFetchClassInstance",78],{"_38":39,"_28":49,"_41":79,"_45":80},{},[81],"A detailed list of the differences between PostgreSQL and MySQL can be found below:",["SingleFetchClassInstance",83],{"_38":39,"_28":84,"_41":85,"_45":88},"div",{"_86":87},"class","table-overflow",[89],["SingleFetchClassInstance",90],{"_38":39,"_28":91,"_41":92,"_45":94},"table",{"_93":8},"collapse",[95,121],["SingleFetchClassInstance",96],{"_38":39,"_28":97,"_41":98,"_45":99},"thead",{},[100],["SingleFetchClassInstance",101],{"_38":39,"_28":102,"_41":103,"_45":104},"tr",{},[105,111,116],["SingleFetchClassInstance",106],{"_38":39,"_28":107,"_41":108,"_45":109},"th",{},[110],"Metric",["SingleFetchClassInstance",112],{"_38":39,"_28":107,"_41":113,"_45":114},{},[115],"Postgres",["SingleFetchClassInstance",117],{"_38":39,"_28":107,"_41":118,"_45":119},{},[120],"MySQL",["SingleFetchClassInstance",122],{"_38":39,"_28":123,"_41":124,"_45":125},"tbody",{},[126,146,164,238,257,276,295,346],["SingleFetchClassInstance",127],{"_38":39,"_28":102,"_41":128,"_45":129},{},[130,136,141],["SingleFetchClassInstance",131],{"_38":39,"_28":132,"_41":133,"_45":134},"td",{},[135],"Licensing",["SingleFetchClassInstance",137],{"_38":39,"_28":132,"_41":138,"_45":139},{},[140],"Released under PostgreSQL license, a free open-source license similar to BSD or MIT licenses",["SingleFetchClassInstance",142],{"_38":39,"_28":132,"_41":143,"_45":144},{},[145],"Source code is available under the terms of the GNU General Public License",["SingleFetchClassInstance",147],{"_38":39,"_28":102,"_41":148,"_45":149},{},[150,155,160],["SingleFetchClassInstance",151],{"_38":39,"_28":132,"_41":152,"_45":153},{},[154],"ACID",["SingleFetchClassInstance",156],{"_38":39,"_28":132,"_41":157,"_45":158},{},[159],"Yes",["SingleFetchClassInstance",161],{"_38":39,"_28":132,"_41":162,"_45":163},{},[159],["SingleFetchClassInstance",165],{"_38":39,"_28":102,"_41":166,"_45":167},{},[168,173,213],["SingleFetchClassInstance",169],{"_38":39,"_28":132,"_41":170,"_45":171},{},[172],"Triggers",["SingleFetchClassInstance",174],{"_38":39,"_28":132,"_41":175,"_45":176},{},[177,178,184,185,190,191,196,197,184,202,190,207,212],"Supports ",["SingleFetchClassInstance",179],{"_38":39,"_28":180,"_41":181,"_45":182},"code",{},[183],"AFTER",", ",["SingleFetchClassInstance",186],{"_38":39,"_28":180,"_41":187,"_45":188},{},[189],"BEFORE",", and ",["SingleFetchClassInstance",192],{"_38":39,"_28":180,"_41":193,"_45":194},{},[195],"INSTEAD OF"," triggers for ",["SingleFetchClassInstance",198],{"_38":39,"_28":180,"_41":199,"_45":200},{},[201],"INSERT",["SingleFetchClassInstance",203],{"_38":39,"_28":180,"_41":204,"_45":205},{},[206],"UPDATE",["SingleFetchClassInstance",208],{"_38":39,"_28":180,"_41":209,"_45":210},{},[211],"DELETE"," statements",["SingleFetchClassInstance",214],{"_38":39,"_28":132,"_41":215,"_45":216},{},[177,217,221,222,196,226,184,230,190,234,212],["SingleFetchClassInstance",218],{"_38":39,"_28":180,"_41":219,"_45":220},{},[183]," and ",["SingleFetchClassInstance",223],{"_38":39,"_28":180,"_41":224,"_45":225},{},[189],["SingleFetchClassInstance",227],{"_38":39,"_28":180,"_41":228,"_45":229},{},[201],["SingleFetchClassInstance",231],{"_38":39,"_28":180,"_41":232,"_45":233},{},[206],["SingleFetchClassInstance",235],{"_38":39,"_28":180,"_41":236,"_45":237},{},[211],["SingleFetchClassInstance",239],{"_38":39,"_28":102,"_41":240,"_45":241},{},[242,247,252],["SingleFetchClassInstance",243],{"_38":39,"_28":132,"_41":244,"_45":245},{},[246],"Unsigned integer",["SingleFetchClassInstance",248],{"_38":39,"_28":132,"_41":249,"_45":250},{},[251],"No support",["SingleFetchClassInstance",253],{"_38":39,"_28":132,"_41":254,"_45":255},{},[256],"A column can be made not to accept negative values",["SingleFetchClassInstance",258],{"_38":39,"_28":102,"_41":259,"_45":260},{},[261,266,271],["SingleFetchClassInstance",262],{"_38":39,"_28":132,"_41":263,"_45":264},{},[265],"Materialized views",["SingleFetchClassInstance",267],{"_38":39,"_28":132,"_41":268,"_45":269},{},[270],"Supported",["SingleFetchClassInstance",272],{"_38":39,"_28":132,"_41":273,"_45":274},{},[275],"Not supported",["SingleFetchClassInstance",277],{"_38":39,"_28":102,"_41":278,"_45":279},{},[280,285,290],["SingleFetchClassInstance",281],{"_38":39,"_28":132,"_41":282,"_45":283},{},[284],"ANSI/ISO SQL compliance",["SingleFetchClassInstance",286],{"_38":39,"_28":132,"_41":287,"_45":288},{},[289],"Fully compliant",["SingleFetchClassInstance",291],{"_38":39,"_28":132,"_41":292,"_45":293},{},[294],"Mostly compliant",["SingleFetchClassInstance",296],{"_38":39,"_28":102,"_41":297,"_45":298},{},[299,304,327],["SingleFetchClassInstance",300],{"_38":39,"_28":132,"_41":301,"_45":302},{},[303],"Drop temporary table",["SingleFetchClassInstance",305],{"_38":39,"_28":132,"_41":306,"_45":307},{},[308,309,314,315,320,321,326],"No ",["SingleFetchClassInstance",310],{"_38":39,"_28":180,"_41":311,"_45":312},{},[313],"TEMP"," or ",["SingleFetchClassInstance",316],{"_38":39,"_28":180,"_41":317,"_45":318},{},[319],"TEMPORARY"," keyword in ",["SingleFetchClassInstance",322],{"_38":39,"_28":180,"_41":323,"_45":324},{},[325],"DROP TABLE"," statement",["SingleFetchClassInstance",328],{"_38":39,"_28":132,"_41":329,"_45":330},{},[331,332,314,336,340,341,345],"Supports the ",["SingleFetchClassInstance",333],{"_38":39,"_28":180,"_41":334,"_45":335},{},[313],["SingleFetchClassInstance",337],{"_38":39,"_28":180,"_41":338,"_45":339},{},[319]," keyword in the ",["SingleFetchClassInstance",342],{"_38":39,"_28":180,"_41":343,"_45":344},{},[325]," statement, allowing you to remove just the temporary table",["SingleFetchClassInstance",347],{"_38":39,"_28":102,"_41":348,"_45":349},{},[350,355,374],["SingleFetchClassInstance",351],{"_38":39,"_28":132,"_41":352,"_45":353},{},[354],"Table partitioning",["SingleFetchClassInstance",356],{"_38":39,"_28":132,"_41":357,"_45":358},{},[177,359,184,364,190,369],["SingleFetchClassInstance",360],{"_38":39,"_28":180,"_41":361,"_45":362},{},[363],"RANGE",["SingleFetchClassInstance",365],{"_38":39,"_28":180,"_41":366,"_45":367},{},[368],"LIST",["SingleFetchClassInstance",370],{"_38":39,"_28":180,"_41":371,"_45":372},{},[373],"HASH",["SingleFetchClassInstance",375],{"_38":39,"_28":132,"_41":376,"_45":377},{},[177,378,184,382,184,387,184,391,184,395,400,401,314,405,409],["SingleFetchClassInstance",379],{"_38":39,"_28":180,"_41":380,"_45":381},{},[363],["SingleFetchClassInstance",383],{"_38":39,"_28":180,"_41":384,"_45":385},{},[386],"COLUMN",["SingleFetchClassInstance",388],{"_38":39,"_28":180,"_41":389,"_45":390},{},[368],["SingleFetchClassInstance",392],{"_38":39,"_28":180,"_41":393,"_45":394},{},[373],["SingleFetchClassInstance",396],{"_38":39,"_28":180,"_41":397,"_45":398},{},[399],"KEY",", and either ",["SingleFetchClassInstance",402],{"_38":39,"_28":180,"_41":403,"_45":404},{},[373],["SingleFetchClassInstance",406],{"_38":39,"_28":180,"_41":407,"_45":408},{},[399]," for composite partitioning",["SingleFetchClassInstance",411],{"_38":39,"_28":49,"_41":412,"_45":413},{},[414],"These differences are important to keep in mind when making the decision to migrate from Postgres to MySQL and vice versa. With that said, let's dive into how we can migrate an existing Postgres database to MySQL.",["SingleFetchClassInstance",416],{"_38":39,"_28":417,"_41":418,"_45":421},"h2",{"_419":420},"id","an-example-migration-from-postgres-to-mysql",[422],["SingleFetchClassInstance",423],{"_38":39,"_28":55,"_41":424,"_45":426},{"_57":425},"#an-example-migration-from-postgres-to-mysql",[427],"An example migration from Postgres to MySQL",["SingleFetchClassInstance",429],{"_38":39,"_28":49,"_41":430,"_45":431},{},[432],"In this article, we'll take the approach of migrating from PostgreSQL to MySQL manually. While there are software tools, ORMs, and other approaches we can take that might abstract the migration from one database to another, and we will look at those approaches in subsequent tutorials, here we want to explore what you'll need to consider at a low level.",["SingleFetchClassInstance",434],{"_38":39,"_28":49,"_41":435,"_45":436},{},[437,438,444,445,61],"For our sample migration, we are going to compare an instance of PostgreSQL and what a migration to MySQL might look like. For PostgreSQL, we'll use a locally hosted server. For MySQL, rather than setting up a local database, we'll test using ",["SingleFetchClassInstance",439],{"_38":39,"_28":55,"_41":440,"_45":442},{"_57":441},"/docs/what-is-planetscale",[443],"PlanetScale's hosted MySQL offering",". If you don't already have a PlanetScale account, you can ",["SingleFetchClassInstance",446],{"_38":39,"_28":55,"_41":447,"_45":449},{"_57":448},"https://auth.planetscale.com/sign-up",[450],"sign up now",["SingleFetchClassInstance",452],{"_38":39,"_28":417,"_41":453,"_45":455},{"_419":454},"differences-between-our-postgres-and-mysql-schema",[456],["SingleFetchClassInstance",457],{"_38":39,"_28":55,"_41":458,"_45":460},{"_57":459},"#differences-between-our-postgres-and-mysql-schema",[461],"Differences between our Postgres and MySQL schema",["SingleFetchClassInstance",463],{"_38":39,"_28":49,"_41":464,"_45":465},{},[466,467,472,473,478,479,484],"In our PostgreSQL schema, we have three tables: a ",["SingleFetchClassInstance",468],{"_38":39,"_28":180,"_41":469,"_45":470},{},[471],"products"," table that holds information about our inventory, a ",["SingleFetchClassInstance",474],{"_38":39,"_28":180,"_41":475,"_45":476},{},[477],"customers"," table that holds information about our customers, and an ",["SingleFetchClassInstance",480],{"_38":39,"_28":180,"_41":481,"_45":482},{},[483],"orders"," table that keeps records of orders placed by our customers.",["SingleFetchClassInstance",486],{"_38":39,"_28":49,"_41":487,"_45":488},{},[489,490,494],"The ",["SingleFetchClassInstance",491],{"_38":39,"_28":180,"_41":492,"_45":493},{},[471]," table in PostgreSQL looks like the following:",["SingleFetchClassInstance",496],{"_38":39,"_28":497,"_41":498,"_45":503},"CodeBlock",{"_499":500,"_501":502},"html","

CREATE TABLE products\n(\n    id          SERIAL,\n    name        VARCHAR,\n    description VARCHAR,\n    price       INTEGER\n);\n

","language","sql",[],["SingleFetchClassInstance",505],{"_38":39,"_28":49,"_41":506,"_45":507},{},[508],"So the data could be represented as:",["SingleFetchClassInstance",510],{"_38":39,"_28":84,"_41":511,"_45":512},{"_86":87},[513],["SingleFetchClassInstance",514],{"_38":39,"_28":91,"_41":515,"_45":516},{"_93":8},[517,545],["SingleFetchClassInstance",518],{"_38":39,"_28":97,"_41":519,"_45":520},{},[521],["SingleFetchClassInstance",522],{"_38":39,"_28":102,"_41":523,"_45":524},{},[525,530,535,540],["SingleFetchClassInstance",526],{"_38":39,"_28":107,"_41":527,"_45":528},{},[529],"id (SERIAL)",["SingleFetchClassInstance",531],{"_38":39,"_28":107,"_41":532,"_45":533},{},[534],"name (VARCHAR)",["SingleFetchClassInstance",536],{"_38":39,"_28":107,"_41":537,"_45":538},{},[539],"description (VARCHAR)",["SingleFetchClassInstance",541],{"_38":39,"_28":107,"_41":542,"_45":543},{},[544],"price (INTEGER)",["SingleFetchClassInstance",546],{"_38":39,"_28":123,"_41":547,"_45":548},{},[549,573],["SingleFetchClassInstance",550],{"_38":39,"_28":102,"_41":551,"_45":552},{},[553,558,563,568],["SingleFetchClassInstance",554],{"_38":39,"_28":132,"_41":555,"_45":556},{},[557],"1",["SingleFetchClassInstance",559],{"_38":39,"_28":132,"_41":560,"_45":561},{},[562],"Achieving PlanetScale",["SingleFetchClassInstance",564],{"_38":39,"_28":132,"_41":565,"_45":566},{},[567],"Achieving PlanetScale teaches you how to think bigger and more horizontally!",["SingleFetchClassInstance",569],{"_38":39,"_28":132,"_41":570,"_45":571},{},[572],"50",["SingleFetchClassInstance",574],{"_38":39,"_28":102,"_41":575,"_45":576},{},[577,582,587,592],["SingleFetchClassInstance",578],{"_38":39,"_28":132,"_41":579,"_45":580},{},[581],"2",["SingleFetchClassInstance",583],{"_38":39,"_28":132,"_41":584,"_45":585},{},[586],"The Database that Could",["SingleFetchClassInstance",588],{"_38":39,"_28":132,"_41":589,"_45":590},{},[591],"Follow the adventures of the database that could accomplish anything it wanted",["SingleFetchClassInstance",593],{"_38":39,"_28":132,"_41":594,"_45":595},{},[596],"100",["SingleFetchClassInstance",598],{"_38":39,"_28":599,"_41":600,"_45":602},"h3",{"_419":601},"varchar-in-postgres-vs-mysql",[603],["SingleFetchClassInstance",604],{"_38":39,"_28":55,"_41":605,"_45":607},{"_57":606},"#varchar-in-postgres-vs-mysql",[608,613],["SingleFetchClassInstance",609],{"_38":39,"_28":180,"_41":610,"_45":611},{},[612],"VARCHAR"," in Postgres vs MySQL",["SingleFetchClassInstance",615],{"_38":39,"_28":49,"_41":616,"_45":617},{},[618,619,624,625,630,631,635,636,640],"Looking at the data types within this table, the migration to MySQL should be pretty straightforward. All of the data types match up with what we have available in MySQL. While on the surface it does look pretty straightforward, there are a few things to consider. In MySQL for the ",["SingleFetchClassInstance",620],{"_38":39,"_28":180,"_41":621,"_45":622},{},[623],"varchar"," type, we have to set a max value, whereas in Postgres we did not. We can instead opt to use the ",["SingleFetchClassInstance",626],{"_38":39,"_28":180,"_41":627,"_45":628},{},[629],"text"," type instead in MySQL, or keep the same ",["SingleFetchClassInstance",632],{"_38":39,"_28":180,"_41":633,"_45":634},{},[623]," type, but specify a max length. If we do opt for the ",["SingleFetchClassInstance",637],{"_38":39,"_28":180,"_41":638,"_45":639},{},[623]," option, we'll have to know what the max value is, otherwise, we'll run into issues when we actually migrate the data over.",["SingleFetchClassInstance",642],{"_38":39,"_28":599,"_41":643,"_45":645},{"_419":644},"serial-in-postgres-vs-mysql",[646],["SingleFetchClassInstance",647],{"_38":39,"_28":55,"_41":648,"_45":650},{"_57":649},"#serial-in-postgres-vs-mysql",[651,613],["SingleFetchClassInstance",652],{"_38":39,"_28":180,"_41":653,"_45":654},{},[655],"SERIAL",["SingleFetchClassInstance",657],{"_38":39,"_28":49,"_41":658,"_45":659},{},[660,661,666,667,672],"Additionally, if we set the ",["SingleFetchClassInstance",662],{"_38":39,"_28":180,"_41":663,"_45":664},{},[665],"serial"," type in MySQL, it'll default the underlying data type to a ",["SingleFetchClassInstance",668],{"_38":39,"_28":180,"_41":669,"_45":670},{},[671],"bigint unsigned auto_increment",". In the grand scheme of things, this won't affect how we store the data too much, but is worth noting because depending on how many records we have, a different data type may be more suitable.",["SingleFetchClassInstance",674],{"_38":39,"_28":49,"_41":675,"_45":676},{},[489,677,494],["SingleFetchClassInstance",678],{"_38":39,"_28":180,"_41":679,"_45":680},{},[477],["SingleFetchClassInstance",682],{"_38":39,"_28":497,"_41":683,"_45":685},{"_499":684,"_501":502},"

CREATE TABLE customers\n(\n    id        SERIAL,\n    full_name VARCHAR,\n    address   VARCHAR,\n    location  POINT\n);\n

",[],["SingleFetchClassInstance",687],{"_38":39,"_28":49,"_41":688,"_45":689},{},[690],"If we look at our sample data for this table, we'll get the following:",["SingleFetchClassInstance",692],{"_38":39,"_28":84,"_41":693,"_45":694},{"_86":87},[695],["SingleFetchClassInstance",696],{"_38":39,"_28":91,"_41":697,"_45":698},{"_93":8},[699,726],["SingleFetchClassInstance",700],{"_38":39,"_28":97,"_41":701,"_45":702},{},[703],["SingleFetchClassInstance",704],{"_38":39,"_28":102,"_41":705,"_45":706},{},[707,711,716,721],["SingleFetchClassInstance",708],{"_38":39,"_28":107,"_41":709,"_45":710},{},[529],["SingleFetchClassInstance",712],{"_38":39,"_28":107,"_41":713,"_45":714},{},[715],"full_name (VARCHAR)",["SingleFetchClassInstance",717],{"_38":39,"_28":107,"_41":718,"_45":719},{},[720],"address (VARCHAR)",["SingleFetchClassInstance",722],{"_38":39,"_28":107,"_41":723,"_45":724},{},[725],"location (POINT)",["SingleFetchClassInstance",727],{"_38":39,"_28":123,"_41":728,"_45":729},{},[730,753],["SingleFetchClassInstance",731],{"_38":39,"_28":102,"_41":732,"_45":733},{},[734,738,743,748],["SingleFetchClassInstance",735],{"_38":39,"_28":132,"_41":736,"_45":737},{},[557],["SingleFetchClassInstance",739],{"_38":39,"_28":132,"_41":740,"_45":741},{},[742],"Robert California",["SingleFetchClassInstance",744],{"_38":39,"_28":132,"_41":745,"_45":746},{},[747],"123 Sunny St, AZ",["SingleFetchClassInstance",749],{"_38":39,"_28":132,"_41":750,"_45":751},{},[752],"(34.411275716904406,-111.6783709992531)",["SingleFetchClassInstance",754],{"_38":39,"_28":102,"_41":755,"_45":756},{},[757,761,766,771],["SingleFetchClassInstance",758],{"_38":39,"_28":132,"_41":759,"_45":760},{},[581],["SingleFetchClassInstance",762],{"_38":39,"_28":132,"_41":763,"_45":764},{},[765],"Nick Claus",["SingleFetchClassInstance",767],{"_38":39,"_28":132,"_41":768,"_45":769},{},[770],"785 North Pole, AL",["SingleFetchClassInstance",772],{"_38":39,"_28":132,"_41":773,"_45":774},{},[775],"(69.72578389209082,-153.14940161799086)",["SingleFetchClassInstance",777],{"_38":39,"_28":49,"_41":778,"_45":779},{},[780],"Now things get a little bit more interesting. While the first three columns are something we dealt with in the previous example, the fourth column represents a data type that is available in Postgres as well as in MySQL, but behaves quite differently in practice.",["SingleFetchClassInstance",782],{"_38":39,"_28":599,"_41":783,"_45":785},{"_419":784},"spatial-data-in-postgres-vs-mysql-point",[786],["SingleFetchClassInstance",787],{"_38":39,"_28":55,"_41":788,"_45":790},{"_57":789},"#spatial-data-in-postgres-vs-mysql-point",[791,792],"Spatial data in Postgres vs MySQL: ",["SingleFetchClassInstance",793],{"_38":39,"_28":180,"_41":794,"_45":795},{},[796],"POINT",["SingleFetchClassInstance",798],{"_38":39,"_28":49,"_41":799,"_45":800},{},[801,802,807,808,813],"In Postgres, working with spatial data is fairly straightforward. When we define the ",["SingleFetchClassInstance",803],{"_38":39,"_28":180,"_41":804,"_45":805},{},[806],"location"," column as a type of ",["SingleFetchClassInstance",809],{"_38":39,"_28":180,"_41":810,"_45":811},{},[812],"Point",", we can insert spatial data into it by giving it the coordinates we want. So if we wanted to add a new record into our database, we could do the following:",["SingleFetchClassInstance",815],{"_38":39,"_28":497,"_41":816,"_45":818},{"_499":817,"_501":502},"

INSERT INTO customers (id, full_name, address, location) VALUES (3, 'Michael West', '532 Alexander St, WA', POINT(47.490272897328325, -122.27293296965925));\n

",[],["SingleFetchClassInstance",820],{"_38":39,"_28":49,"_41":821,"_45":822},{},[823],"And our data would be properly reflected in the database as:",["SingleFetchClassInstance",825],{"_38":39,"_28":84,"_41":826,"_45":827},{"_86":87},[828],["SingleFetchClassInstance",829],{"_38":39,"_28":91,"_41":830,"_45":831},{"_93":8},[832,856],["SingleFetchClassInstance",833],{"_38":39,"_28":97,"_41":834,"_45":835},{},[836],["SingleFetchClassInstance",837],{"_38":39,"_28":102,"_41":838,"_45":839},{},[840,844,848,852],["SingleFetchClassInstance",841],{"_38":39,"_28":107,"_41":842,"_45":843},{},[529],["SingleFetchClassInstance",845],{"_38":39,"_28":107,"_41":846,"_45":847},{},[715],["SingleFetchClassInstance",849],{"_38":39,"_28":107,"_41":850,"_45":851},{},[720],["SingleFetchClassInstance",853],{"_38":39,"_28":107,"_41":854,"_45":855},{},[725],["SingleFetchClassInstance",857],{"_38":39,"_28":123,"_41":858,"_45":859},{},[860,880,900],["SingleFetchClassInstance",861],{"_38":39,"_28":102,"_41":862,"_45":863},{},[864,868,872,876],["SingleFetchClassInstance",865],{"_38":39,"_28":132,"_41":866,"_45":867},{},[557],["SingleFetchClassInstance",869],{"_38":39,"_28":132,"_41":870,"_45":871},{},[742],["SingleFetchClassInstance",873],{"_38":39,"_28":132,"_41":874,"_45":875},{},[747],["SingleFetchClassInstance",877],{"_38":39,"_28":132,"_41":878,"_45":879},{},[752],["SingleFetchClassInstance",881],{"_38":39,"_28":102,"_41":882,"_45":883},{},[884,888,892,896],["SingleFetchClassInstance",885],{"_38":39,"_28":132,"_41":886,"_45":887},{},[581],["SingleFetchClassInstance",889],{"_38":39,"_28":132,"_41":890,"_45":891},{},[765],["SingleFetchClassInstance",893],{"_38":39,"_28":132,"_41":894,"_45":895},{},[770],["SingleFetchClassInstance",897],{"_38":39,"_28":132,"_41":898,"_45":899},{},[775],["SingleFetchClassInstance",901],{"_38":39,"_28":102,"_41":902,"_45":903},{},[904,909,914,919],["SingleFetchClassInstance",905],{"_38":39,"_28":132,"_41":906,"_45":907},{},[908],"3",["SingleFetchClassInstance",910],{"_38":39,"_28":132,"_41":911,"_45":912},{},[913],"Michael West",["SingleFetchClassInstance",915],{"_38":39,"_28":132,"_41":916,"_45":917},{},[918],"532 Alexander St, WA",["SingleFetchClassInstance",920],{"_38":39,"_28":132,"_41":921,"_45":922},{},[923],"(47.490272897328325, -122.27293296965925)",["SingleFetchClassInstance",925],{"_38":39,"_28":49,"_41":926,"_45":927},{},[928],"In MySQL, however, spatial data types behave differently, and depending on the version of MySQL you're using, you may have to use a different approach to storing spatial data. If we updated our MySQL schema to align with our customers schema in our Postgres database, our table might look something like this:",["SingleFetchClassInstance",930],{"_38":39,"_28":497,"_41":931,"_45":933},{"_499":932,"_501":502},"

CREATE TABLE customers\n(\n    id        INT NOT NULL AUTO_INCREMENT,\n    full_name TEXT,\n    address   TEXT,\n    location  POINT,\n    PRIMARY KEY (id)\n);\n

",[],["SingleFetchClassInstance",935],{"_38":39,"_28":49,"_41":936,"_45":937},{},[938],"And to migrate the above data into our MySQL database, we could write a query such as:",["SingleFetchClassInstance",940],{"_38":39,"_28":497,"_41":941,"_45":943},{"_499":942,"_501":502},"

INSERT INTO customers VALUES (1, 'Robert California', '123 Sunny St, AZ', POINT(34.411275716904406,-111.6783709992531));\n

",[],["SingleFetchClassInstance",945],{"_38":39,"_28":49,"_41":946,"_45":947},{},[948],"This will add the first record for Robert California into our database. But if we go and run a SELECT statement on that data, our output will look like the following:",["SingleFetchClassInstance",950],{"_38":39,"_28":84,"_41":951,"_45":952},{"_86":87},[953],["SingleFetchClassInstance",954],{"_38":39,"_28":91,"_41":955,"_45":956},{"_93":8},[957,983],["SingleFetchClassInstance",958],{"_38":39,"_28":97,"_41":959,"_45":960},{},[961],["SingleFetchClassInstance",962],{"_38":39,"_28":102,"_41":963,"_45":964},{},[965,969,974,979],["SingleFetchClassInstance",966],{"_38":39,"_28":107,"_41":967,"_45":968},{},[529],["SingleFetchClassInstance",970],{"_38":39,"_28":107,"_41":971,"_45":972},{},[973],"full_name (TEXT)",["SingleFetchClassInstance",975],{"_38":39,"_28":107,"_41":976,"_45":977},{},[978],"address (TEXT)",["SingleFetchClassInstance",980],{"_38":39,"_28":107,"_41":981,"_45":982},{},[725],["SingleFetchClassInstance",984],{"_38":39,"_28":123,"_41":985,"_45":986},{},[987],["SingleFetchClassInstance",988],{"_38":39,"_28":102,"_41":989,"_45":990},{},[991,995,999,1003],["SingleFetchClassInstance",992],{"_38":39,"_28":132,"_41":993,"_45":994},{},[557],["SingleFetchClassInstance",996],{"_38":39,"_28":132,"_41":997,"_45":998},{},[742],["SingleFetchClassInstance",1000],{"_38":39,"_28":132,"_41":1001,"_45":1002},{},[747],["SingleFetchClassInstance",1004],{"_38":39,"_28":132,"_41":1005,"_45":1006},{},[1007],"0x00000000010100000027DFC4AEA43441403416326E6AEB5BC0",["SingleFetchClassInstance",1009],{"_38":39,"_28":49,"_41":1010,"_45":1011},{},[1012,1013,1018],"The reason for this is because MySQL can store spatial data in multiple different formats. If we wanted to get the actual coordinates for our location, we'd have to use a spatial operator function like ",["SingleFetchClassInstance",1014],{"_38":39,"_28":180,"_41":1015,"_45":1016},{},[1017],"ST_asText",", which would provide the results we want:",["SingleFetchClassInstance",1020],{"_38":39,"_28":497,"_41":1021,"_45":1023},{"_499":1022,"_501":502},"

SELECT id, full_name, address, ST_asText(location) FROM customers;\n

",[],["SingleFetchClassInstance",1025],{"_38":39,"_28":84,"_41":1026,"_45":1027},{"_86":87},[1028],["SingleFetchClassInstance",1029],{"_38":39,"_28":91,"_41":1030,"_45":1031},{"_93":8},[1032,1056],["SingleFetchClassInstance",1033],{"_38":39,"_28":97,"_41":1034,"_45":1035},{},[1036],["SingleFetchClassInstance",1037],{"_38":39,"_28":102,"_41":1038,"_45":1039},{},[1040,1044,1048,1052],["SingleFetchClassInstance",1041],{"_38":39,"_28":107,"_41":1042,"_45":1043},{},[529],["SingleFetchClassInstance",1045],{"_38":39,"_28":107,"_41":1046,"_45":1047},{},[973],["SingleFetchClassInstance",1049],{"_38":39,"_28":107,"_41":1050,"_45":1051},{},[978],["SingleFetchClassInstance",1053],{"_38":39,"_28":107,"_41":1054,"_45":1055},{},[725],["SingleFetchClassInstance",1057],{"_38":39,"_28":123,"_41":1058,"_45":1059},{},[1060],["SingleFetchClassInstance",1061],{"_38":39,"_28":102,"_41":1062,"_45":1063},{},[1064,1068,1072,1076],["SingleFetchClassInstance",1065],{"_38":39,"_28":132,"_41":1066,"_45":1067},{},[557],["SingleFetchClassInstance",1069],{"_38":39,"_28":132,"_41":1070,"_45":1071},{},[742],["SingleFetchClassInstance",1073],{"_38":39,"_28":132,"_41":1074,"_45":1075},{},[747],["SingleFetchClassInstance",1077],{"_38":39,"_28":132,"_41":1078,"_45":1079},{},[1080],"POINT(34.411275716904406,-111.6783709992531)",["SingleFetchClassInstance",1082],{"_38":39,"_28":49,"_41":1083,"_45":1084},{},[1085,1086,221,1091,1096],"If we didn't want to work with spatial functions like this in MySQL, we could always break out the location into two columns. For example, ",["SingleFetchClassInstance",1087],{"_38":39,"_28":180,"_41":1088,"_45":1089},{},[1090],"location_latitude",["SingleFetchClassInstance",1092],{"_38":39,"_28":180,"_41":1093,"_45":1094},{},[1095],"location_longitude",", and store the individual points as decimal data types. But the drawback here is that we would be required to do a little more work on our client to keep track of the faux spatial data types, and we also couldn't utilize any of the spatial functions provided by MySQL.",["SingleFetchClassInstance",1098],{"_38":39,"_28":49,"_41":1099,"_45":1100},{},[489,1101,494],["SingleFetchClassInstance",1102],{"_38":39,"_28":180,"_41":1103,"_45":1104},{},[483],["SingleFetchClassInstance",1106],{"_38":39,"_28":497,"_41":1107,"_45":1109},{"_499":1108,"_501":502},"

CREATE TABLE orders\n(\n    id       UUID default gen_random_uuid(),\n    customer INTEGER,\n    products JSONB\n);\n

",[],["SingleFetchClassInstance",1111],{"_38":39,"_28":49,"_41":1112,"_45":1113},{},[1114,1115,1119],"And our data in the ",["SingleFetchClassInstance",1116],{"_38":39,"_28":180,"_41":1117,"_45":1118},{},[483]," collection looks like:",["SingleFetchClassInstance",1121],{"_38":39,"_28":84,"_41":1122,"_45":1123},{"_86":87},[1124],["SingleFetchClassInstance",1125],{"_38":39,"_28":91,"_41":1126,"_45":1127},{"_93":8},[1128,1151],["SingleFetchClassInstance",1129],{"_38":39,"_28":97,"_41":1130,"_45":1131},{},[1132],["SingleFetchClassInstance",1133],{"_38":39,"_28":102,"_41":1134,"_45":1135},{},[1136,1141,1146],["SingleFetchClassInstance",1137],{"_38":39,"_28":107,"_41":1138,"_45":1139},{},[1140],"id (UUID)",["SingleFetchClassInstance",1142],{"_38":39,"_28":107,"_41":1143,"_45":1144},{},[1145],"customer (INT)",["SingleFetchClassInstance",1147],{"_38":39,"_28":107,"_41":1148,"_45":1149},{},[1150],"product (JSONB)",["SingleFetchClassInstance",1152],{"_38":39,"_28":123,"_41":1153,"_45":1154},{},[1155,1177],["SingleFetchClassInstance",1156],{"_38":39,"_28":102,"_41":1157,"_45":1158},{},[1159,1164,1168],["SingleFetchClassInstance",1160],{"_38":39,"_28":132,"_41":1161,"_45":1162},{},[1163],"10d4e52d-68fd-44f5-bf9b-a960cfb03de1",["SingleFetchClassInstance",1165],{"_38":39,"_28":132,"_41":1166,"_45":1167},{},[557],["SingleFetchClassInstance",1169],{"_38":39,"_28":132,"_41":1170,"_45":1171},{},[1172],["SingleFetchClassInstance",1173],{"_38":39,"_28":180,"_41":1174,"_45":1175},{},[1176],"[{\"product\": \"Achieving PlanetScale\", \"price\": 50}]",["SingleFetchClassInstance",1178],{"_38":39,"_28":102,"_41":1179,"_45":1180},{},[1181,1186,1190],["SingleFetchClassInstance",1182],{"_38":39,"_28":132,"_41":1183,"_45":1184},{},[1185],"3de3f1a5-7a90-4273-a845-7129183edd47",["SingleFetchClassInstance",1187],{"_38":39,"_28":132,"_41":1188,"_45":1189},{},[581],["SingleFetchClassInstance",1191],{"_38":39,"_28":132,"_41":1192,"_45":1193},{},[1194],["SingleFetchClassInstance",1195],{"_38":39,"_28":180,"_41":1196,"_45":1197},{},[1198],"[{\"product\": \"Achieving PlanetScale\", \"price\": 50}, {\"product\": \"The Database that Could\" \"price\": 100}]",["SingleFetchClassInstance",1200],{"_38":39,"_28":599,"_41":1201,"_45":1203},{"_419":1202},"handling-the-uuid-postgres-type-in-mysql",[1204],["SingleFetchClassInstance",1205],{"_38":39,"_28":55,"_41":1206,"_45":1208},{"_57":1207},"#handling-the-uuid-postgres-type-in-mysql",[1209,1210,1215],"Handling the ",["SingleFetchClassInstance",1211],{"_38":39,"_28":180,"_41":1212,"_45":1213},{},[1214],"UUID"," Postgres type in MySQL",["SingleFetchClassInstance",1217],{"_38":39,"_28":49,"_41":1218,"_45":1219},{},[1220,1221,1226,1227,1232,1233,1237,1238,1243],"In this table, we have two fields that MySQL does not natively support, but that doesn't mean we can't have a successful migration. The UUID data type is essentially a 36 character long string. So we can set this data type to a ",["SingleFetchClassInstance",1222],{"_38":39,"_28":180,"_41":1223,"_45":1224},{},[1225],"varchar(36)"," in MySQL. MySQL also supports generating UUID's via a ",["SingleFetchClassInstance",1228],{"_38":39,"_28":180,"_41":1229,"_45":1230},{},[1231],"UUID()"," function, so when it comes to adding new records, we could utilize the ",["SingleFetchClassInstance",1234],{"_38":39,"_28":180,"_41":1235,"_45":1236},{},[1231]," function that behaves similarly to Postgres's ",["SingleFetchClassInstance",1239],{"_38":39,"_28":180,"_41":1240,"_45":1241},{},[1242],"gen_random_uuid()"," function.",["SingleFetchClassInstance",1245],{"_38":39,"_28":599,"_41":1246,"_45":1248},{"_419":1247},"handling-the-postgres-jsonb-type-in-mysql",[1249],["SingleFetchClassInstance",1250],{"_38":39,"_28":55,"_41":1251,"_45":1253},{"_57":1252},"#handling-the-postgres-jsonb-type-in-mysql",[1254],"Handling the Postgres jsonb type in MySQL",["SingleFetchClassInstance",1256],{"_38":39,"_28":49,"_41":1257,"_45":1258},{},[489,1259,1263,1264,1269,1270,1274,1275,1280,1281,1285,1286,1290,1291,1295,1296,1302,1303,1309],["SingleFetchClassInstance",1260],{"_38":39,"_28":180,"_41":1261,"_45":1262},{},[471]," column, on the other hand, is of type ",["SingleFetchClassInstance",1265],{"_38":39,"_28":180,"_41":1266,"_45":1267},{},[1268],"jsonb",", or binary JSON. While MySQL at the moment does not have support for the ",["SingleFetchClassInstance",1271],{"_38":39,"_28":180,"_41":1272,"_45":1273},{},[1268]," data type, it does have support for ",["SingleFetchClassInstance",1276],{"_38":39,"_28":180,"_41":1277,"_45":1278},{},[1279],"json",", so if we set the data type for the ",["SingleFetchClassInstance",1282],{"_38":39,"_28":180,"_41":1283,"_45":1284},{},[471]," column as ",["SingleFetchClassInstance",1287],{"_38":39,"_28":180,"_41":1288,"_45":1289},{},[1279],", we'll get a very similar experience. While ",["SingleFetchClassInstance",1292],{"_38":39,"_28":180,"_41":1293,"_45":1294},{},[1268]," may be a more efficient way to store JSON data in a SQL database, know that even with MySQL, you can still ",["SingleFetchClassInstance",1297],{"_38":39,"_28":55,"_41":1298,"_45":1300},{"_57":1299},"/blog/the-mysql-json-data-type",[1301],"leverage the JSON data type"," and do things like ",["SingleFetchClassInstance",1304],{"_38":39,"_28":55,"_41":1305,"_45":1307},{"_57":1306},"/blog/indexing-json-in-mysql",[1308],"index JSON columns",", update and modify the JSON values, return subsets of the JSON data, and much more.",["SingleFetchClassInstance",1311],{"_38":39,"_28":49,"_41":1312,"_45":1313},{},[1314],"The above examples explore just a few scenarios that you may run into when deciding to migrate from Postgres to MySQL. At the end of the day, migration from one database to another is not impossible, you just have to understand the differences between the two, and also understand the benefits you'll gain when you do migrate. Once you do migrate, ensure that you are following that database's best practices to get the most out of the migration. Below are a couple of other migration factors to consider before making the switch.",["SingleFetchClassInstance",1316],{"_38":39,"_28":417,"_41":1317,"_45":1319},{"_419":1318},"other-migration-factors-going-from-postgres-to-mysql",[1320],["SingleFetchClassInstance",1321],{"_38":39,"_28":55,"_41":1322,"_45":1324},{"_57":1323},"#other-migration-factors-going-from-postgres-to-mysql",[1325],"Other migration factors going from Postgres to MySQL",["SingleFetchClassInstance",1327],{"_38":39,"_28":49,"_41":1328,"_45":1329},{},[1330],"Before migrating from Postgres to MySQL, it’s important to be aware of some differences between the two database systems that may pose an issue. We’ve listed some of these below.",["SingleFetchClassInstance",1332],{"_38":39,"_28":599,"_41":1333,"_45":1335},{"_419":1334},"complications-arising-from-certain-data-models",[1336],["SingleFetchClassInstance",1337],{"_38":39,"_28":55,"_41":1338,"_45":1340},{"_57":1339},"#complications-arising-from-certain-data-models",[1341],"Complications arising from certain data models",["SingleFetchClassInstance",1343],{"_38":39,"_28":49,"_41":1344,"_45":1345},{},[1346],"MySQL and Postgres both share support for many data types. This ranges from traditional SQL types like String, Boolean, Integer, and Timestamp to complex data structures such as JSON, XML, and TEXT. However, it's good to keep in mind there are some Postgres data types that MySQL does not support. You can see some more information about these in the table at the top of this article.",["SingleFetchClassInstance",1348],{"_38":39,"_28":49,"_41":1349,"_45":1350},{},[1351],"So, even though MySQL supports the various traditional SQL types required by a variety of applications to store and process different kinds of data, such as Date, Timestamp, Character, Long Text, Float and Decimal, and Blob, potential complications may arise when trying to migrate complex data structures that may not yet be supported in MySQL. Fortunately, these issues are being addressed in newer MySQL releases.",["SingleFetchClassInstance",1353],{"_38":39,"_28":599,"_41":1354,"_45":1356},{"_419":1355},"differences-in-database-and-sql-capabilities",[1357],["SingleFetchClassInstance",1358],{"_38":39,"_28":55,"_41":1359,"_45":1361},{"_57":1360},"#differences-in-database-and-sql-capabilities",[1362],"Differences in database and SQL capabilities",["SingleFetchClassInstance",1364],{"_38":39,"_28":49,"_41":1365,"_45":1366},{},[1367],"Certain operations are carried out differently in MySQL and Postgres databases. Also, some features may not be supported in one while supported in the other. Knowing these may help you avoid some common pitfalls.",["SingleFetchClassInstance",1369],{"_38":39,"_28":1370,"_41":1371,"_45":1373},"h4",{"_419":1372},"drop-a-temporary-table",[1374],["SingleFetchClassInstance",1375],{"_38":39,"_28":55,"_41":1376,"_45":1378},{"_57":1377},"#drop-a-temporary-table",[1379,1384],["SingleFetchClassInstance",1380],{"_38":39,"_28":180,"_41":1381,"_45":1382},{},[1383],"DROP"," a temporary table",["SingleFetchClassInstance",1386],{"_38":39,"_28":49,"_41":1387,"_45":1388},{},[1389,1390,1395,1396,340,1400,1404],"Though ",["SingleFetchClassInstance",1391],{"_38":39,"_28":180,"_41":1392,"_45":1393},{},[1394],"CREATE TEMPORARY TABLE"," is used to create a temporary table in both MySQL and Postgres databases, only MySQL has the ",["SingleFetchClassInstance",1397],{"_38":39,"_28":180,"_41":1398,"_45":1399},{},[319],["SingleFetchClassInstance",1401],{"_38":39,"_28":180,"_41":1402,"_45":1403},{},[325]," statement. This makes it possible to drop only the temporary table in MySQL without affecting the main table.",["SingleFetchClassInstance",1406],{"_38":39,"_28":49,"_41":1407,"_45":1408},{},[1409,1410,1414,1415,1419],"In Postgres, this omission requires you to be more careful with your naming convention because a temporary table may have the same name as a regular table. And since you can't specify ",["SingleFetchClassInstance",1411],{"_38":39,"_28":180,"_41":1412,"_45":1413},{},[319]," in your ",["SingleFetchClassInstance",1416],{"_38":39,"_28":180,"_41":1417,"_45":1418},{},[325]," statement, you might unintentionally delete important tables.",["SingleFetchClassInstance",1421],{"_38":39,"_28":1370,"_41":1422,"_45":1424},{"_419":1423},"cascade-when-truncating-a-table",[1425],["SingleFetchClassInstance",1426],{"_38":39,"_28":55,"_41":1427,"_45":1429},{"_57":1428},"#cascade-when-truncating-a-table",[1430,1435],["SingleFetchClassInstance",1431],{"_38":39,"_28":180,"_41":1432,"_45":1433},{},[1434],"CASCADE"," when truncating a table",["SingleFetchClassInstance",1437],{"_38":39,"_28":49,"_41":1438,"_45":1439},{},[1440,1441,1446,1447,1451,1452,184,1457,1462,1463,1467],"Postgres’ ",["SingleFetchClassInstance",1442],{"_38":39,"_28":180,"_41":1443,"_45":1444},{},[1445],"TRUNCATE TABLE"," statement for truncating a table supports the ",["SingleFetchClassInstance",1448],{"_38":39,"_28":180,"_41":1449,"_45":1450},{},[1434]," keyword. It also supports features like ",["SingleFetchClassInstance",1453],{"_38":39,"_28":180,"_41":1454,"_45":1455},{},[1456],"RESTART IDENTITY",["SingleFetchClassInstance",1458],{"_38":39,"_28":180,"_41":1459,"_45":1460},{},[1461],"CONTINUE IDENTITY",", transaction-safe, and so on. ",["SingleFetchClassInstance",1464],{"_38":39,"_28":180,"_41":1465,"_45":1466},{},[1456]," tells Postgres to reset all identity columns when truncating a table automatically, and transaction-safe means that the truncation will be safely rolled back if the surrounding transaction doesn’t commit.",["SingleFetchClassInstance",1469],{"_38":39,"_28":49,"_41":1470,"_45":1471},{},[1472,1473,1477,1478,1482],"In contrast, MySQL’s ",["SingleFetchClassInstance",1474],{"_38":39,"_28":180,"_41":1475,"_45":1476},{},[1445]," feature supports neither ",["SingleFetchClassInstance",1479],{"_38":39,"_28":180,"_41":1480,"_45":1481},{},[1434]," nor transaction safe, which means that data can’t be rolled back once it’s deleted. These features are likely to be added in future MySQL releases.",["SingleFetchClassInstance",1484],{"_38":39,"_28":1370,"_41":1485,"_45":1487},{"_419":1486},"stored-procedures",[1488],["SingleFetchClassInstance",1489],{"_38":39,"_28":55,"_41":1490,"_45":1492},{"_57":1491},"#stored-procedures",[1493],"Stored procedures",["SingleFetchClassInstance",1495],{"_38":39,"_28":49,"_41":1496,"_45":1497},{},[1498],"MySQL requires that the procedure is written in the standard SQL syntax. This contrasts with Postgres, where the Procedures are based on functions and can be written in other languages, such as Ruby, Perl, SQL, Python, JavaScript, and others.",["SingleFetchClassInstance",1500],{"_38":39,"_28":1370,"_41":1501,"_45":1503},{"_419":1502},"postgres-extensions",[1504],["SingleFetchClassInstance",1505],{"_38":39,"_28":55,"_41":1506,"_45":1508},{"_57":1507},"#postgres-extensions",[1509],"Postgres extensions",["SingleFetchClassInstance",1511],{"_38":39,"_28":49,"_41":1512,"_45":1513},{},[1514,1515,1521],"If you're using ",["SingleFetchClassInstance",1516],{"_38":39,"_28":55,"_41":1517,"_45":1519},{"_57":1518},"https://www.postgresql.org/download/products/6-postgresql-extensions/",[1520],"extensions in Postgres",", it can make a migration even trickier, so make sure to audit each of these individually before migrating.",["SingleFetchClassInstance",1523],{"_38":39,"_28":1370,"_41":1524,"_45":1526},{"_419":1525},"case-sensitivity-and-ififnull-support",[1527],["SingleFetchClassInstance",1528],{"_38":39,"_28":55,"_41":1529,"_45":1531},{"_57":1530},"#case-sensitivity-and-ififnull-support",[1532,1533,1538],"Case sensitivity and ",["SingleFetchClassInstance",1534],{"_38":39,"_28":180,"_41":1535,"_45":1536},{},[1537],"IF/IFNULL"," support",["SingleFetchClassInstance",1540],{"_38":39,"_28":49,"_41":1541,"_45":1542},{},[1543],"Postgres table and column names are case sensitive if in double quotes. MySQL table and column names, on the other hand, aren't case sensitive. So when doing a migration, make sure to keep this in mind.",["SingleFetchClassInstance",1545],{"_38":39,"_28":49,"_41":1546,"_45":1547},{},[1548,1549,221,1554,1559],"Moreover, MySQL allows for ",["SingleFetchClassInstance",1550],{"_38":39,"_28":180,"_41":1551,"_45":1552},{},[1553],"IF",["SingleFetchClassInstance",1555],{"_38":39,"_28":180,"_41":1556,"_45":1557},{},[1558],"IFNULL"," statements for evaluating conditions, while Postgres doesn’t.",["SingleFetchClassInstance",1561],{"_38":39,"_28":417,"_41":1562,"_45":1564},{"_419":1563},"conclusion",[1565],["SingleFetchClassInstance",1566],{"_38":39,"_28":55,"_41":1567,"_45":1569},{"_57":1568},"#conclusion",[1570],"Conclusion",["SingleFetchClassInstance",1572],{"_38":39,"_28":49,"_41":1573,"_45":1574},{},[1575],"Migrating between one SQL database to another, such as going from Postgres to MySQL, on the surface seems like it would be very straightforward, but the two database technologies are quite different in how they handle and store certain data types, features they do and don't support, and benefits they provide to your application.",["SingleFetchClassInstance",1577],{"_38":39,"_28":49,"_41":1578,"_45":1579},{},[1580,1581,221,1587,1592],"While Postgres may have more features out of the box, MySQL is typically more performant for common use cases. Additionally, MySQL has some fantastic options, such as ",["SingleFetchClassInstance",1582],{"_38":39,"_28":55,"_41":1583,"_45":1585},{"_57":1584},"https://vitess.io",[1586],"Vitess",["SingleFetchClassInstance",1588],{"_38":39,"_28":55,"_41":1589,"_45":1590},{"_57":441},[1591],"PlanetScale",", for scaling massive databases.",["SingleFetchClassInstance",1594],{"_38":39,"_28":49,"_41":1595,"_45":1596},{},[1597],"Understanding the differences between the two technologies and how they fit into your specific use case is the number one thing you should consider before choosing either, or migrating from one to the other. I hope that this article gave you some insight into what a migration from PostgreSQL to MySQL might look like.",["SingleFetchClassInstance",1599],{"_38":39,"_28":49,"_41":1600,"_45":1601},{},[1602],"Happy migrating! :)","body_text","PlanetScale now supports PostgreSQL.\nChoosing a data store is one of the most important decisions you'll make when building software applications. The good and bad news though, is that there is an abundance of options. Depending on the type of application you're building, you may opt for a relational database like MySQL or Postgres, a non-relational database like MongoDB or CouchDB, a graph database like Neo4j, or one of the many other alternatives that each bring their own benefits and drawbacks.\nIt is a difficult choice and sometimes you realize that the database you initially went with no longer serves the needs of your application and you have to make the choice to migrate.\nIn this blog post, we'll take a look at how you can migrate your database schema from PostgreSQL to MySQL. MySQL and PostgreSQL or Postgres are both relational databases that have a lot of similarities, but also have a fair amount of differences that can make migration a challenge.\nA detailed list of the differences between PostgreSQL and MySQL can be found below:\nMetric\nPostgres\nMySQL\nLicensing\nReleased under PostgreSQL license, a free open-source license similar to BSD or MIT licenses\nSource code is available under the terms of the GNU General Public License\nACID\nYes\nYes\nTriggers\nSupports AFTER, BEFORE, and INSTEAD OF triggers for INSERT, UPDATE, and DELETE statements\nSupports AFTER and BEFORE triggers for INSERT, UPDATE, and DELETE statements\nUnsigned integer\nNo support\nA column can be made not to accept negative values\nMaterialized views\nSupported\nNot supported\nANSI/ISO SQL compliance\nFully compliant\nMostly compliant\nDrop temporary table\nNo TEMP or TEMPORARY keyword in DROP TABLE statement\nSupports the TEMP or TEMPORARY keyword in the DROP TABLE statement, allowing you to remove just the temporary table\nTable partitioning\nSupports RANGE, LIST, and HASH\nSupports RANGE, COLUMN, LIST, HASH, KEY, and either HASH or KEY for composite partitioning\nThese differences are important to keep in mind when making the decision to migrate from Postgres to MySQL and vice versa. With that said, let's dive into how we can migrate an existing Postgres database to MySQL.\nAn example migration from Postgres to MySQL\nIn this article, we'll take the approach of migrating from PostgreSQL to MySQL manually. While there are software tools, ORMs, and other approaches we can take that might abstract the migration from one database to another, and we will look at those approaches in subsequent tutorials, here we want to explore what you'll need to consider at a low level.\nFor our sample migration, we are going to compare an instance of PostgreSQL and what a migration to MySQL might look like. For PostgreSQL, we'll use a locally hosted server. For MySQL, rather than setting up a local database, we'll test using PlanetScale's hosted MySQL offering. If you don't already have a PlanetScale account, you can sign up now.\nDifferences between our Postgres and MySQL schema\nIn our PostgreSQL schema, we have three tables: a products table that holds information about our inventory, a customers table that holds information about our customers, and an orders table that keeps records of orders placed by our customers.\nThe products table in PostgreSQL looks like the following:CREATE TABLE products\n(\n id SERIAL,\n name VARCHAR,\n description VARCHAR,\n price INTEGER\n);\n\nSo the data could be represented as:\nid (SERIAL)\nname (VARCHAR)\ndescription (VARCHAR)\nprice (INTEGER)\n1\nAchieving PlanetScale\nAchieving PlanetScale teaches you how to think bigger and more horizontally!\n50\n2\nThe Database that Could\nFollow the adventures of the database that could accomplish anything it wanted\n100\nVARCHAR in Postgres vs MySQL\nLooking at the data types within this table, the migration to MySQL should be pretty straightforward. All of the data types match up with what we have available in MySQL. While on the surface it does look pretty straightforward, there are a few things to consider. In MySQL for the varchar type, we have to set a max value, whereas in Postgres we did not. We can instead opt to use the text type instead in MySQL, or keep the same varchar type, but specify a max length. If we do opt for the varchar option, we'll have to know what the max value is, otherwise, we'll run into issues when we actually migrate the data over.\nSERIAL in Postgres vs MySQL\nAdditionally, if we set the serial type in MySQL, it'll default the underlying data type to a bigint unsigned auto_increment. In the grand scheme of things, this won't affect how we store the data too much, but is worth noting because depending on how many records we have, a different data type may be more suitable.\nThe customers table in PostgreSQL looks like the following:CREATE TABLE customers\n(\n id SERIAL,\n full_name VARCHAR,\n address VARCHAR,\n location POINT\n);\n\nIf we look at our sample data for this table, we'll get the following:\nid (SERIAL)\nfull_name (VARCHAR)\naddress (VARCHAR)\nlocation (POINT)\n1\nRobert California\n123 Sunny St, AZ\n(34.411275716904406,-111.6783709992531)\n2\nNick Claus\n785 North Pole, AL\n(69.72578389209082,-153.14940161799086)\nNow things get a little bit more interesting. While the first three columns are something we dealt with in the previous example, the fourth column represents a data type that is available in Postgres as well as in MySQL, but behaves quite differently in practice.\nSpatial data in Postgres vs MySQL: POINT\nIn Postgres, working with spatial data is fairly straightforward. When we define the location column as a type of Point, we can insert spatial data into it by giving it the coordinates we want. So if we wanted to add a new record into our database, we could do the following:INSERT INTO customers (id, full_name, address, location) VALUES (3, 'Michael West', '532 Alexander St, WA', POINT(47.490272897328325, -122.27293296965925));\n\nAnd our data would be properly reflected in the database as:\nid (SERIAL)\nfull_name (VARCHAR)\naddress (VARCHAR)\nlocation (POINT)\n1\nRobert California\n123 Sunny St, AZ\n(34.411275716904406,-111.6783709992531)\n2\nNick Claus\n785 North Pole, AL\n(69.72578389209082,-153.14940161799086)\n3\nMichael West\n532 Alexander St, WA\n(47.490272897328325, -122.27293296965925)\nIn MySQL, however, spatial data types behave differently, and depending on the version of MySQL you're using, you may have to use a different approach to storing spatial data. If we updated our MySQL schema to align with our customers schema in our Postgres database, our table might look something like this:CREATE TABLE customers\n(\n id INT NOT NULL AUTO_INCREMENT,\n full_name TEXT,\n address TEXT,\n location POINT,\n PRIMARY KEY (id)\n);\n\nAnd to migrate the above data into our MySQL database, we could write a query such as:INSERT INTO customers VALUES (1, 'Robert California', '123 Sunny St, AZ', POINT(34.411275716904406,-111.6783709992531));\n\nThis will add the first record for Robert California into our database. But if we go and run a SELECT statement on that data, our output will look like the following:\nid (SERIAL)\nfull_name (TEXT)\naddress (TEXT)\nlocation (POINT)\n1\nRobert California\n123 Sunny St, AZ\n0x00000000010100000027DFC4AEA43441403416326E6AEB5BC0\nThe reason for this is because MySQL can store spatial data in multiple different formats. If we wanted to get the actual coordinates for our location, we'd have to use a spatial operator function like ST_asText, which would provide the results we want:SELECT id, full_name, address, ST_asText(location) FROM customers;\n\nid (SERIAL)\nfull_name (TEXT)\naddress (TEXT)\nlocation (POINT)\n1\nRobert California\n123 Sunny St, AZ\nPOINT(34.411275716904406,-111.6783709992531)\nIf we didn't want to work with spatial functions like this in MySQL, we could always break out the location into two columns. For example, location_latitude and location_longitude, and store the individual points as decimal data types. But the drawback here is that we would be required to do a little more work on our client to keep track of the faux spatial data types, and we also couldn't utilize any of the spatial functions provided by MySQL.\nThe orders table in PostgreSQL looks like the following:CREATE TABLE orders\n(\n id UUID default gen_random_uuid(),\n customer INTEGER,\n products JSONB\n);\n\nAnd our data in the orders collection looks like:\nid (UUID)\ncustomer (INT)\nproduct (JSONB)\n10d4e52d-68fd-44f5-bf9b-a960cfb03de1\n1\n[{\"product\": \"Achieving PlanetScale\", \"price\": 50}]\n3de3f1a5-7a90-4273-a845-7129183edd47\n2\n[{\"product\": \"Achieving PlanetScale\", \"price\": 50}, {\"product\": \"The Database that Could\" \"price\": 100}]\nHandling the UUID Postgres type in MySQL\nIn this table, we have two fields that MySQL does not natively support, but that doesn't mean we can't have a successful migration. The UUID data type is essentially a 36 character long string. So we can set this data type to a varchar(36) in MySQL. MySQL also supports generating UUID's via a UUID() function, so when it comes to adding new records, we could utilize the UUID() function that behaves similarly to Postgres's gen_random_uuid() function.\nHandling the Postgres jsonb type in MySQL\nThe products column, on the other hand, is of type jsonb, or binary JSON. While MySQL at the moment does not have support for the jsonb data type, it does have support for json, so if we set the data type for the products column as json, we'll get a very similar experience. While jsonb may be a more efficient way to store JSON data in a SQL database, know that even with MySQL, you can still leverage the JSON data type and do things like index JSON columns, update and modify the JSON values, return subsets of the JSON data, and much more.\nThe above examples explore just a few scenarios that you may run into when deciding to migrate from Postgres to MySQL. At the end of the day, migration from one database to another is not impossible, you just have to understand the differences between the two, and also understand the benefits you'll gain when you do migrate. Once you do migrate, ensure that you are following that database's best practices to get the most out of the migration. Below are a couple of other migration factors to consider before making the switch.\nOther migration factors going from Postgres to MySQL\nBefore migrating from Postgres to MySQL, it’s important to be aware of some differences between the two database systems that may pose an issue. We’ve listed some of these below.\nComplications arising from certain data models\nMySQL and Postgres both share support for many data types. This ranges from traditional SQL types like String, Boolean, Integer, and Timestamp to complex data structures such as JSON, XML, and TEXT. However, it's good to keep in mind there are some Postgres data types that MySQL does not support. You can see some more information about these in the table at the top of this article.\nSo, even though MySQL supports the various traditional SQL types required by a variety of applications to store and process different kinds of data, such as Date, Timestamp, Character, Long Text, Float and Decimal, and Blob, potential complications may arise when trying to migrate complex data structures that may not yet be supported in MySQL. Fortunately, these issues are being addressed in newer MySQL releases.\nDifferences in database and SQL capabilities\nCertain operations are carried out differently in MySQL and Postgres databases. Also, some features may not be supported in one while supported in the other. Knowing these may help you avoid some common pitfalls.\nDROP a temporary table\nThough CREATE TEMPORARY TABLE is used to create a temporary table in both MySQL and Postgres databases, only MySQL has the TEMPORARY keyword in the DROP TABLE statement. This makes it possible to drop only the temporary table in MySQL without affecting the main table.\nIn Postgres, this omission requires you to be more careful with your naming convention because a temporary table may have the same name as a regular table. And since you can't specify TEMPORARY in your DROP TABLE statement, you might unintentionally delete important tables.\nCASCADE when truncating a table\nPostgres’ TRUNCATE TABLE statement for truncating a table supports the CASCADE keyword. It also supports features like RESTART IDENTITY, CONTINUE IDENTITY, transaction-safe, and so on. RESTART IDENTITY tells Postgres to reset all identity columns when truncating a table automatically, and transaction-safe means that the truncation will be safely rolled back if the surrounding transaction doesn’t commit.\nIn contrast, MySQL’s TRUNCATE TABLE feature supports neither CASCADE nor transaction safe, which means that data can’t be rolled back once it’s deleted. These features are likely to be added in future MySQL releases.\nStored procedures\nMySQL requires that the procedure is written in the standard SQL syntax. This contrasts with Postgres, where the Procedures are based on functions and can be written in other languages, such as Ruby, Perl, SQL, Python, JavaScript, and others.\nPostgres extensions\nIf you're using extensions in Postgres, it can make a migration even trickier, so make sure to audit each of these individually before migrating.\nCase sensitivity and IF/IFNULL support\nPostgres table and column names are case sensitive if in double quotes. MySQL table and column names, on the other hand, aren't case sensitive. So when doing a migration, make sure to keep this in mind.\nMoreover, MySQL allows for IF and IFNULL statements for evaluating conditions, while Postgres doesn’t.\nConclusion\nMigrating between one SQL database to another, such as going from Postgres to MySQL, on the surface seems like it would be very straightforward, but the two database technologies are quite different in how they handle and store certain data types, features they do and don't support, and benefits they provide to your application.\nWhile Postgres may have more features out of the box, MySQL is typically more performant for common use cases. Additionally, MySQL has some fantastic options, such as Vitess and PlanetScale, for scaling massive databases.\nUnderstanding the differences between the two technologies and how they fit into your specific use case is the number one thing you should consider before choosing either, or migrating from one to the other. I hope that this article gave you some insight into what a migration from PostgreSQL to MySQL might look like.\nHappy migrating! :)","aside","toc",[1608,1613,1630,1636],{"_45":1609,"_419":420,"_1610":1611,"_1612":427},[],"level",2,"title",{"_45":1614,"_419":454,"_1610":1611,"_1612":461},[1615,1619,1622,1625,1628],{"_45":1616,"_419":601,"_1610":1617,"_1612":1618},[],3,"VARCHAR in Postgres vs MySQL",{"_45":1620,"_419":644,"_1610":1617,"_1612":1621},[],"SERIAL in Postgres vs MySQL",{"_45":1623,"_419":784,"_1610":1617,"_1612":1624},[],"Spatial data in Postgres vs MySQL: POINT",{"_45":1626,"_419":1202,"_1610":1617,"_1612":1627},[],"Handling the UUID Postgres type in MySQL",{"_45":1629,"_419":1247,"_1610":1617,"_1612":1254},[],{"_45":1631,"_419":1318,"_1610":1611,"_1612":1325},[1632,1634],{"_45":1633,"_419":1334,"_1610":1617,"_1612":1341},[],{"_45":1635,"_419":1355,"_1610":1617,"_1612":1362},[],{"_45":1637,"_419":1563,"_1610":1611,"_1612":1570},[],"Migrating from Postgres to MySQL","authors",[1641],{"_26":27,"_28":29,"_1642":-7},"x","categories",[1645],"tutorials","excerpt","Learn how to migrate from Postgres to MySQL, Postgres vs MySQL incompatibilities, and more.","createdAt","2023-02-09T15:29:55.100Z","meta",{"_1652":1653,"_1654":1647,"_1655":1656,"_1612":1638},"canonical","https://planetscale.com/blog/migrating-from-postgres-to-mysql","description","image","/assets/migrating-from-postgres-to-mysql-social-DzAwom9y.png","pagination",{"_1659":1660,"_1661":8,"_1662":8,"_1663":1660,"_1664":1665},"page",1,"hasPrev","hasNext","totalPages","url","https://planetscale.com/blog/author/adnan","https://planetscale.com","actionData","errors"]

Posts by Adnan Kukic

Migrating from Postgres to MySQL