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 [...]
Archive for the ‘SQL’ Category
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 [...]
Using Vim syntax highlighting on custom file types
Posted: 20th April 2010 by Tim in PostgreSQL, Ubuntu, VimTags: config, highlighting, linux, syntax highlighting, vim
Let’s say you have a file type whose contents are in XML format but have a different file extension such as .tim . If you want to edit these files with Vim with syntax highlighting, simply add the following to ~/.vimrc (affects only your Vim environment) or /etc/vim/vimrc (affects everyone’s Vim environment): au BufNewFile,BufRead *.tim [...]
SHOW SCHEMAS in PostgreSQL
Posted: 18th February 2010 by Tim in PostgreSQLTags: display, postgres, PostgreSQL, psql, schema, schemas, show
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 …
Getting the insert ID in PostgreSQL
Posted: 9th February 2010 by Tim in PostgreSQLTags: insert, insert id, postrges, select, transaction
Quite often you need to insert a row into a table and then extract the auto-generated ID. Often people do the insert and then query the table again for the highest ID. This method may have concurrency issues (ie: if someone else inserts a record between the two queries), so they wrap it all up [...]
If you’re writing a script which references a hard-coded value, it’s always cleaner to create a variable or constant to reference that value. PostgreSQL allows you to do that. These substitution variables are defined using the \set var_name ‘value’ command. The value of the variable must be enclosed in single quotes. If the value is [...]
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 | [...]
MySQL allows you to extract a list of tables in schema using the SHOW TABLES command. This can be done in PostgreSQL by using the dt command. ie: tim=#dt List of relations Schema | Name | Type | Owner ——-+——+——+——- public | person | table | tim public | business | table | tim (2 [...]
Auto increment fields in PostgreSQL tables
Posted: 30th December 2009 by Tim in PostgreSQLTags: auto increment. serial, field, PostgreSQL, table
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 [...]
SHOW FULL PROCESSLIST in PostgreSQL
Posted: 21st December 2009 by Tim in MySQL, PostgreSQLTags: activity, MySQL, PostgreSQL, processlist, stats
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.
PQexecParams example – PostgreSQL query execution with parameters
Posted: 19th November 2009 by Tim in C, PostgreSQLTags: C, coding, exec, parameter, PostgreSQL, programming, query
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 [...]
Resetting SERIAL counters in PostgreSQL
Posted: 19th November 2009 by Tim in PostgreSQLTags: auto increment, clear, field, increment, PostgreSQL, primary key, reset, sequence, serial, table
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 PostgreSQLTags: database, MySQL, PostgreSQL, select, SET, USE
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 [...]
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 [...]
Inline IF and CASE statements in MySQL
Posted: 13th August 2009 by Tim in MySQLTags: case, data, inline, MySQL, query, statement, switch
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 MySQLTags: constraints, foreign keys, integrity, MySQL, table
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 [...]