PostgreSQL tables

Introduction

List of tables

To see the list of tables, enter

\dt

List of relations
Schema | Name | Type | Owner
--------+--------+-------+----------
public | person | table | postgres
(1 row)

To see the whole table person you can already use the standard

SELECT * FROM person;

Create a table

To create a table, run a query like this

CREATE TABLE IF NOT EXISTS t_name ( Row_ID SERIAL PRIMARY KEY, value TEXT, tmstmp TIMESTAMP, epoch_time INT );

You can make a list of words that cannot be column names based on the keywords table

The difference between creating a table in Postgres and in SQLite3 is that AUTOINCREMENT is not used, and SERIAL is used instead of INTEGER in the first line.

Retrieve a value from a database table

Example - let us write a value from the database to a file with echo

echo $(psql -h localhost -d db_name -U db_user -c "SELECT host_certificate FROM hosts WHERE (addresses='192.168.56.200');") > raw_cert

Database contents

In the previous section we created the booking_sites table in the heihei database

Let us make sure the script ran successfully

Let us switch to the heihei database and check whether the table was created

\c heihei

You are now connected to database "heihei" as user "postgres".

heihei=# \dt

List of relations Schema | Name | Type | Owner --------+---------------+-------+---------- public | booking_sites | table | postgres (1 row)

Table description

If the table was created some time ago. You may have already forgotten which columns exactly it contains.

To describe a table, the \d command is used

\d booking_sites

Table "public.booking_sites" Column | Type | Collation | Nullable | Default ----------------+-----------------------+-----------+----------+------------------------------------------- id | bigint | | not null | nextval('booking_sites_id_seq'::regclass) company_name | character varying(50) | | not null | origin_country | character varying(50) | | not null | age | character varying(3) | | not null | date_of_birth | date | | not null | website_url | character varying(50) | | | Indexes: "booking_sites_pkey" PRIMARY KEY, btree (id)

UPDATE: Changing a value in the table

Let us change the expiry date of the certificate in the certificates

Let us examine the current situation

aodb=> SELECT issuer, not_after FROM certificates WHERE (serial='2402020070');

issuer | not_after --------------------------------------------------------------------------------------------+------------------------ CN=192.168.56.174,OU=Andrei CA 3bbce96c-b117-46l0-65a7-f3c04c9f591b | 2027-07-19 16:39:59+03 (1 row)

Let us update the value

aodb=> UPDATE certificates SET not_after='2022-07-22 16:39:59+03' WHERE (serial='2402020070');

UPDATE 1

Let us check the result

aodb=> SELECT issuer, not_after FROM certificates WHERE (serial='2402020070263697801');

issuer | not_after --------------------------------------------------------------------------------------------+------------------------ CN=192.168.56.174,OU=Andrei CA 3bbce96c-b117-46l0-65a7-f3c04c9f591b | 2022-07-22 16:39:59+03 (1 row)

Changing the table

When you need to update the name of a table column, we use the ALTER command

Suppose the website_url field does not have enough room for 50 characters. Let us increase the length to 60.

ALTER TABLE booking_sites ALTER column website_url TYPE VARCHAR(60);

Let us check whether the table changed

\d booking_sites

Table "public.booking_sites" Column | Type | Collation | Nullable | Default ----------------+-----------------------+-----------+----------+------------------------------------------- id | bigint | | not null | nextval('booking_sites_id_seq'::regclass) company_name | character varying(50) | | not null | origin_country | character varying(50) | | not null | age | character varying(3) | | not null | date_of_birth | date | | not null | website_url | character varying(60) | | | Indexes: "booking_sites_pkey" PRIMARY KEY, btree (id)

Article author: Andrei Olegovich