PostgreSQL中INSERT结合SELECT的实现（含自定义用户表名场景）

Hey there! Let's walk through how to leverage SELECT statements within INSERT operations in PostgreSQL, using your document_dimauser and regcard_dimauser tables as concrete examples. This pattern is perfect when you need to populate a table with existing data from another table, or mix static values with queried data.

Basic Syntax Overview #

The core structure looks like this:

INSERT INTO target_table (column1, column2, ...)
SELECT columnA, columnB, ...
FROM source_table
WHERE [optional condition];

Just make sure the number of columns returned by the SELECT matches the number you specify in the INSERT, and their data types line up.

Example 1: Create RegCard Entries for Specific Documents #

Suppose you want to generate regcard_dimauser records for all documents named "Operation Manual", with auto-generated UUIDs for regcardid, formatted internal/external numbers, and today's date as the intro date. Here's how you'd do it:

First, if you don't already have the UUID extension enabled (to generate new regcardid values), run this once:

CREATE EXTENSION IF NOT EXISTS uuid-ossp;

Then the INSERT with SELECT:

INSERT INTO public.regcard_dimauser (
    regcardid,
    documentid,
    documentintronumber,
    documentexternnumber,
    dateintro
)
SELECT
    uuid_generate_v4(), -- Generate a unique UUID for the new RegCard
    d.documentid,       -- Pull the document ID from the Documents table
    'INT-' || d.documentname, -- Format internal number using the document name
    'EXT-' || d.documentname, -- Format external number similarly
    CURRENT_DATE        -- Use today's date for the intro date
FROM public.document_dimauser d
WHERE d.documentname = 'Operation Manual'; -- Filter to only target documents

Example 2: Bulk Insert RegCards for All Documents #

If you need to create a RegCard entry for every document in your document_dimauser table (say, to initialize the RegCard table), you can simplify it to:

INSERT INTO public.regcard_dimauser (regcardid, documentid, dateintro)
SELECT
    uuid_generate_v4(),
    documentid,
    CURRENT_DATE
FROM public.document_dimauser;

Example 3: Mix Static Values with Queried Data #

Maybe you want to insert a single RegCard entry, but pull the documentid from the Documents table instead of hardcoding it. Here's how:

INSERT INTO public.regcard_dimauser (
    regcardid,
    documentid,
    documentintronumber,
    documentexternnumber,
    dateintro
)
SELECT
    uuid_generate_v4(),
    documentid,
    'INTERNAL-007', -- Static internal number
    'EXTERNAL-007', -- Static external number
    '2024-05-20'    -- Static intro date
FROM public.document_dimauser
WHERE documentid = 'a1b2c3d4-5678-90ef-ghij-klmnopqrstuv'; -- Replace with your document's UUID

Key Notes to Remember #

• Column Count & Type Match: The SELECT must return exactly the same number of columns as you list in the INSERT, and their data types need to be compatible (e.g., don't try to insert a string into a date column).
• Non-Null Constraints: Any columns in the target table with NOT NULL constraints (like regcardid and documentid in your regcard_dimauser table) must either be included in the INSERT, have a default value defined, or be populated by the SELECT.
• Uniqueness: Since regcardid is a primary key, make sure the value you generate (via uuid_generate_v4() or another method) is unique to avoid duplicate key errors.

内容的提问来源于stack exchange，提问作者Travis Bincle