Blog Archives

MERGE in PostgreSQL

Found cool trick how today implement Orable MERGE in PostgreSQL: Oracle statement:   MERGE INTO acme_obj_value d USING ( SELECT object_id         FROM   acme_state_tmp       ) s ON (d.object_id = s.object_id)   WHEN matched

Tagged with: , ,
Posted in Coding, PostgreSQL

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

Tagged with: , , , , ,
Posted in Coding, PostgreSQL

Why you cannot create table and PK constraint with the same name

Very interesting message appeared on the list today: When I do this CREATE TABLE “T1” ( “T1_ID” bigint NOT NULL, CONSTRAINT “T1” PRIMARY KEY (“T1_ID” ) ); I get the following message: NOTICE: CREATE TABLE / PRIMARY KEY will

Tagged with: , , , ,
Posted in PostgreSQL

Determination of a leap year in PostgreSQL

From Wikipedia: A leap year (or intercalary or bissextile year) is a year containing one extra day (or, in the case of lunisolar calendars, a month) in order to keep the calendar year synchronized with the astronomical or seasonal year.

Tagged with: , , , ,
Posted in Coding

Poll: ALTER TYPE enumtype ADD <what?> ‘newlabel’

Yesterday, Hubert ‘depesz’ Lubaczewski wrote a post about new functionality in PostgreSQL 9.1. In a few words we will have the ability to add new values to the existing ENUM type defined earlier. Thanks God! However, Andrew Dunstan proposed to

Tagged with: , , , ,
Posted in Polls, PostgreSQL

1-to-1 relationship in PostgreSQL

Today I received mail from one of my clients: I am using MicroOLAP Database Designer to develop a ERD for a PostGIS database. In my opinion one drawback to PgMDD is the lack of an explicit cardinality determination on the

Tagged with: , , , ,
Posted in MicroOLAP Database Designer for PostgreSQL, PostgreSQL

timestamp :: bigint ?

I have one question. Why PostgreSQL hasn’t implicit casting of timestamps as bigint? We all know that timestamps are stored as eight-byte integers. Yeah, I know that they can be stored as double precision floating-point, but this is deprecated option.

Tagged with: , , ,
Posted in PostgreSQL
Map of wandering

Get every new post delivered to your Inbox.

Join 456 other followers