hubble数据库之特殊的语法形式

原创琳哥 2023-01-31

272

语法	相当于	描述
`AT TIME ZONE`	`timezone()`	时区
`CURRENT_CATALOG`	`current_catalog()`	现在使用的catalog
`COLLATION FOR`	`pg_collation_for()`	排序
`CURRENT_DATE`	`current_date()`	现在的日期(年,月,日)
`CURRENT_ROLE`	`current_user()`	现在的用户
`CURRENT_SCHEMA`	`current_schema()`	现在使用的schema
`CURRENT_TIMESTAMP`	`current_timestamp()`	现在的时间戳
`CURRENT_USER`	`current_user()`	现在的用户
`EXTRACT(<part> FROM <value>)`	`extract("<part>", <value>)`	从…中抽取部分内容
`OVERLAY(<text1> PLACING <text2> FROM <int1> FOR <int2>)`	`overlay(<text1>, <text2>, <int1>, <int2>)`	替换
`OVERLAY(<text1> PLACING <text2> FROM <int>)`	`overlay(<text1>, <text2>, <int>)`	替换
`SESSION_USER`	`current_user()`	session用户
`SUBSTRING(<text> FOR <int1> FROM <int2>))`	`substring(<text>, <int2>, <int1>)`	截取
`SUBSTRING(<text> FOR <int>)`	`substring(<text>, 1, <int>))`
`SUBSTRING(<text> FROM <int1> FOR <int2>)`	`substring(<text>, <int1>, <int2>)`
`SUBSTRING(<text> FROM <int>)`	`substring(<text>, <int>)`
`TRIM(<text1> FROM <text2>)`	`btrim(<text2>, <text1>)`	祛除字符或字符串。
`TRIM(<text2>, <text1>)`	`btrim(<text2>, <text1>)`
`TRIM(FROM <text>)`	`btrim(<text>)`
`TRIM(LEADING <text1> FROM <text2>)`	`ltrim(<text2>, <text1>)`
`TRIM(LEADING FROM <text> )`	`ltrim(<text>)`
`TRIM(TRAILING <text1> FROM <text2>)`	`rtrim(<text2>, <text1>)`
`TRIM(TRAILING FROM<text> )`	`rtrim(<text>)`
`USER`	`current_user()`	查询使用用户

select current_user();

 current_user  
+--------------+
   hubble

select extract(epoch from cast('2022-11-13 11:22:10'  as TIMESTAMP));

   extract  
+--------------+
1668338530

select 1668338530::TIMESTAMP;

       timestamp
-----------------------
  2022-11-13 11:22:10

从第1个位置开始截取，截取4个字符，返回结果:hubb

select  SUBSTRING('hubble', 1, 4);

   SUBSTRING  
+--------------+
   hubb

从8个位置开始截取，截取到最后一个字符

select SUBSTRING('information', 8);

   SUBSTRING  
+--------------+
   tion

正则表达式截取

select SUBSTRING('PostgreSQL' from 'gre' );

   SUBSTRING  
+--------------+
   gre

反向截取

select reverse(SUBSTRING(reverse('PostgreSQL'),1,2));

   reverse  
+--------------+
   QL

select position('om' in 'Thomas');

  position  
+--------------+
   3

select pg_client_encoding();

pg_client_encoding  
+--------------+
   UTF8

select regexp_replace('Thomas', '.[mN]a.', 'M');

regexp_replace 
+--------------+
   ThM

select repeat('hb', 4);

    repeat 
+--------------+
   hbhbhbhb

select trim(both 'x' from 'xTomxx');

  btrim
+--------------+
  Tom

select trim('   xTomxxn    ');

  btrim
+--------------+
  xTomxxn

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者