Posts Tagged ‘sql’

If you have a HyperSQL (HSQL) database stored in a file, it is often useful to be able to query that database from the command line. This can be done using the following command: hsqldb-sqltool –inlineRc url=jdbc:hsqldb:file:<db_name>,user=<username>,password=[<password>] By default, user SA with no password will exist for each database file. If the database does not […]

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; […]

Conditional INSERT in SQL

Posted: 23rd October 2013 by Tim in SQL
Tags: , , , , , ,

Sometimes you want to run an INSERT statement in SQL only if some condition is met. There are a few methods available to do this, but not all of them are supported by all database systems. One method which is supported on all systems the use of a SELECT statement to return the row values, […]

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 […]

DESCRIBE table in PostgreSQL

Posted: 20th January 2010 by Tim in PostgreSQL
Tags: , , , , ,

MySQL has a nice way to get the details of a table structure through the DESCRIBE command. PostgreSQL can also do this for you with \d table_name. For example (sorry about the poor layout): tim=# \d test_table Table “public.test_table” Column | Type | Modifiers ———+—————————–+——————- test_id | integer | not null default nextval(‘test_table_test_id_seq’::regclass) name | […]

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 […]

MySQL comes with a library to make talking to MySQL with C easyish. There are a few things you have to install first, though. I’m using Ubuntu 8.04 for this walkthrough, but things should be similar for other flavours of Linux. Before we start, we have to download the development files required: sudo apt-get install […]