During last months I wrote some tools to help me with my daily duties.
I’d like to let you know you about them, as you might find them useful.
So, here we go: Read more »

EnterpriseDB, responsable de la base de datos PostgreSQL, ha anunciado una nueva modalidad cloud que estará disponible a través de Amazon Web Services y OpenStack. Read more »

Hace unos días una persona me dejó una duda a modo de comentario en la entrada de DBLinks en PostgreSQL. Esta duda es sencilla, y, a la vez interesante, por eso, he decidido crear una entrada explicando la solución.
La duda
¿Es posible crear una vista en PostgreSQL utilizando un DBLink con parámetros dinámicamente? Read more »

Si tenemos una base de datos con información importante, es imprescindible hacer copias de seguridad.
Para ello, con postgres, podemos utilizar el siguiente script: Read more »

Imagine that you have a postgresql server that is capable of handling 10 SQL queries over psql (Network TCP ) at once, without service degrading. ie the performance of postgresql does not slow down, and each query is fulfilled within an expected and acceptable time.
Imagine that as the number of concurrent SQL queries increases over and above 10, that the performance degrades and each query takes longer to fulfil. Eventually the queries take an excessive amount of time that is not acceptable for good service delivery. Often if we could simply queue the SQL queries (psql sessions) and allow only say 10 concurrent queries active at any given time, performance overal will be better. Read more »

If you’re a system administrator, chances are you use programs that interface with databases every day. One great advantage of open source software is that you can modify your applications’ code to customize it for your needs. If your application uses PostgreSQL on the back end, it’s not hard to access your database from a variety of languages. Here’s how to get started, whether your code is in C, C++, PHP, Tcl, Python, or Perl. Read more »

Olá Amigos!
Hoje eu vou tratar de um assunto da época da faculdade, o cálculo da previsão de vendas de um determinado produto, com base nas vendas anteriores, para tal iremos utilizar o PostgreSQL e PHP. Read more »

As much as I like Linux (and, really, I do: I ran Linux 0.99.something on my desktop in college, and wrote my class papers using vim and LaTeX), there are certain things about it that drive me crazy, and the way it reports memory usage is definitely on the list. It should be possible for a reasonably intelligent human being (in which category I place myself) to answer simple questions about system memory usage, such as “How much memory is my database using?” or “How much memory is my web server using?” relatively simply. Read more »

This is a code snippet for the select of a random record in a efficient and scalable way when using PostgreSQL: Read more »

En primer lugar sería recomendable, para todos los usuarios que quieran proseguir con la lectura de este pequeño manual, que echaran previamente un vistazo a Configuración básica, donde el webmaster introduce muchos de los conceptos que cualquiera que pretenda administrar esta base de datos necesitará conocer, y expone una visión global de los ficheros implicados en esta, en ocasiones, ardua tarea. Read more »

PostgreSQL has this interesting placeholder called anyelement which it has had for a long time and its complement anyarray. They are used when you want to define a function that can handle many types arguments or can output many types of outputs. They are particularly useful for defining aggregates, which we demonstrated in Who’s on First and Who’s on Last and several other aggregate articles.
Anyelement / anyarray can be used just as conveniently in other functions. The main gotcha is that when you pass in the first anyelement/anyarray all subsequent anyelement / anyarray must match the same data type as the first anyelement / anyarray. Read more »

Como sabrán .NET es un Framework de programación que en teoría pretende ser independiente de la plataforma y facilitar el desarrollo aunque en la practica esa portabilidad es un tanto cuanto artificial y depende del proyecto Mono para funcionar en más plataformas además de Windows, pero al menos esta estandarizado y permite crear aplicaciones multiplataforma aunque con algunas restricciones.
Npgsql es un .Net Data Provider (Proveedor de Datos) independiente de NET o Mono que permite a estos establecer conexiones a bases de datos PostGreSQL que son tan comunes. Así Npgsql es una alternativa a los ODBC para cuando no tenemos el control de estos o no nos dan los resultados esperados, y Npgsql para el desarrollador resulta muy similar en uso a ODBC. Read more »

Last time I discussed the basics of working with PL/Java. This time I’ll discuss working with lists. Read more »

For those who aren’t familiar with hstore, it’s a key/value storage type that is packaged as an extension or contrib in PostgreSQL 8.2+. In PostgreSQL 9.0 it got a little extra loving in several ways one of which was the introduction of the hstore(record) casting function that converts a record to an hstore. In this article, I’ll demonstrate how you can use this new casting function to do very sleek mail merges right in the database. The only caveat is that it seems to only correctly name the keys if it is fed a real table or view. Derived queries such as aggregates etc get keys named f1, f2, etc.
If you are on PostgreSQL 9.1 or above installing — hstore is just a CREATE EXTENSION hstore; sql command away. If you are on a lower version of PostgreSQL, you can usually find the hstore.sql in share/contribs. Read more »

PostgreSQL “Point-in-time Recovery” (PITR) also called as incremental database backup , online backup or may be archive backup. The PostgreSQL server records all users’ data modification transaction like insert, update or delete and write it into a file call write-ahead (WAL) log file. This mechanism use the history records stored in WAL file to do roll-forward changes made since last database full backup. Read more »

Hi,
Today, let me discuss some thing about PG Optimizer while taking a better execution plan.
Seq Scan (vs) Index Scan
———————————-
Both scans having its own pros and cons. Sometimes, seq scan will give you the better execution time and sometimes Index scan.
Let’s find out.. Read more »

Waiting for 9.2 – excluding data of table from dump Read more »

A veces queremos ejecutar un SQL dentro de una función (como en un trigger) en el que los nombres de los campos o de la tabla a consultar varian, es decir que es dinámico.
Si estamos usando el lenguaje procedimental de PostgreSQL (PL/pgSQL), esto puede hacerse a través de concatenación de cadenas de texto. Read more »

A veces es necesario poder copiar datos entre diferentes bases de datos, que incluso pueden residir en diferentes servidores. Postgresql proporciona una librería interesante para poder realizar dicho cometido. Read more »

In our article The Pure Relational database is dead there were a lot of misunderstandings as a result of our poor choice of words. People thought we were bashing the relational model because in their mind that was what pure meant. I got hit with a lot of poetic insults. I still can’t think of an alternative word to use for what I meant. Simple doesn’t really do it as even relational databases with just standard types were far from simple when you consider the planner and all the other stuff going on under the hood to protect you from the underlying storage structure. What I was trying to say is that in the beginning most relational databases just supported a standard set of types which you could not expand on and most people when they think relational today still think just that. That type of relational database is in my book dead or almost dead. Read more »