To copy a table in PostgreSQL, you can use the CREATE TABLE statement with the AS clause. This allows you to create a new table that is a copy of an existing table. Here’s a basic example:

CREATE TABLE new_table AS
SELECT * FROM existing_table;

Replace new_table with the name you want for the new table and existing_table with the name of the table you want to copy.

If you want to copy specific columns or apply conditions to the data being copied, you can modify the SELECT statement accordingly. For example:

CREATE TABLE new_table AS
SELECT column1, column2, ...
FROM existing_table
WHERE condition;

After running this query, new_table will be a copy of existing_table with the specified columns and data. Note that indexes, constraints, and other properties of the original table won’t be copied using this method.

If you also want to copy indexes, constraints, and other properties, you may consider using tools like pg_dump and pg_restore for a more comprehensive database backup and restoration approach. These tools allow you to dump the entire database or specific tables, and then restore them.

For example:

# Dump the original table
pg_dump -h localhost -p 5432 -U your_username -d your_database -t existing_table -f existing_table_dump.sql

# Restore the dump to create a new table
psql -h localhost -p 5432 -U your_username -d your_database -f existing_table_dump.sql

Replace your_username, your_database, and other parameters with your actual database connection details.

Choose the method that best fits your needs based on whether you want a quick table copy or a more comprehensive backup and restoration process.

Please leave this field empty

Don’t miss these tips!

We don’t spam! Read our privacy policy for more info.

Check your inbox or spam folder to confirm your subscription.