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