====== PostgreSQL Notes ======
Some notes as I learn PostgreSQL bit by bit.

===== Copy Table =====
<code>
1) Buat copy table asal ke temporary table
CREATE TABLE temp AS SELECT col1, col2 FROM table_lama

2) Drop table asal
DROP TABLE table_lama

3) Create structure table baru
CREATE TABLE table_baru ( col1 type(xx), col2 type(xx) );

4) insert data dari table temporary ke table baru
INSERT INTO table_baru SELECT FROM temp 
</code>
http://forums.ittutor.net/index.php?showtopic=24587

===== Function =====
<code sql>
CREATE FUNCTION data_lpm(varchar) RETURNS SETOF lpm_pelajar AS '
    SELECT * FROM lpm_pelajar WHERE no_kp_baru = $1;
    ' LANGUAGE SQL;
</code>

===== Trigger =====
First, create the function:-
<code sql>
create or replace function test() returns trigger as $test$
    BEGIN
        NEW.total_price := calc_itemprice(NEW.item_id, NEW.qty);
        return NEW;
    END;
$test$ language plpgsql;
</code>

create the trigger:-
<code sql>
create trigger calc_itemprice before insert or update 
    on order_items for each row 
    execute procedure test();
</code>

and this is the function that was called inside the trigger function:-
<code sql>
create or replace function calc_itemprice(integer, integer) returns float as
    'select price * $2 from items where id = $1'
    language sql immutable;
</code>

===== psql command =====
  * \x turn on extended display. usefull if the result is too long

===== Alter Column Type =====
in postgres you can't so easily change column type.. the documentation of 'alter table' there's no word about changing column type, as if they didn't want to talk about it :)
for a long time i thought it was only possible by recreating the table, but i found something like this in the faq

To change the data type of a column, do this:
<code sql>
BEGIN;
ALTER TABLE tab ADD COLUMN new_col new_data_type;
UPDATE tab SET new_col = CAST(old_col AS new_data_type);
ALTER TABLE tab DROP COLUMN old_col;
COMMIT;
</code>
You might then want to do VACUUM FULL tab to reclaim the disk space used by the expired rows.
http://www.dbforums.com/archive/index.php/t-1051977.html

Just discover that we can't change type varchar to integer this way. you need to convert the varchar type to text first.

<code sql>
update table set colname = old_col::text::integer
</code>

===== Format Date =====
<code sql>
select to_char(date_column, 'DD-Mon-YYYY') from table_name;
</code>

===== Alternative to Not In =====
<code sql>
select * from user
  where userid not in (
    select userid from authorized_user
  )
</code>

this query would become *slow* if authorized_user is a very
large table since seq scan is used for the inner query. the
alternate way is:
<code sql>
select * from user
  where userid not exists (
    select 1 from authorized_user
      where userid = user.userid
  )
</code>
in this query, rather than selecting the whole row of inner
query looking for one that do not exist, we just check for
true/false whether it exist or not. given table authorized_user
has an index on userid, this query would be faster since index scan
is used.

FIXME: The second form cannot be used if query (outer ??) has a
order by / limit clause.

Note: some guys on irc told me that this have been fixed in postgresql 8.0,
means Not In would be much faster.

===== Listing constraints =====
<code sql>
select n.nspname || '.' || c.relname as table_name,
   con.conname as constraint_name,
   pg_get_constraintdef( con.oid, false) as constraint_def
    from pg_constraint con
         join pg_namespace n on (n.oid = con.connamespace)
         join pg_class c on (c.oid = con.conrelid)
   where con.conrelid != 0 and
     con.contype = 'f';
</code>
ref: http://www.varlena.com/varlena/GeneralBits/116.php