I was entering tens of millions of records per hour when I first published Slonik PostgreSQL client for Node.js. The data being entered was usually flat, making it straightforward to use INSERT INTO ... SELECT * FROM unnset() pattern. I advocated the unnest approach for inserting rows in groups (that was part I).

However, today I’ve found a better way: jsonb_to_recordset.

jsonb_to_recordset expands the top-level JSON array of objects to a set of rows having the composite type defined by an AS clause.

jsonb_to_recordset allows us to query and insert records from arbitrary JSON, like unnest. Since we're giving JSON to PostgreSQL instead of unnest, the final format is more expressive and powerful.

SELECT *
FROM json_to_recordset('[{"name":"John","tags":["foo","bar"]},{"name":"Jane","tags":["baz"]}]')
AS t1(name text, tags text[]);
 name |   tags
------+-----------
 John | {foo,bar}
 Jane | {baz}
(2 rows)

Let’s demonstrate how you would use it to insert data.

Inserting data using json_to_recordset

Say you need to insert a list of people with attributes into the database.

const persons = [
  {
    name: 'John',
    tags: ['foo', 'bar']
  },
  {
    name: 'Jane',
    tags: ['baz']
  }
];

You may be tempted to traverse through the array and insert each record separately, e.g.

for (const person of persons) {
  await pool.query(sql`
    INSERT INTO person (name, tags)
    VALUES (
      ${person.name},
      ${sql.array(person.tags, 'text[]')}
    )
  `);
}

It's easier to read and grasp when working with a few records. If you're like me and troubleshoot a 2M+ insert query per day, batching inserts may be beneficial.

What prompted the search for better alternatives.

Inserting using unnest pattern might look like this:

await pool.query(sql`
  INSERT INTO public.person (name, tags)
  SELECT t1.name, t1.tags::text[]
  FROM unnest(
    ${sql.array(['John', 'Jane'], 'text')},
    ${sql.array(['{foo,bar}', '{baz}'], 'text')}
  ) AS t1.(name, tags);
`);

You must convert arrays into PostgreSQL array strings and provide them as text arguments, which is unsightly. Iterating the array to create slices for each column is likewise unattractive.

However, with jsonb_to_recordset, we can:

await pool.query(sql`
  INSERT INTO person (name, tags)
  SELECT *
  FROM jsonb_to_recordset(${sql.jsonb(persons)}) AS t(name text, tags text[])
`);

In contrast to the unnest approach, using jsonb_to_recordset we can easily insert complex nested data structures, and we can pass the original JSON document to the query without needing to manipulate it.

In terms of performance they are also exactly the same. As such, my current recommendation is to prefer jsonb_to_recordset whenever inserting lots of rows or nested data structures.

Canonicalization and canonical URLs are essential for SEO, and improper implementation can negatively impact your site's performance.

Canonical tags were introduced in 2009 to help webmasters with duplicate or similar content on multiple URLs.

To use canonical tags properly, you must understand their purpose, operation, and implementation.

Canonical URLs and Tags

Canonical tags tell search engines that a certain URL is a page's master copy. They specify a page's canonical URL. Webmasters can avoid duplicate content by linking to the "canonical" or "preferred" version of a page.

How are canonical tags and URLs different? Can these be specified differently?

Tags

Canonical tags are found in an HTML page's head></head> section.

<link rel="canonical" href="https://www.website.com/page/" />

These can be self-referencing or reference another page's URL to consolidate signals.

Canonical tags and URLs are often used interchangeably, which is incorrect.

The rel="canonical" tag is the most common way to set canonical URLs, but it's not the only way.

Canonical URLs

What's a canonical link? Canonical link is the'master' URL for duplicate pages.

In Google's own words:

A canonical URL is the page Google thinks is most representative of duplicate pages on your site.

— Google Search Console Help

You can indicate your preferred canonical URL. For various reasons, Google may choose a different page than you.

When set correctly, the canonical URL is usually your specified URL.

Canonical URLs determine which page will be shown in search results (unless a duplicate is explicitly better for a user, like a mobile version).

Canonical URLs can be on different domains.

Other ways to specify canonical URLs

Canonical tags are the most common way to specify a canonical URL.

You can also set canonicals by:

• Setting the HTTP header rel=canonical.

• All pages listed in a sitemap are suggested as canonicals, but Google decides which pages are duplicates.

• Redirects 301.

Google recommends these methods, but they aren't all appropriate for every situation, as we'll see below. Each has its own recommended uses.

Setting canonical URLs isn't required; if you don't, Google will use other signals to determine the best page version.

To control how your site appears in search engines and to avoid duplicate content issues, you should use canonicalization effectively.

Why Duplicate Content Exists

Before we discuss why you should use canonical URLs and how to specify them in popular CMSs, we must first explain why duplicate content exists. Nobody intentionally duplicates website content.

Content management systems create multiple URLs when you launch a page, have indexable versions of your site, or use dynamic URLs.

Assume the following URLs display the same content to a user:

1. https://www.website.com/category/product-a/

2. https://www.website.com/product-a/

3. https://website.com/product-a/

4. http://www.website.com/product-a/

5. http://website.com/product-a/

6. https://m.website.com/product-a/

7. https://www.website.com/product-a

8. https://www.website.com/product-A/

A search engine sees eight duplicate pages, not one.

• URLs #1 and #2: the CMS saves product URLs with and without the category name.

• #3, #4, and #5 result from the site being accessible via HTTP, HTTPS, www, and non-www.

• #6 is a subdomain mobile-friendly URL.

• URL #7 lacks URL #2's trailing slash.

• URL #8 uses a capital "A" instead of a lowercase one.

Duplicate content may also exist in URLs like:

https://www.website.com
https://www.website.com/index.php

Duplicate content is easy to create.

Canonical URLs help search engines identify different page variations as a single URL on many sites.

SEO Canonical URLs

Canonical URLs help you manage duplicate content that could affect site performance.

Canonical URLs are a technical SEO focus area for many reasons.

Specify URL for search results

When you set a canonical URL, you tell Google which page version to display.

Which would you click?

https://www.domain.com/page-1/

https://www.domain.com/index.php?id=2

First, probably.

Canonicals tell search engines which URL to rank.

Consolidate link signals on similar pages

When you have duplicate or nearly identical pages on your site, the URLs may get external links.

Canonical URLs consolidate multiple pages' link signals into a single URL.

This helps your site rank because signals from multiple URLs are consolidated into one.

Syndication management

Content is often syndicated to reach new audiences.

Canonical URLs consolidate ranking signals to prevent duplicate pages from ranking and ensure the original content ranks.

Avoid Googlebot duplicate page crawling

Canonical URLs ensure that Googlebot crawls your new pages rather than duplicated versions of the same one across mobile and desktop versions, for example.

Crawl budgets aren't an issue for most sites unless they have 100,000+ pages.

How to Correctly Implement the rel=canonical Tag

Using the header tag rel="canonical" is the most common way to specify canonical URLs.

Adding tags and HTML code may seem daunting if you're not a developer, but most CMS platforms allow canonicals out-of-the-box.

These URLs each have one product.

How to Correctly Implement a rel="canonical" HTTP Header

A rel="canonical" HTTP header can replace canonical tags.

This is how to implement a canonical URL for PDFs or non-HTML documents.

You can specify a canonical URL in your site's.htaccess file using the code below.

<Files "file-to-canonicalize.pdf"> Header add Link "< http://www.website.com/canonical-page/>; rel=\"canonical\"" </Files>

301 redirects for canonical URLs

Google says 301 redirects can specify canonical URLs.

Only the canonical URL will exist if you use 301 redirects. This will redirect duplicates.

This is the best way to fix duplicate content across:

• HTTPS and HTTP

• Non-WWW and WWW

• Trailing-Slash and Non-Trailing Slash URLs

On a single page, you should use canonical tags unless you can confidently delete and redirect the page.

Sitemaps' canonical URLs

Google assumes sitemap URLs are canonical, so don't include non-canonical URLs.

This does not guarantee canonical URLs, but is a best practice for sitemaps.

Best-practice Canonical Tag

Once you understand a few simple best practices for canonical tags, spotting and cleaning up duplicate content becomes much easier.

Always include:

One canonical URL per page

If you specify multiple canonical URLs per page, they will likely be ignored.

Correct Domain Protocol

If your site uses HTTPS, use this as the canonical URL. It's easy to reference the wrong protocol, so check for it to catch it early.

Trailing slash or non-trailing slash URLs

Be sure to include trailing slashes in your canonical URL if your site uses them.

Specify URLs other than WWW

Search engines see non-WWW and WWW URLs as duplicate pages, so use the correct one.

Absolute URLs

To ensure proper interpretation, canonical tags should use absolute URLs.

So use:

<link rel="canonical" href="https://www.website.com/page-a/" />

And not:

<link rel="canonical" href="/page-a/" />

If not canonicalizing, use self-referential canonical URLs.

When a page isn't canonicalizing to another URL, use self-referencing canonical URLs.

Canonical tags refer to themselves here.

Common Canonical Tags Mistakes

Here are some common canonical tag mistakes.

301 Canonicalization

Set the canonical URL as the redirect target, not a redirected URL.

Incorrect Domain Canonicalization

If your site uses HTTPS, don't set canonical URLs to HTTP.

Irrelevant Canonicalization

Canonicalize URLs to duplicate or near-identical content only.

SEOs sometimes try to pass link signals via canonical tags from unrelated content to increase rank. This isn't how canonicalization should be used and should be avoided.

Multiple Canonical URLs

Only use one canonical tag or URL per page; otherwise, they may all be ignored.

When overriding defaults in some CMSs, you may accidentally include two canonical tags in your page's <head>.

Pagination vs. Canonicalization

Incorrect pagination can cause duplicate content. Canonicalizing URLs to the first page isn't always the best solution.

Canonicalize to a 'view all' page.

How to Audit Canonical Tags (and Fix Issues)

Audit your site's canonical tags to find canonicalization issues.

SEMrush Site Audit can help. You'll find canonical tag checks in your website's site audit report.

Let's examine these issues and their solutions.

No Canonical Tag on AMP

Site Audit will flag AMP pages without canonical tags.

Canonicalization between AMP and non-AMP pages is important.

Add a rel="canonical" tag to each AMP page's head>.

No HTTPS redirect or canonical from HTTP homepage

Duplicate content issues will be flagged in the Site Audit if your site is accessible via HTTPS and HTTP.

You can fix this by 301 redirecting or adding a canonical tag to HTTP pages that references HTTPS.

Broken canonical links

Broken canonical links won't be considered canonical URLs.

This error could mean your canonical links point to non-existent pages, complicating crawling and indexing.

Update broken canonical links to the correct URLs.

Multiple canonical URLs

This error occurs when a page has multiple canonical URLs.

Remove duplicate tags and leave one.

Canonicalization is a key SEO concept, and using it incorrectly can hurt your site's performance.

Once you understand how it works, what it does, and how to find and fix issues, you can use it effectively to remove duplicate content from your site.

Canonicalization SEO Myths

OpenAI's ChatGPT is an amazing AI-powered chatbot. It can write poetry, stories, and marketing campaigns, and it's also adept at coding. ChatGPT's coding is so amazing that it replicated itself. What happened:

Me: Hi

Hello! Can I help? I'll answer any inquiries you have.

Me: Build a python and flask AI chatbot that responds to user messages on the frontend.

CGPT:

from flask import Flask, request, jsonify

import openai
openai.api_key = "YOUR_API_KEY"

@app.route('/chatbot', methods=['POST'])
def chatbot():
    user_message = request.get_json()['message']
    response = chatbot_response(user_message)
    return jsonify({'response': response})


def chatbot_response(user_message):
    response = openai.Completion.create(
        engine="text-davinci-002",
        prompt=user_message,
        max_tokens=1024,
        n = 1,
        temperature=0.5
    )
    return response['choices'][0]['text']

if __name__ == '__main__':
    app.run()

To integrate a chatbot with a frontend, use Flask to serve an HTML page with a form for user messages and the chatbot's answer. AJAX can submit the form to the /chatbot route, and JavaScript can show the chatbot's response.

I imported code snippets at the top of the file.

Let's add a frontend interface, I said.

CGPT:

<!DOCTYPE html>
<html>
<head>
    <title>AI Chatbot</title>
</head>
<body>
    <h1>AI Chatbot</h1>
    <form id="chatbot-form">
        <label for="message">Message:</label><br>
        <input type="text" id="message" name="message"><br>
        <input type="submit" value="Submit">
    </form> 
    <div id="chatbot-response"></div>

    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
    <script>
        $(document).ready(function() {
            $("#chatbot-form").submit(function(e) {
                e.preventDefault();
                var userMessage = $("#message").val();
                $.ajax({
                    type: "POST",
                    url: "/chatbot",
                    data: JSON.stringify({message: userMessage}),
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function(response) {
                        $("#chatbot-response").text(response.response);
                    }
                });
            });
        });
    </script>
</body>
</html>

To serve this HTML page using Flask, build a new route that returns the page as a GET response.

So far, so good.

Looked quite basic so I got chatGPT to add some tailwind to the project.

Me: Tailwind the chatbot.html page, please. Give it a futuristic style with purple and neon

After a few more prompts, Talk GPT aligned the form to the center, changed some colors, and added padding.

CGPT:

<!DOCTYPE html>
<html>
<head>
    <title>AI Chatbot</title>
    <link href="https://unpkg.com/tailwindcss@^1.0/dist/tailwind.min.css" rel="stylesheet">
</head>
<body class="bg-black flex flex-col justify-center items-center">
    <h1 class="sm:text-center text-purple-500">AI Chatbot</h1>
    <form id="chatbot-form" class="border-purple-500 bg-purple-800 rounded">
        <label for="message">Message:</label><br>
        <input type="text" id="message" name="message" class="min-w-30 rounded"><br>
        <input type="submit" value="Submit" class="bg-purple-500 border-red-500 transform hover:scale-110 rounded">
    </form> 
    <div class="flex justify-center">
        <div id="chatbot-response" class="text-white rounded"></div>
    </div>

    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
    <script>
        var colors = ['red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet'];
        var colorIndex = 0;
        
        $(document).ready(function() {
            setInterval(function() {
                $("#chatbot-form").css('border-color', colors[colorIndex]);
                colorIndex = (colorIndex + 1) % colors.length;
            }, 1000);

            $("#chatbot-form").submit(function(e) {
                e.preventDefault();
                var userMessage = $("#message").val();
                $.ajax({
                    type: "POST",
                    url: "/chatbot",
                    data: JSON.stringify({message: userMessage}),
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function(response) {
                        $("#chatbot-response").text(response.response);
                    }
                });
            });
        });
    </script>
</body>
</html>

Improved:

A few more prompts like “Add more vertical and horizontal padding to the submit button and vertical margin, and vertically align this file's body.”

The result:

Is it effective? Now, I'll chat with ChatGPT's AI Chatbot. I intervened minimally throughout construction. Chat GPT created numerous files, connected and interacted with its own API, and presented the user request response on the front end by itself.

Me to AI Chatbot: Hey, can you give me 6 name suggestions for a black horse?

Me to AI Chatbot: Write a poem in the style of Jim Morrison. Make sure to mention cacti and black holes

ChatGPTs Chatbot:

In the desert night, I am confronted by cacti, 
Their arms reaching out, As if to grab me and take me away. 
The stars in the sky, Beckon me to come, 
To a world of black holes, Where I can be free. 
I am in a trance, My body and mind, 
In a state of limbo, As I wander the night. 
I hear a voice, Calling me to the unknown, 
I cannot resist, The lure of the black hole. 
The cacti watch, As I take my leave, 
To a place of mystery, Where I can find peace.

Of course, the next question I gave it was to build a chatbot!

Me: Build a python and flask AI chatbot that responds to user messages on the frontend.

ChatbotGPTs:

import os from flask 
import Flask, request, jsonify 
from chatterbot 
import ChatBot 
from chatterbot.trainers 
import ListTrainer 

# Create a new chatbot 
bot = ChatBot( 
  "My Bot", 
  storage_adapter="chatterbot.storage.SQLStorageAdapter", 
  logic_adapters=[ 
    "chatterbot.logic.BestMatch", 
    { "import_path": "chatterbot.logic.BestMatch", 
      "statement_comparison_function": "chatterbot.comparisons.levenshtein_distance", 
       "response_selection_method": "chatterbot.response_selection.get_first_response" } 
    ], 
    database_uri="sqlite:///database.sqlite3" ) 

# Train the chatbot with a few sample conversations 
conversation = [ "Hello", "Hi there!", "How are you doing?", "I'm doing great.", "That is good to hear", "Thank you.", "You're welcome." ] 
trainer = ListTrainer(bot) trainer.train(conversation) 

# Create a Flask app 
app = Flask(__name__) 

# Define a route for the default URL 
@app.route("/") def index(): return "<h1>Chatbot</h1>" 

# Define a route for the /get_response URL 
@app.route("/get_response", methods=["POST"]) 

def get_response(): 
# Get the user's message 
user_message = request.form["user_message"] 
# Get the bot's response 
bot_response = bot.get_response(user_message) 
# Return the bot's response 
return str(bot_response) 
# Run the Flask 
app if __name__ == "__main__": app.run()

No matter how many times I tried, the second generation always used chatterbot instead of the ChatGPT API. Even when I told it to use the ChatGPT API, it didn't.

ChatGTP's ability to reproduce or construct other machine learning algorithms is interesting and possibly terrifying. Nothing prevents ChatGPT from replicating itself ad infinitum throughout the Internet other than a lack of desire. This may be the first time a machine repeats itself, so I've preserved the project as a reference. Adding a requirements.txt file and python env for easier deployment is the only change to the code.

I hope you enjoyed this.

If I could tell my younger self a few rules, they would boost my career.

1. Treat design like medicine; don't get attached.

If it doesn't help, you won't be angry, but you'll try to improve it. Designers blame others if they don't like the design, but the rule is the same: we solve users' problems. You're not your design, and neither are they. Be humble with your work because your assumptions will often be wrong and users will behave differently.

2. Consider your design flawed.

Disagree with yourself, then defend your ideas. Most designers forget to dig deeper into a pattern, screen, button, or copywriting. If someone asked, "Have you considered alternatives? How does this design stack up? Here's a functional UX checklist to help you make design decisions.

3. Codeable solutions.

If your design requires more developer time, consider whether it's worth spending more money to code something with a small UX impact. Overthinking problems and designing abstract patterns is easy. Sometimes you see something on dribbble or bechance and try to recreate it, but it's not worth it. Here's my article on it.

4. Communication changes careers

Designers often talk with users, clients, companies, developers, and other designers. How you talk and present yourself can land you a job. Like driving or swimming, practice it. Success requires being outgoing and friendly. If I hadn't said "hello" to a few people, I wouldn't be where I am now.

5. Ignorance of the law is not an excuse.

Copyright, taxation How often have you used an icon without checking its license? If you use someone else's work in your project, the owner can cause you a lot of problems — paying a lot of money isn't worth it. Spend a few hours reading about copyrights, client agreements, and taxes.

6. Always test your design

If nobody has seen or used my design, it's not finished. Ask friends about prototypes. Testing reveals how wrong your assumptions were. Steve Krug, one of the authorities on this topic will tell you more about how to do testing.

7. Run workshops

A UX designer's job involves talking to people and figuring out what they need, which is difficult because they usually don't know. Organizing teamwork sessions is a powerful skill, but you must also be a good listener. Your job is to help a quiet, introverted developer express his solution and control the group. AJ Smart has more on workshops here.