Posts in the “postgresql” category

"Postgres login" FAQ: How do I log into a Postgres database from the command line?

To log into a Postgres database from the command line, use the psql command. Let's take a look at a few psql command line options you can use.

Postgres login commands

If you are logged into the same computer that Postgres is running on you can use the following psql login command, specifying the database (mydb) and username (myuser):

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:

As a quick note, this is how I run the Postgresql pg_dump command to make a database backup while using a password:

pg_dump --no-owner \
        --dbname=postgresql://username:password@localhost:5432/databasename > database.sql

There are other ways to do this, as described on this SO page, but I prefer this solution, which I found on this page. Personally I’d rather have the database password right next to the command, rather than in some file I’ll forget about two weeks from now.

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.

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.

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.

Here you want to use the "describe" command in Postgresql. Assuming that you want to get information about a database table named users, and you're logged into a Postgresql database using the psql client, issue this command at the psql prompt:

\d users

This will provide a description of the table named users. Your output from this command should look like this:

Postgres show tables FAQ: How do I list all the tables in my current Postgresql database?

Once you’re logged into a Postgresql database using the psql client, issue this command at the psql prompt to show all the tables in your Postgres database:

\dt

This will list all of the tables in the current database.

Question: How do I list the Postgres slash commands, i.e., the commands available from the Postgres command line?

Once you're logged into a Postgresql database, you can issue the \? command to see the list of available Postgres "slash" commands, like this:

mydatabase=> \?

For my own convenience, I'm listing these Postgres commands here. These are the commands available in the Postgresql 8.0.3 client:

First, I issue the call for Postgres command line help:

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:

Here's a quick tabular list of Postgres commands related to listing information about a Postgres database. (Technically these are "psql commands", because you issue these commands from the psql command line program.)

These Postgres commands help you answer questions like "What tables are in this postgres database?", or "What databases do I have within Postgres?" (a handy question when it comes time for spring cleaning), and other questions. Other popular psql commands are related to permissions, indexes, views, and sequences.

Wow, it had been a long time since I last started Postgres on my Mac computer (a MacBook Pro, to be specific), and it took almost almost 20 minutes to remember how to do it. So, to keep that from happening again, here's a quick tip on how to start Postgres (Postgresql) on a Mac OS X computer.

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:

If you get a Postgresql error message like this:

ERROR:  must be owner of relation [your_table_here]

don't worry too much, it probably means what it says: You don't own the table (or relation) you're trying to modify. Well, I guess you can worry if you can't get someone to fix the permissions, but if you can it's no big deal.

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

Don't know much about the following site, but it does contain an online version of a book titled Practical PostgreSQL.

Postgresql list FAQ: How do I get a list of databases in a Postgresql database?

Just log into your Postgresql database (with the psql command), and then run this command:

\l

(That's a backslash followed by the lowercase letter "L"). This command provides a nice list of all of the Postgresql databases.

With Postgresql (or Postgres, as I call it) you can execute commands from a file like this:

\i your_file_name_here

I just did that to run a subset of the restore commands from a backup file that I created, and it worked just fine. In my case I put the file in my current working directory, but I'll guess that you can also supply a path to the file and it will work from there as well.