Here, an inner query is used along with the 'WHERE' clause to achieve the desired outcome. In this last scenario we will see how it is also possible to create a new table from an existing one with partial data. In the previous scenario, the response was the text ' SELECT 2' which indicated 2 rows of data were inserted in the new table and in this example the system's response is a different text ' CREATE TABLE AS' which means table is created but with no data. The important point to observe here is the response from the PostgreSQL engine post table creation. Let us create a new table named ' books3' from the existing table ' books' to better understand the scenario. CREATE TABLE new_table AS TABLE existing_table This scenario is particularly important for testing the application across different environments. It is also possible to copy only the table structure and not its data. CREATE TABLE books2 AS TABLE books įrom this image, we can see a new table named ' books2' is created which is having the same structure and same set of data. Next we will execute the below COPY query to create the new table from the existing table ' books'. This image depicts the current scenario of the database. Let us look at the basic syntaxes of all the possible ways to do so: Copy table along with its data CREATE TABLE new_table AS TABLE existing_table The copy table functionality helps to copy an existing table with all its entirety. psql -U postgres -d targetdb -f sourcedb.sql Restore the dump file on the target server. Create a fresh database in target server. Copy the file to target server via putty or any other file transfer process.ģ. pg_dump -U postgres -d sourcedb -f sourcedb.sqlĢ. Dump the source database into a SQL file. Lets us take a look at the steps and their respective commands.ġ. The most efficient way to copy a database from one server to another is explained below. The ' demo' database contains a couple of schemas as shown above in the 1st image.Īfter executing the COPY query, ' demo2' database is created with the same structure. The images below explain the scenario in detail.īefore copying ' demo' database. Database is copied keeping its internal structure intact which means all schemas and their tables and other objects are copied and made available in the new database. The above query instructs PostgreSQL engine to create a new database named ' demo2' using the template of the existing database ' demo'. Let us take a look at the below query query to explain the scenario. This statement copies the source_database to the target_database. PostgreSQL helps to achieve the same using the CREATE DATABASE statement as shown below: Basic Syntax: CREATE DATABASE target_database Copy database within the same serverĪt times it is required to create an exact copy of an existing database within the same server for development or testing purposes. We will discuss both in this article.įirst we will discuss the process of copying a PostgreSQL database on the same server or from a server to another and then we will talk about various ways of copying an existing table to a new table in PostgreSQL. There are two variants of 'COPY' statement, copy a database and copy a table. In this article, we will discuss the 'COPY' functionality of PostgreSQL database which is one of the most important and often used features in practical world.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |