postgresql常用功能

发布时间：2020-12-13 16:59:59 所属栏目：百科来源：网络整理

导读：postgresql使用的版本是通过安装包名称或者select version()函数可以查看本地使用的postgresql的版本。比如我本地使用select version()后可以看到我本地使用的是 PostgreSQL 9.2.19,compiled by Visual C++ build 1600,64-bit 1、根据某个字段分组并查询 S

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

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)
decimal point (uses locale)
`G`	group separator (uses locale)
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)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!