“This is the 17th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

I had been using MySQL until May this year, but I started to use PgSQL in May this year because I changed my job.

How I feel about it

  1. First of all, the grammar of the two is basically the same, there is no big difference in basic use
  2. PgSQL haveSEQUENCEPgSQL creates a primary key when we create a new tableSEQUENCE. (SEQUENCE objects (also known as SEQUENCE generators) are special single-row tables created with CREATE SEQUENCE. A sequence object is usually used to generate a unique identifier for a row or table.)
  3. PgSQL columns with camel names use double quotation marks when querying""Wrap it up, otherwise you won’t find it, because capital letters are automatically converted to lowercase letters
  4. PgSQL json type is very powerful and easy to use, but also because today I learned the use of JSON type, as if to open the door to a new world, sorted out this article.
  5. PgSQL after the table is createdalmostThe order of fields cannot be changed. I have done some research, and there is a solution, but it is extremely complicated, so I use the word “almost”. MySQL doesn’t have this problem. For example, we can use visual tools to drag and drop the order of fields and hit Save. (If you are used to viewing data through visual tools, but do not support changing the field sorting time can be quite painful things; So I got used to ditching visualizations and using SQL commands to query data, which was pretty cool.)

Skills summary

PgSQL supports specifying group by for JSON-type fields

Description:

  • Message is a JSON type

  • Title Content is the field in Message

SELECT userid,message->>'title',message->>'content',COUNT(*) as mcount from tbl_system_message WHERE "type" = 7 and message->>'title' ! ='xxx' GROUP BY userid,message->>'title',message->>'content' ORDER BY mcount DESC limit 100Copy the code

Based on the two fields group by

SELECT code,userid,"count"(*) as mcount FROM tbl_invite_code_consume GROUP BY code,userid ORDER BY mcount DESC
Copy the code

Querying duplicate Data

  • Query the data with the same name and count the number
SELECT DISTINCT name,COUNT(*) FROM tbl_school_info GROUP BY name HAVING COUNT(*) > 1;
Copy the code

Deleting Duplicate Data

  • Delete data with the same name and retain the value with the largest ID
DELETE from tbl_school_info where "id" NOT IN (SELECT max("id") FROM tbl_school_info GROUP BY "name")
Copy the code

Create a table

Create indexes when you create a table structure

create table tbl_video_check_job(
	id bigserial not null primary key,
	"jobId" varchar(40) not null,
	"requestId" varchar(40),
	"mediaId" varchar(40) not null,
	unionid varchar(40),
	suggestion varchar(10),
	label varchar(20),
	result json,
	createtime bigint,
	updatetime bigint
);
create index idx_video_check_job_jobid on tbl_video_check_job("jobId");
Copy the code

between and

  • It contains the left and right boundaries
a between x and y 
Copy the code

Equivalent to

a >=x and a <=y
Copy the code

Last but not least

Technical group please come here. Or add my wechat account wangzhongyang0601 to learn and make progress together.

Thank you for your likes, comments and attention. Thank you for your support and thanks to ღ(´ · ᴗ · ‘)