Tables properties#
In this section, I’ll describe some common tasks associated with posgres and their solutions.
The following cell creates all needed for examples in this page.
docker run --rm -d\
-e POSTGRES_PASSWORD=postgres \
--name tables_properties_example \
postgres:15.4 &> /dev/null
sleep 5
Stop container! After playing with examples, you should turn it off with the following command.
docker stop tables_properties_example &> /dev/null
Columns information#
There is an information_schema.columns table that contains information about the columns from the tables in the database.
The following cell shows a raw view of that table. It’s not empty because PostgreSQL, by default, has some system tables.
docker exec -i tables_properties_example psql -U postgres << EOF
SELECT *
FROM information_schema.columns
LIMIT 10;
EOF
table_catalog | table_schema | table_name | column_name | ordinal_position | column_default | is_nullable | data_type | character_maximum_length | character_octet_length | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | character_set_catalog | character_set_schema | character_set_name | collation_catalog | collation_schema | collation_name | domain_catalog | domain_schema | domain_name | udt_catalog | udt_schema | udt_name | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier | is_self_referencing | is_identity | identity_generation | identity_start | identity_increment | identity_maximum | identity_minimum | identity_cycle | is_generated | generation_expression | is_updatable
---------------+--------------+---------------+-------------+------------------+-------------------------------------------+-------------+-----------+--------------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+-----------------------+----------------------+--------------------+-------------------+------------------+----------------+----------------+---------------+-------------+-------------+------------+----------+---------------+--------------+------------+---------------------+----------------+---------------------+-------------+---------------------+----------------+--------------------+------------------+------------------+----------------+--------------+-----------------------+--------------
postgres | public | list_tables_1 | id | 1 | nextval('list_tables_1_id_seq'::regclass) | NO | integer | | | 32 | 2 | 0 | | | | | | | | | | | | | postgres | pg_catalog | int4 | | | | | 1 | NO | NO | | | | | | NO | NEVER | | YES
postgres | public | list_tables_1 | name | 2 | | YES | text | | 1073741824 | | | | | | | | | | | | | | | | postgres | pg_catalog | text | | | | | 2 | NO | NO | | | | | | NO | NEVER | | YES
postgres | public | list_tables_2 | id | 1 | nextval('list_tables_2_id_seq'::regclass) | NO | integer | | | 32 | 2 | 0 | | | | | | | | | | | | | postgres | pg_catalog | int4 | | | | | 1 | NO | NO | | | | | | NO | NEVER | | YES
postgres | public | list_tables_2 | name | 2 | | YES | text | | 1073741824 | | | | | | | | | | | | | | | | postgres | pg_catalog | text | | | | | 2 | NO | NO | | | | | | NO | NEVER | | YES
postgres | public | list_tables_3 | id | 1 | nextval('list_tables_3_id_seq'::regclass) | NO | integer | | | 32 | 2 | 0 | | | | | | | | | | | | | postgres | pg_catalog | int4 | | | | | 1 | NO | NO | | | | | | NO | NEVER | | YES
postgres | public | list_tables_3 | name | 2 | | YES | text | | 1073741824 | | | | | | | | | | | | | | | | postgres | pg_catalog | text | | | | | 2 | NO | NO | | | | | | NO | NEVER | | YES
postgres | public | list_tables_4 | id | 1 | nextval('list_tables_4_id_seq'::regclass) | NO | integer | | | 32 | 2 | 0 | | | | | | | | | | | | | postgres | pg_catalog | int4 | | | | | 1 | NO | NO | | | | | | NO | NEVER | | YES
postgres | public | list_tables_4 | name | 2 | | YES | text | | 1073741824 | | | | | | | | | | | | | | | | postgres | pg_catalog | text | | | | | 2 | NO | NO | | | | | | NO | NEVER | | YES
postgres | public | list_tables_5 | id | 1 | nextval('list_tables_5_id_seq'::regclass) | NO | integer | | | 32 | 2 | 0 | | | | | | | | | | | | | postgres | pg_catalog | int4 | | | | | 1 | NO | NO | | | | | | NO | NEVER | | YES
postgres | public | list_tables_5 | name | 2 | | YES | text | | 1073741824 | | | | | | | | | | | | | | | | postgres | pg_catalog | text | | | | | 2 | NO | NO | | | | | | NO | NEVER | | YES
(10 rows)
The following cell shows how to check the data types of the just created table:
docker exec -i tables_properties_example psql -U postgres << EOF
CREATE TABLE dtype_example (
id INTEGER,
text TEXT
);
SELECT column_name, data_type FROM information_schema.columns WHERE table_name='dtype_example';
DROP TABLE dtype_example;
EOF
CREATE TABLE
column_name | data_type
-------------+-----------
id | integer
text | text
(2 rows)
DROP TABLE
Count raws#
To get counts of observations in table you can use command: SELECT COUNT(*) FROM <table_name>.
The example in the following cell shows counting the result of the generate_series(1, 1000000) function, which generates a series of values with the specified length.
docker exec -i tables_properties_example psql -U postgres << EOF
SELECT COUNT(*) FROM generate_series(1, 1000000);
EOF
count
---------
1000000
(1 row)
List tables#
It’s a common task to list all available tables for the current database. So in this section I want to mention some options.
The following cell prepares a set of tables that we’ll use as examples.
docker exec -i tables_properties_example psql -U postgres << EOF
DO \$\$
DECLARE
num_tables INT := 5; -- specify the number of tables
BEGIN
FOR i IN 1..num_tables LOOP
EXECUTE format('CREATE TABLE list_tables_%s (id SERIAL PRIMARY KEY, name TEXT);', i);
END LOOP;
END \$\$;
EOF
DO
\dt#
Very simple method, but it doesn’t always work.
docker exec -i tables_properties_example psql -U postgres << EOF
\dt
EOF
List of relations
Schema | Name | Type | Owner
--------+---------------+-------+----------
public | list_tables_1 | table | postgres
public | list_tables_2 | table | postgres
public | list_tables_3 | table | postgres
public | list_tables_4 | table | postgres
public | list_tables_5 | table | postgres
(5 rows)
pg_catalog.pg_tables table#
This is a service table that contains the tables available for this base.
Folowing cell just shows whole pg_catalog.pg_tables.
docker exec -i tables_properties_example psql -U postgres << EOF
SELECT * FROM pg_catalog.pg_tables;
EOF
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
--------------------+--------------------------+------------+------------+------------+----------+-------------+-------------
public | list_tables_1 | postgres | | t | f | f | f
public | list_tables_2 | postgres | | t | f | f | f
public | list_tables_3 | postgres | | t | f | f | f
public | list_tables_4 | postgres | | t | f | f | f
public | list_tables_5 | postgres | | t | f | f | f
pg_catalog | pg_statistic | postgres | | t | f | f | f
pg_catalog | pg_type | postgres | | t | f | f | f
pg_catalog | pg_foreign_table | postgres | | t | f | f | f
pg_catalog | pg_authid | postgres | pg_global | t | f | f | f
pg_catalog | pg_statistic_ext_data | postgres | | t | f | f | f
pg_catalog | pg_user_mapping | postgres | | t | f | f | f
pg_catalog | pg_subscription | postgres | pg_global | t | f | f | f
pg_catalog | pg_attribute | postgres | | t | f | f | f
pg_catalog | pg_proc | postgres | | t | f | f | f
pg_catalog | pg_class | postgres | | t | f | f | f
pg_catalog | pg_attrdef | postgres | | t | f | f | f
pg_catalog | pg_constraint | postgres | | t | f | f | f
pg_catalog | pg_inherits | postgres | | t | f | f | f
pg_catalog | pg_index | postgres | | t | f | f | f
pg_catalog | pg_operator | postgres | | t | f | f | f
pg_catalog | pg_opfamily | postgres | | t | f | f | f
pg_catalog | pg_opclass | postgres | | t | f | f | f
pg_catalog | pg_am | postgres | | t | f | f | f
pg_catalog | pg_amop | postgres | | t | f | f | f
pg_catalog | pg_amproc | postgres | | t | f | f | f
pg_catalog | pg_language | postgres | | t | f | f | f
pg_catalog | pg_largeobject_metadata | postgres | | t | f | f | f
pg_catalog | pg_aggregate | postgres | | t | f | f | f
pg_catalog | pg_statistic_ext | postgres | | t | f | f | f
pg_catalog | pg_rewrite | postgres | | t | f | f | f
pg_catalog | pg_trigger | postgres | | t | f | f | f
pg_catalog | pg_event_trigger | postgres | | t | f | f | f
pg_catalog | pg_description | postgres | | t | f | f | f
pg_catalog | pg_cast | postgres | | t | f | f | f
pg_catalog | pg_enum | postgres | | t | f | f | f
pg_catalog | pg_namespace | postgres | | t | f | f | f
pg_catalog | pg_conversion | postgres | | t | f | f | f
pg_catalog | pg_depend | postgres | | t | f | f | f
pg_catalog | pg_database | postgres | pg_global | t | f | f | f
pg_catalog | pg_db_role_setting | postgres | pg_global | t | f | f | f
pg_catalog | pg_tablespace | postgres | pg_global | t | f | f | f
pg_catalog | pg_auth_members | postgres | pg_global | t | f | f | f
pg_catalog | pg_shdepend | postgres | pg_global | t | f | f | f
pg_catalog | pg_shdescription | postgres | pg_global | t | f | f | f
pg_catalog | pg_ts_config | postgres | | t | f | f | f
pg_catalog | pg_ts_config_map | postgres | | t | f | f | f
pg_catalog | pg_ts_dict | postgres | | t | f | f | f
pg_catalog | pg_ts_parser | postgres | | t | f | f | f
pg_catalog | pg_ts_template | postgres | | t | f | f | f
pg_catalog | pg_extension | postgres | | t | f | f | f
pg_catalog | pg_foreign_data_wrapper | postgres | | t | f | f | f
pg_catalog | pg_foreign_server | postgres | | t | f | f | f
pg_catalog | pg_policy | postgres | | t | f | f | f
pg_catalog | pg_replication_origin | postgres | pg_global | t | f | f | f
pg_catalog | pg_default_acl | postgres | | t | f | f | f
pg_catalog | pg_init_privs | postgres | | t | f | f | f
pg_catalog | pg_seclabel | postgres | | t | f | f | f
pg_catalog | pg_shseclabel | postgres | pg_global | t | f | f | f
pg_catalog | pg_collation | postgres | | t | f | f | f
pg_catalog | pg_parameter_acl | postgres | pg_global | t | f | f | f
pg_catalog | pg_partitioned_table | postgres | | t | f | f | f
pg_catalog | pg_range | postgres | | t | f | f | f
pg_catalog | pg_transform | postgres | | t | f | f | f
pg_catalog | pg_sequence | postgres | | t | f | f | f
pg_catalog | pg_publication | postgres | | t | f | f | f
pg_catalog | pg_publication_namespace | postgres | | t | f | f | f
pg_catalog | pg_publication_rel | postgres | | t | f | f | f
pg_catalog | pg_subscription_rel | postgres | | t | f | f | f
pg_catalog | pg_largeobject | postgres | | t | f | f | f
information_schema | sql_features | postgres | | f | f | f | f
information_schema | sql_implementation_info | postgres | | f | f | f | f
information_schema | sql_parts | postgres | | f | f | f | f
information_schema | sql_sizing | postgres | | f | f | f | f
(73 rows)
But we can impose restrictions, for example, on the schemaname column and get only those tables we are interested in.
docker exec -i tables_properties_example psql -U postgres << EOF
SELECT * FROM pg_catalog.pg_tables WHERE schemaname='public';
EOF
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+---------------+------------+------------+------------+----------+-------------+-------------
public | list_tables_1 | postgres | | t | f | f | f
public | list_tables_2 | postgres | | t | f | f | f
public | list_tables_3 | postgres | | t | f | f | f
public | list_tables_4 | postgres | | t | f | f | f
public | list_tables_5 | postgres | | t | f | f | f
(5 rows)