postgresql常用功能

postgresql使用的版本是

通过安装包名称或者select version()函数可以查看本地使用的postgresql的版本。

比如我本地使用select version()后可以看到我本地使用的是

PostgreSQL 9.2.19, compiled by Visual C++ build 1600, 64-bit

1、根据某个字段分组并查询

SELECT * FROM tableA where columnA in(
select DISTINCT(columnA) from tableA
GROUP BY columnA)
and columnB in(
SELECT "max"(columnB) FROM tableA GROUP BY columnA
)

2、日期格式化

https://www.postgresql.org/docs/8.1/static/functions-formatting.html

select to_char(now(), 'YYYYDDMMHH24MISS')

日期格式说明

Pattern	Description
HH	hour of day (01-12)
HH12	hour of day (01-12)
HH24	hour of day (00-23)
MI	minute (00-59)
SS	second (00-59)
MS	millisecond (000-999)
US	microsecond (000000-999999)
SSSS	seconds past midnight (0-86399)
AM or A.M. or PM or P.M.	meridian indicator (uppercase)
am or a.m. or pm or p.m.	meridian indicator (lowercase)
Y,YYY	year (4 and more digits) with comma
YYYY	year (4 and more digits)
YYY	last 3 digits of year
YY	last 2 digits of year
Y	last digit of year
IYYY	ISO year (4 and more digits)
IYY	last 3 digits of ISO year
IY	last 2 digits of ISO year
I	last digits of ISO year
BC or B.C. or AD or A.D.	era indicator (uppercase)
bc or b.c. or ad or a.d.	era indicator (lowercase)
MONTH	full uppercase month name (blank-padded to 9 chars)
Month	full mixed-case month name (blank-padded to 9 chars)
month	full lowercase month name (blank-padded to 9 chars)
MON	abbreviated uppercase month name (3 chars)
Mon	abbreviated mixed-case month name (3 chars)
mon	abbreviated lowercase month name (3 chars)
MM	month number (01-12)
DAY	full uppercase day name (blank-padded to 9 chars)
Day	full mixed-case day name (blank-padded to 9 chars)
day	full lowercase day name (blank-padded to 9 chars)
DY	abbreviated uppercase day name (3 chars)
Dy	abbreviated mixed-case day name (3 chars)
dy	abbreviated lowercase day name (3 chars)
DDD	day of year (001-366)
DD	day of month (01-31)
D	day of week (1-7; Sunday is 1)
W	week of month (1-5) (The first week starts on the first day of the month.)
WW	week number of year (1-53) (The first week starts on the first day of the year.)
IW	ISO week number of year (The first Thursday of the new year is in week 1.)
CC	century (2 digits)
J	Julian Day (days since January 1, 4712 BC)
Q	quarter
RM	month in Roman numerals (I-XII; I=January) (uppercase)
rm	month in Roman numerals (i-xii; i=January) (lowercase)
TZ	time-zone name (uppercase)
tz	time-zone name (lowercase)

字符串转数字

SELECT to_number('22222.555555555', '99G99D99S');

转换后是2222.55

Pattern	Description
9	value with the specified number of digits
0	value with leading zeros
. (period)	decimal point
, (comma)	group (thousand) separator
PR	negative value in angle brackets
S	sign anchored to number (uses locale)
L	currency symbol (uses locale)
D	decimal point (uses locale)
G	group separator (uses locale)
MI	minus sign in specified position (if number < 0)
PL	plus sign in specified position (if number > 0)
SG	plus/minus sign in specified position
RN	roman numeral (input between 1 and 3999)
TH or th	ordinal number suffix
V	shift specified number of digits (see notes)
EEEE	scientific notation (not implemented yet)

查询一个表中的所有字段

select * from information_schema.columns
where table_schema='public' and table_name='users';

判断一个表中是否有该字段，如果没有则需要增加

-- Function: granttable(character varying)


-- DROP FUNCTION granttable(character varying);


CREATE OR REPLACE FUNCTION granttable(tab character varying)
  RETURNS integer AS
$BODY$
DECLARE i integer;
declare createtab character varying := '';
BEGIN
  i = (select count(*) from information_schema.columns
where table_schema='public' and table_name='table_name' and column_name =  tab );
  if (i=0) then
   createtab := 'ALTER TABLE table_name ADD COLUMN ' || tab || ' integer';
   execute createtab;
   return 1; 
  end if ;return 0;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION granttable(character varying) OWNER TO postgres;


select granttable('test1')

删除函数需要传参数，因为函数允许重载

drop function granttable(tab character varying);

判断角色是否存在

-- Function: grantroletable(character varying)


-- DROP FUNCTION grantroletable(character varying);


CREATE OR REPLACE FUNCTION grantroletable(tab character varying,tabOrView character varying)
  RETURNS integer AS
$BODY$
DECLARE i integer;
declare createtab character varying := '';
BEGIN
  i = (SELECT count(*) FROM pg_roles where rolname = 'test');
  if (i>0) then
   createtab := 'ALTER '|| tabOrView || ' ' || tab || ' OWNER TO test';
   execute createtab;
   createtab := 'GRANT ALL ON table ' || tab || ' TO postgres';
   execute createtab;
   createtab := 'GRANT ALL ON table ' || tab || ' TO test';
   execute createtab;
   return 1; 
  end if ;return 0;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION grantroletable(character varying,character varying) OWNER TO postgres;


select grantroletable('view_test','view');

drop function grantroletable(tab character varying,tabOrView character varying);

保留小数点

    to_number(‘63.33333333636’, '999999999999G99999999999D99999999S',text) ,

保留小数点 

保留两位小数

round(1.222222222,2);  

打印出单引号

使用两个单引号就可以了

比如'''' 这样就可以出来一个单引号了。