Search⌘ K
Home
Courses
The Art of PostgreSQL

Data Manipulation and Concurrency Control: Insert

Explore PostgreSQL's insert capabilities and concurrency control by learning how to insert multiple rows, use select statements as data sources, and apply joins. Understand techniques to handle conflicts and optimize data insertion in multi-user environments.

We'll cover the following...

Given our model of Tweets, the first thing we need is users. Here’s how to create our first users:

PostgreSQL
insert into tweet.users (userid, uname, nickname, bio)
     values (default, 'Theseus', 'Duke Theseus', 'Duke of Athens.');

The SQL standard values clause is usable anywhere select is expected in our truth tables earlier. Also, values accepts several rows at a time.

PostgreSQL
insert into tweet.users (uname, bio)
     values ('Egeus', 'father to #Hermia.'),
            ('Lysander', 'in love with #Hermia.'),
            ('Demetrius', 'in love with #Hermia.'),
            ('Philostrate', 'master of the revels to Theseus.'),
            ('Peter Quince', 'a carpenter.'),
            ('Snug', 'a joiner.'),
            ('Nick Bottom', 'a weaver.'),
            ('Francis Flute', 'a bellows-mender.'),
            ('Tom Snout', 'a tinker.'),
            ('Robin Starveling', 'a tailor.'),
            ('Hippolyta', 'queen of the Amazons, betrothed to Theseus.'),
            ('Hermia', 'daughter to Egeus, in love with Lysander.'),
            ('Helena', 'in love with Demetrius.'),
            ('Oberon', 'king of the fairies.'),
            ('Titania', 'queen of the fairies.'),
            ('Puck', 'or Robin Goodfellow.'),
            ('Peaseblossom', 'Team #Fairies'),
            ('Cobweb', 'Team #Fairies'),
            ('Moth', 'Team #Fairies'),
            ('Mustardseed', 'Team #Fairies'),
            ('All', 'Everyone speaking at the same time'),
            ('Fairy', 'One of them #Fairies'),
            ('Prologue', 'a play within a play'),
            ('Wall', 'a play within a play'),
            ('Pyramus', 'a play within a play'),
            ('Thisbe', 'a play within a play'),
            ('Lion', 'a play within a play'),
            ('Moonshine', 'a play within a play');

If you have lots of rows to insert into your database, consider using the copy command instead of doing a series of inserts. If, for some reason, you can’t use the copy command for performance reasons, consider using a single transaction, making several insert statements, each with many values.

Using insert into and select together

The ...