PostgreSQL Notes

Some notes as I learn PostgreSQL bit by bit.

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 

http://forums.ittutor.net/index.php?showtopic=24587

CREATE FUNCTION data_lpm(varchar) RETURNS SETOF lpm_pelajar AS '
    SELECT * FROM lpm_pelajar WHERE no_kp_baru = $1;
    ' LANGUAGE SQL;

First, create the function:-

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;

create the trigger:-

CREATE TRIGGER calc_itemprice before INSERT OR UPDATE 
    ON order_items FOR each row 
    execute procedure test();

and this is the function that was called inside the trigger function:-

CREATE OR REPLACE FUNCTION calc_itemprice(integer, integer) returns float AS
    'select price * $2 from items where id = $1'
    LANGUAGE sql immutable;

• \x turn on extended display. usefull if the result is too long

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:

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;

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.

UPDATE TABLE SET colname = old_col::text::integer

SELECT to_char(date_column, 'DD-Mon-YYYY') FROM table_name;

SELECT * FROM user
  WHERE userid NOT IN (
    SELECT userid FROM authorized_user
  )

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:

SELECT * FROM user
  WHERE userid NOT EXISTS (
    SELECT 1 FROM authorized_user
      WHERE userid = user.userid
  )

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.

: 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.

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';

ref: http://www.varlena.com/varlena/GeneralBits/116.php