Category：Other DB
-
Like query optimization scheme in PostgreSQL
At that time, when the quantity was relatively large, the efficiency of fuzzy query was very slow. In order to optimize the query efficiency, try the following methods for efficiency comparison 1、 Description of comparison: 1. Data volume: 100W pieces of data 2. Execute SQL 2、 Comparison results explain analyze SELECT c_patent, c_applyissno, d_applyissdate, d_applydate, […]
-
Solve some small problems in the use of PostgreSQL array
In PostgreSQL, you can use array data structures, such as select array[1,2,3]; return {1,2,3} But if select array[1,2,3][1]; — Will report an error Select (select array [1,2,3]) [1] – can be used So you’re using the regular matching function regexp_ Match will encounter such problems, such as select regexp_match(‘123-123’, ‘(\d+)-(\d+)’); –return {123, 123} select regexp_ […]
-
Implementation of disabling full table scanning in PostgreSQL
PostgreSQL can disable full scan / SEQ scan through some settings be careful: Setting this function does not completely avoid full table scanning, but does not go through full table scanning as long as there are results that can not be obtained through full table scanning. If there is no way, you must scan the […]
-
The PostgreSQL implementation replaces the empty value of the field with the specified value
1. Null indicates a missing value and ” ” indicates a null value Null returns null for all expression operations involved Use is null to determine whether it is null Is not null judge non null 2. Nullif (value1, Value2) returns NULL if value1 = = Value2 3、coalesce(arg1, arg2, …) Returns the first non null […]
-
The PostgreSQL implementation modifies a value in the jsonb field
I won’t talk more nonsense. Let’s look at the code directly~ UPDATE tablename SET tags = jsonb_ Set (Tags -‘landuse_area ‘,’ {landuse_area} ‘(‘ “‘| round ((st_area (st| transform (geom, 4527)) * 0.0015):: numeric, 3) |’ ‘):: jsonb, true) where tags @ >’ {” name “:” Zhang San “} ‘; The purpose of round is to […]
-
Tips for using the coalesce() function in PostgreSQL
Scenario: There is a user white list table, which provides the user name and region information of this user, and judges whether this user is in this white list table. For example: Name white list area Zhang San, China, USA You can use the following statement: SELECT ID, user, area_list FROM t_white_user WHERE user = […]
-
PostgreSQL coalesce function data conversion method
Oracle’s NVL (col, 0) is to judge that if the col field is empty, it is assigned 0, and the function in the same PostgreSQL is coalesce. The usage is the same SELECT coalesce(collect_result,value) as collect_result FROM collect If the queried field in the database is collect_ If result is empty, assign value to collect_ […]
-
PgSQL variable assignment method and precautions
1. Generally speaking, the methods on the Internet are as follows: : =, assignment, such as user_ id := 20; Select into assignment, for example SELECT INTO myrec * FROM emp WHERE empname = myname 2. What I introduce today is a more general and practical assignment method select …into … Use example: A variable, […]
-
PostgreSQL imports database tables and resets self incrementing attributes
PostgreSQL uses Navicat software to export database tables, and the operation of importing the database will be. The auto increment field of PostgreSQL is realized through sequence. 1. Delete the auto increment attribute in the exported database table first 2. After importing the database table, you need to create a sequence. Note: generally, the sequence […]
-
Summary of three methods of PostgreSQL storing function call variables
1、 Suppose there is a table student, and the fields include ID, remark, name and other fields. 2、 Write a storage function to update the content of remark according to the passed variable ID. The format of calling this storage function is as follows: select update_student(1); 3、 Examples of storage functions are as follows: CREATE […]
-
PostgreSQL stored procedure circular calling method
Requirement description In case of demand, you need to insert 50000 pieces of data into the table. You plan to use stored procedures, but Postgres database does not have SQL for stored procedures, so you use functions to realize it The integrity of table data structure requires that two records be inserted at one time. […]
-
PostgreSQL traversal of arrays
PostgreSQL provides array types. Let me demonstrate how to use it Create a table with array type fields. create table test_array(id serial primary key, str1 int[][][]); Insert two pieces of test data. insert into test_array values (1,array[[[1,2],[3,4],[5,6]],[[20,30],[40,50],[70,100]]]); insert into test_array values (2,array[[[100,200],[300,400],[500,600]],[[2000,3000],[4000,5000],[7000,10000]]]); In order to see the result set intuitively, we have to replace the […]