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