Create free account
User profile menu open
Open user profile menu
Payment & Billing
Payment & Billing
Create free account
19 May 2016
Pivot Tables in PostgreSQL Using the Crosstab Function
Some years ago, when PostgreSQL version 8.3 was released, a new extension called tablefunc was introduced. This extension provides a really interesting set of functions. One of them is the crosstab function, which is used for pivot table creation. That's what we'll cover in this article. The simplest way to explain how this function works is using an example with a pivot table. First, we will explain our initial point from a practical perspective, then we'll define the pivot table we want.
5 May 2015
5 Tips to Optimize Your SQL Queries
The SQL Language seems easy to learn – the commands follow a simple syntax and don't describe the specific algorithms used to retrieve data. The simplicity might, however, be deceptive. Not all the database functions operate with the same efficiency. Two very similar queries can vary significantly in terms of the computation time. This article presents some of the best practices that can greatly boost your SQL queries. 1. Learn How to Create Indexes Properly Learning how to index properly is the best thing you can do to improve the performance of your SQL queries.
21 Oct 2014
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.
3 Sep 2014
S.Q.L or Sequel: How to Pronounce SQL?
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. Is it "S.Q.L" [ˈɛs kjuː ˈɛl] or is it "sequel" [ˈsiːkwəl]? SQL... Where it all started Let's start at the beginning. Relational databases came into existence with E.F. Codd's 1970 publication "A Relational Model of Data for Large Shared Data Banks." While Codd's ideas were remarkable for the time, in San Jose, California, two colleagues named Donald D.
26 Aug 2014
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.
21 Aug 2014
Can SQL Help Solve Crossword Puzzles?
Everyone has solved crossword puzzles and has certainly had some problems finding an appropriate word. Thanks to SQL, it is ridiculously simple to quickly dispel your crossword doubts and give you the correct answers. Of course, Google is commonly known as a universal cure for many doubts, but handling the problem yourself is much more rewarding. Recently I came across some simple and interesting examples from Andrew Cumming's book "
22 May 2014
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
21 May 2014
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;
7 Jan 2014
MySQL's group_concat Equivalents in PostgreSQL, Oracle, DB2, HSQLDB, and SQLite