postgres tables - ghdrako/doc_snipets GitHub Wiki

create table tab(id int);
create unlogged table unlog(id int);
create temporary table temp(id int);
\dt
\timing
\timing
insert into tab values(generate_series(1,1000000));
insert into unlog values(generate_series(1,1000000));
insert into temp values(generate_series(1,1000000)); -- tylko dla sesji znika po wylogowaniu i jest przechowywana w pamieci  temp_buffers

\dt+ unlog
\dt+ temp
show temp_buffers;

Table size

SELECT PG_SIZE_PRETTY(
PG_TOTAL_RELATION_SIZE('schema1.tab1')
);

Table cloning

Using Like Syntax

Cavits:

not cloning FOREIGN KEYs, triggers
not cloning data to clone data insert into new as select from old;
if you have a "serial" value or some other column defaulting to a sequence, it'll use the same sequence as the old table!. To fix it ALTER TABLE yourtable ALTER COLUMN serialcol DROP DEFAULT; and add new

CREATE TABLE new_table_name (LIKE old_table_name INCLUDING ALL);

create table new (
    like old
    including defaults
    including constraints
    including indexes
    including comments
);

INCLUDING ALL =  including defaults - Dziedziczy domyślne wartości kolumn (DEFAULT).
                 including constraints - Kopiuje ograniczenia kolumn (np. NOT NULL, CHECK).
                 including indexes - Tworzy kopie indeksów tabeli źródłowej, niezależnych od oryginalnych.
                 including comments -  Kopiuje komentarze zdefiniowane dla kolumn i tabeli.
                 including storage -  Przenosi informacje o sposobie przechowywania danych (np. parametry kolumn dotyczące kompresji lub przydziału miejsca).
                 including statistics - Dziedziczy ustawienia statystyk dla kolumn (używane przez planista zapytań PostgreSQL).
                 including generated - Kopiuje kolumny wygenerowane (np. GENERATED ALWAYS AS (...) STORED)
                 including identity  - Przenosi kolumny typu IDENTITY (następca SERIAL), w tym definicje ich powiązania z sekwencjami.

cloning and add new column

CREATE TABLE new (like old, extra_column text);

Using pg_dump

pg_dump -s -t old databases | sed 's/oldtable/newtable/g' | psql

Note! if your data happen to include the "oldtable" string, this command will corrupt your data!

Using CREATE TABLE AS TABLE

https://www.postgresql.org/docs/current/sql-createtableas.html

Note that this won't copy keys,constraints like NOT NULL, defaults

CREATE TABLE new_table_name AS 
TABLE original_table
WITH DATA | WITH NO DATA;

CREATE TABLE films2 AS
    TABLE films
    WITH NO DATA;

Using CREATE TABLE AS SELECT

https://www.postgresql.org/docs/current/sql-createtableas.html

The stated command is not able to copy the indexes or constraints, such as NOT NULL, PRIMARY KEY, FOREIGN KEY, etc.

CREATE TABLE sample_table_copy AS (SELECT * FROM sample_table WHERE 1 = 2)

A better way to express 'WHERE 1=2' would be 'WHERE false' or no WHERE clause at all, but 'LIMIT 0 ' instead. Another way duplicate only structure

CREATE TABLE new_table_name AS 
SELECT * FROM existing_table_name
WITH NO DATA

Using INHERITS Option

CREATE TABLE child_table(
col_name data_type constraint
) 
INHERITS (parent_table);

CREATE TABLE child_table()
INHERITS (parent_table);