openGauss插件使用指南:GUC参数说明（2）

最新推荐文章于 2024-07-23 09:36:44 发布

openGauss小助手

最新推荐文章于 2024-07-23 09:36:44 发布

阅读量179

点赞数 3

文章标签：数据库 openGauss

本文链接：https://blog.csdn.net/weixin_53596073/article/details/140485653

版权

示例：

--创建表test1。
openGauss=# CREATE TABLE test1
(
  a1 smallint not null,
  a2 int not null,
  a3 bigint not null,
  a4 float not null,
  a5 double not null,
  a6 numeric not null,
  a7 varchar(5) not null
);
CREATE TABLE
--向表中插入记录失败。
openGauss=# insert into test1(a1,a2) values(123412342342314,3453453453434324);
ERROR:  smallint out of range
CONTEXT:  referenced column: a1
--查询表失败
openGauss=# select a1,a2 from test1 group by a1;
ERROR:  column "test1.a2" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select a1,a2 from test1 group by a1;

--向表中插入记录成功。
openGauss=# set dolphin.sql_mode = '';
SET
openGauss=# insert into test1(a1,a2) values(123412342342314,3453453453434324);
WARNING:  invalid input syntax for numeric: ""
CONTEXT:  referenced column: a6
WARNING:  smallint out of range
CONTEXT:  referenced column: a1
WARNING:  integer out of range
CONTEXT:  referenced column: a2
INSERT 0 1
--查询表成功
openGauss=# select a1,a2 from test1 group by a1;
  a1   |     a2
-------+------------
 32767 | 2147483647
(1 row)

--删除表
openGauss=# DROP TABLE test1;
DROP TABLE

--ansi_quotes效果展示
openGauss=# create database test_db dbcompatibility 'B';
CREATE DATABASE
openGauss=# \c test_db 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "test_db" as user "luozihao".
test_db=# show dolphin.sql_mode ;
                                           dolphin.sql_mode                                           
------------------------------------------------------------------------------------------------------
 sql_mode_strict,sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date,pad_char_to_full_length
(1 row)

test_db=# create table test(a varchar(20));
CREATE TABLE
test_db=# insert into test values('test');
INSERT 0 1
test_db=# select "a" from test;
  a   
------
 test
(1 row)

test_db=# set dolphin.sql_mode to 'sql_mode_strict,sql_mode_full_group,pipes_as_concat,no_zero_date,pad_char_to_full_length';
SET
test_db=# select "a" from test;
 ?column? 
----------
 a
(1 row)

-- block_return_multi_results 效果展示 

test_db=# set dolphin.sql_mode to 'block_return_multi_results';
SET
test_db=# create table tab_1143768(id int,pid int,a1 char(8));
CREATE TABLE
test_db=# insert into tab_1143768 values(1,2,'s'),(2,3,'b'),(3,4,'c'),(4,5,'d');
INSERT 0 4
--创建存储过程，包含查询语句

test_db=# create or replace procedure pro_1143768()
 as
 begin
 select * from tab_1143768 ;
 end;
 /
CREATE PROCEDURE
test_db=# call pro_1143768();
 id | pid | a1
----+-----+----
  1 |   2 | s
  2 |   3 | b
  3 |   4 | c
  4 |   5 | d
(4 rows)

CALL