GET DIAGNOSTICS with COPY statement in Pl/pgsql function

Now GET DIAGNOSTIC will return the number of rows processed by COPY statement in a Pl/Pgsql function. COPY statement in Pl/Pgsql Function: CREATE OR REPLACE FUNCTION public.copy_data(fname text) RETURNS integer AS $$ declare copy_qry text; cnt integer; Begin copy_qry := 'copy t from'||quote_literal(fname)||' with CSV HEADER;'; Execute copy_qry; GET DIAGNOSTICS cnt = ROW_COUNT; return cnt; end; $$ Language plpgsql; Previous release: -bash-4.1$ psql psql.bin (9.2.3) Type "help" for help. postgres=# select copy_data('/usr/local/pg93beta/t_load.

Continue reading →

Disk page checksums to detect filesystem failures in PostgreSQL 9.3Beta 1

New feature introduced in PostgreSQL 9.3Beta 1 i.e. “Disk page checksums”. Thanks to author Ants Aasama and Simon Riggs, Jeff Davis,Greg Smith. In earlier releases, if there’s any data corruption block on disk it was silently ignored until any pointer arrives on it or some wrong results shown by the queries. Now, data corruption detected beforehand by triggering WARNING message instead of silently using or waiting for hit on the corrupted block.

Continue reading →

What if, import file (txt/csv) having "BOM-ed UTF-8" encoding?

So what is “UTF-8 BOM” mean ? its byte order mark for UTF-8, some bytes (0xEF,0xBB,0xBF) are added at the start of the file to indicate that the file having unicode characters in it. BOM Characters “9”. As per Unicode documentation, the presence of BOM in file are useless, because it causes problems with non-BOM-aware software’s to identify or parse the leading characters having at the start. Same has been quoted at the bottom of the Wikipedia page:

Continue reading →

Configuring .pgpass (Password File) on Windows/Linux/Solaris

PostgreSQL users access to the database goes through libpq library. It also has a feature of allowing automation for users without prompting password by keeping their details in .pgpass file. Lets see… Points to be considered when setting .pgpass file. 1. It should be created in postgres user home directory. 2. It should disallow any access-level to world or group. 3. Location can be controlled with PGPASSFILE environment variable. Syntax to follow in .

Continue reading →

psql client can be a chess board now...

Yes, pgChess extension now allows you to play a chess game with Postgres Instance(computer). Thanks to authors. Installation is very simple with pgxnclient. pgChess extension supported with another extension called pg2podg. Documentation Link: https://github.com/gciolli/pgChess/blob/master/doc/ pgxnclient install pgchess pgxnclient install pg2podg After installing, just run CREATE EXTENSION commands create extension pgchess; create extension pg2podg; Two types of play, (1) PC vs PC and (2) PC vs Human. To play a new game of PC vs Human run below file (download source from the above link for the files) in unaligned format of psql terminal.

Continue reading →