Shulou(Shulou.com)06/01 Report--

Getting started with PostgreSQL-installation and basic use (Ubuntu)

PostgreSQL is a free object-relational database server (ORDBMS), which claims to be "the most advanced open source relational database in the world".

PostgreSQL is an object-relational database based on POSTGRES 4.2 developed by the computer Department of the University of California.

Today on the Ubuntu system, let's install and simply use the PostgreSQL database.

1. Check the current system version: $cat / etc/issueUbuntu 16.04.6 LTS\ n\ l $sudo lsb_release-aLSB Version: core-9.20160110ubuntu0.2-amd64:core-9.20160110ubuntu0.2-noarch:security-9.20160110ubuntu0.2-amd64:security-9.20160110ubuntu0.2-noarchDistributor ID: UbuntuDescription: Ubuntu 16.04.6 LTSRelease: 16.04Codename: xenial

The system is Ubuntu 16.04.6 LTS.

two。 Install PostgreSQL$ sudo apt-get install postgresql

The execution example is as follows:

$sudo apt-get install postgresqlReading package lists... DoneBuilding dependency tree Reading state information... DoneThe following additional packages will be installed: libpq5 postgresql-9.5 postgresql-client-9.5 postgresql-client-common postgresql-common postgresql-contrib-9.5 ssl-cert... ... Creating config file / etc/postgresql-common/createcluster.conf with new versionCreating config file / etc/logrotate.d/postgresql-common with new versionBuilding PostgreSQL dictionaries from installed myspell/hunspell packages...Removing obsolete dictionary files:Setting up postgresql-9.5 (9.5.19-0ubuntu0.16.04.1). Creating new cluster 9.5/main. Config / etc/postgresql/9.5/main data / var/lib/postgresql/9.5/main locale en_US.UTF-8 socket / var/run/postgresql port 5432update-alternatives: using / usr/share/postgresql/9.5/man/man1/postmaster.1.gz to provide / usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto modeSetting up postgresql (9.5+173ubuntu0.2)... Setting up postgresql-contrib-9.5. 19-0ubuntu0.16.04.1)... Processing triggers for libc-bin (2.23-0ubuntu11)... Processing triggers for ureadahead (0.100.0-19.1)... Processing triggers for systemd (229-4ubuntu21.21).

The server (postgresql-9.5) and client (postgresql-client-9.5) of postgresql are installed by default.

PostgreSQL 12 has been released on October 3, 2019. If you want to install the latest version, you need to update the source and join PostgreSQL Apt Repository.

You can use psql-- version to view the currently installed version:

$psql-- versionpsql (PostgreSQL) 9.5.19

After installation, a database named postgres and a database user named postgres are generated by default.

At the same time, a Linux system user named postgres is generated.

You can view it using the following command:

# View user $cat / etc/passwd# view user group $cat / etc/group3. To use the PostgreSQL console to modify the password of a postgres database user

The database user of the default generated postgres does not have a password, so now we use the identity of the postgres Linux user to log in to the administrative console.

# switch to postgres user. $sudo su-postgrespostgres@iZm5e8p54dk31rre6t96xuZ:~$ postgres@iZm5e8p54dk31rre6t96xuZ:~$ whoamipostgres

The Linux user postgres logs in as a postgres database user with the same name without entering a password.

Postgres@iZm5e8p54dk31rre6t96xuZ:~$ psqlpsql (9.5.19) Type "help" for help.postgres=#

Use the\ password command to set a password for the postgres user

Postgres=# postgres=# CREATE USER db_user WITH PASSWORD 'PWD123456';CREATE ROLEpostgres=#

Create the user database, in this case testdb, and specify the owner as db_user.

Postgres=# CREATE DATABASE testdb OWNER db_user;CREATE DATABASEpostgres=#

Give all permissions of the testdb database to the db_user database user, otherwise db_user can only log on to the console and has no database operation rights.

Postgres=# GRANT ALL PRIVILEGES ON DATABASE testdb TO db_user;GRANT

Use\ du to view the current database user:

Http://m.qd8.com.cn/yiyao/xinxi21_3709996.html

Postgres=#\ du List of rolesRole name | Attributes | Member of-+-db_user | | | {} postgres | Superuser | Create role,Create DB,Replication,Bypass RLS | {}

Finally, exit the console with the\ Q command and the current db_user Linux user with the exit command.

Postgres=#\ qpostgres@iZm5e8p54dk31rre6t96xuZ:~$ postgres@iZm5e8p54dk31rre6t96xuZ:~$ exitlogout4. An example of basic operation of database

Create the database and delete the database:

# create database postgres=# CREATE DATABASE lusiadas;CREATE DATABASE# and delete database postgres=# DROP DATABASE lusiadas;DROP DATABASE

Use\ c to switch the database:

Postgres=# CREATE DATABASE testdb;CREATE DATABASEpostgres=#\ c testdb;SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) You are now connected to database "testdb" as user "postgres".

New and deleted tables:

# create a table tb_test: (two fields, where id is self-increasing ID) testdb= > CREATE TABLE tb_test (id bigserial, name VARCHAR (20)); CREATE TABLE# delete a table tb_testtestdb= > DROP table tb_test;DROP TABLE

Add, delete, modify and search operations:

# create a user table tb_users (three fields, where id is self-increment ID) testdb= > CREATE TABLE tb_users (id bigserial, age INT DEFAULT 0, name VARCHAR (20)); CREATE TABLE # insert data testdb= > INSERT INTO tb_users (name, age) VALUES ('Zhang Sanfeng', 212) using INSERT statement; INSERT 01 testbb = > INSERT INTO tb_users (name, age) VALUES ('Li Siguang', 83); INSERT 01 testdb= > INSERT INTO tb_users (name, age) VALUES ('Wang Zhongyang', 58) INSERT 0 query data testdb= > select * from tb_users; id | age | name-+-+-1 | 212 | Zhang Sanfeng 2 | 83 | Li Siguang 3 | 58 | Wang Chongyang (3 rows) testdb= > select * from tb_users WHERE id=3 Id | age | name-+-+-3 | 58 | Wang Chongyang (1 row) # Update data (the number of updates is output after execution, and the output is `Chongyang 0` because the second execution fails) testdb= > UPDATE tb_users set name = 'Quanzhen faction Wang Chongyang' WHERE name = 'Wang Chongyang'; UPDATE 1testdb= > UPDATE tb_users set name = 'Quanzhen faction Wang Chongyang' WHERE name = 'Wang Chongyang' UPDATE insert 2 data testdb= > INSERT INTO tb_users (name, age) VALUES ('Zhao Si', 0); INSERT 0 1 testDB = > INSERT INTO tb_users (name, age) VALUES ('Zhao Wuniang', 0); INSERT 0 'fuzzy query testdb= > SELECT * FROM tb_users WHERE name LIKE' Zhao%' Id | age | name-+-+-4 | 0 | Zhao Wuniang 5 | 0 | Zhao Si (2 rows) # modify table structure: add field testdb=# ALTER TABLE tb_users ADD email VARCHAR (50); ALTER TABLE# modify table structure: modify field testdb=# ALTER TABLE tb_users ALTER COLUMN email TYPE VARCHAR (100); ALTER TABLE# delete field testdb=# ALTER TABLE tb_users DROP COLUMN email ALTER TABLE# delete record testdb= > DELETE FROM tb_users WHERE id = 5th delete 1

Use pg_database_size () to view the size of the database:

Testdb=# select pg_database_size ('testdb'); pg_database_size-7991967 (1 row) testdb=# select pg_size_pretty (pg_database_size (' testdb')); pg_size_pretty-7805 kB (1 row) 5.PostgreSQL timestamp type

Query current_timestamp

Testdb=# select current_timest current_timestamp-- 2019-11-11 08 33 JV 35.369887 PUB00 (1 row)

Use current_timestamp (0) to define a time type with a precision of 0: (with time zone)

Testdb=# select current_timestamp (0); current_timestamp-2019-11-11 08 row 31 purl 0815 00 (1 row)

Using current_timestamp (0) to define the time type precision is 0: (excluding time zone) Jiaozuo National Medical Hall Gastrointestinal Hospital is good: http://jz.lieju.com/zhuankeyiyuan/37175212.htm

Testdb=# select current_timestamp (0): timestamp without time zone; current_timestamp-2019-11-11 08:31:20 (1 row) testdb=# select cast (current_timestamp (0) as timestamp without time zone); current_timestamp-2019-11-11 08:32:26 (1 row)

Timestamp:

Testdb=# select extract (epoch from now ()); date_part-1573461495.47821 (1 row)

Set the database time zone:

The view pg_timezone_names saves all the time zones to choose from:

# check time zone select * from pg_timezone_names

For example, you can choose Shanghai Asia/Shanghai or Chongqing Asia/Chongqing, the simplest direct PRC:

Testdb=# set time zone 'PRC'; SETtestdb=# show time zone; TimeZone-PRC (1 row) testdb=# SELECT LOCALTIMESTAMP (0); localtimestamp-2019-11-11 16:42:54 (1 row)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.