postgresql

recent posts related to the postgresql database

Postgresql index - What to do when a Postgresql index count is wrong

I had an interesting circumstance happen with Postgres (PostgreSQL) and I want to remember how I fixed the problem.

Somehow I had four entries in a database table, but the key for that table thought that there were only two entries. I don't yet know how this happened, but at the time I didn't care, I just needed to get past the problem. The details of the problem, and the fix, follow.

In my case the table name was carrier, and this table and the key were created like this:

Postgresql regular expressions (syntax, operators)

Here’s a quick note about using Postgresql regular expressions and its regular expression syntax.

If you’re familiar with Unix and Perl regular expressions, these are very similar, so it’s an easy pickup, and a very powerful way to search for data in Postgres. Just use these regular expressions with SQL SELECT queries instead of the LIKE operator, and you’re ready to go.

Postgresql regex syntax

Here’s a little table of the Postgresql regular expression operators I have used:

Postgres case insensitive searching

I started looking into making my “blog” software use case-insensitive searching. I thought that would make the “Search” facility for this site much more powerful.

The secret to Postgresql case-insensitive searching with SQL SELECT queries is to use regular expressions. If you’re used to standard Unix/POSIX regular expressions, the implementation is pretty easy as well. Instead of using the standard database LIKE operator, the solution is to use the Postgres ~* operator.

Postgresql backup - how to backup a Postgres database

Postgresql Backup FAQ: How do I create a backup of a Postgresql database?

Solution: I create a Postgresql backup (a Postgresql dump) for this website using the pg_dump command, like this:

pg_dump -h db_server -U db_user -W db_name > db.20070725.dump
Password:

With this Postgres / pg_dump command I'm doing the following:

A SQL "select where date" example

Here's a quick "SQL select where date" example, showing how to select all orders from an example orders table where the order_date is greater than a date we specify.

The SQL "select where date" example

With these assumptions:

  • You have a database table named orders
  • This table has two fields named order_id and order_date

This SQL will give you information about orders placed in the last two days with PostreSQL:

How to get the Postgresql serial field value after an INSERT

Assuming you are using Postgres (Postgresql), and:

  • you have *just* done an INSERT into a table named order,
  • the name of the sequence for that table is order_order_id_seq
  • you are using the Java programming language
  • you are still using the same database connection that you used with your INSERT statement

to get the value of the Postgres serial value just created after your INSERT statement, use some code like this: