PostgreSQLSQLRDBMS
Last updated at 2023-08-31

PostgreSQL Insert Into Query CRUD Snippet

ClickUp
Last Edit By
Last edited time
Aug 31, 2023 12:47 PM
Metatag
Slug
postgresql-crud-insert-into-query
AI Status
Published
Published
Date
Aug 31, 2023
Category
PostgreSQL
SQL
RDBMS
The ubiquitious usage of PostgreSQL nowadays makes the skill to perform optimized query very well appreciated.
One of the most important SQL query is to insert data into the table. In PostgreSQL you can use INSERT INTO query and then fetch the data using SELECT query.
In this tutorial, you will learn the basics of Postgres CRUD queries and specifically how to perform an insert into a table.

The Table posts Schema

Suppose you are working with blog post table schema. 
CREATE TABLE posts (
    id UUID PRIMARY KEY,
    slug VARCHAR(255) NOT NULL,
    title VARCHAR(255) NOT NULL,
    categories TEXT[] NOT NULL,
    cover VARCHAR(255),
    date DATE NOT NULL,
    published BOOLEAN NOT NULL,
    lasteditedat BIGINT NOT NULL
);
The table posts contain these columns
  • id: UUID column as the primary key for the post
  • slug: A unique slug for the post
      • Example: postgresql-crud-query-snippet
  • title: The title of the post
      • Example: PostgreSQL CRUD Query Snippet Tutorial
  • categories: An array of categories associated with the post
      • Example: SQL, RDBMS, Database
  • cover: URL to the cover image of the post (optional)
  • date: The date when the post was published
  • published: A boolean indicating whether the post is published or not
  • lasteditedat: A timestamp indicating the last time the post was edited (as a Unix timestamp in milliseconds)

INSERT INTO Query: Add Post to posts Table

You need to fill the table row with your posts.
This is the snippet to insert your post into the PostgreSQL database.
INSERT INTO posts (id, slug, title, categories, cover, date, published, lasteditedat)
VALUES (
    '2a141849-579b-4c20-be10-8073cbbd417b',
    'react-pdf-module-parse-failed-next-js',
    'How to Resolve the react-pdf Issue: "Module parse failed: Unexpected character \'�\' (1:0)" in Next.js',
    ARRAY['Next.js'],
    NULL,
    '2023-08-21',
    TRUE,
    1692612960000
);
This will add the post How to Resolve the react-pdf Issue: "Module parse failed: Unexpected character \'�\' (1:0)" in Next.js to the posts table.

SELECT Query: Check If the Post is Correctly Inserted

You need to check whether the post is inserted into the posts table.
SELECT * FROM posts;

SELECT Column Query: Only Check Certain Column Values

When you need to check the post last update, you can query to fetch the lasteditedat and title column.
SELECT id, title, lasteditedat FROM posts;
💡
Using select columns increases your query speed; if your application doesn’t need to fetch all columns, it is better to select only specific columns to fetch.

What Happen When Insert With Similar ID?

The post table has id as primary key. A primary key usually has constraint to avoid duplication in order to avoid conflict between entities inside the database.
In this section, you will learn what happen to the database if you insert new post with similar id.
It is important to know how PostgreSQL report the error when you do something wrong so it can saves you a lot of time when debugging similar issue that happen later in your app.

Insert Post with Similar ID

This query inserts a new post with similar id. The difference between previous post are the slug, title, and categories.
INSERT INTO posts (id, slug, title, categories, cover, date, published, lasteditedat)
VALUES (
    '2a141849-579b-4c20-be10-8073cbbd417b',
    'postgresql-new-slug',
    'New Post with Similar ID with Previous Post',
    ARRAY['RDBMS'],
    NULL,
    '2023-08-21',
    TRUE,
    1692612960000
);
The insertion should error on id only.

Error Message for Similar ID

It will report the error like this:
Query 1 ERROR: ERROR:  duplicate key value violates unique constraint "posts_pkey"
DETAIL:  Key (id)=(2a141849-579b-4c20-be10-8073cbbd417b) already exists.
The error is very descriptive. It says that you insert value that has unique constraint. The detail says that the key that is duplicate is id key.
Awesome!
💡
Get used to the error message and you will learn PostgreSQL faster

Conclusion

Now that you have learned how to perform INSERT INTO query and check if the value is correctly inserted, you can test it to your Postgres database.
Happy querying!
 

Discussion (0)

Related Posts