Creating table if not already exists

The CREATE TABLE has one clause that we can add to create table if it doesn’t exist already. So this comes handy if you already have created a table and instead of throwing an error it simply gracefully handles the query and doesn’t re-create the table.

Let’s first creat a table, as usual, we’ll use the goodol users table.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

Let’s insert some data into the table.

SQLite SQL: Create Temporary Table

Link: https://www.meetgor.com/sqlog/sqlite-create-temp-table

Context

Source: techstructive-weekly-61

SQLite SQL: Many to Many Table without RowID

Link: https://www.meetgor.com/sqlog/sqlite-many-to-many-table-without-rowid

Context

Source: techstructive-weekly-61

Temporary Table in SQLite

A Temporary table as the name suggests, is a temporary table. It only resisdes on the database until the current session of the database.

In case of sqlite, the temporary table is available in the session until the file reader or session driver closes the connection.

The table is created in a separate database file called temp that is stored in your temporary path.

Create a temporary table

To create the temporary table, simply use the TEMP or TEMPORARY keyword before the TABLE in CREATE TABLE expression. So, CREATE TEMP TABLE or CREATE TEMPORARY TABLE will be the notation to create a temporary table in sqlite.

Collate Column Modifier

Collate is a modifier in SQL that specifies the column how to arrange and compare the values that would be populated in the rows.

So, for a given table, we can specify a few modifiers that would let SQL decide how to handle the values.

Adding Collate Modifier

To add a collate modifier, we can use the COLLATE keyword, followed by the collation name.

There are 3 collations available in SQLite:

Many to Many Relation

We saw the basic example of Many-To-Many Relation in the second last post from this, there we just focused on the concept of the relation and not so much on the structure of the junction table.

Let’s take a look at the schema again:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL
);

CREATE TABLE author_post (
    user_id INTEGER NOT NULL,
    post_id INTEGER NOT NULL,
    PRIMARY KEY (user_id, post_id),
    FOREIGN KEY (user_id) REFERENCES users (id),
    FOREIGN KEY (post_id) REFERENCES posts (id)
);

Let’s now populate the tables.

Weekly #60

Another great week, consistently learning SQL, developing another streak for solving problems on FreeCodeCamp with Javascript and Python, reading instead of doomscrolling.

I have been learning about SQL since maybe 10 weeks now and finding myself in a good position, for the past 4 weeks I have written posts every day and it has helped me get back to the technical writing flow.

On the work side, it was a fun week, tinkering with a system that is already working decently and wanted it to get the most of it, turning the bits and knobs of a system is so cool.

My Favorite Postgres 18 feature: Virtual generated columns

Link: https://tselai.com/virtual-gencolumns

Context

: I agree to this, there are pros and cons of both. Stored makes write heavier but are read efficient. Virtual makes it write easier and read heavier. You have trade-offs, you need to decide based on the computation that impacts how you want the column to be generated. I don’t like the notion of JSON flattening in Postgres. Postgres is not a database that would be ideal for that kind of data. I know there are tons and tons of support for JSON, but tables and JSON, I can’t bare it at once. Those two are just separate entities for me. Maybe they are useful in one-off values, not not much. Switch to NoSQL if you have that lengthy data.

One to One Relations

I might missed this basic relationship model as I haven’t really found it quite commonly used, but its still used in very specific examples.

That one relationship is one-to-one. As the name suggests, it maps one row to exactly one row.

Unlike the one-to-many relation which had one of the records/rows from the tables or entities connected by multiple ropes, both the records/rows from the tables or entities are connected by only one rope in one-to-one relation.