WebTuts - seomat.com

Your ultimate destination for comprehensive, easy-to-follow web tutorials.

Creating a table in a Postgres database

In a database, a table represents a structured collection of data, organizing information into rows and columns.

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:

    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)
    ('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.

id name age
1 Eduard 32
2 Assiye 31
3 Emma 7
4 Nicholas 11

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

SELECT name, age FROM Persons;
name age
Eduard 32
Assiye 31
Emma 7
Nicholas 11