Languages
[Edit]
EN

PostgreSQL - insert data from one table to another

0 points
Created by:
Dirask Admin - MR
1340

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