background preloader


Facebook Twitter

PostgreSQL General Bits Newsletter. This is PostgreSQL General Bits issue number 20!

PostgreSQL General Bits Newsletter

There have been 120 items covering about that many threads in pgsql-general. Those threads contained about 642 messages. But those numbers do not count all of the messages read, tests run and research done. I'd like to thank the active contributors to pgsql-general as well as the question askers for providing interesting and useful topics and information to write about.

I'd also like to thank the people who have given me excellent feedback and suggestions. With this issue, I would like to ask two things of you. PostgreSQL is so generous about formatting incoming dates that it creates a bit of a hazard. The DATESTYLE option is used primarily for output. Here PostgreSQL assumes that 13 cannot possibly be a month, therefore, it must be the day whether mm.dd.yy or is used as the input format. select '13.01.03'::timestamp; --> 2003-01-13 00:00:00 select '01.13.03'::timestamp; --> 2003-01-13 00:00:00. Creating Functions. There are several ways to create function with PL/pgSQL. You can use the editor of your choice to develop the code for the function. Once you have written the code, you can load the file with psql . The syntax for loading the file via psql is below. psql -f filename.sql database You can login into psql or other client application and execture a query with the CREATE FUNCTION command.

Example 9-1. CREATE FUNCTION first() RETURNS integer AS ' /*This is the body of a function. You can also use the \e option from psql and input your function into the edit buffer. Quick Guide to writing PLPGSQL Functions: Part 3 - NOTICES, RECU. In this third part of our PLPGSQL Quick Guide series, we shall delve into writing recursive functions.

Quick Guide to writing PLPGSQL Functions: Part 3 - NOTICES, RECU

Before we do that, we shall demonstrate a very important but trivial feature in PostgreSQL and that is the RAISE NOTICE feature. There are more elegant ways of debugging, but this is the simple brain dead way of doing so. RAISE Notices in plpgsql are generally used for two reasons: As a simple debugging tool to output state variables in a function call.As a WARNING to a user to inform them of important things such as this function is deprecated and should not be used or they are using something in an incorrect way. A simple example of notices and recursion is shown below. RAISE also has other variants namely DEBUG(1-5), LOG, INFO, EXCEPTION. How to check, if a value is an integer with plpgsql? - Stack Ove. Checking for the existence of a column in a table « Systems Engi. Posted by decipherinfosys on August 30, 2007 As DBAs or database developers, when we need to write up scripts that need to be rolled out from the development environment to QA or production environment, it is always a good idea to have non-destructive DDL scripts in place.

Checking for the existence of a column in a table « Systems Engi

One such scenario is an addition of a column to an existing table. We want to add a new column but first we want to make sure that the column does not exist in the table rather than getting an error at the time of running the script. In this blog post, we will show you how can we check for the existence of a column before adding it to the table using a small code snippet for Oracle and SQL Server.

Let us first create a table. How to optimize PostgreSQL database size. PostgreSQL is a powerful, open source relational database system.

How to optimize PostgreSQL database size

It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. One of the PostgreSQL's most sophisticated features is so called Multi-Version Concurrency Control (MVCC), a standard technique for avoiding conflicts between reads and writes of the same object in database. MVCC guarantees that each transaction sees a consistent view of the database by reading non-current data for objects modified by concurrent transactions. Thanks to MVCC, PostgreSQL has great scalability, a robust hot backup tool and many other nice features comparable to the most advanced commercial databases.

Official Docs

PostgreSQL 8.3 PLPGSQL Cheatsheet. MySQL vs PostgreSQL. MySQL vs PostgreSQL is a decision many must make when approaching open-source relational database management systems.

MySQL vs PostgreSQL

Both are time-proven solutions that compete strongly with proprietary database software. MySQL has long been assumed to be the faster but less full-featured of the two database systems, while PostgreSQL was assumed to be a more densely featured database system often described as an open-source version of Oracle.

MySQL has been popular among various software projects because of its speed and ease of use, while PostgreSQL has had a close following from developers who come from an Oracle or SQL Server background. These assumptions, however, are mostly outdated and incorrect. MySQL has come a long way in adding advanced functionality while PostgreSQL dramatically improved its speed within the last few major releases. [edit] Architecture PostgreSQL is a unified database server with a single storage engine.

[edit] Performance [edit] Beginnings [edit] Raw Speed.