A Postgresion of Data Types

jamesmoore
Jul 25, 2017 · 2 min read

Welcome SQL Devs,

As part of your onboarding into our dev environment, a basic familiraity with our baseline database will be necessary. Our primary database is PostgreSQL and it has some interesting features implemented on top of the SQL standard.

First off, there is an implementation of all the standard data types: bigint, bit, bit varying, boolean, char, character varying, character, varchar, date, double precision, integer, interval, numeric, decimal, real, smallint, time, timestamp, and xml. Of those, we commonly use a small subset:

boolean — works as you would expect, true or false

varchar(n) — variable character with a length up to n. Used to set field lengths and common for strings.

integer and decimal — standard data types for integers and floats.

There are also several other PostgreSQL specific implementations:

json — datatype specifically for storing json data. As long as we’re not supporting legacy applications that still use xml, json is much easier for our other developers to manipulate.

text — this as an infinite length text store, great for adding things like blog articles and this entire entry.

money — nice implementation of storing money values when we make all of it.

uuid — universal unique ID, e.g. a great data type for assigning transaction ID’s automatically and dozens of other uses.

The last datatype that we should talk about in PostgreSQL is the one that we have not created yet. Yes, PostgreSQL allows you to custom create data types with the CREATE TYPE command. Implementation is beyond the scope of your indoc today, but you should take note of the capability. Custom data types can be a powerful addition to our database for a myriad of purposes from speeding up our queries and inserts to validating data inputs for authenticity and security.

Now that you’ve finished this, your senior database administrator Nakaz will cover implementing custom data types with some live code demos.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade