Creating a table in a Postgres database

A database consists of one or more tables.

Tables are the basic structures that store the data. These consist of rows and columns, where each row represents a record, and each column represents a field or attribute.

Create a new table

To create a table in Postgres, you use the SQL command CREATE TABLE. Here's a simple example:

CREATE TABLE Persons (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

This creates a table named Persons with columns id, name, and age. The id column is an auto-incrementing serial column, and PRIMARY KEY designates it as the primary key for the table.

Postgres supports a variety of data types. Here's a list of some common data types:

Numeric Types:

• INTEGER, BIGINT, SMALLINT: Integer types.
• NUMERIC, DECIMAL: Fixed-point or floating-point numbers.

Character Types:

• CHAR(n), VARCHAR(n): Fixed-length and variable-length character strings.
• TEXT: Variable-length character string.

Binary Types:

• BYTEA: Binary data.

Date/Time Types:

• DATE: Date only.
• TIME: Time of day.
• TIMESTAMP: Date and time.
• INTERVAL: Period of time.

Boolean Type:

• BOOLEAN: true or false.

Enumerated Types:

• ENUM: A static, ordered set of values.

Geometric Types:

• POINT, LINE, LSEG, BOX, PATH, POLYGON, CIRCLE: Geometric primitives.

Network Address Types:

• INET: IPv4 or IPv6 host address.
• CIDR: IPv4 or IPv6 network address.

Bit String Types:

• BIT(n), BIT VARYING(n): Fixed or variable-length bit strings.

Text Search Types:

• TSVECTOR, TSQUERY: Text search vector and query.

UUID Type:

• UUID: Universally unique identifier.

JSON Types:

• JSON, JSONB: JSON data types.

Array Types:

• Arrays of any data type, e.g., INTEGER[] or TEXT[].

Composite Types:

• User-defined types with multiple fields.

Range Types:

• A range of values of a specified data type.

Hstore Type:

• A key-value store.

Insert data into table

To insert data into a Postgres table, you use the INSERT INTO statement. Here's an example based on the Persons table created earlier:

INSERT INTO Persons (name, age)
VALUES
    ('Eduard', 32),
    ('Assiye', 31),
    ('Emma', 7),
    ('Nicholas', 11)

Get the data from table

To retrieve data from a table in Postgres, you use the SELECT statement.

SELECT * FROM Persons;

This query retrieves all columns from the Persons table. The asterisk or star symbol (*) means all columns.

If you only want specific columns, you can list them instead of using the (*). For instance:

SELECT name, age FROM Persons;