![]() ![]() We can use this utility to generate the SQL CREATE TABLE required to recreate the table, as well as any indexes, constraints, etc. The pg_dump UtilityĪnother option is to use the pg_dump utility. Also, CREATE TABLE AS offers a superset of the functionality provided by SELECT INTO. That’s because this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret the INTO clause differently. Note that the Postgres documentation recommends that we use the CREATE TABLE AS option instead of SELECT INTO if we want a similar result. We can use the SELECT INTO statement to copy the table and its data, but not its indexes: SELECT * INTO pets5 FROM pets Note that the SQL standard actually requires either WITH DATA or WITH NO DATA to be included in the statement, but PostgreSQL makes this optional. If we don’t want the data, we can use WITH NO DATA: CREATE TABLE pets4 AS TABLE pets WITH NO DATA Note that this didn’t copy the indexes, foreign keys, or NOT NULL constraints.īy default, all data is copied to the new table. This created a table called pets4, based on the pets table, and copied the data from pets to pets4. AS TABLE statement: CREATE TABLE pets4 AS TABLE pets AS TABLE StatementĪnother way to copy the table and its data is to use the CREATE TABLE. ![]() This option doesn’t copy the data but it does copy any NOT NULL constraints associated with the table. LIKE option: CREATE TABLE pets3 (LIKE pets) For example: CREATE TABLE pets2 ASĪs mentioned, this method doesn’t copy any indexes or constraints associated with the table (such as primary keys, foreign keys or NOT NULL constraints). If we don’t want to insert the data, we can use the WITH NO DATA clause: CREATE TABLE pets2 ASĪlternatively, we can modify the query to return no results. I also copied all data from the original table to its clone. Here, I copied a table called pets and named the new table pets2. We can use the following statement to copy that table, along with its data: CREATE TABLE pets2 AS AS SELECT statement to copy the table and its data, but not its indexes. The method we use will depend on things like, whether or not we want to transfer the data, indexes, etc. Below are six methods we can use to clone a table in Postgres. PostgreSQL provides us with several options when it comes to cloning a table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |