Tag Archives: PostgreSQL
Creating Delete Triggers in PostgreSQL
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 … Continue reading
Connection pooling with mod_perl
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 … Continue reading
How to change ownership of a PostgreSQL database
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 * … Continue reading
PostreSQL Upgrade Process
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 trust And prevent clients to access the database by … Continue reading
Rebuild Postgres for specific platform
# 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 … Continue reading
Creating a PostgreSQL datasource with ColdFusion MX
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: … Continue reading
How to delete records with duplicate fields, such as dupplicate emails?
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 … Continue reading
How to delete dupplicate records in DB2, Oracle, MySQL, and PostgreSQL
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 … Continue reading
PostgreSQL to DB2 export conversion
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 … Continue reading
Can’t edit PostgreSQL datasource in CFMX
The original URL where we found this solution is here: PostgreSQL DSN Config Bug Workaround for PostgreSQL DSN Config Bug How to fix a bug for PostgreSQL Data Source configuration in the ColdFusion MX Administrator. The author discovered the solution … Continue reading
