PostgreSQL has a window function called rank() that returns the rank of the current row, with gaps.
“With gaps” means that it returns the same rank for any ties (i.e. two or more rows with the same value), but then subsequent ranks jump forward to account for the ties.
This means that there’s the potential for noncontiguous rank values. For example it could go 1, 2, 5, etc if several rows are ranked at 2. If there are no ties, then the rank values will be contiguous.
Continue readingIn PostgreSQL, we can use the row_number() function to get each row’s number within its partition. This allows us to create a column with incrementing row numbers that reset with each new partition.
The row_number() function is a window function that’s specifically designed to return the number of the current row within its partition, starting at 1 and incrementing sequentially.
We have a number of options when it comes to returning a list of sequences in PostgreSQL. By “sequences”, I mean “sequence objects”, or “sequence generators”. These are objects we can create in order to generate a series of sequential numbers.
Here are three ways to get a list of sequences in Postgres.
Continue readingIf you’re getting an error in PostgreSQL that tells you that a column “…can only be updated to DEFAULT” with detail that explains that it “…is an identity column defined as GENERATED ALWAYS“, it’s probably because you’re trying to update an identity column with your own value, but the identity column was created with the GENERATED ALWAYS option.
If you’re getting an error that reads something like “nextval: reached minimum value of sequence “sequence1” (-3)” in PostgreSQL, it’s probably because you’re trying to generate a new value from a descending sequence, but the sequence has already reached its minimum value.
We have a number options when it comes to dealing with this issue.
Continue readingIf you’re getting an error that reads “step size cannot equal zero” when creating a series with the generate_series() function in PostgreSQL, it’s because you’re using a step size of 0 (i.e. zero).
The generate_series() function doesn’t accept a zero step size.
To fix this issue, either use a non-zero step size, or remove the step size altogether (so that the default step is used).
Continue readingWhen we create a sequence in PostgreSQL, we have the option of making it a non-repeating sequence or repeating sequence.
By “repeating” sequence, I mean that, once it reaches its maximum value (or minimum if it’s a descending sequence), it starts all over again, re-using numbers previously generated.
Continue readingIf you’re getting an error that reads something like “MINVALUE (1) must be less than MAXVALUE (1)” in PostgreSQL when you’re trying to create a sequence, it’s probably because your sequence’s minimum possible value is higher than the maximum value.
To fix this issue, be sure that the sequence’s maximum value is greater than the minimum value.
Continue readingIf you’re getting an error that reads something like “START value (101) cannot be greater than MAXVALUE (100)” in PostgreSQL when you’re trying to create a sequence, it’s probably because your sequence’s maximum possible value is lower than the start value.
To fix this issue, change either the start value or maximum value so that the maximum value is greater than the start value.
Continue readingIf you’re getting an error in PostgreSQL that reads something like “cannot insert into column” with detail that explains that the “…is an identity column defined as GENERATED ALWAYS“, it’s probably because you’re trying to insert your own value into an identity column that was created with the GENERATED ALWAYS option, but you’re not explicitly overriding the column from auto generating its own value.
To fix this issue, use OVERRIDING SYSTEM VALUE when inserting the value.