SELECT_INTO(7pg14)

SELECT INTO (aka my absolute fave way to make a table clone) ✨

okay so bestie, welcome to SELECT INTO(7pg14) — this is the PostgreSQL 14.24 documentation page and honestly? it's giving "sleeper hit." let me walk you through it 💅

---

🌸 NAME 🌸

SELECT_INTO — define a new table from the results of a query. yes, you heard me right. we're making a whole new table out of a query result and it's iconic.

---

🎀 SYNOPSIS 🎀

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ { * | expression [ [ AS ] output_name ] } [, ...] ]
    INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

like just look at that. a whole SQL statement. it ate and left no crumbs fr 🫶

---

💖 DESCRIPTION 💖

okay so here's the tea: SELECT INTO creates a new table and fills it with data computed by a query. slay.

but here's the thing bestie — the data is not returned to the client, unlike with a normal SELECT. so don't be out here expecting rows to come back to you. not how this works.

the new table's columns have the names and data types associated with the output columns of the SELECT. periodt. that's the vibe. 💅

---

✨ PARAMETERS ✨

• TEMPORARY or TEMP — if you specify this, the table is created as a temporary table. it's giving "here for a good time not a long time." refer to CREATE TABLE (CREATE_TABLE(7)) for deets.
• UNLOGGED — if you specify this, the table is created as an unlogged table. lowkey mysterious. refer to CREATE TABLE (CREATE_TABLE(7)) for the full story.
• new_table — the name of the table to be created (optionally schema-qualified). pick something cute. or don't. it's your table bestie.
• all other parameters — these are described in detail under SELECT(7). go read that if you want the whole picture, iykyk.

---

🔥 NOTES 🔥

okay so this is where it gets real. CREATE TABLE AS is functionally similar to SELECT INTO. very similar. like they're basically siblings.

but — and this is a big but — CREATE TABLE AS is the recommended syntax. why? because this form of SELECT INTO is not available in ECPG or PL/pgSQL, since they interpret the INTO clause differently. and like, we don't want confusion in our lives, right? 🙈

furthermore (yes i'm using furthermore), CREATE TABLE AS offers a superset of the functionality provided by SELECT INTO. so it's giving more for the same price. obsessed.

⚠️ important caveat: in contrast to CREATE TABLE AS, SELECT INTO does <strong>not</strong> allow specifying properties like a table's access method with USING method or the table's tablespace with TABLESPACE tablespace_name. so if you need that, use CREATE TABLE AS. literally just do it.

therefore (again with the fancy words), the default table access method is chosen for the new table. see default_table_access_method for more info. that's the tea. ☕

---

🌈 EXAMPLES 🌈

okay so imagine you want to create a new table films_recent consisting of only recent entries from the table films. here's how you'd do it:

SELECT * INTO films_recent FROM films WHERE date_prod >= '2002-01-01';

and boom. new table. fresh. clean. iconic. you're welcome. 💖

---

💜 COMPATIBILITY 💜

okay so the SQL standard uses SELECT INTO to represent selecting values into scalar variables of a host program, rather than creating a new table. so it's giving "different priorities" in standard-land.

this indeed is the usage found in ECPG (see Chapter 36) and PL/pgSQL (see Chapter 43). so like, that's what they're doing over there. not our vibe but valid.

the PostgreSQL usage of SELECT INTO to represent table creation is historical. like, it's been around. it's a legacy. respect the elders. some other SQL implementations also use SELECT INTO in this way (but most SQL implementations support CREATE TABLE AS instead).

apart from such compatibility considerations, it is best to use CREATE TABLE AS for this purpose in new code. no cap. fr fr. do that instead. 💅

---

🫶 SEE ALSO 🫶

• CREATE TABLE AS (CREATE_TABLE_AS(7)) — the recommended alternative. go check it out bestie, it's the main character of this story honestly.

---

and that's the whole page! 🎉

big love and shoutout to PostgreSQL 14.24 — this documentation page is dated 2026 and honestly? it's timeless. ✨

stay querying bestie, and remember: when in doubt, CREATE TABLE AS is your bestie. 💖👑