PostgreSQL
PostgreSQL Cheat Sheet
PostgreSQL is one of our favorite database engines for a variety of reasons. Here is our cheat sheet to help you get online and get around Postgres with minimal effort. Database Access Security Database security is handled primarily in two place, from the system service level via a file called pg_hba.conf and within the database metadata files themselves. The pg_hba.conf file controls what level of credentials are needed based on what IP address the requesting connection is coming from. The metadata within the engine itself generally controls user level access once they are connected and approved at the system level. Systemwide Configuration via pg_hba.conf This file matches IP address with a set of rules to determine how much data you need to provide in the first place before getting access to the database engine. It includes the IP address, the username ...
0 Comment | Posted in Database,PostgreSQL,blog
July 04, 2010
Analyzing Queries in Postgres
Yesterday I was curious if there was a good way to write my IDS query using a single 'select'. All of us had the right idea of using a sub-query to limit the results from the join, although there were different ideas about how to use a sub-query. Richard's solution was the most succinct. However, there is an important follow-up lesson that I want to talk about. What I asked yesterday is representative of something we all seem to ask ourselves consistently: "What is the most clever way I can do this?" When it comes to database queries I believe we have a tendency to see how much we can accomplish in one swoop. At the heart of this tendency is the assumption that more queries equals less efficiency. Please strive to constantly challenge this assertion. Our thoughts about software should be continuously reinforced by demonstrable ...
0 Comment | Posted in PostgreSQL,SQL,blog
January 22, 2010
Data Translation Service with IDS
Cyber Sprocket Labs first came across Inlet Data Systems (IDS) in January, 2005 via a simple Guru posting asking for assistance "converting data from one proprietary format to another". The objective was to created an automated process by which a client could upload a file which would be automatically processed by the system & put it into a customer inbox for later retrieval in their required format. The System Today Over the years this system has grown into a fully automated, fully customizable web portal for sharing and translating data between clients. The system now supports over two dozen input & output file formats from a variety of sources. New file layouts can be created and managed via a 100% web driven interface. The administrator can select from several base files types such as CSV, XML, printed report layouts, fixed length, and multi-record fixed length formats. A simple data mapping application allows ...
Comments Off | Posted in Perl,PostgreSQL,System Administration,portfolio
January 05, 2010
PostgreSQL Stored Procedures Part 1
Stored procedures are a great way to enforce business and data access logic in a complex environment. Often with larger projects you will have multiple applications architected by a varied set of professionals all interacting with the same data sets. Stored procedures are a tried and true way to regain some control over those data sets and ensure standards compliance. The following paragraphs will show the basic syntax and some simple examples for using stored procedures in PostgreSQL. Syntax CREATE [ OR REPLACE ] FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [ RETURNS rettype ] { LANGUAGE langname | IMMUTABLE | STABLE | VOLATILE | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | COST execution_cost | ROWS result_rows | SET configuration_parameter { TO value | = value | FROM ...
0 Comment | Posted in Database,PostgreSQL,SQL,blog
November 10, 2009
PostgreSQL Introduction
Intro Possibly the most robust freeware DB on earth. Some argue better than MySQL even though MySQL has a much larger following. From the makers PostgreSQL is a powerful, open source relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL92 and SQL99 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among ...
0 Comment | Posted in Database,PostgreSQL,blog
May 08, 2009
Postgres::Handler
Our local updated perl docs. These enhanced notes will be published on our next push to CPAN.
0 Comment | Posted in Database,Perl,PostgreSQL,Programming Languages,blog
May 08, 2008
