In PostgreSQL array_shuffle() is a system function that randomly shuffles the first dimension of a given array.
The array_shuffle() function was introduced in PostgreSQL 16, which was released on September 14th 2023.
Category: Object-Relational
Fix “function array_sample(integer, integer) does not exist” in PostgreSQL
If you’re getting an error that reads “function array_sample(integer, integer) does not exist” when using the array_sample() function in PostgreSQL, it’s probably because your first argument is not an array. In particular, this specific error message implies that the first argument is an integer.
The first argument for this function must be an array.
To fix this error, be sure that your first argument to array_sample() is an array, not an integer.
An Introduction to the array_sample() Function in PostgreSQL
In PostgreSQL array_sample() is a system function that returns an array of a specified number of items randomly selected from the given array.
The array_sample() function was introduced in PostgreSQL 16, which was released on September 14th 2023.
An Overview of the date_subtract() Function in PostgreSQL
PostgreSQL 16 introduced the date_subtract() function that allows us to subtract an interval from a timestamp with time zone.
It computes times of day and daylight-savings adjustments according to the time zone named by the third argument, or the current TimeZone setting if that is omitted.
PostgreSQL date_add() Function Explained
PostgreSQL 16 introduced the date_add() function that allows us to add an interval to a timestamp with time zone.
It computes times of day and daylight-savings adjustments according to the time zone named by the third argument, or the current TimeZone setting if that is omitted.
2 Ways to Rename the Columns when using VALUES to Create a Constant Table in PostgreSQL
When we use the VALUES statement to create a constant table, PostgreSQL automatically names the columns column1, column2, etc. This saves us from having to provide names for the columns or from getting a blank column header.
But we also have the option of naming the columns ourselves.
Continue readingHow to use VALUES as a Stand Alone SQL Statement in PostgreSQL
Many of us who work with SQL databases have used the VALUES keyword with the INSERT statement when inserting data into a database.
But perhaps a lesser known fact is that some RDBMSs allow us to use VALUES as a stand alone SQL statement. PostgreSQL is one such RDBMS.
When used as a stand alone statement, we can use a VALUES statement instead of a SELECT statement.
Oracle Adds Support for IF EXISTS and IF NOT EXISTS Syntax Modifiers
Many RDBMSs implement IF EXISTS and IF NOT EXISTS syntax modifiers that can be used with DDL object creation, modification, and deletion, such as CREATE TABLE and DROP TABLE statements, to name just a couple.
These syntax modifiers allow us to run such statements without getting an error in the event that the object already exists (if we’re trying to create it or modify it) or doesn’t exist (if we’re trying to drop it).
Continue readingFix Error “function lag(numeric, numeric) does not exist” in PostgreSQL
If you’re getting an error in PostgreSQL that reads something like “function lag(numeric, numeric) does not exist“, it could be because your second argument is the wrong data type.
The second argument to the lag() function is optional, but if it’s provided, it must be an integer.
So to fix this issue, make sure the second argument is an integer. Alternatively, you can omit the second argument altogether if you’re happy to use the default value of 1.
Fix Error “argument of ntile must be greater than zero” in PostgreSQL
If you’re getting an error that reads “ERROR: argument of ntile must be greater than zero” in PostgreSQL, it’s probably because you’re calling the ntile() function with a non-positive integer.
This error occurs when we pass a non-positive integer to the ntile() function.
To fix this issue, be sure to pass a positive integer to the function.
Continue reading