System Columns in Postgresql

In postgresql ever table has several system columns that are implicitly defined by the system. Therefore, these names cannot be used as names of user-defined columns. 

oid

The object identifier (object ID) of a row. This column is only present if the table was created using WITH OIDS, or if the default_with_oids configuration variable was set at the time. This column is of type oid (same name as the column).

tableoid

The OID of the table containing this row. This column is particularly handy for queries that select from inheritance hierarchies, since without it, it's difficult to tell which individual table a row came from. The tableoid can be joined against the oid column of pg_class to obtain the table name.

xmin

The identity (transaction ID) of the inserting transaction for this row version. (A row version is an individual state of a row; each update of a row creates a new row version for the same logical row.)

cmin

The command identifier (starting at zero) within the inserting transaction.

xmax

The identity (transaction ID) of the deleting transaction, or zero for an undeleted row version. It is possible for this column to be nonzero in a visible row version. That usually indicates that the deleting transaction hasn't committed yet, or that an attempted deletion was rolled back.

cmax

The command identifier within the deleting transaction, or zero.

ctid

The physical location of the row version within its table. Note that although the ctid can be used to locate the row version very quickly, a row's ctid will change each time it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier. The OID, or even better a user-defined serial number, should be used to identify logical rows.

PostgreSQL vs. SQL Server, Oracle: Enterprise-ready and able to compete

Why are you paying so much in licensing costs and annual maintenance when you could use PostgreSQL for free, and get community support and upgrades for free as well? This is the question that Neil Matthew and Richard Stones pose to smaller companies regarding their less critical applications.

More on PostgreSQL: Why PostgreSQL can best SQLServer, Oracle Face-off: MySQL, PostgreSQL and SQL Server go head to head

Matthew and Stones, authors of Apress' Beginning Databases with PostgreSQL, discuss the advantages and drawbacks of using this open source relational database management system instead of Microsoft SQL Server or Oracle Database Management System. They also explain why the choice of database toolsets dictates database choices.

What are the most compelling reasons to dump or keep Microsoft SQL Server and bring in PostgreSQL?

Neil Matthew: For applications where downtime seriously threatens a company's financial results, executives are always going to want to deal with a big player who has the resources -- both technical and financial -- to 'be there' should things go wrong. The risk involved in the failure of a company's invoice and payment system for any significant period of time is huge. In an emergency, having companies the size of Microsoft or Oracle to call on may significantly mitigate that risk.

For many smaller, less critical applications, ask yourself why you are paying so much in licensing costs and annual maintenance when you could use PostgreSQL for free, and also get community support and upgrades free?

How does PostgreSQL's feature set compare to that of proprietary databases?

Richard Stones: In terms of standard SQL support, it's very good indeed. If a feature is in the SQL92 standard, you can be pretty sure that PostgreSQL is going to support it correctly.

Normally, Neil and I advise developers to stay away from extensions to the SQL standard in any case.

What are the advantages and weaknesses of PostgreSQL?

Stones: Technically, the main disadvantages of using PostgreSQL are in three areas. First, the ability to write functions and stored procedures is somewhat more limited than you would get with Oracle's PL/SQL or Sybase's T-SQL. Unless you are doing some extremely sophisticated work in stored procedures, this is not a major limitation.

Secondly, features for very large databases like table spaces, partitioned tables and highly complicated locking are still strongest in the proprietary databases vendors' offerings; however, PostgreSQL is moving forward in these areas all the time.

Finally, proprietary development tools are stronger. Microsoft particularly has excellent tools, which not surprisingly work best with a Microsoft product set. This toolset advantage does have an effect on the choice of database product.

Matthew: The advantages of PostgreSQL are cost and the ability to look at the source code to understand what's going on. Very few developers will ever make changes to the source or, even better, submit fixes. I do believe the ability to examine the code in order to understand why something doesn't behave as you expect is a great benefit.

Is PostgreSQL capable of competing, feature-wise, with Microsoft SQL Server or Oracle? It is enterprise ready?

Stones: In my opinion, PostgreSQL is enterprise ready. For many uses, PostgreSQL is just as suitable as Microsoft SQL Server or Oracle, but with a big cost advantage. The features that you need 95% of the time are there and work as expected. The underlying engine is very stable and copes well with a good range of data volumes. It also runs on your choice of hardware and operating system, not just whatever some big vendor might insist you buy to run your database .

Neil: Absolutely. Of course, it's not the solution to all database needs, any more than any other vendor's product would be. For a large, multi-terabyte data warehouse you still need a specialized database product with some advanced features, specifically for handling those kinds of data volumes.

What are some situations where PostgreSQL might be used in conjunction with Microsoft SQL Server or Oracle or MySQL?

Matthew: A wide range of needs naturally leads to a choice of solutions. By sticking to standard SQL92 functionality, companies can mix and match their database solutions to best fit the problem while minimizing costs and complexity. You can have large, expensive highly featured products for large complex problems and cost-effective, but still very reliable, products for more everyday needs.

Stones: It's difficult to see why you would use MySQL and PostgreSQL side-by-side. MySQL has historically traded some functionality for performance. For most purposes, you don't need to make that trade, and PostgreSQL performance is more than adequate.

Make Database Template in postgresql using php

Views

SELECT table_name,view_definition from information_schema.views where table_catalog='$db_name' and table_schema='$schema_name'  order by 1;

Tables 
SELECT table_name From information_schema.tables where table_type='BASE TABLE'  and table_catalog='$db_name' and table_schema='$schema_name'  order by 1;

Columns in a Table with properties

SELECT column_name,data_type,is_nullable from information_schema.columns where table_catalog='$db_name' and table_schema='$schema_name' and table_name='$table_name';


Functions 
SELECT routine_name,routine_definition from information_schema.routines where routine_schema='$schema_name' and routine_catalog='$db_name' order by 1;

sorting in postgresql

When I was working on a query I wrongly typed the where condition as order by then i found it accepts and gives result by sorting on the order of values i have given check below , We can use this cool feature for sorting columns like month week days etc., instead of storing print order we can use this (after checking the performance factor)

Example : 

SELECT leather_type_id as a,leather_type_name from _mleather_type order by leather_type_id in (200795,200794,200796) desc;

   a    | leather_type_name

--------+-------------------

 200795 | Goat

 200796 | Pig

 200794 | Cow

 200797 | Sheep

 202082 | Reptile

 200190 | Hair Sheep

 203319 | touchleather

 203320 | cow touch leather

 200193 | Others

 200793 | Buffalo