{"id":104381,"date":"2024-10-31T17:32:35","date_gmt":"2024-10-31T17:32:35","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?post_type=collection&p=104381"},"modified":"2026-03-18T11:42:11","modified_gmt":"2026-03-18T11:42:11","slug":"series-learning-postgresql-with-grant","status":"publish","type":"collection","link":"https:\/\/www.red-gate.com\/simple-talk\/collections\/series-learning-postgresql-with-grant\/","title":{"rendered":"Learning PostgreSQL with Grant"},"content":{"rendered":"\n

Welcome to the ongoing series of posts by Grant Fritchey<\/a> as he takes his vast knowledge of SQL Server and applies it to learning PostgreSQL. And of course, as every great writer does…shares it with you so you can learn from his triumphs and mistakes.<\/p>\n\n\n\n

\n
\n
\n
\n
\n
\n
\n
\n \n\t\"\"\t<\/a> <\/div>\n
\n \n
\n \n \n \"Image\n Grant Fritchey<\/span>\n <\/a>\n\n \n in<\/span>\n \n PostgreSQL <\/a>\n <\/span>\n<\/div>\n\n
\n

\n How User-Defined Types work in PostgreSQL: a complete guide <\/h1>\n

\n Learn PostgreSQL User-Defined Types (UDTs) with clear examples of DOMAIN, COMPOSITE, ENUM, and RANGE types, plus practical SQL code for… <\/p>\n <\/div>\n

\n
\n
\n
\n \n\t\"\"\t<\/a> <\/div>\n
\n \n
\n \n \n \"Image\n Grant Fritchey<\/span>\n <\/a>\n\n \n in<\/span>\n \n PostgreSQL <\/a>\n <\/span>\n<\/div>\n\n
\n

\n Learning PostgreSQL with Grant: Data Storage <\/h1>\n

\n Learn how PostgreSQL stores data on disk, including page architecture, TOAST storage, file structure, tablespaces, and I\/O performance optimization strategies.… <\/p>\n <\/div>\n

\n
\n
\n
\n \n\t\"\"\t<\/a> <\/div>\n
\n \n
\n \n \n \"Image\n Grant Fritchey<\/span>\n <\/a>\n\n \n in<\/span>\n \n PostgreSQL <\/a>\n <\/span>\n<\/div>\n\n
\n

\n Multi-Version Concurrency Control (MVCC) in PostgreSQL: Learning PostgreSQL with Grant <\/h1>\n

\n It\u2019s a tale as old as time. You want to read data. Your mate wants to write data. You\u2019re stepping… <\/p>\n <\/div>\n

\n
\n
\n
\n \n\t\"\"\t<\/a> <\/div>\n
\n \n
\n \n \n \"Image\n Grant Fritchey<\/span>\n <\/a>\n\n \n in<\/span>\n \n PostgreSQL <\/a>\n <\/span>\n<\/div>\n\n
\n

\n PostgreSQL Backups to Support Point In Time Recovery: Learning PostgreSQL with Grant <\/h1>\n

\n The focus of your backup strategy should not be backups. Instead, you should be focused on restoring databases, because, after… <\/p>\n <\/div>\n

\n
\n
\n
\n \n\t\t\t\t\t\t\t\t\t\t\t\t\tPostgreSQL<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\"Simple-Talk\n\t\t\t<\/a> <\/div>\n
\n \n
\n \n \n \"Image\n Grant Fritchey<\/span>\n <\/a>\n\n \n in<\/span>\n \n PostgreSQL <\/a>\n <\/span>\n<\/div>\n\n
\n

\n Functions and Procedures: Learning PostgreSQL with Grant <\/h1>\n

\n One of the most useful constructs in SQL Server is the stored procedure. It gives you a way to do… <\/p>\n <\/div>\n

\n
\n
\n
\n \n\t\t\t\t\t\t\t\t\t\t\t\t\tPostgreSQL<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\"Simple-Talk\n\t\t\t<\/a> <\/div>\n
\n \n
\n \n \n \"Image\n Grant Fritchey<\/span>\n <\/a>\n\n \n in<\/span>\n \n PostgreSQL <\/a>\n <\/span>\n<\/div>\n\n
\n

\n Introducing VACUUM: Learning PostgreSQL With Grant <\/h1>\n

\n While there are many features within PostgreSQL that are really similar to those within SQL Server, there are some that… <\/p>\n <\/div>\n

\n
\n
\n
\n \n\t\t\t\t\t\t\t\t\t\t\t\t\tPostgreSQL<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\"Simple-Talk\n\t\t\t<\/a> <\/div>\n
\n \n
\n \n \n \"Image\n Grant Fritchey<\/span>\n <\/a>\n\n \n in<\/span>\n \n PostgreSQL <\/a>\n <\/span>\n<\/div>\n\n
\n

\n Querying PostgreSQL: Learning PostgreSQL with Grant <\/h1>\n

\n Writing queries to retrieve the data from a database is probably the single most common task when it comes to… <\/p>\n <\/div>\n

\n
\n
\n
\n \n\t\t\t\t\t\t\t\t\t\t\t\t\tPostgreSQL<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\"Simple-Talk\n\t\t\t<\/a> <\/div>\n
\n \n
\n \n \n \"Image\n Grant Fritchey<\/span>\n <\/a>\n\n \n in<\/span>\n \n PostgreSQL <\/a>\n <\/span>\n<\/div>\n\n
\n

\n Manipulating Data In PostgreSQL: Learning PostgreSQL with Grant <\/h1>\n

\n So far in the series I\u2019ve shown how to create databases, tables, constraints, indexes and schema. Now, it\u2019s time to… <\/p>\n <\/div>\n

\n
\n
\n
\n \n\t\t\t\t\t\t\t\t\t\t\t\t\tPostgreSQL<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\"Simple-Talk\n\t\t\t<\/a> <\/div>\n
\n \n
\n \n \n \"Image\n Grant Fritchey<\/span>\n <\/a>\n\n \n in<\/span>\n \n PostgreSQL <\/a>\n <\/span>\n<\/div>\n\n
\n

\n PostgreSQL Schema: Learning PostgreSQL with Grant <\/h1>\n

\n An important aspect of building and managing a database is organizing the objects within your database. You may have tables… <\/p>\n <\/div>\n

\n
\n
\n
\n \n\t\t\t\t\t\t\t\t\t\t\t\t\tPostgreSQL<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\"Simple-Talk\n\t\t\t<\/a> <\/div>\n
\n \n
\n \n \n \"Image\n Grant Fritchey<\/span>\n <\/a>\n\n \n in<\/span>\n \n PostgreSQL <\/a>\n <\/span>\n<\/div>\n\n
\n

\n PostgreSQL Constraints: Learning PostgreSQL with Grant <\/h1>\n

\n One of the most important aspects of data management is the ability to ensure that the data in your database… <\/p>\n <\/div>\n

\n
\n
\n
\n \n\t\t\t\t\t\t\t\t\t\t\t\t\tPostgreSQL<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\"Simple-Talk\n\t\t\t<\/a> <\/div>\n
\n \n
\n \n \n \"Image\n Grant Fritchey<\/span>\n <\/a>\n\n \n in<\/span>\n \n PostgreSQL <\/a>\n <\/span>\n<\/div>\n\n
\n

\n Index Types in PostgreSQL: Learning PostgreSQL with Grant <\/h1>\n

\n As with any other relational data management system (RDBMS), PostgreSQL uses indexes as a mechanism to improve data access. PostgreSQL… <\/p>\n <\/div>\n

\n
\n
\n
\n \n\t\t\t\t\t\t\t\t\t\t\t\t\tPostgreSQL<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\"Simple-Talk\n\t\t\t<\/a> <\/div>\n
\n \n
\n \n \n \"Image\n Grant Fritchey<\/span>\n <\/a>\n\n \n in<\/span>\n \n PostgreSQL <\/a>\n <\/span>\n<\/div>\n\n
\n

\n Data Types in PostgreSQL: Learning PostgreSQL with Grant <\/h1>\n

\n PostgreSQL has an unusually large number of data types. Grant Fritchey reviews each data type and discusses the good and… <\/p>\n <\/div>\n

\n
\n
\n
\n \n\t\t\t\t\t\t\t\t\t\t\t\t\tPostgreSQL<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\"Simple-Talk\n\t\t\t<\/a> <\/div>\n
\n \n
\n \n \n \"Image\n Grant Fritchey<\/span>\n <\/a>\n\n \n in<\/span>\n \n PostgreSQL <\/a>\n <\/span>\n<\/div>\n\n
\n

\n How to back up and restore in PostgreSQL: Learning PostgreSQL with Grant <\/h1>\n

\n You can perform PostgreSQL backups with the pg_dump command. In this article, Grant Fritchey explains how to back up and… <\/p>\n <\/div>\n

\n
\n
\n
\n \n\t\t\t\t\t\t\t\t\t\t\t\t\tPostgreSQL<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\"Simple-Talk\n\t\t\t<\/a> <\/div>\n
\n \n
\n \n \n \"Image\n Grant Fritchey<\/span>\n <\/a>\n\n \n in<\/span>\n \n PostgreSQL <\/a>\n <\/span>\n<\/div>\n\n
\n

\n Creating a Database and Tables in PostgreSQL: Learning PostgreSQL with Grant <\/h1>\n

\n The syntax for creating databases and tables in PostgreSQL is similar to other RDBMS. In this article, Grant Fritchey shows… <\/p>\n <\/div>\n

\n
\n
\n
\n \n\t\t\t\t\t\t\t\t\t\t\t\t\tFeatured<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\"Simple-Talk\n\t\t\t<\/a> <\/div>\n
\n \n
\n \n \n \"Image\n Grant Fritchey<\/span>\n <\/a>\n\n \n in<\/span>\n \n Featured <\/a>\n <\/span>\n<\/div>\n\n
\n

\n Connecting to PostgreSQL: Learning PostgreSQL with Grant <\/h1>\n

\n Database professionals often work with more than one database platform. Grant Fritchey explains setting up and connecting to PostgreSQL database.… <\/p>\n <\/div>\n

\n