Home » PostgreSQL » Page 3

Tag Archives: PostgreSQL

Downsides of Mixed Identifiers When Porting Between Oracle and PostgreSQL Databases

Both the Oracle database and the PostgreSQL database use the presence or absence of double quotes to indicate case sensitive or case insensitive identifiers. Each of these databases allows identifiers to be named without quotes (generally case insensitive) or with double quotes (case sensitive). This blog post discusses some of the potential negative consequences of mixing quoted (or delimited) identifiers ...

Read More »

Passing Arrays to a PostgreSQL PL/pgSQL Function

It can be handy to pass a collection of strings to a PL/pgSQL stored function via a PostgreSQL array. This is generally a very easy thing to accomplish, but this post demonstrates a couple of nuances to be aware of when passing an array to a PL/pgSQL function from JDBC or psql. The next code listing is for a contrived ...

Read More »

Setting PostgreSQL psql Variable Based Upon Query Result

When using PostgreSQL‘s psql command-line tool to interact with a PostgreSQL database via operator interaction or script, it is not uncommon to want to set psql variables based on the results of a particular query. While PostgreSQL’s procedural language PL/pgSQL supports approaches such as SELECT INTO and assignment (:=) to set PL/pgSQL variables based on a query result, these approaches ...

Read More »

Procedure-Like Functions in PostgreSQL PL/pgSQL

PostgreSQL does not support stored procedures in the sense that a database such as Oracle does, but it does support stored functions. In this post, I look at a few tactics that can make the use of a stored function in PostgreSQL (stored function and its calling code both written in PL/pgSQL) feel like using a stored procedure. These simple ...

Read More »

Installing PostgreSQL PL/Java as a PostgreSQL Extension

In 2011 I wrote a series of articles on PostgreSQL PL/Java. The basic information is still solid but there is a now a much easier way to install PL/Java from source. This also eliminates the need to depend on third parties to create packages. These notes will be fairly brief since I assume my readers are already familiar with git ...

Read More »

Extending PostgreSQL: Complex Number Data Type

A few years ago I discussed creating user-defined types using PL/java. (Introduction to PostgreSQL PL/Java, part 4: User Defined Types) Today I follow up on the comment that we would use a standard C-language PostgreSQL extension in practice. What is an extension? A PostgreSQL extension is nothing more than bundled SQL statements and an optional native library. SQL-only extensions contain ...

Read More »

Unleash the power of storing JSON in Postgres

An article by Sarah Mei titled “Why you should never use MongoDB” discusses the issues you’ll run into if you try to use a NoSQL database when a relational database would be far superior. An example of this is when data that was thought to be in a silo needs to cross boundaries (what relational DBs are great at). Another ...

Read More »

Postgres indexes

Recently, I had a situation where I needed to think how I was using Postgres indexes. I had a simple Book table with the following schema… >\d book Table "shopping.book" Column | Type | Modifiers ---------------------+------------------------+----------- id | uuid | not null version | bigint | not null amount_minor_units | integer | not null currency | character varying(255) | not ...

Read More »

Discovering the Computer Science Behind Postgres Indexes

This is the last in a series of Postgres posts that Pat Shaughnessy wrote based on his presentation at the Barcelona Ruby Conference. You can also watch the video recording of the presentation. The series was originally published on his personal blog, and we are republishing it on Codeship with his kind permission. You can also read posts one, two, ...

Read More »