Today I Learned

hashrocket A Hashrocket project

Postgres Arrays are One-Based

Today I was reminded that Postgres arrays are one-based. This realization came while using Ruby's times method (zero-based index) to generate SQL targeting a Postgres array (one-based index).

Take this table:

 name  |      pay_by_quarter
 Bill  | {10000,10000,10000,10000}
 Carol | {20000,25000,25000,25000}
(2 rows)

To select the first-quarter pay for all employees, use index 1.

my_database=# select pay_by_quarter[1] from sal_emp;
(2 rows)

Using your first Ruby index value 0 to produce pay_by_quarter[0] returns a sad, empty column.

See More #sql TILs
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.