-
-
Pgsql
2019-12-09 11:10:281.设置数据库主键自增 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:571.以下两种操作均不会删除表中数据 #删除带上回滚一起执行 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:39PGSQL12.4.1版本 -
pgsql problem
2021-01-08 12:02:55devilbox-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... -
pgsql: enhance checks in pgsql_real_start to prevent incorrect status…
2020-12-25 20:46:36May 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:49pgsql_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:37nativecat for pgsql nativecat for pgsql nativecat for pgsql -
Pgsql 使用
2020-04-24 09:17:031.安装后授权给所有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
-
pgsql thinkphp5_ThinkPHP链接 PgSQL
2020-12-30 16:13:07ThinkPHP 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:01PGSQL 9.5.7 widndows 64位安装版本版本,直接安装就可以了 -
pgsql配置说明
2018-11-16 18:00:54pgsql服务data下各个配置项说明,最大连接池,回收时长设置等 -
pgsql on glusterfs
2021-01-07 13:04:04Running 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:28Pgsql安装 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:52pgsql自动生成时间序列 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:51pgsql.zip 下载下载 -
mysql和pgsql_pgsql与mysql有什么区别
2021-02-08 04:57:05pgsql与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:21pgsql以多进程方式运行 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 ...
-
linux基础入门和项目实战部署系列课程
-
leetcode算法第三题
-
2014年上半年 信息系统监理师 上午试卷 综合知识 软考真题【含答案和答案解析】
-
小程序的全栈开发新时代
-
JMETER 性能测试基础课程
-
无线计算卸载的能量和时间优化
-
射影级双缝光子晶体光机腔设计
-
基于Qt的LibVLC开发教程
-
【CTF资料-0x0002】简易Linux堆利用入门教程by arttnba3
-
arwin:Visual C ++项目中的arwin-源码
-
NearFi应用-源码
-
MaxScale 实现 MySQL 读写分离与负载均衡
-
在 Linux 上构建企业级 DNS 域名解析服务
-
2013年上半年 信息系统监理师 上午试卷 综合知识 软考真题【含答案和答案解析】
-
spark大数据分析与实战
-
FyreString:FyreString是PHP的免费开源字符串实用程序库-源码
-
二叉树搜索树(无左旋右旋)C 迭代寻找插入
-
2019年下半年 信息系统监理师 上午试卷 综合知识 软考真题【含答案和答案解析】
-
FPS游戏逆向-UE4虚幻四游戏逆向
-
illustrator软件Mac电脑入门学习必备