In PostgreSQL the nth_value()
function is a window function that returns the value from the given row of the current window frame. We provide the column and row number as an argument when we call the function.
Category: PostgreSQL
Using the CUME_DIST() Function to Get the Cumulative Distribution in PostgreSQL
In PostgreSQL, we can use the cume_dist()
function to return the cumulative distribution of a value within a group of values.
It calculates this as follows: (the number of partition rows preceding or peers with current row) / (total partition rows).
The return value ranges from 1/N
to 1.
Overview of the PERCENT_RANK() Function in PostgreSQL
In PostgreSQL, we can use the percent_rank()
function to return the relative rank of each row, expressed as a percentage ranging from 0 to 1 inclusive.
Using the NTILE() Function to Divide a Partition into Buckets in PostgreSQL
In PostgreSQL, the ntile()
function is a window function that divides a partition into the specified number of groups (buckets), distributing the rows as equally as possible, and returns the bucket number of the current row within its partition.
Understanding the DENSE_RANK() Function in PostgreSQL
PostgreSQL has a window function called dense_rank()
that returns the rank of the current row, without gaps.
It works the same way that the rank()
function works, but without gaps (the rank()
function includes gaps).
Using the RANK() Function to Add a “Rank” Column in PostgreSQL
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 readingAdd a Column of Row Numbers in PostgreSQL: The ROW_NUMBER() Function
In 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.
3 Ways to List Sequences in PostgreSQL
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 readingFix “column … can only be updated to DEFAULT” in PostgreSQL
If 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.
6 Ways to Fix “nextval: reached minimum value of sequence” in PostgreSQL
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 reading