Posted on Monday, 3rd August 2009 by Balazs
One of the common data integrity issues that can happen in a database is the unintended deletion of a row. Here is how to create a DELETE trigger in PostgreSQL.
The example code below assumes you have a “customer_cus” table and a “customer_archive_cua” table with at least two fields. It is a good idea [...]
Tags: PL/PGSQL, PostgreSQL, SQL, Trigger
Posted in DB2, Technology | Comments (Comments)
Posted on Sunday, 31st May 2009 by Balazs
I found this info in the PostgreSQL archives. Here are 2 methods:
Best method from Dan Lyke: Apache::DBI will pool across Perl programs, and you don’t have to change anything in your scripts.
Next best method from Gilles DAROLD: in your perl script use the following code
use vars qw($dbh);
$dbh ||= DBI::connect($datasrc, $dbuser, $dbpwd);
These can be use [...]
Tags: Apache, DB2, DBI, IBM, mod_perl, PostgreSQL
Posted in DB2, Perl | Comments (Comments)
Posted on Sunday, 31st May 2009 by Balazs
To change the owner of a database we have found this solution:
UPDATE pg_database SET datdba=(SELECT usesysid FROM pg_shadow WHERE usename=’new_owner’) WHERE datname=’db_name’;
http://archives.postgresql.org/pgsql-admin/2003-07/msg00301.php
* From: Tom Lane
* To: Devrim GUNDUZ
* Subject: Re: changing ownership of db
* Date: Tue, 29 Jul [...]
Tags: PostgreSQL
Posted in Technology | Comments (Comments)
Posted on Sunday, 31st May 2009 by Balazs
First you need to change permissions on the localhost to trust:
# cd /var/lib/pgsql/data
# vi pg_hba.conf
Change the autorization/authentication for the line that starts with “local” to trust:
local all [...]
Tags: PostgreSQL
Posted in Technology | Comments (Comments)
Posted on Sunday, 31st May 2009 by Balazs
# rpm -i postgres-[version number].src.rpm
# rpmbuild -bb –target i686 /usr/src/redhat/SPECS/postgresql.spec
If you get a problem with unpackaged files, use the following trick:
vi /usr/lib/rpm/macros
An modify some of the lines as follows:
#
# Script gets packaged file list on input and buildroot as first parameter.
# Returns list of unpackaged files, i.e. files in $RPM_BUILD_ROOT not packaged.
#
# Note: Disable (by [...]
Tags: PostgreSQL
Posted in Technology | Comments (Comments)
Posted on Sunday, 3rd May 2009 by Balazs
To set up a PostgreSQL datasource for ColdFusion (CFMX), follow these steps:
Open CFMX administrator
Click on “Data Sources”
In the “add new datasource” write in a name and select “other” before clicking “add”
Fill our the fields as follows:
CF Data Source Name: JDBC
URL: jdbc:postgresql://:5432/
Driver Class: org.postgresql.Driver
Driver Name: PostgreSQL
Username: db_user_name
Password: ******
Description:
Click submit
Test connection (Status shoudl say “OK”)
Share and Enjoy:
Tags: CFMX, Cold Fusion, PostgreSQL
Posted in Java | Comments (Comments)
Posted on Sunday, 3rd May 2009 by Balazs
To delete records where only a field is duplicated, we can use a similar technique to the general duplicate removal technique:
CREATE TABLE temp AS SELECT DISTINCT ON (email) * FROM table_to_deduplicate;
DROP TABLE table_to_deduplicate;
ALTER TABLE temp RENAME TO table_to_deduplicate;
Be mindful of the fact that this process will not preserve the constraints on the original table. So [...]
Tags: DB2, IBM, MySQL, Oracle, PostgreSQL
Posted in DB2, Technology | Comments (Comments)
Posted on Sunday, 3rd May 2009 by Balazs
To delete dupplicate records in SQL, the following sequence of commands will do the trick:
CREATE TABLE temp AS SELECT DISTINCT * FROM table_to_deduplicate;
DROP TABLE table_to_deduplicate;
ALTER TABLE temp RENAME TO table_to_deduplicate;
Be mindful of the fact that this process will not preserve the constraints on the original table. So if you have indexes, NOT NULL attributes, primary [...]
Tags: DB2, IBM, MySQL, Oracle, PostgreSQL
Posted in DB2, Technology | Comments (Comments)
Posted on Thursday, 26th March 2009 by Balazs
PostgreSQL and DB2 use different styles of conventions to represent NULL. In PostgreSQL, the convention is to have “N” in the data to be loaded with the COPY command. In DB2, the LOAD command can be set to consider and empty field as NULL. Thus, to convert from DB2 to POstgreSQL style [...]
Tags: DB2, IBM, PostgreSQL
Posted in DB2 | Comments (Comments)