IsEven or IsEvenER?

Recently friend of mine sent me a funny picture:

IsEven on Java
IsEven on Java

Cool, ha?!

Continue reading “IsEven or IsEvenER?”

Advertisements

Serial-Identity mix

Serial-identity
Serial-identity
As you probably know PostgreSQL 10 introduced IDENTITY columns. They are pretty much like serial columns from the first sight. To learn about differences one may refer to the perfect blog post by Peter Eisentraut.

So I was wondering what if I mix some serial with some identity. So I executed such simple query:

CREATE TABLE foo(
id SERIAL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
);

Of course, I got an error:

ERROR: both default and identity specified for column "id" of table "foo"
SQL state: 42601

“SQL state: 42601” stands for a syntax error. But the interesting thing is that SERIAL, as you know, converted to CREATE SEQUENCE and SET DEFAULT.

So now I know what exactly the reason of error. You cannot have both DEFAULT and IDENTITY for a column.

Easy PostgreSQL docs for Sublime Text

I’m using this perfect tool called Sublime Text 3 for a bunch of tasks. One of them is viewing SQL scripts from time to time. ST3 has perfect SQL highlighting, but what I miss the most is the context help functionality, e.g. I select “LEFT JOIN” and hit F1 hot key. But that’s not problem since ST3 has a lot of packages. To solve my problem I need GotoDocumentation package and some tuning. Here is my settings for GotoDocumentation:

{
"docs": {
// obj containing the docs for each scope
// these are merged with the default ones
// the key value pair represent scope -> doc url
// supported placeholders:
// - %(query)s the selected text/word
// - %(scope)s the current scope
"sql": "http://www.postgresql.org/search/?q=%(query)s"

"pascal": "http://docwiki.embarcadero.com/Libraries/Seattle/en/Special:Search/%(query)s",
},
// if we have no docs for the current scope
// we will try using the fallback one,
// to disable set to false
"fallback_scope": "google"
}

ST3 context help  for pg sql
ST3 context help for pg sql

Creating titles from text (SQL)

Task: get title (or excerpt) from given text (body)
Solution:

SELECT
left(body, 29) ||
COALESCE( substring( substr( body, 30 ) from '(.+?)(\,| )' ), '' ) as title,

Details: Take the first 29 characters, concatenate them with substring started from the 30-th character till the first occurrence of comma or space. Or concatenate with empty string, if pattern doesn’t match (COALESCE).

MERGE in PostgreSQL

Found cool trick how today implement Orable MERGE in PostgreSQL:

Oracle statement:

 

  1. MERGE INTO acme_obj_value d
  2. USING ( SELECT object_id
  3.         FROM   acme_state_tmp
  4.       ) s
  5. ON (d.object_id = s.object_id)
  6.   WHEN matched THEN
  7.     UPDATE SET d.date_value = LEAST(l_dt, d.date_value)
  8.   WHEN NOT matched THEN
  9.     INSERT (d.id, d.object_id, d.date_value)
  10.     VALUES (acme_param_sequence.NEXTVAL, s.object_id, l_dt)

PostgreSQL statement:

 

  1. WITH s AS (
  2.      SELECT object_id
  3.      FROM   acme_state_tmp
  4. ),
  5. upd AS (
  6.      UPDATE acme_obj_value
  7.      SET    date_value = LEAST(l_dt, d.date_value)
  8.      FROM   s
  9.      WHERE  acme_obj_value.object_id = s.object_id
  10.      RETURNING acme_obj_value.object_id
  11. )
  12. INSERT INTO acme_obj_value(id, object_id,  date_value)
  13. SELECT NEXTVAL(‘acme_param_sequence’), s.object_id, l_dt
  14. FROM   s
  15. WHERE  s.object_id NOT IN (SELECT object_id FROM upd)

 

Factorial using CTE in PostgreSQL

Not so long ago I used Common Table Expressions for Fibonacci Numbers calculation.

Today I had a conversation with one client about SQL in general and about PosgreSQL dialect in particular. We talked about SQL’s Turing completeness also. Well my opponent is sure that SQL (Postgres dialect either) is not Turing Complete. But I know for sure that if SQL supports CTE it is Turing Complete. Well, I’m sure about it because some time ago at Oscon 2009 David Fetter said so. And my confidence in this man is boundless. 🙂

Anyway, my client proposed to implement Factorial calculation on a pure SQL. I choose Postgres dialect. He agreed.

That was his first mistake! He didn’t know that Postgres has built in “!” and “!!” operators for this purpose. 🙂

But to be more convincing, I have wrote this code:

WITH RECURSIVE fact(i, f) AS (
    VALUES (2, 1)
UNION ALL
    SELECT i + 1, i * f FROM fact
)
SELECT f FROM fact LIMIT 10;