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) | |
| AMorA.M.orPMorP.M. | meridian indicator (uppercase) | |
| amora.m.orpmorp.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 | |
| BCorB.C.orADorA.D. | era indicator (uppercase) | |
| bcorb.c.oradora.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
| 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) |
| THorth | ordinal number suffix |
| V | shift specified number of digits (see notes) |
| EEEE | scientific notation (not implemented yet) |
