PostgreSQL.create_table(table_name, column_specs, schema_name=None, verbose=False)

Create a table.

  • table_name (str) – name of a table

  • column_specs (str) – specifications for each column of the table

  • schema_name (str or None) – name of a schema; when schema_name=None (default), it defaults to DEFAULT_SCHEMA (i.e. 'public')

  • verbose (bool or int) – whether to print relevant information in console as the function runs, defaults to False


>>> from pyhelpers.dbms import PostgreSQL

>>> testdb = PostgreSQL('localhost', 5432, 'postgres', database_name='testdb')
Password (postgres@localhost:5432): ***
Creating a database: "testdb" ... Done.
Connecting postgres:***@localhost:5432/testdb ... Successfully.

>>> tbl_name = 'test_table'
>>> col_spec = 'col_name_1 INT, col_name_2 TEXT'

>>> testdb.create_table(table_name=tbl_name, column_specs=col_spec, verbose=True)
Creating a table "public"."test_table" ... Done.

>>> testdb.table_exists(table_name=tbl_name)

>>> # Get information about all columns of the table "public"."test_table"
>>> test_tbl_col_info = testdb.get_column_info(table_name=tbl_name, as_dict=False)
>>> test_tbl_col_info
                            column_0      column_1
table_catalog                 testdb        testdb
table_schema                  public        public
table_name                test_table    test_table
column_name               col_name_1    col_name_2
ordinal_position                   1             2
column_default                  None          None
is_nullable                      YES           YES
data_type                    integer          text
character_maximum_length        None          None
character_octet_length           NaN  1073741824.0
numeric_precision               32.0           NaN
numeric_precision_radix          2.0           NaN
numeric_scale                    0.0           NaN
datetime_precision              None          None
interval_type                   None          None
interval_precision              None          None
character_set_catalog           None          None
character_set_schema            None          None
character_set_name              None          None
collation_catalog               None          None
collation_schema                None          None
collation_name                  None          None
domain_catalog                  None          None
domain_schema                   None          None
domain_name                     None          None
udt_catalog                   testdb        testdb
udt_schema                pg_catalog    pg_catalog
udt_name                        int4          text
scope_catalog                   None          None
scope_schema                    None          None
scope_name                      None          None
maximum_cardinality             None          None
dtd_identifier                     1             2
is_self_referencing               NO            NO
is_identity                       NO            NO
identity_generation             None          None
identity_start                  None          None
identity_increment              None          None
identity_maximum                None          None
identity_minimum                None          None
identity_cycle                    NO            NO
is_generated                   NEVER         NEVER
generation_expression           None          None
is_updatable                     YES           YES

>>> # Get data types of all columns of the table "public"."test_table"
>>> test_tbl_dtypes = testdb.get_column_dtype(table_name=tbl_name)
>>> test_tbl_dtypes
{'col_name_1': 'integer', 'col_name_2': 'text'}

>>> # Drop the table "public"."test_table"
>>> testdb.drop_table(table_name=tbl_name, verbose=True)
To drop the table "public"."test_table" from postgres:***@localhost:5432/testdb
? [No]|Yes: yes
Dropping "public"."test_table" ... Done.

>>> # Delete the database "testdb"
>>> testdb.drop_database(verbose=True)
To drop the database "testdb" from postgres:***@localhost:5432
? [No]|Yes: yes
Dropping "testdb" ... Done.