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:

By Alvin Alexander on August 8, 2011

Postgres regex - Using regular expressions in queries

Postgres regex support. Some time ago I learned that about Postgres regular expression searching, including Postgres case insensitive searching.

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 SELECT in your PostgreSQL queries instead of the LIKE operator, and you're ready to go.

By Alvin Alexander on October 3, 2009

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 Postgres case insensitive searching 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, you use the Postgres ~* operator.

By Alvin Alexander on October 3, 2009

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:

By Alvin Alexander on August 6, 2011

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:

By Alvin Alexander on August 6, 2011

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:

By Alvin Alexander on March 3, 2013

Where can I get a JDBC driver for Postgres?

Java JDBC / database FAQ: Where can I get a JDBC driver for Postgres/Postgresql?

By Alvin Alexander on November 30, 2011
Syndicate content