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 [...]
Posts Tagged ‘sql’
CREATE LANGUAGE if it doesn’t exist in PostgreSQL
Posted: 27th August 2011 by Tim in PostgreSQLTags: create, create language, exist, if exists, if not exists, language, plpgsql, postgres, PostgreSQL, query, script, sql, statement
1
Conditional COUNT in SQL
Posted: 10th October 2010 by Tim in SQLTags: count, COUNTIF, if, mssql, MySQL, oracle, PostgreSQL, query, sql, sql server, statement, sum
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, [...]
Concatenation in PostgreSQL
Posted: 15th June 2010 by Tim in PostgreSQLTags: concat, concatenation, database, PostgreSQL, result, server, SET, sql, string
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 [...]
Inline CASE statement in PostgreSQL
Posted: 9th June 2010 by Tim in PostgreSQLTags: case, database, if, PostgreSQL, query, select, server, sql, statement, switch
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 PostgreSQLTags: describe, detail, MySQL, sql, syntax, table
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 | [...]
Connecting to MySQL in C
Posted: 20th August 2009 by Tim in C, MySQL, UbuntuTags: C, coding, compile, connectivity, database, development, gcc, MySQL, sockets, sql, Ubuntu
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 [...]