PostgreSQL - Day 9 - Part 1

• Tue, Jun 14, 2022
• 2-minute read

Keep learning PostgreSQL by Learn PostgreSQL Tutorial - Full Course for Beginners.

In this part, we’ll learn more about foreign keys, how to export query to CSV, and the Sequence type.

Let’s dig in!

50. Deleting Records Referenced By Foreign Keys

How to delete when you have a foreign key constraint.

• First, we create a record that saves a person who owns a car.
• What will happen when we delete the car owned by the person?

  DELETE from car WHERE id = 13;
  

  • You will get an error about violating the foreign key constraint.

    ERROR:  update or delete on table "car" violates foreign key constraint "person_car_id_fkey" on table "person"
    DETAIL:  Key (id)=(13) is still referenced from table "person".
    

• There are two ways to solve it.
  • Delete the Quill’s record
  • Remove the car of Quill. => Update car_id to NULL.

Cascade

You can have a cascade on your table creation.

Cascade simply ignores the actual foreign key and goes ahead and removes every single row where that key is referenced.

It’s not a good practice. You should have full control of your data.

51. Exporting Query Results to CSV

You can find how to output in the section, Input/Output, of help by the \? command.

Use the \copy command to export query results to CSV.

• Syntax

  \copy (<your_query>) TO <output_file_path> [DELIMITER '<the delimiter which you want to use>'] CSV [HEADER]
  

52. Serial & Sequences

In this section, we’ll learn the BIGSERIAL data type.

In description of table, our BIGSERIAL column is saved as BIGINT data type with a default value.

The default value is nextval('person_id_seq'::regclass)

\d shows the person_id_seq is a sequence type.

A sequence in PostgreSQL is a user-defined schema-bound object that generates a sequence of integers based on a specified specification.

In short, the sequence generates a sequence of integers.

And person_id_seq is creating integers for the id column in the person table.

Lab

• We can select the person_id_seq to check what it contains.

• We may suggest that the next value generated by person_id_seq is last_value plus 1.

• Try to execute the command we saw in the table description.

  select nextval('person_id_seq'::regclass);
  

  

  As we expected, we got a value of 5.

• Now, let’s check the person_id_seq again. The last_value was increased to 5.

• If we insert a record now, we’ll get a record with an id is 6.

Restart a Sequence

ALTER SEQUENCE person_id_seq RESTART WITH 10;