Initial practice of PostgreSQL array type

Practical environment

Database: PostgreSQL 9.4; operating system: Windows

To create a database that contains an array type

Note that when setting the default value (you can of course not specify a default value), declare the type of the array, declaring ":: bigint[]" as such.

CREATE TABLE Testarray (ID serial primary key,images bigint[] default array[]::bigint[]);

Inserting array values

Note that when you insert an array, you also declare the type of the array, as above

INSERT into Testarray (images) values (array[1,2,3,4,5,6]::bigint[]);

Querying the data you just inserted

Query statement:

SELECT * from Testarray;

Results:

Operation of the arrayDetermine if an element exists (operator: "any")

Be careful to specify the type for the queried array.

Select 0 = Any ((select images from Testarray where id=1):: int[]) as Iscontain

Query Result:

Delete Element (Array_romeve), but does not affect persistent data

Select Array_remove ((select images from Testarray):: varchar[], ' 1 ');

Query Result:

Delete element, save result

The idea is that after the data is queried for operation ("| |" is an array operator, merging elements and arrays), and then saving back to the database. You can delete a single element, or you can delete an element set (another array), extrapolate.

Update Testarray Set images = (SELECT images from Testarray where id=1):: int[] | | 1;

Select images from Testarray;

Results:

Adding elements to the data (operator: "| |" ), but do not save

Adding elements to an array is actually merging the elements into an array, in many ways, only from the "| |" The use of the operator.

Adding a single element to an array

Select (select images from Testarray where id=1):: int[] | | Newimages;

Results:

Add multiple elements to an array

Select (select images from Testarray where id=1):: int[] | | Array[200,300,300,400]::int[] as newimages;

Results:

Add elements to the data and save

Ideas with 5.3

Update Testarray update Set images = (SELECT images from Testarray where id=1):: int[] | | Array[200,300,300,400]::int[];

Select images from Testarray;

Results:

Appendix

This blog is not a systematic tutorial, just a simple introduction to the use of PostgreSQL data types. Operators and functions on the PostgreSQL array type can be found in the official documentation: http://www.postgresql.org/docs/9.1/static/functions-array.html

PostgreSQL Array type preliminary practice