Normalize array subscripts so they start with 1

by

Eventually, something more elegant popped up with Postgres 9.6. The manual:

It is possible to omit the lower-bound and/or
upper-bound of a slice specifier; the missing bound is replaced by the lower or upper limit of the array’s subscripts. For example:

So it’s simple now:

SELECT my_arr[:];

With my example array literal you need enclosing parentheses to make the syntax unambiguous:

SELECT ('[5:7]={1,2,3}'::int[])[:];

About the same performance as Daniel’s solution with hard-coded max array subscripts – which is still the way to go with Postgres 9.5 or earlier.

More Related Contents:

  1. PostgreSQL unnest() with element number
  2. Best way to select random rows PostgreSQL
  3. Check if value exists in Postgres array
  4. Optimise PostgreSQL for fast testing
  5. Return multiple columns of the same row as JSON array of objects
  6. SQL SELECT speed int vs varchar
  7. Is there something like a zip() function in PostgreSQL that combines two arrays?
  8. Selecting data into a Postgres array
  9. PostgreSQL JOIN with array type with array elements order, how to implement?
  10. Multiple array_agg() calls in a single query
  11. How to store array or multiple values in one column
  12. Get execution time of PostgreSQL query
  13. Check if NULL exists in Postgres array
  14. What is the fastest way to truncate timestamps to 5 minutes in Postgres?
  15. Grouped LIMIT in PostgreSQL: show the first N rows for each group?
  16. MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
  17. Optimize query with OFFSET on large table
  18. SQL ‘like’ vs ‘=’ performance
  19. postgres column “X” does not exist
  20. How many rows in a database are TOO MANY?
  21. What are the pros and cons of performing calculations in sql vs. in your application
  22. SQL syntax term for ‘WHERE (col1, col2) < (val1, val2)'
  23. PostgreSQL: encoding problems on Windows when using psql command line utility
  24. Aggregate a single column in query with many columns
  25. Aggregating (x,y) coordinate point clouds in PostgreSQL
  26. Does Postgres support nested or autonomous transactions?
  27. Why is iterating through a large Django QuerySet consuming massive amounts of memory?
  28. Improving performance of cluster index GUID primary key
  29. Update a column of a table with a column of another table in PostgreSQL
  30. Return rows from INSERT with ON CONFLICT without needing to update

Leave a Comment