PostgreSQL Data Type Formatting Functions

The PostgreSQL formatting functions provide a powerful set of tools for converting various data types (date/time, integer, floating point, numeric) to formatted strings and for converting from formatted strings to specific data types. Table below list them.  These functions all follow a common calling convention: the first argument is the value to be formatted and the second argument is a template that defines the output or input format.

The to_timestamp function can also take a single double precision argument to convert from Unix epoch to timestamp with time zone. (Integer Unix epochs are implicitly cast to double precision.)

Fetch first row in DB2

Today I face a problem: If I select data using order by transaction_date, the result will be ordered by date (ascending by default) an row count may be more than 1. This is query that I’m use:

SELECT * FROM transaction ORDER BY transaction_date

But, cost (of I/O) of this query is too big.. this query execution will need very long time depend on how many data in table Transaction. I don’t want use this query, but I need the latest data transaction, I need first row only.

In DB2, we can simplify this matter, just modify query above to be like this:

SELECT * FROM transaction ORDER BY transaction_date FETCH FIRST ROW ONLY

Using this new query, cost of I/O will be small, and query execution will be so fast

TRIM in DB2

Today I want to try how to TRIM in select, using database DB2.
We can use function RTRIM (for TRIM right space) or LTRIM (TRIM left space).

For example.. assume that we have column field1 in table transactions with datatype varchar and have value ‘   hello’:

SELECT LTRIM(field1) FROM transactions

Cobe above will return ‘hello’ respectively. So simple

How to change PostgreSQL root password

1. Find file pg_hba.conf. its here:
   C:\Program Files\PostgreSQL\9.1\data
2. Open, and modifiy this line:
   #this for IPv4
   host all all 127.0.0.1/32 trust
   #this for IPv6
   host all all ::1/128 trust
3. Restart your PC
4. After that, login to database as postgres. Note that ‘postgres’ is root user. Now you won’t be asked for the password
5. execute this query:
   ALTER USER postgres WITH PASSWORD ‘<your-new-password>’;
   example:
   ALTER USER postgres WITH PASSWORD ‘bayu’;