精华内容
下载资源
问答
  • Pgsql

    2019-12-09 11:10:28
    1.设置数据库主键自增 1.1 在创建数据库时,将要自增的字段设置为 serial类型. 1.2 当数据已经修建完成后的补救方案. CREATE SEQUENCE 表名_字段名_seq START WITH 1 INCREMENT BY 1 NO MINVALUE ...

    1.设置数据库主键自增

    1.1 建表时

    在创建数据库时,将要自增的字段设置为 serial类型.

    1.2 当数据已经修建完成后的补救方案.

    CREATE SEQUENCE 表名_字段名_seq
        START WITH 1
        INCREMENT BY 1
        NO MINVALUE
        NO MAXVALUE
        CACHE 1;
    ALTER TABLE 表名 ALTER COLUMN 字段名
    SET DEFAULT nextval( '表名_字段名_seq' );
    

    2. 设置分页查询 使用limit

    SELECT * FROM 表名
    LIMIT 每页显示数量 OFFSET 起始位置
    

    3.Bool类型

    SELECT * FROM vip WHERE is_valid = true
    

    4.查询字符串时,要使用 ‘’ 包起来,不能使用"".

    在pg中的sql,单引号用来标识实际的值,双引号用来标识表名(table name)或列名(column name)等数据库中存在的值。

    展开全文
  • PGSQL

    2019-11-25 23:32:57
    1.以下两种操作均不会删除表中数据 #删除带上回滚一起执行 delete from classinfo where classid=1; ROLLBACK; #退出当前事务 delete from classinfo where classid=1;...ABORT TRANSACTION;...2.定义枚举类型,current_...

    1.以下两种操作均不会删除表中数据

    #删除带上回滚一起执行
    delete from classinfo where classid=1;
    ROLLBACK;

    #退出当前事务
    delete from classinfo where classid=1;
    ABORT TRANSACTION;

     

     

    2.定义枚举类型,current_mood为枚举类型,值只能输入'sad', 'ok', 'happy'其中一个,输入其它报错

    CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
    CREATE TABLE person (
        name text,
        current_mood mood
    );

     

     

    3.非常好用的PARTITION BY

    想要分组后再进行条件筛选,每组选出符合条件的数据,group by 使用时select后面的字段必须出现在 GROUP BY 子句中或者在聚合函数中使用,所以使用起来非常不方便

    SELECT
        iiii,
        rain_1hour,
        data_time,
        ROW_NUMBER () OVER (PARTITION BY iiii ORDER BY xxx desc)as rownumber
    FROM
        w_data_site_rain
    WHERE
        data_time < '20180522091500'
      and rownumber<=1

     

    获取到了分组中最大的一组值

     

     

    4.循环删除不需要数据插入到新的表中

    WITH moved_rows AS (
       DELETE FROM w_data_site_rain
       WHERE
          data_time < '20180522091500'
       RETURNING * 
    )
    INSERT INTO w_data_site_rain (SELECT * FROM moved_rows)
     

     

     

     

    展开全文
  • PGSQL12.4.1版本

    2020-12-24 14:04:39
    PGSQL12.4.1版本
  • pgsql problem

    2021-01-08 12:02:55
    devilbox-pgsql-:/var/lib/postgresql/data/pgdata:rw" is used in service "pgsql" but no declaration was found in the volumes section. <h4>Goal 该提问来源于开源项目:cytopia/devilbox...
  • May 9 10:05:21 bl460g9n9 crmd[29121]: info: Transition aborted by status-bundle-0-last-failure-pgsql.monitor_60000 doing create last-failure-pgsql#monitor_60000=1557363921: Transient attribute ...
  • opensipsctl PgSQL

    2020-11-22 04:38:49
    pgsql_query: /usr/bin/psql -A -q -t -P fieldsep=' ' -h mydb.domain.com -p5235 -U opensips_prod opensips_prod01 -c 'select * FROM dispatcher ORDER BY setid; ' psql: could not ...
  • nativecat for pgsql

    2018-05-10 20:16:37
    nativecat for pgsql nativecat for pgsql nativecat for pgsql
  • Pgsql 使用

    2020-04-24 09:17:03
    1.安装后授权给所有IP和用户链接 1).修改pgsql/data/postgresql.conf 配置文件 listen_addresses = '*' 2).修改pgsql/data/ph_hba.conf 配置文件 host all all0.0.0.0/0 md5 ...

    1.安装后授权给所有IP和用户链接

      1). 修改pgsql/data/postgresql.conf 配置文件

       listen_addresses = '*'

      2).修改pgsql/data/ph_hba.conf 配置文件

       host all all 0.0.0.0/0 md5

    展开全文
  • ThinkPHP version: 5.1.*使用 ThinkPHP 框架连接PGSQL时遇到的坑, 记录下来...避免一次一顿百度....改配置修改 /config/database.php 这个配置文件connect pgsql config如果此时对数据操作会报错could not find ...

    ThinkPHP version: 5.1.*

    使用 ThinkPHP 框架连接PGSQL时遇到的坑, 记录下来...避免一次一顿百度....

    改配置

    修改 /config/database.php 这个配置文件

    connect pgsql config

    如果此时对数据操作会报错

    could not find driver

    开扩展

    修改 php.ini 打开连接pgsql需要的扩展, 如果没有请到 http://pecl.php.net/ 下载对应版本的扩展

    修改了 php.ini 别忘了重启apache/nginx

    php extensions

    执行sql

    在使用之前请先在 navicat 中运行一下SQL, ThinkPHP中自带这一个SQL文件

    /thinkphp/library/think/db/connector/pgsql.sql

    CREATE OR REPLACE FUNCTION pgsql_type(a_type varchar) RETURNS varchar AS

    $BODY$

    DECLARE

    v_type varchar;

    BEGIN

    IF a_type='int8' THEN

    v_type:='bigint';

    ELSIF a_type='int4' THEN

    v_type:='integer';

    ELSIF a_type='int2' THEN

    v_type:='smallint';

    ELSIF a_type='bpchar' THEN

    v_type:='char';

    ELSE

    v_type:=a_type;

    END IF;

    RETURN v_type;

    END;

    $BODY$

    LANGUAGE PLPGSQL;

    CREATE TYPE "public"."tablestruct" AS (

    "fields_key_name" varchar(100),

    "fields_name" VARCHAR(200),

    "fields_type" VARCHAR(20),

    "fields_length" BIGINT,

    "fields_not_null" VARCHAR(10),

    "fields_default" VARCHAR(500),

    "fields_comment" VARCHAR(1000)

    );

    CREATE OR REPLACE FUNCTION "public"."table_msg" (a_schema_name varchar, a_table_name varchar) RETURNS SETOF "public"."tablestruct" AS

    $body$

    DECLARE

    v_ret tablestruct;

    v_oid oid;

    v_sql varchar;

    v_rec RECORD;

    v_key varchar;

    BEGIN

    SELECT

    pg_class.oid INTO v_oid

    FROM

    pg_class

    INNER JOIN pg_namespace ON (pg_class.relnamespace = pg_namespace.oid AND lower(pg_namespace.nspname) = a_schema_name)

    WHERE

    pg_class.relname=a_table_name;

    IF NOT FOUND THEN

    RETURN;

    END IF;

    v_sql='

    SELECT

    pg_attribute.attname AS fields_name,

    pg_attribute.attnum AS fields_index,

    pgsql_type(pg_type.typname::varchar) AS fields_type,

    pg_attribute.atttypmod-4 as fields_length,

    CASE WHEN pg_attribute.attnotnull THEN ''not null''

    ELSE ''''

    END AS fields_not_null,

    pg_attrdef.adsrc AS fields_default,

    pg_description.description AS fields_comment

    FROM

    pg_attribute

    INNER JOIN pg_class ON pg_attribute.attrelid = pg_class.oid

    INNER JOIN pg_type ON pg_attribute.atttypid = pg_type.oid

    LEFT OUTER JOIN pg_attrdef ON pg_attrdef.adrelid = pg_class.oid AND pg_attrdef.adnum = pg_attribute.attnum

    LEFT OUTER JOIN pg_description ON pg_description.objoid = pg_class.oid AND pg_description.objsubid = pg_attribute.attnum

    WHERE

    pg_attribute.attnum > 0

    AND attisdropped <> ''t''

    AND pg_class.oid = ' || v_oid || '

    ORDER BY pg_attribute.attnum' ;

    FOR v_rec IN EXECUTE v_sql LOOP

    v_ret.fields_name=v_rec.fields_name;

    v_ret.fields_type=v_rec.fields_type;

    IF v_rec.fields_length > 0 THEN

    v_ret.fields_length:=v_rec.fields_length;

    ELSE

    v_ret.fields_length:=NULL;

    END IF;

    v_ret.fields_not_null=v_rec.fields_not_null;

    v_ret.fields_default=v_rec.fields_default;

    v_ret.fields_comment=v_rec.fields_comment;

    SELECT constraint_name INTO v_key FROM information_schema.key_column_usage WHERE table_schema=a_schema_name AND table_name=a_table_name AND column_name=v_rec.fields_name;

    IF FOUND THEN

    v_ret.fields_key_name=v_key;

    ELSE

    v_ret.fields_key_name='';

    END IF;

    RETURN NEXT v_ret;

    END LOOP;

    RETURN ;

    END;

    $body$

    LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

    COMMENT ON FUNCTION "public"."table_msg"(a_schema_name varchar, a_table_name varchar)

    IS '获得表信息';

    ---重载一个函数

    CREATE OR REPLACE FUNCTION "public"."table_msg" (a_table_name varchar) RETURNS SETOF "public"."tablestruct" AS

    $body$

    DECLARE

    v_ret tablestruct;

    BEGIN

    FOR v_ret IN SELECT * FROM table_msg('public',a_table_name) LOOP

    RETURN NEXT v_ret;

    END LOOP;

    RETURN;

    END;

    $body$

    LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

    COMMENT ON FUNCTION "public"."table_msg"(a_table_name varchar)

    IS '获得表信息';

    展开全文
  • pgsql重启

    千次阅读 2018-07-14 16:05:00
    由于cpu过高,找不到原因,只能重启,无奈又忘了pgsql安装位置和重启命令。折腾半天终于搞定,先记录如下: 执行ps -ef | grep postgres 查看进程的情况 从上图可以看到,pgsql 的位置在 /usr/pgsql-10/bin 目录...
  • PGSQL下载包

    2018-06-29 09:32:01
    PGSQL 9.5.7 widndows 64位安装版本版本,直接安装就可以了
  • pgsql配置说明

    2018-11-16 18:00:54
    pgsql服务data下各个配置项说明,最大连接池,回收时长设置等
  • pgsql on glusterfs

    2021-01-07 13:04:04
    Running PgSQL will cause PgSQL to fail to start when a gluster node is unavailable <p>pgdata [root /]# ll /var/lib/portsip/pgsql/data/pg_stat_tmp/ ls: cannot access /var/lib/portsip/pgsql/data/pg_...
  • Pgsql安装

    2018-11-18 20:58:28
    Pgsql安装 1. 数据库安装 yum install postgresql-server 2. 数据库服务初始化 service postgresql initdb 3. 启动数据库服务 service postgressql start 或者 /etc/init.d/postgresql start 4. 修改数据库...
  • pgsql笔记

    2020-04-13 20:13:52
    pgsql自动生成时间序列 select day_id,cate1 from ( SELECT 1 AS JOIN_ID,cate1 FROM (select distinct cate1 AS cate1 from dim_prd_category) a ) t2 left join ( select 1 AS JOIN_ID,day_id from (SELECT ...
  • pgsql.zip 下载下载

    2020-09-11 15:04:51
    pgsql.zip 下载下载
  • pgsql与mysql的区别有:1、在pgsql上的功能实现比mysql严谨,对表的连接支持更加完整;2、pgsql的优化器功能完整,支持索引类型多,而mysql的优化器比较简单,适用于简单的查询操作等等。pgsql与mysql的区别有:在...
  • pgsql9.6 绿色版

    2018-11-16 18:08:57
    绿色版pgsql9.6的文件包,直接解压出来通过命令行或者代码创建pgsql服务以及账号,建库建表操作,免安装操作,可以集成到程序中达到免安装的效果
  • osm2pgsql https://osm2pgsql.org osm2pgsql is a tool for loading OpenStreetMap data into a PostgreSQL / PostGIS database suitable for applications like rendering into a map, geocoding with Nominatim, ...
  • pgsql安装

    千次阅读 2016-08-14 12:08:37
    安装pgsql 1)解压postgresql-9.5.3.tar.gz 2)进入到postgresql-9.5.3目录 3)编译pgsql源码 #./configure –prefix=/usr/local/pgsql #make #make install二.创建postgresql数据库 1)创建用户 #...
  • PgSQL Query objects

    2020-12-29 13:24:52
    <div><p>Some refactoring and brand new PgSQL Query objects</p><p>该提问来源于开源项目:auraphp/Aura.Sql</p></div>
  • pgsql监控

    2019-01-03 17:54:21
    pgsql以多进程方式运行 ps -ef|grep postgres 2,查看进程活动情况 select * from pg_stat_activity; select * from pg_stat_bgwriter; select * from pg_stat_archiver; select * from pg_stat_database; ...
  • java 连接 pgsql

    千次阅读 2019-11-12 08:46:05
    首先在jdbc的配置中新增pgsql的配置属性: pgsql.driverClassName=org.postgresql.Driver pgsql.url=jdbc:postgresql://12.1.16.211:5432/fskp pgsql.username=admin pgsql.password=123456 java连接pgsql ...
  • connection to pgsql

    2020-12-31 20:19:05
    <p>I use the command ( docker-compose -f docker-compose_v3_alpine_pgsql_latest.yaml up -d ) for starting the zabbix containers. <p>But, with a software, i would like to connect to the pgsql server. I ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 9,563
精华内容 3,825
关键字:

pgsql