Languages
[Edit]
EN

PostgreSQL - insert data from one table to another

0 points
Created by:
Dirask Admin - Marvin
1940

In this article, we would like to show how to insert data from one table to another in PostgreSQL.

Quick solution:

INSERT INTO "dst_table" (SELECT * FROM "src_table")

Where:

  • src_table means source table name,
  • dst_table means destination table name.

Practical examples

In this section, we want to show how to copy data with INSERT ... SELECT query.

PostgreSQL Insert data from one table to another
target table - "users" - HeidiSQL
PostgreSQL Insert data from one table to another
source table - "members" - HeidiSQL

Note: at the end of this article you can find database preparation SQL queries.

Query:

INSERT INTO "users" ("name", "email", "country")
SELECT "name", "email", ''
FROM "members";

Result:

PostgreSQL - insert data from one table to another
users table after query execution - HeidiSQL

Database preparation

create_tables.sql file:

CREATE TABLE "users" (
	"id" SERIAL,
	"name" VARCHAR(100),
	"email" VARCHAR(100),
	"country" VARCHAR(15),
	PRIMARY KEY ("id")
);

CREATE TABLE "members" (
	"id" SERIAL,
	"name" VARCHAR(50) NOT NULL,
	"surname" VARCHAR(50) NOT NULL,
	"email" VARCHAR(50),
	PRIMARY KEY ("id")
);

insert_data.sql file:

INSERT INTO "users"
    ("name", "email", "country")
VALUES
    ('Tom', 'tom@email.com', 'Poland'),
    ('Chris', 'chris@email.com', 'Spain'),
    ('Jack', 'jack@email.com', 'Spain'),
    ('Kim', 'kim@email.com', 'Vietnam'),
    ('Marco', 'marco@email.com', 'Italy'),
    ('Kate', 'kate@email.com', 'Spain'),
    ('Nam', 'nam@email.com', 'Vietnam');

INSERT INTO "members"
	( "name", "surname", "email")
VALUES
	('John', 'Stewart', 'john@email.com'),
	('Chris', 'Brown', NULL),
	('Kate', 'Lewis', NULL),
	('Ailisa', 'Gomez', 'ailisa@email.com'),
	('Gwendolyn', 'James', NULL),
	('Simon', 'Collins', NULL),
	('Taylor', 'Martin', NULL),
	('Andrew', 'Thompson', 'andrew@email.com');
Native Advertising
🚀
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

❤️💻 🙂

Join