Articles Cookbook
5 Jan 2015
Patrycja Dybka

PostgreSQL Collations

List Collations Collations in PostgreSQL are available depending on operating system support. For example, in Ubuntu type the following to list the names of the available collations: locale -a The same locales are available in PostgreSQL in the pg_collation catalog (mappings from an SQL name to operating system locale categories). select * from pg_collation; collname | collnamespace | collowner | collencoding | collcollate | collctype -----------+---------------+-----------+--------------+-------------+------------ default | 11 | 10 | -1 | | C | 11 | 10 | -1 | C | C POSIX | 11 | 10 | -1 | POSIX | POSIX C.
Read more
31 Dec 2014
Agnieszka Kozubek-Krycuń

MySQL Collations

List Collations To list all collations available in MySQL, use SHOW COLLATION; +-------------------+----------+-----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +-------------------+----------+-----+---------+----------+---------+ | big5_chinese_ci | big5 | 1 | Yes | Yes | 1 | | big5_bin | big5 | 84 | | Yes | 1 | | dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 | | dec8_bin | dec8 | 69 | | Yes | 1 | | cp850_general_ci | cp850 | 4 | Yes | Yes | 1 | | cp850_bin | cp850 | 80 | | Yes | 1 | | hp8_english_ci | hp8 | 6 | Yes | Yes | 1 | | hp8_bin | hp8 | 72 | | Yes | 1 | | koi8r_general_ci | koi8r | 7 | Yes | Yes | 1 | | koi8r_bin | koi8r | 74 | | Yes | 1 | | latin1_german1_ci | latin1 | 5 | | Yes | 1 | | latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 | | latin1_danish_ci | latin1 | 15 | | Yes | 1 | | latin1_german2_ci | latin1 | 31 | | Yes | 2 | | latin1_bin | latin1 | 47 | | Yes | 1 | | latin1_general_ci | latin1 | 48 | | Yes | 1 | .
Read more
22 Dec 2014
Agnieszka Kozubek-Krycuń

How Does a Database Sort Strings?

Different languages have different alphabets and different ways to order letters within those alphabets. For example, a Polish character Ł comes right after L and before M. In Swedish, a letter Å comes almost at the end, right after Z. In French diacritics marks have no impact on the alphabetical order, so the letters À, Á and  are treated as the letter A when sorting strings. A collation is a set of rules that defines how to compare and sort character strings.
Read more
29 Oct 2014
Patrycja Dybka

Basic Date and Time Functions in MS SQL Server

As a follow up to our article “The Most Useful Date and Time Functions in Oracle Database”, let’s review what date and time functions look like in MS SQL Server. Let’s start with functions that extract a year, month and day from a given date. declare @dt date = '2014-10-20' select year (@dt) as year, month (@dt) as month, day (@dt) as day SQL Server uses GETDATE() and SYSDATETIME() to get a current date and time.
Read more
21 Oct 2014
Patrycja Dybka

The Most Useful Date and Time Functions

Date and time functions and formats are quite different in various databases. In this article, let's review the most common functions that manipulates dates in an Oracle database. The function SYSDATE() returns a 7 byte binary data element whose bytes represents: century, year, month, day, hour, minute, second It's important to know that select sysdate from dual in SQL*Plus gives the same result as select to_char(sysdate) from dual because SQL*Plus binds everything into character strings so it can print it.
Read more
3 Sep 2014
Patrycja Dybka

S.Q.L or Sequel: How to Pronounce SQL?

Have you ever thought about how to pronounce SQL? It's something that confuses a lot of people, even though SQL has been a big part of the tech industry for decades. Should you spell it out as “S-Q-L” or just say “sequel”? Let's dig into this debate and see if we can figure it out. SQL has been around for decades and supports a many billion dollar market. However, many people still struggle with just how to pronounce the term SQL.
Read more
26 Aug 2014
Patrycja Dybka

SQL vs. Mongo query

In recent times, NoSQL databases have become a hot topic and have gained a crowd of advocates. Indeed, when NoSQL database started to arise, SQL-to-NoSQL converters arose with them as well. But unfortunately, thanks to the code conversion possibility, SQL supporters gained unquestionable evidence that in many cases the good, old SQL is much easier to use. Especially when it comes to queries. I performed a quick Google search and found plenty of nice converter tools.
Read more
22 May 2014
Agnieszka Kozubek-Krycuń

DELETE RETURNING clause in PostgreSQL

The standard DELETE statement in SQL returns the number of deleted rows. DELETE FROM external_data; DELETE 10 In PostgreSQL you can make DELETE statement return something else. You can return all rows that have been deleted. DELETE FROM external_data RETURNING *; id | creation_date | user_id | data ----+---------------------------+---------+---------------- 101 | 2014-05-06 13:10:45.09484 | 23 | 'Some text' 102 | 2014-06-10 22:23:12.12045 | 25 | 'Some other text' (2 rows) DELETE 2
Read more
21 May 2014
Agnieszka Kozubek-Krycuń

How to Select the First Row in a Group?

Often you want to select a single row from each GROUP BY group. PostgreSQL has a statement especially for that: SELECT DISTINCT ON. Let's say I want to select one weather report for each location. location time report Ottawa 2014-05-15 8:00 sunny 2014-05-15 11:00 cloudy 2014-05-15 15:00 rainy Warsaw 2014-05-15 8:00 overcast 2014-05-15 11:00 sunny 2014-05-15 15:00 rainy SELECT DISTINCT ON (location) location, time, report FROM weather_reports;
Read more
7 Jan 2014
Agnieszka Kozubek-Krycuń

MySQL's group_concat Equivalents in PostgreSQL, Oracle, DB2, HSQLDB, and SQLite

The GROUP_CONCAT() function in MySQL MySQL has a very handy function which concatenates strings from a group into one string. For example, let's take a look at the children table with data about parents' and children's names. if (typeof VertabeloEmbededObject === 'undefined') {var VertabeloEmbededObject = "loading";var s=document.createElement("script");s.setAttribute("type","text/javascript");s.setAttribute("src", "https://my.vertabelo.com/js/public-model/v1/api.js");(document.getElementsByTagName("head")[0] || document.documentElement ).appendChild(s);} parent_name child_name John Tom Michael Sylvie John Anna Michael Sophie To get the names of children of each person as a comma-separated string, you use the GROUP_CONCAT() function as follows:
Read more