分类：postgreSQL

2021
01-08

postgresql合并string_agg函数的实例

postgresql合并string_agg函数的实例 1有时候我们会需要将多条数据根据一些特别的字段做一些合并。比如下面这个查询，正常会查询出3条数据，但是我们会希望根据create_by分成两列显示2这时候需要用到string_agg函数，先通过groupby分组，在进行合并，当然查询结果需要满足groupby的限制；sql语句：selectcreate_by,string_agg(videoname,',')asvideonamesfromw008_video_addr_infowhereidin(4248,538,546)groupbycreate_by查询结果：3复杂一些的应用... 继续阅读 >
2021
01-08

postgreSQL的crud操作详解

postgreSQL的crud操作详解 postgreSQL学习笔记欢迎大家指出问题！版本从6.0开始支持SQL解释器安装一路下一步。1.创建数据库​//命令行操作​createdbdatabase_name;​CREATEDATABASEdatabase_name;psql-l//展示所有数据库psqldatabase_name//进入数据库2.删除数据库​dropdbdatabase;​DROPDATABASEdatabase_name;3.创建表​createtable表名(titlevarchar(255),contenttext);//修改表名altertablepo... 继续阅读 >
2021
01-08

PostgreSQL 序列增删改案例

创建序列CREATESEQUENCEifnotexiststest_mergetable_id_seqINCREMENT1MINVALUE1MAXVALUE999999999START1CACHE1;//或者：createsequenceifnotexiststest_mergetable_id_seqincrementby1minvalue1nomaxvaluestartwith1;指定序列（给表的主键指定创建好的序列）altertabletest_mergetablealtercolumn"i_id"setdefaultnextval('test_mergetable_id_seq');设置序列自增长从当前最大值开始SELECTset... 继续阅读 >
2021
01-08

postgresql重置序列起始值的操作

我就废话不多说了，大家还是直接看代码吧~--序列重置到1000altersequencesequence_namerestartwith1000--验证SELECTnextval('sequence_name');补充：postgresql重置序列和自增主键1.问题背景数据表中插入了几条测试数据，后又手动删除，导致后面插入数据的时候报主键冲突：ERROR:duplicatekeyvalueviolatesuniqueconstraint"tableName_pkey"DETAIL:Key(id)=(1)alreadyexists.即使采用INSERTIGNORE的方式或者... 继续阅读 >
2021
01-08

PostgreSQL批量修改函数拥有者的操作

PostgreSQL批量修改函数拥有者的操作 Postgresql如何批量修改函数拥有者，默认创建的函数对象的拥有者为当前创建的用户，如果使用postgres超级管理员创建一个test()的函数，拥有者就是postgres用户。下面讲解下如何批量修改拥有者。本文演示的Postgresql版本如下：PostgreSQL9.6.8相关视图要查询Postgresql的函数和函数参数需要使用函数视图和参数视图，分别记录了函数信息和参数列表信息。视图一：information_schema.routines视图routines包含当前数据库中所有的函... 继续阅读 >
2021
01-08

PostgreSQL 默认权限查看方式

PostgreSQL 默认权限查看方式 如何查看PostgreSQL默认权限当我们对Postgresql的某个用户授予默认权限时，pg_default_acl表存储要被分配给新创建对象的初始权限。你可能通过查询该表获取默认权限，先了解下官方文档的解释：表50-17.pg_default_acl的列：名称类型引用描述oidoid行标识符（隐藏属性，必须被显式选择才会显示）defaclroleoidpg_authid.oid... 继续阅读 >
2021
01-08

postgresql 赋权语句 grant的正确使用说明

postgresql 赋权语句 grant的正确使用说明 grantselectonalltablesinschemapublictousername;此句是有效的复制语句PG中有schema的概念，以下的语句就是不行网上得来终觉浅，错误比较多，还是要自己试试才行。补充：postgresql关于访问视图需要的权限某个用户访问一个视图，这个用户需要具备这个视图的schema的usage和这个视图本身的select权限，如果视图的基表来自其他schema，可能还需要其他schema的usage权限(根据postgresql改造的redshift发现了此问题)，但是这... 继续阅读 >
2021
01-08

Postgresql 赋予用户权限和撤销权限的实例

1、对数据库授权postgresql授权某个数据库的权限给wang账号使该账号只能操作指定DB不能操作其他DBalteruserwangsetdefault_transaction_read_only=on;grantallondatabasetesttowang;grantselectonalldatabasetesttowang;grantselectonalltablesinschemapublictowang;//起作用的是这句要进入数据库test操作,在那个db环境执行就授哪个db的权配置权限ve=#grantallonschemapublictofoo;v... 继续阅读 >
2021
01-08

postgresql限制某个用户仅连接某一个数据库的操作

创建数据库bbb且owner为用户b：postgres9.6@[local]:5432postgres#createdatabasebbbownerb;CREATEDATABASETime:259.099ms默认情况下使用用户c也可以连接数据库bbb：postgres9.6@[local]:5432postgres#\cbbbcYouarenowconnectedtodatabase"bbb"asuser"c".回收public的connectondatabasebbb权限：c@[local]:5432bbb#\cpostgrespostgres9.6Youarenowconnectedtodatabase"postgres"asuser"post... 继续阅读 >
2021
01-08

PostgreSQL用户、数据库及表的管理、操作与授权方式

摘要PostgreSQL的常用命令1、登录数据库/*切换到数据库用户*/su-postgres/*登录*/psql登录成功显示如下：bash-4.2$psqlpsql(9.3.17)Type"help"forhelp.postgres=>2、切换数据库/*登录指定数据库*/psql-Uuser-ddbname/*列举数据库*/\l/*切换数据库*/\cdbname3、用户管理/*创建用户*/CREATEROLErolename;CREATEUSERusernameWITHPASSWORD'*****';/*显示所有用户*/\du/*修改用户权限*/ALTERROLE... 继续阅读 >
2021
01-08

PostgreSQL 实现快速删除一个用户

背景在多租户场景或者其他场景下，很多时候需要主动清理一些用户，本文将介绍PostgreSQL下如何快速删除一个用户（role）。具体方法一般情况下直接执行droprolexxx;就可以把这个用户删除。但是很多时候会因为用户有依赖而报错。权限依赖postgres=#createroletestwithlogin;CREATEROLEpostgres=#grantallondatabasepostgrestotest;GRANTpostgres=#droproletest;ERROR:role"test"cannotbedroppedbecauses... 继续阅读 >
2021
01-03

PostgreSQL报错 解决操作符不存在的问题

最近才接触到一个用PostgreSQL的项目，然后在开发的过程中发现了这样的一个问题。错误:操作符不存在:character=integer反正还有很多报错的，原因都是类型的转换问题。在mysql中似乎对类型这个概念不是那么敏感，而在PostgreSql中Integer、Long、Date、String等等之间转换都会存在操作符不存在的报错。所以在使用非实体进行数据传输的时候，例如Map等等就需要手动设置数据类型。LongorgId=(maps.get("orgId")!=null&&m... 继续阅读 >
2021
01-03

PostgreSQL 自定义自动类型转换操作(CAST)

背景PostgreSQL是一个强类型数据库，因此你输入的变量、常量是什么类型，是强绑定的，例如在调用操作符时，需要通过操作符边上的数据类型，选择对应的操作符。在调用函数时，需要根据输入的类型，选择对应的函数。如果类型不匹配，就会报操作符不存在，或者函数不存在的错误。postgres=#select'1'+'1';ERROR:operatorisnotunique:unknown+unknownLINE1:select'1'+'1';^HINT:Couldnotchooseabestcan... 继续阅读 >
2021
01-03

postgresql 实现多表关联删除

postgresql 实现多表关联删除 t_aj_ajjbxxt_aj_ajfbxxt_xt_dwxx两表关联，删除一张表中数据deletefromdb_lx.t_aj_ajfbxxajfbUSINGdb_lx.t_xt_dwxxdwwheredw.c_bh=ajfb.c_ssdwanddw.c_sfbh='65';只有t_aj_ajfbxx表中数据被删除三表关联，删除一张表中数据deletefromdb_lx.t_aj_ajjbxxajjbUSINGdb_lx.t_aj_ajfbxxajfb,db_lx.t_xt_dwxxdwwhereajjb.c_bh=ajfb.c_ajbhandajfb.c_ssdw=dw.c_bhanddw.c_sfbh='65';只有t_aj_ajjbxx表中数据被删... 继续阅读 >
2021
01-03

postgresql 两表关联更新操作

我就废话不多说了，大家还是直接看代码吧~UPDATE要更新的表SET字段1=cqt.字段1,字段2=cqt.字段2,FROM数据来源表cqtWHERE要更新的表.bsm=cqt.bsm补充：Postgresql连表更新字段语法update下面这段sql本来目的是想更新条件下的数据，可是这段sql却更新了整个表的数据。sql如下：UPDATEtops_visa.visa_orderSETop_audit_abort_pass_date=now()FROMtops_visa.visa_orderast1INNERJOINtops_visa.visa_visitoras... 继续阅读 >