Category: PostgreSQL

Do we need LIMIT clause in UPDATE and DELETE statements for PostgreSQL?

Was working with MySQL recently. Noticed that UPDATE command of it has special LIMIT clause which is really useful as for me, e.g.

UPDATE `Product_download` SET `version`='9.2.1' WHERE `product_download_id`=367 LIMIT 1;

Number into words convertion in PostgreSQL

I was playing with some unit tests for PostgresDAC recently. And one test case was to check TPSQLDataset.Locate routine which allows partial-string and case-sensitive matching options. So I want to have some test result set with integer and text columns. The simplest for me are numbers in both representations: using digits and using words, e.g.

  1. one
  2. two
  3. three
  4. four
  5. etc.

Since I hadn’t much spare time I used nice VALUES command:

VALUES (1, 'one'), (2, 'two'), (3, 'three');

Considering I needed special names for columns I used more complex query:

SELECT col1, col2::varchar(10) FROM 
(VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t(col1, col2);

I did my task well. However I was wondering is there any built-in function in PostgreSQL which may convert integer to such words representation. The answer is: not exactly.

I found some references to cash_words function. But there is no any information (except entry in 7.3 release notes) in the official documents. The use case is quite simple:

SELECT cash_words(gen.i::money) FROM generate_series(1, 10) AS gen(i);
cash_words text

"One dollar and zero cents" "Two dollars and zero cents" "Three dollars and zero cents" "Four dollars and zero cents" "Five dollars and zero cents" "Six dollars and zero cents" "Seven dollars and zero cents" "Eight dollars and zero cents" "Nine dollars and zero cents" "Ten dollars and zero cents"

I’m fine with this result set, who needs only numbers may trim values using standard functions.

PS In case someone wants to create his own implementation using C (or plpgsql, who knows), cash_words function may be found in the \src\backend\utils\adt\cash.c file. It uses num_word private routine.

PGConf.EU 2012: My schedule

PGConf.EUWell, I suppose it’s time to choose the most interesting talks to visit at PGConf.EU 2012. I sketched out a rough list.

Wednesday, October 24

11:10 - 12:00
Writing a foreign data wrapper
Bernd Helmle

12:10 - 13:00
MultiMaster Replication: Applications, Comparison, Implementation
Andres Freund, Simon Riggs

14:00 - 14:50
Understanding EXPLAIN's output
Guillaume Lelarge

15:20 - 16:10
Range Types in PostgreSQL 9.2 - Your Life Will Never Be The Same
Jonathan S. Katz

16:20 - 17:10
Gianni Ciolli

Thursday, October 25

09:30 - 10:20
Graphs and topology with PostgreSQL and PostGIS
Vincent Picavet

10:50 - 11:40
Universal Data Access with SQL/MED
David Fetter

11:50 - 12:40
Elephants and Windmills
Josh Berkus

13:40 - 14:30
Inside PostgreSQL Shared Memory
Bruce Momjian

14:40 - 15:30
Embracing the Web with JSON and PLV8
Will Leinweber

Friday, October 26

09:30 - 10:20
PostBIS - A Bioinformatics Booster for PostgreSQL
Michael Schneider

10:50 - 11:40
PostgreSQL makes dev happy, a pgAgent + pl/pgsql use case
Julien Rouhaud

11:50 - 12:40
Debugging complex SQL queries with writable CTEs
Gianni Ciolli

13:40 - 14:30
Using PostgreSQL for storing time-series data
Sebastian Harl

Meet me at the PGConf.EU soon!

How to move data from one schema to another in Postgres?

I saw a question today :

I have PostpreSQL 9.1. In my database there are 2 schemas: public and test. How can I quickly move all objects and data from public to test?

Opps. I’m stuck with this issue for some time. I was thinking about dump and restore tricks, about direct changes to the pg_catalog schema etc. I even appeared on the IRC channel with this question.

There I had conversation with Jon SuckMojo Erdman, who’s first thought was dump way either. But then we have found a simple and elegant solution independently.

Assuming schema test is empty:





Backward compatibility? Never heard of it!

Playing with fresh PostgreSQL 9.2beta2 I cannot find spclocation column of a system catalog pg_tablespace. First my thought was that I’m too tired. But then I found mentioning about this at pgsql-hackers by Magnus Hagander:

And IIRC, we don’t actually *use* spclocation anywhere. How about we
just get rid of them as independents?

Oh, really?!!! WTF? How about other developers, ha? I’m using this. I really do. Don’t know how about other guys, but I will need additional nasty checks for server version to know what should I use: pg_get_tablespace_location(oid) or tablespace.spclocation

Thanks a lot guys! Way to go!

PG 9.2beta2 installation on Windows XP fails? Don’t worry!

Shit happens guys. And sometimes PostgreSQL windows installer fails. Now I’m talking about EnterpriseDB One-Click Installer for PostgreSQL 9.2beta2 particularly.

As for me, I got “An error occurred executing the Microsoft VC++ runtime installer” message. Strange. I have a holy zoo of different runtimes on my test machine.

For those of you who need my advice. Run installer with additional parameter:

postgresql-9.2.0-beta2-windows.exe --install_runtimes 0

This will run installer without VC runtime check.

PS If you’ll need VC runtime you always may find it on the MS official site.

The tale of how PostgresDAC became the cross-platform component suite

As some probably know Delphi XE2 now have compilers for Win32, Win64, Mac and iOS. And as you probably guessed, we were inundated with questions from our customers about support for this zoo in our DACs.

Frankly speaking this situation is perfectly described in Joel’s Spolsky “Fire and motion”. We thought: “This just can’t be done in a sane time!”

However “you never know what you can do till you try” and “the devil is not so black as he is painted”. So we decided to port PostgresDAC first. The main reason was the using of libpq.dll client library, which may be built for a huge amount of target platforms.

Thus we don’t need to worry about low-level network routines and may pay the whole of the attention to the middle layer, have no idea how to name it.

As for Win64 platform, then were no problems at all. We’ve prepared x64 deploy libraries and we done with it. There were no dangerous places in our source code were address arithmetics may produce unsuspected behavior. This simplicity is understandable. We just stayed within one Windows platform in general.

Then it was Mac’s turn. When we started I have never seen MacOS before… That was scary move. Especially for my self-esteem. :)

We crossed the fingers, chose OS X as the target platform and the fun began. I knew our code was Windows-centric, but I was so far from the real understanding of the scope of the tragedy. :)

Here is the seven deadly sins for the cross-platform library written in Delphi:

1. Using of Windows unit is prohibited. The only things you have are System ans SysUtils. Deal with it!

2. Using of VCL units (Controls, StdVCL, ExtCtrls etc) is prohibited. There’s no VCL on Mac, FireMonkey only.

3. Using of forms is prohibited. So no custom dialogs, message boxes etc. You have no idea under which platform library is built. Well, this is so true for console application too. That’s why we’ve removed annoying trial screen.

4. Ignoring compiler directives is stupid. Remember the first sin? Yeah, sometimes you’ll need low-level OS’s functionality. If so, do it smart:

Posix.SysTypes, Posix.Stdio, Posix.Stdlib

5. Using of messages, window procedures, handles, file mappings and other Win-shit is prohibited. That’s why our TPSQLMonitor is still useless for Mac. We need to rewrite it from scratch. And we ready for it.

6. Use correct type naming, e.g. LongWord instead of DWORD.

7. Use proper functions or implement them for all platforms. No place for ZeroMemory, CopyMemory, GetTickCount on Mac etc.

You know, when I wrote the number of seven I didn’t think that I’m so damn write! Of course in your case there may be dozen of others incompatibilities. That was just a joke. :)