Postgres SQL

btim, length: (remove those records with invalid chars only like **()*%0_  )
select * from stocks
where length(btrim(description,' <>=*@_''-?%&()/":`.0'))>0
limit 10000

upper: (case sensitive)
select upper('aa')

replace:
replace('gary sham'.'sham','cen')

concatenate:
string||string

regex:
http://www.postgresql.org/docs/8.3/static/functions-matching.html

--remove price, g means replace all, no g means replace once
update aaa
set des = regexp_replace(des,E'\\$\s*[0-9,\\.]*','------------','g');

--get 1st blocks specified by regex
(regexp_matches(des,E'\\w+.*\\w+'))[1]

--get 5th separate blocks splitted by10+ dashes, regex for splitter
(regexp_split_to_array(des,E'\\-{10,}'))[5]

reverse:
https://wiki.postgresql.org/wiki/Reverse_string(supports only above 8.3)

data type:
http://www.postgresql.org/docs/9.3/static/datatype.html

string:
http://www.postgresql.org/docs/9.1/static/functions-string.html

operator:
http://www.postgresql.org/docs/6.3/static/c09.htm

vacuum space:
Vacuum city_table

Function:
call:
select run_proc()

define:
CREATE OR REPLACE FUNCTION run_proc() RETURNS void AS $func$
DECLARE
    func_body text;
BEGIN
    func_body := '
update descr d
set formatted_city=city
from loc c
where c.state=d.state' ;

    EXECUTE func_body;
END;
$func$ LANGUAGE plpgsql;

Function example:
--drop FUNCTION singular2plural(input text)
create FUNCTION singular2plural(input text) RETURNS text
LANGUAGE plpgsql IMMUTABLE STRICT AS $$
DECLARE
    result text = '';
    root text;
    suffix text = '';
    pluralres text = '';
    VOWELS text = 'aeiou';
BEGIN
  IF btrim(length(btrim(input)))=0 then
     return input;
  END IF;
  IF length(regexp_replace(btrim(input), E'(\\w+\\s*)*','','g'))>0 then
RAISE EXCEPTION 'singular2plural contains invalid character';
  END IF;

  SELECT  plural INTO pluralres
        FROM    aa_aberrant_plural_map
        WHERE   singular = $1;

  if pluralres<>'' then
      return pluralres;
  end if;
  root = input;
  if substring(input,length(input)) = 'y' AND strpos(VOWELS,substring(input,length(input)-1,1))=0 then
       root = substring(input,1,length(input)-1);
       suffix = 'ies';
  elsif substring(input,length(input)) = 's' then
 if strpos(VOWELS,substring(input,length(input)-2,1))>0 then
   if substring(input,length(input)-3)='ius' then
root = substring(input,1,length(input)-2);
suffix = 'i';
            else
                root = substring(input,1,length(input)-1);
                suffix = 'ses';
            end if;
          else
   suffix = 'es';
 end if;
  elsif substring(input,length(input)-1)='ch' or substring(input,length(input)-1)='sh' then
        suffix = 'es';
  else  suffix = 's';
  end if;
  result = root||suffix;
  RETURN result;
END$$;

Function example:
drop FUNCTION if exists aa_sp_cat();
CREATE FUNCTION aa_sp_cat() RETURNS void AS $$
   update q set city='New York' from cities c where cityid=5;
$$ LANGUAGE SQL;

Table options:
Create table products

( 
    productid integer,
    desc varying_character
) with (Appendonly=true, compresslevel=5, orientation=column)
Distributed by (productid);

appendonly=can only insert records, no delete/update
orientation=separate table by columns
partition=separate table into bulks of rows
Distributed=physical storage key

Impot data from file using psql:
create table city_table
(
   city character varying(100)
   ,stateabbr character varying(10)
)

1. Start  psql with the following command: psql.exe -h 10.10.10.10 -d databasename
2. CTP_4M->  \copy  public.city_table from C:\city.csv with csv header quote as '"'

in city.csv, column names: city, stateabbr