EN
Node.js - PostgreSQL CREATE TABLE AS
0 points
In this article, we would like to show you how to create a new table from a query using CREATE TABLE AS statement in the Postgres database from Node.js level.
Note: at the end of this article you can find database preparation SQL queries.
xxxxxxxxxx
1
const { Client } = require('pg');
2
3
const client = new Client({
4
host: '127.0.0.1',
5
user: 'my_username',
6
database: 'my_database',
7
password: 'my_password',
8
port: 5432,
9
});
10
11
const newTableFromQuery = async () => {
12
const query = `
13
CREATE TABLE "users_without_email" AS
14
SELECT *
15
FROM "users"
16
WHERE "email" IS NULL;
17
`;
18
await client.connect(); // creates connection
19
try {
20
await client.query(query); // sends query
21
} finally {
22
await client.end(); // closes connection
23
}
24
};
25
26
newTableFromQuery()
27
.then(() => console.table('New table created!'))
28
.catch(error => console.error(error.stack));
Result:
create_tables.sql
file:
xxxxxxxxxx
1
CREATE TABLE "users" (
2
"id" SERIAL,
3
"name" VARCHAR(50) NOT NULL,
4
"surname" VARCHAR(50) NOT NULL,
5
"email" VARCHAR(100),
6
"department_id" INTEGER,
7
"salary" DECIMAL(15,2) NOT NULL,
8
PRIMARY KEY ("iusers_without_emaild")
9
);
insert_data.sql
file:
xxxxxxxxxx
1
INSERT INTO "users"
2
( "name", "surname", "email", "department_id", "salary")
3
VALUES
4
('John', 'Stewart', 'john@email.com', 1, '3512.00'),
5
('Chris', 'Brown', 'chris@email.com', 2, '1344.00'),
6
('Kate', 'Lewis', NULL, 3, '6574.00'),
7
('Ailisa', 'Gomez', 'ailisa@email.com', 2, '6500.00'),
8
('Gwendolyn', 'James', NULL, NULL, '4200.00'),
9
('Simon', 'Collins', NULL, 4, '3320.00'),
10
('Taylor', 'Martin', NULL, NULL, '1500.00'),
11
('Andrew', 'Thompson', 'andrew@email.com', NULL, '2100.00');
Native SQL query (used in the above example):
xxxxxxxxxx
1
CREATE TABLE "users_without_email1" AS
2
SELECT *
3
FROM "users"
4
WHERE "email" IS NULL;