-
Recent Posts
Recent Comments
Categories
Meta
PostgreSQL and soap / xpath
Recently I have been working on integrating soap into our postgresql database. Its not something easily built into postgresql, so I wrote some plpgsql functions to achieve the same purpose. — Create a XML request for uploading CREATE OR REPLACE … Continue reading
Posted in Database, PostgreSQL, Programming
Comments Off
How to set Postgresql money type to use 3 decimal places in Windows
LINUX ===== Under Linux, type command in postgres database: set lc_monetary to ‘ar_BH.utf8′; ar_BH represents Arabic (Bahrain) locale, the above command change the locale monetary setting on-the-fly in postgresql database, then let do a test: select 34.888::text::money; You will get … Continue reading
Posted in Database, Linux, PostgreSQL, Windows
Comments Off
PostgreSQL Money / Numeric localisation
Living in a country, where our currency symbol is the $ x.00, I wasn’t aware of the vast differences between countries and how they deal with currency. France uses , instead of . for decimals. Germany uses a space instead … Continue reading
Outputting from Postgres to CSV
\f ‘,’\a\t\o /home/john/moocow.csvSELECT foo,bar FROM whatever;\o\q If a field has newlines, this will break. You can do something like this instead….. SELECT foo, bar, ‘”‘ || REPLACE(REPLACE(field_with_newilne, ‘\n’, ‘\\n’), ‘”‘, ‘”"‘) || ‘”‘ FROM whatever; Then you … Continue reading
Posted in Database, PostgreSQL, Programming
Comments Off
change postgresql time zone for west Australia
In west Australia, like Perth, when you are using postgresql database, you may need to change the time zone abbreviations to Australia: change it for a session postgres=# set timezone_abbreviations to ‘Australia’; change it for database postgres=# alter database DATABASENAME … Continue reading
Posted in Database, PostgreSQL
Comments Off
psql: FATAL: Ident authentication failed for user "username" Error and Solution
Q. I’ve installed Postgresql under Red Hat Enterprise Linux 5.x server. I’ve created username / password and database. But when I try to connect it via PHP or psql using following syntax: psql -d myDb -U username -W It gives … Continue reading
Posted in Database, Linux, PostgreSQL
Comments Off
Regular Expressions in PostgreSQL
Every programmer should embrace and use regular expressions (INCLUDING Database programmers). There are many places where regular expressions can be used to reduce a 20 line piece of code into a 1 liner. Why write 20 lines of code when … Continue reading
Posted in Database, PostgreSQL
Comments Off