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.

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-09-23 08:59:33.969619	{'A': 10, 'B': 20}
False	30	2025-09-23 08:59:33.969619	{'A': 10, 'B': 20}
False	32	2025-09-23 08:59:33.969619	{'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.

A great way to find information about the tables in the PostgreSQL is to load the pg_catalog.pg_tables table:

--postgreSQL
SELECT * FROM pg_catalog.pg_tables;

SELECT 69

schemaname	tablename	tableowner	tablespace	hasindexes	hasrules	hastriggers	rowsecurity
public	default_values_example	postgres		False	False	False	False
pg_catalog	pg_statistic	postgres		True	False	False	False
pg_catalog	pg_type	postgres		True	False	False	False
pg_catalog	pg_foreign_table	postgres		True	False	False	False
pg_catalog	pg_authid	postgres	pg_global	True	False	False	False
pg_catalog	pg_statistic_ext_data	postgres		True	False	False	False
pg_catalog	pg_user_mapping	postgres		True	False	False	False
pg_catalog	pg_subscription	postgres	pg_global	True	False	False	False
pg_catalog	pg_attribute	postgres		True	False	False	False
pg_catalog	pg_proc	postgres		True	False	False	False
pg_catalog	pg_class	postgres		True	False	False	False
pg_catalog	pg_attrdef	postgres		True	False	False	False
pg_catalog	pg_constraint	postgres		True	False	False	False
pg_catalog	pg_inherits	postgres		True	False	False	False
pg_catalog	pg_index	postgres		True	False	False	False
pg_catalog	pg_operator	postgres		True	False	False	False
pg_catalog	pg_opfamily	postgres		True	False	False	False
pg_catalog	pg_opclass	postgres		True	False	False	False
pg_catalog	pg_am	postgres		True	False	False	False
pg_catalog	pg_amop	postgres		True	False	False	False
pg_catalog	pg_amproc	postgres		True	False	False	False
pg_catalog	pg_language	postgres		True	False	False	False
pg_catalog	pg_largeobject_metadata	postgres		True	False	False	False
pg_catalog	pg_aggregate	postgres		True	False	False	False
pg_catalog	pg_statistic_ext	postgres		True	False	False	False
pg_catalog	pg_rewrite	postgres		True	False	False	False
pg_catalog	pg_trigger	postgres		True	False	False	False
pg_catalog	pg_event_trigger	postgres		True	False	False	False
pg_catalog	pg_description	postgres		True	False	False	False
pg_catalog	pg_cast	postgres		True	False	False	False
pg_catalog	pg_enum	postgres		True	False	False	False
pg_catalog	pg_namespace	postgres		True	False	False	False
pg_catalog	pg_conversion	postgres		True	False	False	False
pg_catalog	pg_depend	postgres		True	False	False	False
pg_catalog	pg_database	postgres	pg_global	True	False	False	False
pg_catalog	pg_db_role_setting	postgres	pg_global	True	False	False	False
pg_catalog	pg_tablespace	postgres	pg_global	True	False	False	False
pg_catalog	pg_auth_members	postgres	pg_global	True	False	False	False
pg_catalog	pg_shdepend	postgres	pg_global	True	False	False	False
pg_catalog	pg_shdescription	postgres	pg_global	True	False	False	False
pg_catalog	pg_ts_config	postgres		True	False	False	False
pg_catalog	pg_ts_config_map	postgres		True	False	False	False
pg_catalog	pg_ts_dict	postgres		True	False	False	False
pg_catalog	pg_ts_parser	postgres		True	False	False	False
pg_catalog	pg_ts_template	postgres		True	False	False	False
pg_catalog	pg_extension	postgres		True	False	False	False
pg_catalog	pg_foreign_data_wrapper	postgres		True	False	False	False
pg_catalog	pg_foreign_server	postgres		True	False	False	False
pg_catalog	pg_policy	postgres		True	False	False	False
pg_catalog	pg_replication_origin	postgres	pg_global	True	False	False	False
pg_catalog	pg_default_acl	postgres		True	False	False	False
pg_catalog	pg_init_privs	postgres		True	False	False	False
pg_catalog	pg_seclabel	postgres		True	False	False	False
pg_catalog	pg_shseclabel	postgres	pg_global	True	False	False	False
pg_catalog	pg_collation	postgres		True	False	False	False
pg_catalog	pg_parameter_acl	postgres	pg_global	True	False	False	False
pg_catalog	pg_partitioned_table	postgres		True	False	False	False
pg_catalog	pg_range	postgres		True	False	False	False
pg_catalog	pg_transform	postgres		True	False	False	False
pg_catalog	pg_sequence	postgres		True	False	False	False
pg_catalog	pg_publication	postgres		True	False	False	False
pg_catalog	pg_publication_namespace	postgres		True	False	False	False
pg_catalog	pg_publication_rel	postgres		True	False	False	False
pg_catalog	pg_subscription_rel	postgres		True	False	False	False
pg_catalog	pg_largeobject	postgres		True	False	False	False
information_schema	sql_parts	postgres		False	False	False	False
information_schema	sql_features	postgres		False	False	False	False
information_schema	sql_implementation_info	postgres		False	False	False	False
information_schema	sql_sizing	postgres		False	False	False	False

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#