Welcome to Tencent Cloud + community, get more Tencent mass technology practice dry goods oh ~

This article was published by Angel Yu in Cloud + Community

What is PostgreSQL?

PostgreSQL is a free object-relational database server distributed under a flexible BSD-style license. It provides users with an alternative to other open source database systems and proprietary systems. We suggest you use the cloud to build the database, save trouble operation such as data migration, database see: cloud.tencent.com/product/cdb…

In this article, we’ll discuss how to create and manage tables in the postgreSQL interface. You’ll learn how to properly configure tables and use them to store your information.

How do I install and log in to PostgreSQL on Ubuntu

We will install PostgreSQL on Ubuntu, but it should work in the default repository for most other distributions.

Enter the following commands to install:

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib
Copy the code

After installation, create a new user to manage the database we will create:

sudo adduser postgres_user
Copy the code

Log in to the default PostgreSQL user (called “Postgres”) to create a database and assign it to a new user:

sudo su  -  postgres
PSQL
Copy the code

You will be put into the PostgreSQL command prompt.

Create a new user that matches the system user you created. Then create the database managed by the user:

CREATE USER postgres_user The password is'password';
CREATE DATABASE my_postgres_db OWNER postgres_user ;
Copy the code

Run the following command to exit the screen:

\q
Copy the code

Exit the default “Postgres” user account and log in to the user you created using the following command:

exit
sudo su - postgres_user
Copy the code

Use the following command to log in to the database you created:

psql my_postgres_db
Copy the code

We are now ready to look at table management.

Table creation syntax in PostgreSQL

We don’t have any tables in our database yet. We can verify this with this command:

\d
Copy the code
No relations found.
Copy the code

We can create a new table by using the following syntax:

CREATE TABLE new_table_name (
    table_column_title TYPE_OF_DATA column_constraints,
    next_column_title TYPE_OF_DATA column_constraints,
    table_constraint
    table_constraint
) INHERITS existing_table_to_inherit_from;
Copy the code

All columns in an existing table are inherited in addition to those listed in the previous definition. The sections in parentheses are divided into two parts: column definitions and table constraints.

PostgreSQL column and table definitions

Column definitions follow the following syntax pattern:

column_name data_type (optional_data_length_restriction) column_constraints
Copy the code

Column names should be self-explanatory.

PostgreSQL Indicates the data type

The data type can be any of the following:

  • Boolean: Declare true or false values using “Boolean” or “bool”.
  • Character values
    • Char: holds one character
    • Char (#) : Saves the number of characters. Space will be inserted to fill any extra space.
    • Varchar (#) : Contains a maximum of # characters.
  • An integer value
    • Smallint: An integer between -32768 and 32767.
    • Int: An integer between -214783648 and 214783647.
    • Serial: an integer that is automatically filled.
  • Floating point value
    • Float (#) : a floating point number with at least # precision points.
    • Real: 8-byte floating point number
    • Numeric (#, after_dec) : A real number with # digits, followed by after_dec digits
  • Date and time values
    • Date: stores the date value
    • Time: indicates the storage time value
    • Timestamp: Stores date and time values
    • Timestamptz: Stores a timestamp that contains time zone data
    • Interval: Stores the difference between two timestamp values
  • The geometric data
    • Point: Stores the coordinates of a pair of defined points
    • Line: Stores a set of points that map to a line
    • Lseg: Stores data that defines a line segment
    • Box: Stores the data that defines the rectangle
    • Polygon: Stores data that defines any enclosed space
  • Equipment specification
    • Inet: storage IP address
    • Macaddr: indicates the MAC address of the storage device

PostreSQL column and table constraints

Column definitions can also have constraints that provide rules for the data types found in the column. The following can be used as space-delimited values following data types:

  • NOT NULL: Columns cannot have NULL values
  • UNIQUE: The column values of any record cannot be the same. Null is always treated as a unique value
  • PRIMARY KEY: a combination of the above two constraints. Each table can only be used once
  • CHECK: Ensures that the condition of the value in the column is true
  • REFERENCES: Values must exist in a column of another table

After the columns are defined, table scope constraints can be declared. Table scope constraints can be UNIQUE, PRIMARY KEY, CHECK, or REFERENCES.

How to create a table in PostgreSQL

We will create a table called “PG_equipment” that defines various playground equipment. Enter the following table definitions:

CREATE TABLE pg_equipment (
    equip_id serial PRIMARY KEY,
    type varchar (50) NOT NULL,
    color varchar (25) NOT NULL,
    location varchar(25) check (location in ('north'.'south'.'west'.'east'.'northeast'.'southeast'.'southwest'.'northwest')),
    install_date date
    );
Copy the code
NOTICE:  CREATE TABLE will create implicit sequence "pg_equipment_equip_id_seq" for serial column "pg_equipment.equip_id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pg_equipment_pkey" for table "pg_equipment"
CREATE TABLE
Copy the code

We can view our new table by typing “\ d” at the prompt:

\d
Copy the code
                       List of relations
 Schema |           Name            |   Type   |     Owner     
--------+---------------------------+----------+---------------
 public | pg_equipment              | table    | postgres_user
 public | pg_equipment_equip_id_seq | sequence | postgres_user
(2 rows)
Copy the code

List the table, as well as the sequence created by the Equip_ID serial Data type statement.

How do I change table data in PostgreSQL

We can change the definition of a table using the following general syntax:

ALTER TABLE table_name Action_TO_Take;
Copy the code

For example, we can add a column to our “PG_equipment” table by typing:

ALTER TABLE pg_equipment ADD COLUMN functioning bool;
Copy the code
ALTER TABLE
Copy the code

We can view additional columns by typing:

\d pg_equipment
Copy the code
    Column    |         Type          |                            Modifiers                            
--------------+-----------------------+-----------------------------------------------------------------
 equip_id     | integer               | not null default nextval('pg_equipment_equip_id_seq'::regclass)
 type         | character varying(50) | not null
 color        | character varying(25) | not null
 location     | character varying(25) | 
 install_date | date                  | 
 functioning  | boolean               |
 . . .
Copy the code

To add a default value, provide the following command:

ALTER TABLE pg_equipment ALTER COLUMN functioning SET DEFAULT 'true';
Copy the code

If we want to ensure that this value is not null, we can do this:

ALTER TABLE pg_equipment ALTER COLUMN functioning SET NOT NULL;
Copy the code

To rename the column, use the following syntax:

ALTER TABLE pg_equipment RENAME COLUMN functioning TO working_order;
Copy the code

To delete the column we just created, type the following command:

ALTER TABLE pg_equipment DROP COLUMN working_order;
Copy the code

We can rename the entire table with the following command:

ALTER TABLE pg_equipment RENAME TO playground_equip;
Copy the code

Delete a PostgreSQL table

We can delete the table we created by typing the following command:

DROP TABLE playground_equip;
Copy the code
DROP TABLE
Copy the code

If we supply this command to a table that does not exist, we see the following error:

ERROR: table "playground_equip" does not exist
Copy the code

To avoid this error, we can tell postgreSQL to delete the table and return it successfully in any way. We do this by issuing the following command:

DROP TABLE IF EXISTS playground_equip;
Copy the code
NOTICE:  table "playground_equip" does not exist, skipping
DROP TABLE
Copy the code

This time, it tells us we can’t find the table, but continues instead of throwing an error.

conclusion

You should now know how to create and manage simple tables in PostgreSQL. These skills are useful if you are learning how to control PostgreSQL from the command line. For more Linux tutorials, please keep an eye on the Tencent Cloud + community.

Reference: How To Create, Remove, & Manage Tables in PostgreSQL on a Cloud Server

Question and answer

Hidden features of PostgreSQL?

reading

Nginx installation and configuration troubleshooting

How to back up your MySQL database

This section describes the changes in MySQL 8.0

Has been authorized by the author tencent cloud + community release, the original link: https://cloud.tencent.com/developer/article/1160560?fromSource=waitui

Welcome to Tencent Cloud + community or follow the wechat public account (QcloudCommunity), the first time to get more mass technology practice dry goods oh ~

Massive technical practice experience, all in the cloud plus community!