programmer group

Preliminary Exploration of New Functions | RDS MySQL 8.0 Supports DML Statement returning

background MySQL execution result messages for state are usually divided into two categories: Resultset and OK/ERR. For DML statements, OK/ERR messages are returned, including several impact records, scanning records and other attributes. However, in many business scenarios, DML statements such as INSERT/UPDATE/DELETE are usually followed by SELECT to query the current record content for subsequent business processing. In order to reduce a Client <-> DB Server interaction, similar PostgreSQL/Oracle provide returning clause to support...

blog.jooq.org

Using DISTINCT ON in Non-PostgreSQL Databases

A nice little gem in PostgreSQL’s SQL syntax is the DISTINCT ON clause, which is as powerful as it is esoteric. In a previous post, we’ve blogged about some caveats to think of when DISTINCT and ORDER BY are used together. The bigger picture can be seen in our article about the logical order of operations in SQL SELECT. The PostgreSQL documentation explains it well: SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are...

programmer group

Practice of pgbench

pgbench is a simple program to run benchmarks on PostgreSQL. It may run the same sequence of SQL commands over and over in concurrent database sessions and calculate the average transaction rate (the number of transactions per second). pgbench supports two ways of testing: 1. By default, pgbench will test a TPC-B-based but more relaxed scenario involving five SELECT, UPDATE, and INSERT commands per transaction. 2. Users can easily test other situations by writing their own transaction script files. Let's first look at the default test method...

programmer group

Upgrade of PostgreSQL Large Version

0. Explanation The experiment was upgraded from pg10 to pg11 1. Source code installation, compiling new version itm_pg@oracle-> cd postgresql-11.1 Use the new data directory itm_pg@oracle-> ./configure --prefix=/home/itm_pg/pgsql11.1 --with-perl --with-python itm_pg@oracle->make world -j 8 itm_pg@oracle->make install-world 2. Initialization of a new database cluster itm_pg@oracle-> /home/itm_pg/pgsql11.1/bin/initdb -D /home/itm_pg/pgsql11.1/pgdata -E UTF8 --locale=C -U postgres Here initdb must be written as an absolute path!...

programmer group

springboot+mybatis to realize the separation of reading and writing of database

introduce With the development of business, besides splitting business modules, the separation of reading and writing of database is also a common optimization method. The solution uses AbstractRouting Data Source and mybatis plugin to dynamically select data sources The main reason for choosing this solution is that there is no need to change the original business code. It is very friendly. Note: mybatis-plus is used in demo, as is mybatis in practice The database used in demo is postgres, and the actual database example of any type of...

blog.logrocket.com

How to create a React Native app with PostgreSQL and GraphQL: Part 1

React Native is a great option when it comes to developing mobile applications for both iOS and Android smartphones. With React Native you can write an application that works on both platforms with the only difference coming at the view level, with iOS and Android rendering differently. In this two-part series, we’ll be looking at how we can develop an application with React Native and GraphQL. To do this, we’ll be building a simple note-taking app that allows our user to add notes, view, edit and delete them. For the first part, we’ll...

programmer group

Hot standby of postgresql and Realization of master-slave switching

Backup deployment Pre-conditions The main and standby databases for deploying the main and standby machines should be in the same version, and a full-time operation of the master machine is required at the beginning. So leave plenty of space and don't do it during peak business hours. The modification of main engine and standby machine mainly involves pg_hba.conf postgresql.conf *** recovery.conf(salve) or recovery.done(master)*** Three documents The configuration here is: master 10.0.4.119 slave 10.0.4.104 The database version for...

programmer group

Use SQL to get the table structure of each database (MySQL, PostgreSQL, Oracle, MsSQL)

Usually when we refer to database table structure, we are concerned about several attributes:Field name, type, length, whether primary key, whether self-increasing, whether empty, default value, notes.So what is it like to use SQL statements to get these attributes in mainstream databases? MySQL database As the most popular database nowadays, MySQL is the easiest to get SQL.Usually we can do it with the following line: SHOW FULL COLUMNS FROM xxx; Of course, you can also use MySQL’s own databaseinformation_schemaTables such as...

programmer group

Settings of Self-Increasing Fields in Databases (MySQL, PostgreSQL, Oracle, MsSQL)

In the process of database design, we usually set the ID field in the database table to increase by itself. Here are some of the common onesData dictionary tableFor example, how to set up self-incremental fields under each database is explained. MySQL MySQL database only needs to be added to the target fieldAUTO_INCREMENTAnd set the tableAUTO_INCREMENT=x。xNumber from the beginning of the increase. Reference examples: CREATE TABLE `dictionary` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', ` parent_id `int(10) unsigned...