Object-Relational

Understanding the Difference Between SCALE(), MIN_SCALE() and TRIM_SCALE() in PostgreSQL

by

Anyone who looks at PostgreSQL’s list of mathematical functions will notice that there are some functions that have “scale” in their name. In particular, scale(), min_scale(), and trim_scale().

While these functions all have a similar name, they each have a different purpose. That said, they’re all related to the scale of the given number. The scale is the number of digits to the right of the decimal point in a number. For example, the number 1.95 has a scale of 2, and 1.958 has a scale of 3.

Read more

2 Ways of Creating a Composite Type in PostgreSQL

by

In PostgreSQL, a composite type is a data type that represents the structure of a row or record. It’s basically a list of field names and their data types.

We can use composite types in many of the same ways we can use simple types, such as in table columns.

Below are two ways of creating composite types in PostgreSQL.

Read more

How to Create an Array Column in PostgreSQL

by

PostgreSQL allows us to create arrays and store them in a database column. When we do this, we can use various array related tools to retrieve data from such arrays, as well as manipulate the data within them.

We do need to define the column as an array column though. If we don’t do this, we will likely run into trouble when we want to retrieve data from the array. For example, we can’t just store an array as the text type and then expect to be able to use subscripts to refer to its individual elements.

Read more

A Quick Look at the FACTORIAL() Function in PostgreSQL

by

In PostgreSQL, factorial() is a mathematical function that returns the factorial of a given integer. We pass the integer (it accepts bigint), and it returns the factorial as a numeric value.

In mathematics, the factorial is the product of all positive integers less than or equal to a given positive integer. It’s denoted by that integer and an exclamation point.

Read more

How to Update an Array in PostgreSQL

by

PostgreSQL allows us to create arrays and store them in the database as an array type. We can then go back later and update those arrays as required.

We can either replace the whole array, append or prepend elements, or update individual elements within the array. When we update individual elements, we can update one element or a whole slice. Below are examples of updating an array in PostgreSQL.

Read more