精华内容
下载资源
问答
  • postgresql查询主键
    万次阅读
    2018-03-08 14:12:50
     作者:瀚高PG实验室 (Highgo PG Lab)
    --查询主键名称
    SELECT
        pg_constraint.conname AS pk_name
    FROM
        pg_constraint
    INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.oid
    WHERE
        pg_class.relname = ' table_name'
    AND pg_constraint.contype = 'p';
    --查询主键的详细信息
    SELECT
        pg_constraint.conname AS pk_name,
        pg_attribute.attname AS colname,
        pg_type.typname AS typename
    FROM
        pg_constraint
    INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.oid
    INNER JOIN pg_attribute ON pg_attribute.attrelid = pg_class.oid
    AND pg_attribute.attnum = pg_constraint.conkey [ 1 ]
    INNER JOIN pg_type ON pg_type.oid = pg_attribute.atttypid
    WHERE
        pg_class.relname = ' table_name'
    AND pg_constraint.contype = 'p';
    更多相关内容
  • SELECT t4.tablename AS tableName, string_agg(DISTINCT t3.attname,',') AS primaryKeyColumn FROM ... INNER JOIN pg_attribute t3 ON t3.attrelid = t2.oid AND array_position(t1.conkey,t3.att
    SELECT
      t4.tablename AS tableName,
      string_agg(DISTINCT t3.attname,',')  AS primaryKeyColumn
    FROM
      pg_constraint t1
      INNER JOIN pg_class t2 ON t1.conrelid = t2.oid
      INNER JOIN pg_attribute t3 ON t3.attrelid = t2.oid AND array_position(t1.conkey,t3.attnum) is not null
      INNER JOIN pg_tables t4 on t4.tablename = t2.relname
      INNER JOIN pg_index t5 ON t5.indrelid = t2.oid AND t3.attnum = ANY (t5.indkey)
      LEFT JOIN pg_description t6 on t6.objoid=t3.attrelid and t6.objsubid=t3.attnum
    WHERE  t1.contype = 'p'
         AND length(t3.attname) > 0
         AND  t2.oid = (select tablename 
                         from pg_tables t7
                         where schemaname='public'
                         and t7.tablename = t2.relname) :: regclass
    group by t4.tablename
    
    展开全文
  • PostgreSQL查询表主键及注释内容

    千次阅读 2020-07-31 18:19:24
    网上关于pgSql获取表主键的内容都是千篇一律,并且对于存在多主键的场景不支持。 附上测试后可获取多个主键字段值的SQL SELECT string_agg(DISTINCT t3.attname,',') AS primaryKeyColumn ,t4.tablename AS ...

    网上关于pgSql获取表主键的内容都是千篇一律,并且对于存在多主键的场景不支持。

    附上测试后可获取多个主键字段值的SQL

    SELECT
       string_agg(DISTINCT t3.attname,',')  AS primaryKeyColumn
      ,t4.tablename AS tableName
      , string_agg(cast(obj_description(relfilenode,'pg_class') as varchar),'') as comment
    FROM
      pg_constraint t1
      INNER JOIN pg_class t2 ON t1.conrelid = t2.oid
      INNER JOIN pg_attribute t3 ON t3.attrelid = t2.oid AND array_position(t1.conkey,t3.attnum) is not null
      INNER JOIN pg_tables t4 on t4.tablename = t2.relname
      INNER JOIN pg_index t5 ON t5.indrelid = t2.oid AND t3.attnum = ANY (t5.indkey)
      LEFT JOIN pg_description t6 on t6.objoid=t3.attrelid and t6.objsubid=t3.attnum
    WHERE  t1.contype = 'p'
           AND length(t3.attname) > 0
          AND  t2.oid = '表名' :: regclass
      group by t4.tablename

    目前只找到了获取指定表的主键信息,对于批量获取没有找到。

    bug:

    对于表主键没有配置的,也会获取到第一个字段作为主键输出,如果有大佬解决了这个问题,望给与解答

    展开全文
  • 查看Postgresql表名、主键、列名

    千次阅读 2019-08-09 11:25:16
    Postgresql有很多系统,可以查主键、列名、类型、备注、索引等。 参考:https://www.yiibai.com/manual/postgresql/catalogs.html 为了配置datax通用脚本,用到这些,记录一下: select table_schema,table_name...

    Postgresql有很多系统表,可以查主键、列名、类型、备注、索引等。
    参考:https://www.yiibai.com/manual/postgresql/catalogs.html

    为了配置datax通用脚本,用到这些,记录一下:

    select table_schema,table_name,concat('s01_',table_name) as target_table,t.colname,string_agg(column_name,',') as COLS 
    from information_schema.columns
    LEFT JOIN (select pg_class.relname as tablename,pg_attribute.attname as colname from
    pg_constraint inner join pg_class
    on pg_constraint.conrelid = pg_class.oid
    inner join pg_attribute on pg_attribute.attrelid = pg_class.oid
    and pg_attribute.attnum = pg_constraint.conkey[1]
    where pg_constraint.contype='p') t
    on table_name=t.tablename
    where TABLE_NAME = 'mem_base_info'
    group by table_schema,table_name,t.colname;

    结果:

    展开全文
  • postgresql查看主键 需要指定nspname=‘public’,会显示分区还是正常的 SELECT t.schemaname ,t.tablename,t.tableowner,t.tablespace, CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' ...
  • PostgreSQL数据库中获取表主键名称

    千次阅读 2020-12-24 13:03:09
    PostgreSQL数据库中获取表主键名称一、如下表示,要获取teacher主键信息:select pg_constraint.conname as pk_name,pg_attribute.attname as colname,pg_type.typname as typename frompg_constraint inner ...
  • 今天库里需要建一张新,需要三个字段联合作为主键。在Navicat里是可以通过点击最后一栏设置主键的。 但是我心想,一张只有一个主键,这个1,2,3表示的是啥啊?心里有点担心的自己的主键有没有设置成功,就查了...
  • PG获取不带主键

    2020-02-28 11:04:34
    select relname from pg_class where relkind='r' and relname not like 'pg_%' except select conrelid::regclass::varchar FROM pg_constraint where contype='p';
  • PG获取自增序列主键

    2022-04-14 15:19:10
    insert into … values … Returning id(序列主键)
  • PG库 修改主键字段为自增

    千次阅读 2020-11-12 20:23:31
    起因:页面需要往数据库插入数据,但是id没有设置自增,这很不利于我们的开发,要根据id来区别数据的唯一性 做法:首先创建一个序列: create sequence "public"."generator_room_id" increment 1 ...
  • 第一章 问题解决 step1 找出数据库中所有的英文名称 找出数据库中所有的英文名称,命名为A SELECT * from pg_class where pg_class....找出所有主键,命名为B SELECT pg_class.relname from pg_class whe
  • postgresql 数据库查找主键

    千次阅读 2020-05-12 14:39:59
    pg_catalog.pg_get_userbyid(c.relowner) as ownername from pg_catalog.pg_class c left join pg_catalog.pg_namespace n on n.oid = c.relnamespace where 1=1 --r = 普通, i = 索引, S = 序列...
  • pg库怎么设置主键自增?

    千次阅读 2021-04-26 15:07:48
    方法一: create table test_a ( id serial, name character varying(128), constraint pk_test_a_id ...这两种方法用的是pg的serial类型实现自增,drop的时候指定的序列也会drop掉 方法三:先创建主键表 cre.
  • PostgreSQL 查看表结构、主键(多个)

    千次阅读 2018-10-11 15:17:16
    在网上查阅的资料都只能查出一张表的一个主键(我的数据库有些表是多个主键维护的),最后还是把不同的解决方法组合起来,解决了既能查看表结构,也能查看多个主键。下面是SQL语句: SELECT  A.ordinal_position, ...
  • mysql与pg主键索引说明

    千次阅读 2020-06-19 09:53:27
    mysql与pg主键说明mysql插入顺序与插入随机测试案例B+Tree原理id顺序的方式插入随机的方式插入Postgresql堆组织CTIDPg的元组及索引原理Pg与mysql与oracel问题 mysql 插入顺序与插入随机测试案例 创建了两张 ...
  • PG设置主键自增

    千次阅读 2020-06-09 10:48:07
    -- 创建序列 create sequence seq_user_...-- 建表,并用上面的序列作为主键自增序列 CREATE TABLE public.user_camera_version ( id int4 NOT NULL DEFAULT nextval('seq_user_camera_version'::regclass), user_i..
  • postgresql 给添加主键的最优方案

    千次阅读 2021-07-26 11:11:08
    NOTICE: ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "pk_tmp_t0_id" to "pk_tmp_t0" ALTER TABLE 查看表的定义 postgres=# \d tmp_t0 Table "public.tmp_t0" Column | Type | Collation | ...
  • (select relname||'--'||(select description from pg_description where objoid=oid and objsubid=0) as comment from pg_class where oid=a.attrelid) as table_name, a.attname as column_name, format_type(a....
  • #1. 删除主键约束 ALTER TABLE "table_name" DROP CONSTRAINT "table_name_pkey"; #2. 增加主键约束 ALTER TABLE "table_name" add primary key (xx,xx,xx);
  • 我的版本是 PostgreSQL 10.4 on x86_64-pc-mingw64, compiled by gcc.exe (Rev5, Built by MSYS2 project) 4.9.2, 64-bit1、查看版本select version();2、获取数据库中所有view名 视图:SELECT viewnameFROM pg_...
  • PostgreSQL数据库测试环境中有多张表没有添加主键约束,只有一个serial的自增...我们看下pg_class这个,里面有个relhaspkey字段,如果为t说明有主键,f即没有主键。例如下面这个sql 。SELECTn.nspnameAS"Schema",c...
  • PostgreSQL数据库修改增加主键

    千次阅读 2021-01-17 15:54:25
    PostgreSQL数据库测试环境中有多张表没有添加主键约束,只有一个serial的...我们看下pg_class这个,里面有个relhaspkey字段,如果为t说明有主键,f即没有主键。例如下面这个sql 。SELECT n.nspname AS "Schema",c...
  • Postgresql 数据库大没有主键在线增加 由于前期操作失误,导致 Postgresql 数据库部分创建时未增加主键,记录处理过程; 查询数据库中没有创建主键 SELECT pg_class.relname,pg_constraint.conname AS pk_...
  • Caused by: org.postgresql....2533) 在使用jpa保存对象的时候,提示主键ID值为空,但是我通过序列获取了nextval,是有值的,于是去看了下序列与表主键是否关联,结果不出所料,序列与未进行关联绑定,导致了这个异常出现.
  • PostgreSQL数据库查询没有主键 SELECT * FROM ( SELECT pc.relname , pc2.conname , pc2.contype FROM pg_catalog.pg_class pc INNER JOIN information_schema.tables t ON t.table_name = pc....
  • pg_size_pretty(pg_total_relation_size('"' || schemaname || '"."' || tb.relname || '"')) AS "reserved", pg_size_pretty(pg_table_size('"' || schemaname || '"."' || tb.relname || '"')) AS "data", pg_...
  • Postgresql设置主键自增长

    千次阅读 2022-03-29 18:34:24
    Postgresql设置主键自增长,在pgAdmin中的Default(默认值)中填写nextval('my_sequence'),其中my_sequence是新建的序列,记得要在nextval函数加上单引号
  • 总结:通过上面的方法就可以在没有主键或者唯一键的情况下删除重复记录,如果有多张表,则可以配合shell脚本批量删除中的重复记录 参考:https://github.com/digoal/blog/blob/master/201706/20170602_01.md...
  • pg 主键自增设置

    2017-05-09 17:42:00
    @Id @Column(name = "Id") @SequenceGenerator(name="名称A", sequenceName="库中已存在的sequence名称",allocationSize=递增值) @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="名称A") ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 5,715
精华内容 2,286
关键字:

pg查看表主键