SQL / INSERTING, UPDATING, AND DELETING ROWS
Copying tables with SELECT INTO and CREATE TABLE AS
Create a new table directly from a query using CREATE TABLE AS or SELECT INTO, and know which parts of the source table do not come with it.
What you will learn
- Copy filtered rows into a brand-new table with CREATE TABLE copy AS SELECT ... FROM src
- Create an empty same-shape table with WITH NO DATA or a WHERE 1 = 0 filter
- Re-add primary keys, indexes and defaults after the copy, since CTAS drops them
- Alias every expression so the new table gets usable column names
Understanding Copying tables with SELECT INTO and CREATE TABLE AS
CREATE TABLE orders_feb AS SELECT ... does two things in one statement: it works out what the result of the query looks like, then builds a table from that description and pours the rows in. Column names come from the output labels of the select list and column types come from the expressions, and the target name must be free, because CTAS creates a table and never appends to one. That is the whole difference from INSERT INTO ... SELECT, where you wrote the CREATE TABLE yourself and the query only had to line up with columns that already existed.
What travels between the two tables is a result set, and a result set does not know that id was a primary key or that reading had DEFAULT 0. Keys, unique and ordinary indexes, foreign keys, defaults, triggers and sequence ownership belong to the source table's definition, so none of them appear on the copy; in PostgreSQL even NOT NULL is dropped, whereas SQL Server's SELECT INTO does preserve nullability and, in a plain column-for-column copy, the IDENTITY property. Anything the copy needs you add afterwards with ALTER TABLE and CREATE INDEX. Types are looser than people expect too: sum(amount) over a numeric(8,2) column produces an unconstrained numeric, and summing an int column produces bigint.
Two spellings exist for historical reasons. CREATE TABLE AS is the portable one, accepted by PostgreSQL, Oracle, MySQL, SQLite, Snowflake and BigQuery, while SELECT ... INTO newtable is the T-SQL form and is your only option on SQL Server, which has no CREATE TABLE AS. PostgreSQL accepts SELECT INTO for compatibility but steers you to CTAS, because inside PL/pgSQL (and Oracle's PL/SQL) SELECT INTO already means "store the query result in a variable", and one statement cannot sensibly mean both.
-- PostgreSQL
CREATE TABLE orders (
id int PRIMARY KEY,
customer text NOT NULL,
amount numeric(8,2),
placed_on date
);
INSERT INTO orders VALUES
(1, 'Ada', 120.00, DATE '2026-01-04'),
(2, 'Grace', 45.50, DATE '2026-02-11'),
(3, 'Ada', 310.25, DATE '2026-02-28');
-- The new table is defined by the shape of this query's result
CREATE TABLE orders_feb AS
SELECT id, customer, amount
FROM orders
WHERE placed_on >= DATE '2026-02-01';
SELECT * FROM orders_feb ORDER BY id;
-- The primary key did not come along, so this duplicate is accepted
INSERT INTO orders_feb VALUES (3, 'Ada', 310.25);
SELECT id, count(*) AS copies
FROM orders_feb
GROUP BY id
ORDER BY id;CREATE TABLE AS and SELECT INTO define the new table from the query's result metadata, so you inherit columns and rows and nothing else: no keys, indexes, defaults or triggers.
Worked examples
An empty table with the same columns
Builds a staging table shaped like the original and shows which column properties were left behind.
-- PostgreSQL
CREATE TABLE sensor_log (
id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
device text NOT NULL,
reading numeric(6,2) DEFAULT 0
);
INSERT INTO sensor_log (device, reading) VALUES ('probe-a', 21.75);
-- Same columns, zero rows
CREATE TABLE sensor_log_staging AS
SELECT * FROM sensor_log WITH NO DATA;
INSERT INTO sensor_log_staging (device) VALUES ('probe-b');
SELECT device,
id IS NULL AS id_is_null,
reading IS NULL AS reading_is_null
FROM sensor_log_staging;Example explained
Line 1WITH NO DATA runs the query only to learn its columns, so the table is created empty; WHERE 1 = 0 is the portable trick on engines that lack the clause.
Line 2id_is_null is t because the identity sequence stayed with sensor_log: the copy's id is a plain int that generates nothing.
Line 3That NULL also proves the source column's PRIMARY KEY and NOT NULL were not carried over, or the insert would have failed.
Line 4reading_is_null is t because DEFAULT 0 belongs to sensor_log's definition, so the omitted column stored NULL instead of zero.
SELECT INTO with an aggregate
Shows the SELECT INTO spelling and how the new column's type is derived from the expression, not the source column.
-- PostgreSQL
CREATE TABLE orders (
id int PRIMARY KEY,
customer text,
amount numeric(8,2)
);
INSERT INTO orders VALUES
(1, 'Ada', 120.00),
(2, 'Grace', 45.50),
(3, 'Ada', 310.25);
SELECT customer, sum(amount) AS lifetime_value
INTO customer_totals
FROM orders
GROUP BY customer;
SELECT customer, lifetime_value FROM customer_totals ORDER BY customer;
SELECT format_type(atttypid, atttypmod) AS lifetime_value_type
FROM pg_attribute
WHERE attrelid = 'customer_totals'::regclass
AND attname = 'lifetime_value';Example explained
Line 1INTO customer_totals sits between the select list and FROM, and the table is built from the grouped result: one row per customer, not one per order.
Line 2The alias lifetime_value becomes the column name; without it PostgreSQL would name the column sum, and SQL Server would refuse the statement with Msg 8155, "No column name was specified".
Line 3The reported type is plain numeric, not numeric(8,2): sum() widens its result, so the copy has no precision limit even though amount did.
Line 4SQL Server offers only this form, while PostgreSQL treats it as a legacy spelling of CREATE TABLE AS.
Important notes
If you want the keys, defaults and indexes as well, CTAS is the wrong tool: use CREATE TABLE copy (LIKE orders INCLUDING ALL) in PostgreSQL or CREATE TABLE copy LIKE orders in MySQL, then load the rows with an INSERT.
Inside PL/pgSQL and Oracle PL/SQL, SELECT INTO assigns the result to a variable and creates nothing, so use CREATE TABLE AS in procedural code.
Common mistakes
Treating a CTAS copy as an equivalent table: duplicate rows and NULL keys creep in later, and queries scan the whole table, because no primary key, unique constraint or index was ever created on it.
Re-running a script and hitting "relation orders_feb already exists" (SQL Server: "There is already an object named"), since CTAS only creates; you must DROP TABLE first, and CREATE TABLE IF NOT EXISTS ... AS silently keeps the stale rows.
Leaving an expression unaliased, so SQL Server aborts with Msg 8155 while PostgreSQL cheerfully creates a column called sum or ?column? that you can only reference in double quotes.
Try it yourself
Change, predict, then run
Create employees(id int primary key, name text, dept text, salary numeric(9,2)) with five rows, then use CREATE TABLE high_earners AS to copy only the rows with salary above 60000. Insert a row reusing an existing id to confirm the copy has no primary key, then add one with ALTER TABLE.
Open the SQL workspaceCheck your understanding
You run CREATE TABLE orders_2026 AS SELECT * FROM orders; where orders.id is the primary key with a unique index behind it. Immediately afterwards, what is true of orders_2026?
- It has the same rows and the same primary key, because SELECT * copies each column's full definition.
- It is created empty; a separate INSERT INTO ... SELECT is still needed to fill it.
- It has the same rows, but duplicate id values can now be inserted because no primary key or index was created.
- It shares storage with orders, so rows deleted from orders vanish from orders_2026 as well.
Show answer
CTAS can only read column names, types and values off the query's result set, so orders_2026 gets matching columns and every row but nothing that enforces uniqueness. The first option is tempting because "copy the table" sounds total, yet the star expands to a column list, not to a schema definition; and CTAS materialises independent storage, so deletes in orders cannot touch the copy, which is what a view would have given you.