Common small syntax 2 in postgresql

Example 1. PostgreSQL testing may require loop insertion of N multiple data. At this time, writing a function is troublesome. We can use generate_series For example: mrapp # createtabletest_series (idint)

Postgresql commonly used small syntax 2 postgresql commonly used small syntax http://www.2cto.com/database/201305/210793.html 1. for tests in PG, we may need to insert N multiple data cyclically. In this case, writing a function is troublesome. We can use generate_series For example: mrapp = # create table test_series (id int)

Common small syntax 2 in postgresql

Common small syntax in postgresql

Http://www.2cto.com/database/201305/210793.html

1. for tests in PG, we may need to insert N multiple data cyclically. At this time, writing a function is quite troublesome. We can use generate_series.

Example:

Mrapp = # create table test_series (id int );

CREATE TABLE

Mrapp = # insert into test_series (id) select generate_series (1,100000 );

Inserts 0 100000

Mrapp = # select count (1) from test_series;

Count

--------

100000

(1 line record)

2. Compare the number of overlapping strings in postgresql

First, we need to convert array into row data and then deduplicate it with another converted array, and then combine it into an array.

Example:

Mrapp = # select unnest (array [1, 2, 4]);

Unnest

--------

1

2

4

(3 rows of Records)

Mrapp = # select unnest (array [1, 2, 4]) intersect select unnest (array [2, 3, 4]);

Unnest

--------

2

4

(2 rows of Records)

Mrapp = # select array (select unnest (array [1, 2, 4]) intersect select unnest (array [2

, 3, 4]);

Array

-------

{2, 4}

(1 line record)

3. Convert postgresql columns to Arrays

The array_agg function can be used for processing.

Example:

Mrapp = # select 1 as a union select 2 as a union select 3 as;

A

---

1

2

3

(3 rows of Records)

Mrapp = # select array_agg (t. a) from (select 1 as a union select 2 as a union sele

Ct 3 as a) as t;

Array_agg

-----------

{1, 2, 3}

(1 line record)