Operating tables

Operating tables#

This section focuses on creating, fulfilling, and analyzing tables in PostgreSQL.

For more infomation check:

Manual on CREATE TABLE directive in postgreSQL.

docker run -d --rm\
    --name building_tables_examples_pg\
    -e POSTGRES_PASSWORD=postgres\
    postgres:15.4

sleep 5

c5fefba317fe96b98bff03f96e05c92d6ea2c446aa6d249b0478007988e0fa83

Note: Don’t forget to stop the experimental container afterward.

docker stop building_tables_examples_pg

building_tables_examples_pg

Default values#

You can define default values that will be used during table creation. You need to use the DEFAULT keyword after specifying the column type, followed by the value that the column should take.

An important practical nuance is that you can use expressions as values for DEFAULT. These are used when new table records are created.

Check more:

Page “Default Values” on the official postgreSQL manual.

In the following example, a table is created that defines DEFAULT values for columns of different data types:

In the following example, a table is created with columns of different data types, and DEFAULT values are assigned to each. Specifically:

For BOOL, REAL and JSONB data types, literal values are used as defaults.
For TIMESTAMP, the NOW() function is used as a default expression, which returns the current timestamp at the moment of insertion.

--postgreSQL
DROP TABLE IF EXISTS default_values_example;
CREATE TABLE default_values_example (
    bool_value BOOL DEFAULT True,
    real_value REAL DEFAULT 10,
    date_time TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    json_type JSONB DEFAULT '{"A": 10, "B": 20}'
)

NOTICE: table "default_values_example" does not exist, skipping
DROP TABLE
CREATE TABLE

The follwing cell shows inserting to the tables procedure. With sepecifying DEFAULT instead of columns names you can create row with totaly default values.

--postgreSQL
INSERT INTO default_values_example DEFAULT VALUES;

INSERT 0 1

Or you can specify only some of the columns; the others will use their default values:

--postgreSQL
INSERT INTO default_values_example(bool_value, real_value) 
VALUES
    (false, 30),
    (false, 32);

INSERT 0 2

The following cell displays the table that we obtained at the end.

--postgreSQL
SELECT * FROM default_values_example;

SELECT 3

bool_value	real_value	date_time	json_type
True	10	2025-05-10 17:12:59.923168	{'A': 10, 'B': 20}
False	30	2025-05-10 17:12:59.923168	{'A': 10, 'B': 20}
False	32	2025-05-10 17:12:59.923168	{'A': 10, 'B': 20}

Tables properties#

It’s a typical task to analyze your tables. This section considers questions associated with columns in tables, their types, and other properties. For more check special page.

The following cell shows an example of how you can list all tables available in your database by using the \dt command.

docker exec -i building_tables_examples_pg psql -U postgres << EOF
\dt
EOF

Error response from daemon: No such container: building_tables_examples_pg

Indexes#

Indexing in PostgreSQL is a way to increase the performance of queries, but it adds overhead to the database system as a whole.

For more information, check the corresponding chapter in the PostgreSQL documentation.

The following cell creates a table that contains a relatively large number of rows. An index is added to one of the columns (indexed_id).

--postgreSQL
DROP TABLE IF EXISTS indexes_example;
CREATE TABLE indexes_example (just_id integer, indexed_id integer, content varchar);

INSERT INTO 
    indexes_example (just_id, indexed_id, content)
SELECT
    (random()*100)::INT,
    (random()*100)::INT,
    chr(65 + floor(random()*26)::INT)
FROM
    generate_series(1, 1000000);

CREATE INDEX test_index ON indexes_example (indexed_id);

NOTICE: table "indexes_example" does not exist, skipping
DROP TABLE
CREATE TABLE
INSERT 0 1000000
CREATE INDEX

Now let’s compare the performance of the query that adds conditions under the just_id column.

--postgreSQL
EXPLAIN ANALYZE SELECT * FROM indexes_example WHERE just_id=50;

EXPLAIN

QUERY PLAN
Gather (cost=1000.00..12114.33 rows=5000 width=40) (actual time=0.168..28.916 rows=9869 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on indexes_example (cost=0.00..10614.33 rows=2083 width=40) (actual time=0.025..22.682 rows=3290 loops=3)
Filter: (just_id = 50)
Rows Removed by Filter: 330044
Planning Time: 0.168 ms
Execution Time: 29.217 ms

And the query that adds conditions under the indexed_id column.

--postgreSQL
EXPLAIN ANALYZE SELECT * FROM indexes_example WHERE indexed_id=50;

EXPLAIN

QUERY PLAN
Bitmap Heap Scan on indexes_example (cost=59.17..5640.65 rows=5000 width=40) (actual time=1.246..5.600 rows=9967 loops=1)
Recheck Cond: (indexed_id = 50)
Heap Blocks: exact=4580
-> Bitmap Index Scan on test_index (cost=0.00..57.92 rows=5000 width=0) (actual time=0.627..0.627 rows=9967 loops=1)
Index Cond: (indexed_id = 50)
Planning Time: 0.059 ms
Execution Time: 5.880 ms

The second query typically executes faster.

Operating tables

Contents

Operating tables#

Default values#

Tables properties#

Indexes#