PostgreSQL Tutorials: From Basic to Advanced

1 How to set up PostgreSQL and pgAdmin on Windows

2 How to set up PostgreSQL and pgAdmin on Mac

3 How to set up and configure PostgreSQL on Ubuntu

4 PostgreSQL vs MySQL: Which is Better for Your Database Needs?

5 PostgreSQL vs MongoDB: Which is for scale and speed?

6 How to Add and Delete Users in PostgreSQL

7 PostgreSQL: 2 Ways to Reset Root Password on Windows

8 PostgreSQL: 3 Ways to Reset Root Password on Mac

9 PostgreSQL: Ways to Reset Root Password on Ubuntu

10 3 Ways to See all Users and Permissions in PostgreSQL

11 5 Ways to Drop a Database in PostgreSQL

12 3 Ways to See All Databases in PostgreSQL

13 2 Ways to Import and Export Databases in PostgreSQL

14 How to See the Size of a Database in PostgreSQL

15 How to Change User Password in PostgreSQL

16 PostgreSQL: How to Grant/Revoke Permissions to a User

17 3 Ways to Auto-Backup a PostgreSQL Database on Ubuntu

18 PostgreSQL: 4 Ways to Auto-Backup a Database on Mac and Windows

19 How to Migrate Data from MySQL to PostgreSQL

20 PostgreSQL: 3 Ways to Migrate Data to a New Server

21 3 Ways to Create a Table in PostgreSQL if It Does Not Exist

22 Ways to Delete a Table in PostgreSQL

23 How to Alter/Change a Table in PostgreSQL (5 Examples)

24 How to Rename a Column in PostgreSQL

25 How to Add/Remove a Column in PostgreSQL Table

26 How to Drop a Table in PostgreSQL if It Exists

27 How to See All Tables in PostgreSQL Database

28 PostgreSQL: How to Set Index on a Table Column

29 PostgreSQL: How to Set Unique Constraint on a Table Column

30 How to Set Unsigned Integer in PostgreSQL Table Column

31 How to insert a new record in PostgreSQL

32 How to Bulk Insert Records in PostgreSQL

33 PostgreSQL: How to Insert Values with Quotes (')

34 PostgreSQL: How to Set Default Value for a Column

35 PostgreSQL: created_at and updated_at columns

36 PostgreSQL: Timestamp vs. Timestamp with Time Zone

37 PostgreSQL: Add 'not null' constraint to existing column

38 PostgreSQL: Use 'Returning' with 'Insert' statement to get information about inserted row

39 PostgreSQL: How to Update a Row and Return Updated Record

40 PostgreSQL: How to Bulk Update/Delete Rows with IDs

41 PostgreSQL: How to Delete a Row and Return Deleted Record

42 PostgreSQL Upsert: Update if Exists, Insert if Not

43 How to select all rows from a table in PostgreSQL

44 PostgreSQL: Select a single row by ID/Primary Key

45 PostgreSQL: Using OFFSET and LIMIT for Pagination

46 4 Ways to Select Distinct Rows in PostgreSQL

47 3 Ways to select a single random row in PostgreSQL

48 2 Ways to Select N Random Rows in PostgreSQL

49 How to use the WHERE clause in PostgreSQL

50 How to Add Aliases to Columns in PostgreSQL

51 PostgreSQL ORDER BY: Ascending and Descending Sorting

52 Using 'IN' and 'NOT IN' in PostgreSQL

53 Using 'FETCH FIRST' and 'OFFSET' in PostgreSQL

54 Using 'BETWEEN' and 'NOT BETWEEN' in PostgreSQL

55 Using 'LIKE' and 'ILIKE' in PostgreSQL

56 Using 'IS NULL' and 'IS NOT NULL' in PostgreSQL

57 PostgreSQL: How to use concatenation operator (||) to combine strings

58 PostgreSQL: Using CONCAT and CONCAT_WS Functions to Combine Strings

59 JSON data type in PostgreSQL: How to store JSON data

60 PostgreSQL: Saving categories and subcategories in one table

61 CHAR, VARCHAR, and TEXT data types in PostgreSQL: What's the difference?

62 PostgreSQL: 4 ways to store comments and nested comments

63 PostgreSQL: How to reset the auto-increment value of a column

64 Boolean data type in PostgreSQL: How to store true/false values

65 NUMERIC and DECIMAL data types in PostgreSQL: Explained by examples

66 A deep dive into PostgreSQL DATE and TIME data types

67 TIMESTAMP and TIMESTAMPTZ data types in PostgreSQL (7 examples)

68 How to generate and use UUIDs in PostgreSQL

69 Array data type in PostgreSQL: Store multiple values in a single column

70 hstore and network address data types in PostgreSQL

71 PostgreSQL: Using 'CAST' and '::' for data conversion

72 PostgreSQL: Implicit and Explicit Data Type Conversion

73 PostgreSQL: How to Drop FOREIGN KEY Constraints

74 Understanding PostgreSQL: to_char and to_number Conversion Functions

75 Understanding PostgreSQL: to_date and to_timestamp Functions

76 How to Use ENUM in PostgreSQL

77 PostgreSQL: ENUM with a default value and a custom schema

78 PostgreSQL: Using 'CREATE DOMAIN' to create custom data types

79 PostgreSQL: Using 'CREATE TYPE' to create custom data types

80 PostgreSQL: Using 'ALTER TYPE' to change a custom data type definition

81 Custom Collations and Types in PostgreSQL: The Complete Guide

82 Setting FOREIGN KEY constraint in PostgreSQL

83 Using CHECK constraint in PostgreSQL: A complete guide

84 PostgreSQL: Using a Table without a Primary Key

85 PostgreSQL: Table with Text Primary Key

86 How to Drop a Constraint in PostgreSQL

87 PostgreSQL: How to add/update foreign key in an existing table

88 PostgreSQL: Create a sequence, advance a sequence, get current value, set value

89 PostgreSQL: Restart, rename a sequence, and use pgAdmin to alter a sequence

90 PostgreSQL: Sequence with START WITH, INCREMENT, MINVALUE and MAXVALUE

91 PostgreSQL: Creating a sequence using a specific data type

92 PostgreSQL: Descending sequence and CYCLE sequence

93 PostgreSQL: How to Delete a Sequence

94 PostgreSQL: How to attach a sequence to a table column

95 PostgreSQL: How to list all sequences of a database

96 PostgreSQL: How to share a sequence between tables

97 PostgreSQL: How to create an alphanumeric sequence

98 PostgreSQL String Functions: UPPER, LOWER, and INITCAP

99 Using LEFT and RIGHT String Functions in PostgreSQL

100 Using REVERSE String Function in PostgreSQL

101 Using SPLIT_PART String Function in PostgreSQL

102 PostgreSQL: TRIM, BTRIM, LTRIM, and RTRIM Functions

103 Mastering LPAD and RPAD String Functions in PostgreSQL

104 Using the LENGTH function in PostgreSQL

105 PostgreSQL: Selecting Rows Randomly Based on Weight (3 Ways)

106 PostgreSQL: Ways to Remove Old Indices from a Table

107 Working with Temporary Tables in PostgreSQL

108 Using the POSITION function in PostgreSQL

109 REINDEX in PostgreSQL: A Complete Guide

110 PostgreSQL: How to Combine Data from 2 Tables (4 examples)

111 Using the STRPOS Function in PostgreSQL

112 Using the SUBSTRING function in PostgreSQL

113 Using the REPEAT function in PostgreSQL

114 Using the REPLACE Function in PostgreSQL

115 Using Regular Expressions in PostgreSQL: Basic to Advanced Examples

116 PostgreSQL: Counting Results Using the COUNT Function

117 PostgreSQL COUNT(*), COUNT(column_name), and COUNT(1): Which is fastest?

118 PostgreSQL aggregation: SUM, AVG, MIN, and MAX

119 Understanding PostgreSQL: GREATEST and LEAST Functions

120 PostgreSQL: Combining columns using math operators

121 PostgreSQL: How to store images in database (and why you shouldn’t)

122 Using cursor-based pagination in PostgreSQL: Step-by-Step Guide

123 3 Ways to See the Structure of a Table in PostgreSQL

124 Time of day formats and input/output in PostgreSQL

125 Strings to Dates conversion in PostgreSQL

126 Using TO_TIMESTAMP Function in PostgreSQL: A Complete Guide

127 How to format dates in PostgreSQL

128 Explore Date Construction Functions in PostgreSQL

129 Using MAKE_INTERVAL Function in PostgreSQL: A Comprehensive Guide

130 Using MAKE_TIMESTAMPTZ Function in PostgreSQL

131 Explore Date Value Extractors in PostgreSQL

132 How to use math operators with dates in PostgreSQL

133 Using OVERLAPS operator with dates in PostgreSQL

134 Understanding the AGE Function in PostgreSQL

135 PostgreSQL: Using CURRENT_DATE and CURRENT_TIME Functions

136 PostgreSQL: Date accuracy with EPOCH

137 PostgreSQL: Checking Current Timezone

138 PostgreSQL: Using NOW() and CURRENT_TIMESTAMP() Functions

139 PostgreSQL: Using date_part and date_trunc Functions

140 PostgreSQL GROUP BY: How to Group Rows by One or Multiple Columns

141 PostgreSQL: Group rows by day, week, month, and year

142 PostgreSQL Aggregation in Group By: SUM, AVG, MIN, MAX, and COUNT

143 Using HAVING clause with GROUP BY in PostgreSQL

144 PostgreSQL: Select rows between two dates/timestamps

145 PostgreSQL: Using LEFT JOIN to join multiple tables

146 PostgreSQL: Using RIGHT JOIN to join multiple tables

147 PostgreSQL: Using INNER JOIN with WHERE clause

148 Using FULL JOIN in PostgreSQL (basic and advanced examples)

149 Joining more than two tables in PostgreSQL

150 Self Joins in PostgreSQL: How to Join a Table to Itself

151 CROSS Joins in PostgreSQL: How to Join Two Tables Without a Common Column

152 Append tables in PostgreSQL: How to combine multiple tables into one

153 PostgreSQL: Combine results with UNION, INTERSECT, and EXCEPT

154 PostgreSQL Schema Operations: Add, Alter, and Delete

155 Schema Hierarchy in PostgreSQL: Explained

156 3 Ways to See Error Logs in PostgreSQL

157 How to grant privileges to a user in PostgreSQL

158 PostgreSQL: Using SELECT INTO to create a new table from an existing table

159 PostgreSQL: Import/Export CSV files using psql

160 How to see the size of a table in PostgreSQL (7 Approaches)

161 PostgreSQL: Implementing fixed-size tables with triggers

162 PostgreSQL: How to add a calculated column in SELECT query

163 PostgreSQL: How to view privileges info across all databases

164 PostgreSQL: How to check live and dead rows in tables

165 See Postgres command history with psql

166 PostgreSQL: Common psql commands you should never forget

167 How to create and use a cursor in PostgreSQL

168 How to create a trigger in PostgreSQL

169 How to audit data with triggers in PostgreSQL

170 How to view trigger variables in PostgreSQL

171 PostgreSQL TRIGGER: Modify data at INSERT, UPDATE, and DELETE

172 Working with Conditional Statements in PostgreSQL

173 Using Conditional Triggers in PostgreSQL

174 How to drop a trigger in PostgreSQL

175 Understanding Table Partitioning in PostgreSQL

176 PostgreSQL: Full text search with tsvector and tsquery

177 PostgreSQL: Ranking rows with RANK, DENSE_RANK, and ROW_NUMBER

178 PostgreSQL: REGEXP_MATCHES, REGEXP_REPLACE, REGEXP_SPLIT_TO_ARRAY, REGEXP_SPLIT_TO_TABLE

179 PostgreSQL SIMILAR TO operator

180 PostgreSQL: Convert Text Into Structured Data

181 PostgreSQL ROLLUP and CUBE: Grouping Sets

182 Using WINDOW and ROWS BETWEEN in PostgreSQL

183 Using LEAD and LAG Functions in PostgreSQL

184 PostgreSQL: FIRST_VALUE, LAST_VALUE, and NTH_VALUE

185 PostgreSQL: Using 'SELECT' without 'FROM'

186 PostgreSQL: Order entries in UNION without using ORDER BY

187 PostgreSQL: Using Subqueries with JOINs

188 Checking Index Size in PostgreSQL

189 How to Drop an Index in PostgreSQL

190 PostgreSQL: Using json_agg to aggregate data

191 PostgreSQL: Update and Delete JSON Data

192 PostgreSQL: JSON and JSONB data types

193 PostgreSQL: Generate JSON from a table

194 PostgreSQL: Build a JSON array

195 PostgreSQL: Extracting data from JSON documents

196 Perform JSON search in PostgreSQL

197 PostgreSQL: Indexing on JSONB

198 Understanding PostgreSQL Array Search Functions

199 PostgreSQL: Array comparison with IN, ALL, ANY, and SOME

200 PostgreSQL: Using Arrays in Tables

201 PostgreSQL: Insert/Query/Update/Delete array data

202 Using multi-dimensional arrays in PostgreSQL

203 Adding a Data Validation Check in PostgreSQL

204 PostgreSQL: Selecting the first row in each GROUP BY group

205 PostgreSQL Crosstab Query: Display data vertically

206 Best practices to name tables and columns in PostgreSQL

207 PostgreSQL: How to select distinct on multiple columns

208 PostgreSQL: ASC sorting, but NULL values first/last

209 How to Select the NTH Row in PostgreSQL

210 How to delete duplicate rows in PostgreSQL

211 PostgreSQL: Efficiently count rows in an extremely large table

212 How to upgrade PostgreSQL to the latest version on Ubuntu

213 How to Implement Caching in PostgreSQL

214 How to Use Loops in PostgreSQL (with Examples)

215 How to Run PostgreSQL on a Custom Port

216 Overriding Sequence Ownership in PostgreSQL

217 How to select specific columns in PostgreSQL

218 How to Temporarily Lock a Sequence in PostgreSQL

219 PostgreSQL: Can a table contain 10 million rows?

220 PostgreSQL: What is the max number of columns in a table?

221 PostgreSQL: Deleting orphan rows in one-to-many relationship

222 Understanding Hash Indexes in PostgreSQL

223 PostgreSQL GiST (Generalized Search Tree) Indexes: Explained with Examples

224 Exploring GIN (Generalized Inverted Indexes) in PostgreSQL (with Examples)

225 PostgreSQL: Making Use of BRIN (Block Range Indexes)

226 PostgreSQL SP-GiST (Space-Partitioned Generalized Search Tree)

227 Bloom Filters in PostgreSQL: A Practical Guide

228 Composite Indexes in PostgreSQL: Explained with Examples

229 PostgreSQL: Using Partial Indexes to Improve Efficiency