{"id":1768,"date":"2020-06-17T04:23:48","date_gmt":"2020-06-17T11:23:48","guid":{"rendered":"https:\/\/renanmf.com\/?p=1768"},"modified":"2021-02-13T22:48:50","modified_gmt":"2021-02-14T01:48:50","slug":"sql-data-types","status":"publish","type":"post","link":"https:\/\/renanmf.com\/sql-data-types\/","title":{"rendered":"SQL: Data Types"},"content":{"rendered":"<h1>Data Types<\/h1>\n<p>Each field in a Table has a Type.<\/p>\n<p>A Data Type is used to specify what kind of object that particular field will store.<\/p>\n<p>When creating your own structures, using the right type for the right data is essential to prevent data loss and to work better with the information you have.<\/p>\n<p>It is important to note that each database has different types.<\/p>\n<p>They might be similar, but they are not exactly the same and you should be aware of this when dealing with different products like Oracle or MySQL, for instance.<\/p>\n<p>I will list PostgreSQL Data Types in this article to give you an understanding of the main types.<\/p>\n<p>If you are working with other databases, just check the documentation of each one and you should see lots of similarities to among them.<\/p>\n<p>A few popular examples are:<\/p>\n<ul>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/data-types.html\">MysSQL Data Types<\/a><\/li>\n<li><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/data-types\/data-types-transact-sql?view=sql-server-ver15\">SQL Server Data Types<\/a><\/li>\n<li><a href=\"https:\/\/docs.oracle.com\/cd\/B28359_01\/server.111\/b28318\/datatype.htm#CNCPT012\">Oracle Data Types<\/a><\/li>\n<\/ul>\n<p>Let&#8217;s learn about PostgreSQL Data Types.<\/p>\n<h2>Data Types in PostgreSQL<\/h2>\n<p>If you check the oficial documentation for <a href=\"https:\/\/www.postgresql.org\/docs\/9.5\/datatype.html\">PostgreSQL Data Types<\/a>, you will see some very specific types for very specific cases.<\/p>\n<p>Let&#8217;s keep things simple and use some Pareto&#8217;s 80\/20 to show you the types you are going to use most of the time, they are:<\/p>\n<ul>\n<li>Numeric Types: integer and floating-point<\/li>\n<li>Character types: char, varchar and text<\/li>\n<li>Boolean<\/li>\n<li>Date\/Time Types: date, time, timestamp, and interval<\/li>\n<\/ul>\n<p>These types are very generic and you can use them to store numbers, text, and dates, which is enough for most use cases.<\/p>\n<h2>Numeric Types<\/h2>\n<p>You can work with either integers of float-pointing numbers.<\/p>\n<h3>Integer<\/h3>\n<p>For integers you can work with:<\/p>\n<ul>\n<li>smallint: it stores 2 bytes and can store values in the range of -32768 to +32767.<\/li>\n<li>integer: it stores 4 bytes and can store values in the range of -2147483648 to +2147483647<\/li>\n<li>bigint: it can store 8 bytes and can store values in the range of -9223372036854775808 to +9223372036854775807<\/li>\n<\/ul>\n<p>All of them have their <code>serial<\/code> counterparts, which is a specific type that automatically increments for every new row, it is very common for ID columns where you store values sequentially for each new record like 1, 2, 3, 4, 5 and so on.<\/p>\n<p>For instance, the serial integer is:<\/p>\n<ul>\n<li>serial: stores 4 bytes and ranges from 1 to 2147483647<\/li>\n<\/ul>\n<p>Notice its range starts on 1, not on a negative number since it doesn&#8217;t make much sense to have and ID of, say, -20 for an employee, for instance.<\/p>\n<h3>Floating-point numbers<\/h3>\n<p>For floating-point numbers, in general you can choose to use <code>numeric<\/code> for most cases.<\/p>\n<p>It supports a very large number of digits and it is easy to work with when you need to deal with monetary values or simply needs a good precision.<\/p>\n<h2>Character types<\/h2>\n<p>These types are used to store strings.<\/p>\n<ul>\n<li>varchar: can store a string of the size you pre-define, and it does not pad spaces if the string to be stored is shorter than the declared length<\/li>\n<li>char: can store a string of the size you pre-define, but it pads spaces if the string to be stored is shorter than the declared length<\/li>\n<li>text: can store a string of any size, no need to predefine the number of characters<\/li>\n<\/ul>\n<h2>Boolean<\/h2>\n<p>The boolean type is by far the most simple one.<\/p>\n<p>It stores 1 byte that represents <code>true<\/code>, <code>false<\/code> or <code>null<\/code> value for when you have an unknown state.<\/p>\n<p>If you insert either of these in you database, PostgreSQL will understand them as <code>true<\/code>: true, yes, on, and 1.<\/p>\n<p>If you insert either of these in you database, PostgreSQL will understand them as <code>false<\/code>: false, no, off, and 0.<\/p>\n<h2>Date\/Time<\/h2>\n<p>You can basically store time, date, date and time simultaneously, and data\/time with time zone:<\/p>\n<ul>\n<li>timestamp: you can store the date and the time<\/li>\n<li>date: you can store only the date<\/li>\n<li>time: you can store only the time<\/li>\n<li>timestamptz: is the same as timestamp with the addition of time zone<\/li>\n<li>interval: you can store a period of time, a time interval<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Data Types Each field in a Table has a Type. A Data Type is used to specify what kind of object that particular field will store. When creating your own structures, using the right type for the right data is essential to prevent data loss and to work better with the information you have. It [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":1776,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[24,28],"tags":[29],"class_list":["post-1768","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database","category-sql","tag-sql"],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":6}},"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.3 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>SQL: Data Types<\/title>\n<meta name=\"description\" content=\"Learn about Data Types in SQL\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/renanmf.com\/sql-data-types\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL: Data Types\" \/>\n<meta property=\"og:description\" content=\"Learn about Data Types in SQL\" \/>\n<meta property=\"og:url\" content=\"https:\/\/renanmf.com\/sql-data-types\/\" \/>\n<meta property=\"og:site_name\" content=\"Renan Moura - Software Engineering\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/renanmouraf\" \/>\n<meta property=\"article:published_time\" content=\"2020-06-17T11:23:48+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-02-14T01:48:50+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/renanmf.com\/wp-content\/uploads\/2020\/06\/image-1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"630\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Renan Moura\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/renanmouraf\" \/>\n<meta name=\"twitter:site\" content=\"@renanmouraf\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Renan Moura\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/renanmf.com\\\/sql-data-types\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/renanmf.com\\\/sql-data-types\\\/\"},\"author\":{\"name\":\"Renan Moura\",\"@id\":\"https:\\\/\\\/renanmf.com\\\/#\\\/schema\\\/person\\\/1a6fd46256318d200c1c8a867448e5a8\"},\"headline\":\"SQL: Data Types\",\"datePublished\":\"2020-06-17T11:23:48+00:00\",\"dateModified\":\"2021-02-14T01:48:50+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/renanmf.com\\\/sql-data-types\\\/\"},\"wordCount\":644,\"publisher\":{\"@id\":\"https:\\\/\\\/renanmf.com\\\/#organization\"},\"image\":{\"@id\":\"https:\\\/\\\/renanmf.com\\\/sql-data-types\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/renanmf.com\\\/wp-content\\\/uploads\\\/2020\\\/06\\\/image-1.png\",\"keywords\":[\"sql\"],\"articleSection\":[\"Database\",\"SQL\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/renanmf.com\\\/sql-data-types\\\/\",\"url\":\"https:\\\/\\\/renanmf.com\\\/sql-data-types\\\/\",\"name\":\"SQL: Data Types\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/renanmf.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/renanmf.com\\\/sql-data-types\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/renanmf.com\\\/sql-data-types\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/renanmf.com\\\/wp-content\\\/uploads\\\/2020\\\/06\\\/image-1.png\",\"datePublished\":\"2020-06-17T11:23:48+00:00\",\"dateModified\":\"2021-02-14T01:48:50+00:00\",\"description\":\"Learn about Data Types in SQL\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/renanmf.com\\\/sql-data-types\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/renanmf.com\\\/sql-data-types\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/renanmf.com\\\/sql-data-types\\\/#primaryimage\",\"url\":\"https:\\\/\\\/renanmf.com\\\/wp-content\\\/uploads\\\/2020\\\/06\\\/image-1.png\",\"contentUrl\":\"https:\\\/\\\/renanmf.com\\\/wp-content\\\/uploads\\\/2020\\\/06\\\/image-1.png\",\"width\":1200,\"height\":630,\"caption\":\"sql data types\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/renanmf.com\\\/sql-data-types\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"In\u00edcio\",\"item\":\"https:\\\/\\\/renanmf.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL: Data Types\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/renanmf.com\\\/#website\",\"url\":\"https:\\\/\\\/renanmf.com\\\/\",\"name\":\"Renan Moura - Software Engineering\",\"description\":\"Software development, machine learning\",\"publisher\":{\"@id\":\"https:\\\/\\\/renanmf.com\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/renanmf.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/renanmf.com\\\/#organization\",\"name\":\"Renan Moura\",\"url\":\"https:\\\/\\\/renanmf.com\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/renanmf.com\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/renanmf.com\\\/wp-content\\\/uploads\\\/2020\\\/03\\\/me-e1583179172701.jpeg\",\"contentUrl\":\"https:\\\/\\\/renanmf.com\\\/wp-content\\\/uploads\\\/2020\\\/03\\\/me-e1583179172701.jpeg\",\"width\":120,\"height\":120,\"caption\":\"Renan Moura\"},\"image\":{\"@id\":\"https:\\\/\\\/renanmf.com\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/renanmouraf\",\"https:\\\/\\\/x.com\\\/renanmouraf\",\"https:\\\/\\\/instagram.com\\\/renanmouraf\",\"https:\\\/\\\/www.linkedin.com\\\/in\\\/renanmouraf\\\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/renanmf.com\\\/#\\\/schema\\\/person\\\/1a6fd46256318d200c1c8a867448e5a8\",\"name\":\"Renan Moura\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/efb78bdd04aa5627f80307aed5a9b31989d901c536d1e014a29a3c3591338af8?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/efb78bdd04aa5627f80307aed5a9b31989d901c536d1e014a29a3c3591338af8?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/efb78bdd04aa5627f80307aed5a9b31989d901c536d1e014a29a3c3591338af8?s=96&d=mm&r=g\",\"caption\":\"Renan Moura\"},\"description\":\"I'm a Software Engineer working in the industry for a decade now. I like to solve problems with as little code as possible. I\u2019m interested in solving all sorts of problems with technology in creative and innovative ways. From everyday shell scripts to machine learning models. I write about Software Development, Machine Learning, and Career in tech.\",\"sameAs\":[\"https:\\\/\\\/renanmf.com\\\/\",\"https:\\\/\\\/www.instagram.com\\\/renanmouraf\\\/\",\"https:\\\/\\\/www.linkedin.com\\\/in\\\/renanmouraf\\\/\",\"https:\\\/\\\/x.com\\\/https:\\\/\\\/twitter.com\\\/renanmouraf\"],\"url\":\"https:\\\/\\\/renanmf.com\\\/author\\\/renanmoura\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL: Data Types","description":"Learn about Data Types in SQL","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/renanmf.com\/sql-data-types\/","og_locale":"en_US","og_type":"article","og_title":"SQL: Data Types","og_description":"Learn about Data Types in SQL","og_url":"https:\/\/renanmf.com\/sql-data-types\/","og_site_name":"Renan Moura - Software Engineering","article_publisher":"https:\/\/www.facebook.com\/renanmouraf","article_published_time":"2020-06-17T11:23:48+00:00","article_modified_time":"2021-02-14T01:48:50+00:00","og_image":[{"width":1200,"height":630,"url":"https:\/\/renanmf.com\/wp-content\/uploads\/2020\/06\/image-1.png","type":"image\/png"}],"author":"Renan Moura","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/renanmouraf","twitter_site":"@renanmouraf","twitter_misc":{"Written by":"Renan Moura","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/renanmf.com\/sql-data-types\/#article","isPartOf":{"@id":"https:\/\/renanmf.com\/sql-data-types\/"},"author":{"name":"Renan Moura","@id":"https:\/\/renanmf.com\/#\/schema\/person\/1a6fd46256318d200c1c8a867448e5a8"},"headline":"SQL: Data Types","datePublished":"2020-06-17T11:23:48+00:00","dateModified":"2021-02-14T01:48:50+00:00","mainEntityOfPage":{"@id":"https:\/\/renanmf.com\/sql-data-types\/"},"wordCount":644,"publisher":{"@id":"https:\/\/renanmf.com\/#organization"},"image":{"@id":"https:\/\/renanmf.com\/sql-data-types\/#primaryimage"},"thumbnailUrl":"https:\/\/renanmf.com\/wp-content\/uploads\/2020\/06\/image-1.png","keywords":["sql"],"articleSection":["Database","SQL"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/renanmf.com\/sql-data-types\/","url":"https:\/\/renanmf.com\/sql-data-types\/","name":"SQL: Data Types","isPartOf":{"@id":"https:\/\/renanmf.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/renanmf.com\/sql-data-types\/#primaryimage"},"image":{"@id":"https:\/\/renanmf.com\/sql-data-types\/#primaryimage"},"thumbnailUrl":"https:\/\/renanmf.com\/wp-content\/uploads\/2020\/06\/image-1.png","datePublished":"2020-06-17T11:23:48+00:00","dateModified":"2021-02-14T01:48:50+00:00","description":"Learn about Data Types in SQL","breadcrumb":{"@id":"https:\/\/renanmf.com\/sql-data-types\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/renanmf.com\/sql-data-types\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/renanmf.com\/sql-data-types\/#primaryimage","url":"https:\/\/renanmf.com\/wp-content\/uploads\/2020\/06\/image-1.png","contentUrl":"https:\/\/renanmf.com\/wp-content\/uploads\/2020\/06\/image-1.png","width":1200,"height":630,"caption":"sql data types"},{"@type":"BreadcrumbList","@id":"https:\/\/renanmf.com\/sql-data-types\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"In\u00edcio","item":"https:\/\/renanmf.com\/"},{"@type":"ListItem","position":2,"name":"SQL: Data Types"}]},{"@type":"WebSite","@id":"https:\/\/renanmf.com\/#website","url":"https:\/\/renanmf.com\/","name":"Renan Moura - Software Engineering","description":"Software development, machine learning","publisher":{"@id":"https:\/\/renanmf.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/renanmf.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/renanmf.com\/#organization","name":"Renan Moura","url":"https:\/\/renanmf.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/renanmf.com\/#\/schema\/logo\/image\/","url":"https:\/\/renanmf.com\/wp-content\/uploads\/2020\/03\/me-e1583179172701.jpeg","contentUrl":"https:\/\/renanmf.com\/wp-content\/uploads\/2020\/03\/me-e1583179172701.jpeg","width":120,"height":120,"caption":"Renan Moura"},"image":{"@id":"https:\/\/renanmf.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/renanmouraf","https:\/\/x.com\/renanmouraf","https:\/\/instagram.com\/renanmouraf","https:\/\/www.linkedin.com\/in\/renanmouraf\/"]},{"@type":"Person","@id":"https:\/\/renanmf.com\/#\/schema\/person\/1a6fd46256318d200c1c8a867448e5a8","name":"Renan Moura","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/efb78bdd04aa5627f80307aed5a9b31989d901c536d1e014a29a3c3591338af8?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/efb78bdd04aa5627f80307aed5a9b31989d901c536d1e014a29a3c3591338af8?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/efb78bdd04aa5627f80307aed5a9b31989d901c536d1e014a29a3c3591338af8?s=96&d=mm&r=g","caption":"Renan Moura"},"description":"I'm a Software Engineer working in the industry for a decade now. I like to solve problems with as little code as possible. I\u2019m interested in solving all sorts of problems with technology in creative and innovative ways. From everyday shell scripts to machine learning models. I write about Software Development, Machine Learning, and Career in tech.","sameAs":["https:\/\/renanmf.com\/","https:\/\/www.instagram.com\/renanmouraf\/","https:\/\/www.linkedin.com\/in\/renanmouraf\/","https:\/\/x.com\/https:\/\/twitter.com\/renanmouraf"],"url":"https:\/\/renanmf.com\/author\/renanmoura\/"}]}},"_links":{"self":[{"href":"https:\/\/renanmf.com\/wp-json\/wp\/v2\/posts\/1768","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/renanmf.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/renanmf.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/renanmf.com\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/renanmf.com\/wp-json\/wp\/v2\/comments?post=1768"}],"version-history":[{"count":13,"href":"https:\/\/renanmf.com\/wp-json\/wp\/v2\/posts\/1768\/revisions"}],"predecessor-version":[{"id":3002,"href":"https:\/\/renanmf.com\/wp-json\/wp\/v2\/posts\/1768\/revisions\/3002"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/renanmf.com\/wp-json\/wp\/v2\/media\/1776"}],"wp:attachment":[{"href":"https:\/\/renanmf.com\/wp-json\/wp\/v2\/media?parent=1768"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/renanmf.com\/wp-json\/wp\/v2\/categories?post=1768"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/renanmf.com\/wp-json\/wp\/v2\/tags?post=1768"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}