Posts Tagged ‘PostgreSQL’

PHP is able to communicate with PostgreSQL databases using some relatively simple calls. In a similar manner to other database systems, the script needs to do the following: connect to the database using pg_connect execute queries using pg_query and pg_free_result close the database connection using pg_close For example, consider the following script: <?PHP // database […]

Consider the following query (tested on PostgreSQL – some other systems may require a table to be specified): SELECT ‘Yes’ AS Value_Returned WHERE 1 != 2; This query returns 1 row: ( Value_Returned = ‘Yes’ ), as one would expect. But what if we compare against NULL? SELECT ‘Yes’ AS Value_Returned WHERE 1 != NULL; […]

If you’re writing functions in postgres then you’ll probably be using a language such as plpgsql. Let’s say you’re writing a script to to add all of these functions to a new database, but you don’t know whether that language has been created yet. You’ll probably want to do something like CREATE LANGUAGE IF NOT […]

Conditional COUNT in SQL

Posted: 10th October 2010 by Tim in SQL
Tags: , , , , , , , , , , ,

Sometimes you want to count the number of results which meet a certain condition. Excel has a COUNTIF(…) function but there is nothing like that in the (current) SQL standard. The solution is to mix the SUM function with a CASE statement. Confused? Let’s see an example. Imagine you have two tables – student (student_id, […]

It’s often useful to concatenate two fields together in an SQL query. In PostgreSQL, fields may be concatenated using the || operator. The syntax is really simple. Just place the double bar between the fields you want to join, and (optionally) give a label to that new field. Strings can also be concatenated with the […]

PostgreSQL, unfortunately, does not provide an inline IF statement like some other SQL servers. CASE statements, however, can be run inline which can be quite handy. Let’s say you have a user table with a num_heads field. You want to know if the user is a zombie, human or alien with one query. This could […]

If you’re getting a FATAL: Ident authentication failed for user [username] Error when attempting to connect to postgres as a specific user, chances are you need to change some security settings. Postgresql, by default, only allows you to connect to postgres if the postgres username is the same as your username on the operating system. […]

SHOW SCHEMAS in PostgreSQL

Posted: 18th February 2010 by Tim in PostgreSQL
Tags: , , , , , ,

If you’re looking for a command to give you a list of schemas in PostgreSQL, much like MySQL’s SHOW SCHEMAS command, then \dn is what you’re looking for. ie: \dn List of schemas Name               | Owner ——————-+——— information_schema | postgres pg_catalog         | postgres person             | tim …

The unix epoch timestamp is a number representing the number of seconds since the first of January, 1970. This may seem like a strange representation, but it allows dates to be compared very easily. To get the epoch timestamp of a date in PostgreSQL, we can use the EXTRACT function like so: tim=# SELECT EXTRACT(EPOCH […]

Database systems such as MySQL allow you to specify a column as being auto incrementing. PostgreSQL, on the other hand, does things differently. Fields are incremented using sequences; external counters which are incremented manually. To use this requires setting up the sequence with a unique name and telling PostgreSQL to grab and increment the sequence […]

Like MySQL, there is a way to see all processes in PostgreSQL. It’s one simple command: SELECT * FROM pg_stat_activity; Note that if the current_query field keeps coming up empty, you might need to enable stats_command_string in your postgresql.conf. Enjoy.

The PostgreSQL documentation states that PQexecParams can be called like so: PGresult *PQexecParams(PGconn *conn, const char *command, int nParams, const Oid *paramTypes, const char * const *paramValues, const int *paramLengths, const int *paramFormats, int resultFormat); There are two parts of this call which can be tricky to novice C and/or PostgreSQL users and aren’t explained […]

There are times when you want to explicitly set or reset the counter value on a SERIAL (auto incrementing) field or on a SEQUENCE. This may be done using a SELECT SETVAL(…) command. If you’re using a SERIAL field, PostgreSQL will actually create a SEQUENCE for you, which increments every time you insert a row. […]

USE [schema_name] in PostgreSQL

Posted: 17th November 2009 by Tim in PostgreSQL
Tags: , , , , ,

For those moving from MySQL to PostgreSQL, there are a few differences which you will need to get used to. One of these differences is the USE command, used to select the schema to select tables, views, etc from. PostgreSQL does not have the USE command. Instead, you can use: SET search_path TO [schema_name] For […]