Practical introduction to relational databases

Copy a database table

Sometimes we'll need to copy an existing database table's structure, without the content. Situations where you have a lot of data (hundreds of millions of rows), and you need to make some changes to the structure but you want to start with what's available, but you don't want to wait until the changes are applied to all rows, or simply you want to experiment without fear of destroying data.

In those cases we can use a like operator instead of manually specifying each individual columns.

create table if not exists public.products_2 (
    like public.products
    including all
    excluding comments
);

I've included some other parts in this query to illustrate additional options you can have here. I'm including the absolute minimal version of the above query later in the page.

colour coded query: create is do what, table is with what, if not exists if do what modifier, public is schema, products_2 is table name, like is table structure specifier, public is schema name, products is table name, and including all excluding comments are structure modifiers

This essentially means "create a new table named products_2 that has a structure and all other parts to it as products, except for the column comments, if any. Ignore those."

The absolute minimal version of the above is this:

create table products_2 (
    like public.products
);

It will behave slightly differently, like throw an error if there's already a table with the name products_2, and will copy the comments too, but for our purposes it's the same thing.

IF NOT EXIST

A lot of queries can have this modifier on them: create table, add column, to name the two most important ones.

The effect of using this in the query is that if the table / column / whatever already exists, the query is going to silently not do anything, and not raise an error. If the if not exist was not there, the same queries would throw an error telling you it can't complete because the thing you're trying to do is already there.

This is useful for resiliency, or when all you care about is to ensure that a thing is there, but you don't care whether it's been there the whole time, or you needed to create it then.

Reading postgres documentation

By now you have probably seen a few postgres documentation pages and wondered how the heck are you supposed to make sense of the Synopsis that they have going on on those pages. The look like this:

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
{ column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
...

I've put together an addendum page to try to clear up and demystify some of the symbols being used there through examples and colour coding.

Have a look at how to read documentation pages.

We're now ready to learn how to select/fetch information out from tables.