Posts Tagged ‘MySQL’

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

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

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.

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

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

Inline IF and CASE statements in MySQL

Posted: 13th August 2009 by Tim in MySQL
Tags: , , , , , ,

There are times where running IF statements inside a query can be useful. MySQL provides a simple way to do this through the use of IF and CASE statements. The IF statement takes three arguments; the conditional, the true value and the false value. False and true values may be static values or column values. […]

MySQL Foreign Keys

Posted: 23rd July 2009 by Tim in MySQL
Tags: , , , ,

Foreign keys are a good way to ensure consistency between tables, by ensuring that the value in one table column exists in another table. For example, you might have a person table which contains person_id, first_name and surname. You may then have another table, person_ages, which contains person_id and date_of_birth. You would add a foreign […]