This course will provide you an understanding of how to use built-in PostgreSQL functions in your SQL queries to manipulate different types of data including strings, character, numeric and date/time. We'll travel back to a time where Blockbuster video stores were on every corner and if you wanted to watch a movie, you actually had to leave your house to rent a DVD! You'll also get an introduction into the robust full-text search capabilities which provides a powerful tool for indexing and matching keywords in a PostgreSQL document. And finally, you'll learn how to extend these features by using PostgreSQL extensions.
Overview of Common Data TypesFree
Learn about the properties and characteristics of common data types including strings, numerics and arrays and how to retrieve information about your database.Welcome!50 xpText data types50 xpGetting information about your database100 xpDetermining data types100 xpDate and time data types50 xpProperties of date and time data types50 xpInterval data types100 xpWorking with ARRAYs50 xpAccessing data in an ARRAY100 xpSearching an ARRAY with ANY100 xpSearching an ARRAY with @>100 xp
Working with DATE/TIME Functions and Operators
Explore how to manipulate and query date and time objects including how to use the current timestamp in your queries, extract subfields from existing date and time fields and what to expect when you perform date and time arithmetic.Overview of basic arithmetic operators50 xpAdding and subtracting date and time values100 xpINTERVAL arithmetic100 xpCalculating the expected return date100 xpFunctions for retrieving current date/time50 xpCurrent timestamp functions50 xpWorking with the current date and time100 xpManipulating the current date and time100 xpExtracting and transforming date/ time data50 xpUsing EXTRACT100 xpUsing DATE_TRUNC100 xpPutting it all together100 xp
Parsing and Manipulating Text
Learn how to manipulate string and text data by transforming case, parsing and truncating text and extracting substrings from larger strings.Reformatting string and character data50 xpConcatenating strings100 xpChanging the case of string data100 xpReplacing string data100 xpParsing string and character data50 xpDetermining the length of strings100 xpTruncating strings100 xpExtracting substrings from text data100 xpCombining functions for string manipulation100 xpTruncating and padding string data50 xpPadding100 xpThe TRIM function100 xpPutting it all together100 xp
Full-text Search and PostgresSQL Extensions
An introduction into some more advanced capabilities of PostgreSQL like full-text search and extensions.Introduction to full-text search50 xpA review of the LIKE operator100 xpWhat is a tsvector?100 xpBasic full-text search100 xpExtending PostgreSQL50 xpUser-defined data types100 xpGetting info about user-defined data types100 xpUser-defined functions in Sakila100 xpIntro to PostgreSQL extensions50 xpEnabling extensions100 xpMeasuring similarity between two strings100 xpLevenshtein distance examples100 xpPutting it all together100 xpWrap Up50 xp
PrerequisitesData Manipulation in SQL
Brian PiccoloSee More
Sr. Director, Digital Strategy
A results-driven digital leader with 15+ years of experience as an entrepreneur, agency executive and senior manager at a Fortune 100 company. Unique and diverse background combining hands-on technology and software development experience with product management and business acumen.