The following is an introduction to the PostgreSQL website.

PostgreSQL is a powerful open source object-relational database system that uses and extends the SQL language and combines many features that can safely store and scale even the most complex data workloads. PostgreSQL, which traces its origins back to 1986 as part of the POSTGRES project at the University of California, Berkeley, has been actively developing PostgreSQL on its core platform for over 30 years.

Both the PostgreSQL website and the Chinese community can be used to learn about PostgreSQL. The production version can be downloaded from EDB as required.

This article uses version 12.4 under Windows-X64.

Perform the installation

In Windows, the installation is relatively simple. Run the installation package and select an installation path as prompted

You are advised to install PostgreSQL in NTFS partitions

Select the required components. By default, select all

Select the location of the data store

Example Set the password of the super user (database administrator)

Use the default port number

Select the locale of the database (the character encoding format of the database at runtime), locale Settings. Select the Default [Default localt] or “C”(no locale is used, select “C” if “Default locale” will cause incorrect installation)

And then install it

After installation, “Launch Stack Builder atexit?” If you deselect Launch Stack Builder, it will install various ancillary tools that you don’t need if you’re using PostgreSQL only. Click “Finish” to complete the installation

Modify Settings file

Go to the PostgreSQL data file, data\, and find its Settings file, postgresql.conf.

Open the file and locate the Settings item listen_addresses. The default value is *, indicating that all hosts can connect to the database. In general, for security reasons, the database only allows local connections, so change it to localhost and only allow local connections.

listen_addresses = 'localhost'		# what IP address(es) to listen on;
					# comma-separated list of addresses;
					# defaults to 'localhost'; use '*' for all
					# (change requires restart)
Copy the code

The PostgreSQL service must be repeated to take effect

Manage and use PostgreSQL

PostgreSQL provides web-based graphical management tool (pgAdmin 4) and SQL command line management tool SQL Shell(PSQL).

`pgAdmin 4`introduce

Open pgAdmin 4 and log in

As shown in the following figure, open pgAdmin 4. After the program starts, the web page managed by pgAdmin will be opened in the browser and the password will be entered.

PgAdmin 4 Uses pgAdmin rewritten using Python and JS /jQuery. PgAdmin 4 can be deployed in three modes: Desktop Deployment, Server Deployment (default), and Container Deployment.

Example Set the Chinese interface of pgAdmin 4

PgAdmin supports multi-language interfaces. The default interface is English. You can change the interface to Chinese, click the “File” drop-down menu and select “Preferences”. Find “Miscellaneous” -> “User Language” in the list on the left, then select “Chinese (Simplified)” in the drop-down list on the right, and click Save.

Refresh the page and it will become Chinese.

Database connection and management

Below, click on the left side of pgAdmin, Servers(which will prompt you for a login password), and when you expand, you will see PostgreSQL 12 and its associated database objects.

You can see that the newly installed PostgreSQL has a database, Postgres; Two tablespaces: pg_default and pg_global.

The default pg_default tablespace is used to store the system directory object, user table, user table index, temporary table, temporary table index, and internal temporary table. It is the default tablespace of the template database template0 and TEMPLate1.

Pg_global is the default space used to store shared system directories.

Creating a Server Connection

Right-click on the Servers on the left and choose “Create” — “Server”. In the “Create – Server” dialog box, set a connection name and specify a server group

Specify hostname or IP address, port, data, username, and password in Connect. Click “Save” to create and connect to the database server.

You can view and manage various database objects in the object browser on the left.

Right-click any node under the “Database” node and choose “Query Tools…” , or click any node under “Database”, under the “Tools” menu – click “Query Tools” to open the query editor.

The query tool is used to execute SQL statements and commands, run SQL scripts, display query execution plans, and export query results

Creating a database

Under the “server” or “database” node, right-click – “Create” – select “Database” to create a database.

As follows, specify database name, encoding table space, and so on

You can see SQL statements under the “SQL” TAB

CREATE DATABASE demo
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    CONNECTION LIMIT = - 1;

COMMENT ON DATABASE demo
    IS 'Tests to create database';
Copy the code

Create a table

Under the “Schemas” node, right click “Tables” and select Create table

In the “Create table” dialog box, specify the table name, and under the “Columns” TAB, add the necessary column names, types, etc

In SQL, you can view the corresponding SQL statement

CREATE TABLE public.test
(
    "Id" integer NOT NULL,
    "Name" character varying(50),
    "Desc" character varying(120),
    PRIMARY KEY ("Id")
);

ALTER TABLE public.test
    OWNER to postgres;

COMMENT ON TABLE public.test
    IS 'Create table Test';
Copy the code

After saving, you can see the newly created table test under the table

Delete table

Right-click the table “test” and select Delete/Remove

Deleting a Database

Right-click database “Demo” and select Delete/Remove

Command line management interface PSQL

PSQL use

Command line management interface

On Windows, go to the Start menu, PostgreSQL 12, or just search for PSQL in the Start menu

After opening PSQL, enter server, database, port, username and password to log in

\help allows you to view the syntax of each command

PSQL is used on the command line

You can also log in to PostgreSQL using the PSQL command line interface (CLI or Powershell) to perform query and management operations

Can be<PostgerSQL installation directory >\bin\ psqL.exePaths are added to environment variables for direct use in commands

Run the PSQL -u postgres command to log in to PostgreSQL using PLSQL. If postgres=# is displayed, the login is successful

PS C:\WINDOWS\ System32 > PSQL -u postgres Password of user postgres: PSQL (12.4) Enter"help"To get help information postgres=#
Copy the code

Create a database in PSQL

Create the database and switch to the new database as follows

postgres=# CREATE DATABASE SHOP;
CREATE DATABASE
postgres=# \c shopYou are now connected to the database"shop", the user"postgres".
shop=#
Copy the code

Change database: \c [database_name]

Change user: \c – [user_name]

PSQL Other commands

Specify username and database when logging in

When logging in to PostgreSQL, run the PSQL -u user -d dbname command to specify the user name and database

psql -U postgres -d postgres
Copy the code

List all databases:`\l`

shop=# \l Database list name|The owner|Character encoding|Proofread rules|             Ctype              |Access permissions-----------+----------+----------+--------------------------------+--------------------------------+-------------------- ---
 demo      | postgres | UTF8     | Chinese (Simplified)_China936. | Chinese (Simplified)_China936. |
 postgres  | postgres | UTF8     | Chinese (Simplified)_China936. | Chinese (Simplified)_China936. |
 shop      | postgres | UTF8     | Chinese (Simplified)_China936. | Chinese (Simplified)_China936. |
 template0 | postgres | UTF8     | Chinese (Simplified)_China936. | Chinese (Simplified)_China936. | =c/postgres          +
           |          |          |                                |                                | postgres=CTc/postgres
 template1 | postgres | UTF8     | Chinese (Simplified)_China936. | Chinese (Simplified)_China936. | =c/postgres          +
           |          |          |                                |                                | postgres=CTc/postgres
(5Line record) shop=#
Copy the code

Switching databases`\c dbname`

List the table`\dt`; View index`\di`; View table structure`\d tablename`

Changing a User password`\password [USERNAME]`

Exit the PSQL`\q`

other

PostgreSQL – Query table structure and index information

Query the table structure through the system data dictionary

select
col.table_schema,
col.table_name,
col.ordinal_position,
col.column_name,
col.data_type,
col.character_maximum_length,
col.numeric_precision,
col.numeric_scale,
col.is_nullable,
col.column_default,
des.description
from
information_schema.columns col left join pg_description des on
col.table_name::regclass = des.objoid
and col.ordinal_position = des.objsubid
where
table_schema = 'public'
and table_name = 'product'
order by
ordinal_position;
Copy the code

select * from information_schema.columns
where table_schema='public' and table_name='product';
Copy the code

Query index information in the system data dictionary

select
A.SCHEMANAME,
A.TABLENAME,
A.INDEXNAME,
A.TABLESPACE,
A.INDEXDEF,
B.AMNAME,
C.INDEXRELID,
C.INDNATTS,
C.INDISUNIQUE,
C.INDISPRIMARY,
C.INDISCLUSTERED,
D.DESCRIPTION
from
PG_AM B left join PG_CLASS F on
B.OID = F.RELAM left join PG_STAT_ALL_INDEXES E on
F.OID = E.INDEXRELID left join PG_INDEX C on
E.INDEXRELID = C.INDEXRELID left outer join PG_DESCRIPTION D on
C.INDEXRELID = D.OBJOID,
PG_INDEXES A
where
A.SCHEMANAME = E.SCHEMANAME
and A.TABLENAME = E.RELNAME
and A.INDEXNAME = E.INDEXRELNAME
and E.SCHEMANAME = 'public'
and E.RELNAME = 'product';
Copy the code

Query all table names

select
n.nspname,
relname
from
pg_class c,
pg_namespace n
where
c.relnamespace = n.oid
and nspname = 'public'
and relkind = 'r'
order by
relname;
Copy the code

View table structure

SELECT a.attnum, a.attname AS field, t.typname AS type, a.attlen AS length, a.atttypmod AS lengthvar
    , a.attnotnull AS notnull, b.description AS comment
FROM pg_class c, pg_attribute a
    LEFT JOIN pg_description b
    ON a.attrelid = b.objoid
        AND a.attnum = b.objsubid, pg_type t
WHERE c.relname = 'product'
    AND a.attnum > 0
    AND a.attrelid = c.oid
    AND a.atttypid = t.oid
ORDER BY a.attnum;
Copy the code

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

The first step toward a PostgreSQL | SQL tutorial zero: comprehensive PostgreSQL installation and use are introduced

Perform the installation

Modify Settings file

Manage and use PostgreSQL

`pgAdmin 4`introduce

Open pgAdmin 4 and log in

Example Set the Chinese interface of pgAdmin 4

Database connection and management

Creating a Server Connection

Creating a database

Create a table

Delete table

Deleting a Database

Command line management interface PSQL

PSQL use

PSQL is used on the command line

Create a database in PSQL

PSQL Other commands

Specify username and database when logging in

List all databases:`\l`

Switching databases`\c dbname`

List the table`\dt`; View index`\di`; View table structure`\d tablename`

Changing a User password`\password [USERNAME]`

Exit the PSQL`\q`

other

The first step toward a PostgreSQL | SQL tutorial zero: comprehensive PostgreSQL installation and use are introduced

Perform the installation

Modify Settings file

Manage and use PostgreSQL

pgAdmin 4introduce

Open pgAdmin 4 and log in

Example Set the Chinese interface of pgAdmin 4

Database connection and management

Creating a Server Connection

Creating a database

Create a table

Delete table

Deleting a Database

Command line management interface PSQL

PSQL use

PSQL is used on the command line

Create a database in PSQL

PSQL Other commands

Specify username and database when logging in

List all databases:\l

Switching databases\c dbname

List the table\dt; View index\di; View table structure\d tablename

Changing a User password\password [USERNAME]

Exit the PSQL\q

other

Related Posts

TLS 1.3 Handshake Protocol (上)

SpringCloud learning

Configure multipath multipathing in Linux

`pgAdmin 4`introduce

List all databases:`\l`

Switching databases`\c dbname`

List the table`\dt`; View index`\di`; View table structure`\d tablename`

Changing a User password`\password [USERNAME]`

Exit the PSQL`\q`