PostgreSQL Copy from Stdin

PostgreSQL like other database management systems supports the standard streams. These streams are responsible for manipulating data for storage in PostgreSQL. These are the input and output channels of communication between the application and the environment that is created at the time of execution.

Whenever we execute a command in PostgreSQL, the streams make the connection with the text terminal where the psql (shell) is running. However, in the case of inheritance, each child’s process inherits the streams from the parent process. Not every program needs these streams to be introduced in the code, some functions such as getchar() and putchar() use the input and output streams automatically. Streams lie in the category of 3.

Stdin: It is a standard input stream. It is used where the program reads the input data.

Stdout: This implies the standard output stream used when the application writes the data (output) to the file.

Stderr: This stream refers to the errors in the application. This is used to display or notify the user about the occurrence of an error during the execution.

The common syntax for these three types is:

FILE *stdin;

FILE *stdout;

FILE *stderr;

Standard input is read by the input device “keyboard”, whereas standard output and standard errors are displayed to the output device monitor screen. The first two streams are used to fetch and display the data in simple words, but the third one is mostly used when we need to diagnose the errors. I am talking about exception handling in programming languages.

Standard input (stdin):

While creating a source code, most functions are dependent on the stdin stream for the input feature. But some programs like dir and ls programs do not require this functionality, as they take the command-line arguments. This situation happens when the program relies on the system for the input but doesn’t interact with the user. For example, the programs related to the directory and paths don’t require input to get executed.

Each file that is under the process of execution is allocated with a unique number by the system. This is called a file descriptor. For the standard input, the value of the file descriptor is “0”. In C programming language, the file descriptor has variable file * stdin, similarly for C language. variable is defined as std : : cin.

Stdin in PostgreSQL

After the installation and configuration of the database, for the connectivity with the server, you need to provide a password to proceed further. These measures are for the authentication of the user.

Copy Data from Stdin to a Table

To acknowledge the mechanism of stdin, we need to create a dummy table. So that we can read and copy the data from a file to another by incorporating stdin.

Once the table is created, we will add the values in the table by using an insert command. Add some sample data in few rows, rest will be added by using “STDIN”.

>> insert into school values ( 1, ‘Ahmad’, ‘lahore’,‘sciences’),( 2, ‘shazain’, ‘Islamabad’,‘Arts’),( 3, ‘Zain’, ‘karachi’,‘sciences’);

Other than the “INSERT” statement, there exists an alternative to load the values in the table. This is through “STDIN”. In PostgreSQL , we enter data in the table from the terminal row-wise using a delimiter. This delimiter is a separator between the values of two columns of a row. This delimiter may be a space, comma, or a blank in any case. But using a delimiter as stdin, CSV (comma-separated values) is recommended. And no other symbol is referred here. A keyword ‘COPY’ is used that will copy the data from the psql screen to the table particularly.

>> Copy school from stdin (Delimiter ‘,’);

When you use the query, some instructions for the placement of data are mentioned here. These are the points to guide the user so that you must be able to enter data correctly. Each row should be entered in a new line.

We will go step by step here. Each value written before or between the commas represents each column. As, there are 4 columns so 4 values are used as CSV. Enter the first row and then press the tab.

As one row is completed, you will be then moved towards the next row. No matter how many rows you want to add, just like an insert statement, all the limitless data will be placed inside the table. Coming back to the example, now we have written the second row and proceed for the next.

We have used 2 rows to demonstrate. Actual insertion will take data up to the mark of requirement. If you are done with adding rows in the table and want to quit this mechanism, you will surely use an end of file (EOF).

You need to wrap up adding data with a backslash () and a period (.) at the last line when you don’t want to add further rows.

Now let us have a finalized look at the whole code from the query to the EOF. At the end “copy 3” indicates that 3 rows are added to the table.

Note: The EOF operator is not added as a symbol in the new row of the table.

Keep adding data through “stdin” according to the requirement. You can check the data that you have inserted through the select statement.

Copy Data from a Table to Stdin

If you are interested in copying the data in one table from the table, then we use stdin for that. It is not possible to directly copy one table into the other in PostgreSQL .

Create a sample table to copy all the data from the table (school). One should be aware of adding the column’s data, type similar to the targeted table.

Now, add the data of that file using the same stdin statement of a copy. The data can be the same or you can alter it by adding a new row that was not present in the original table.

>> copy school_copy from stdin (delimeter ‘,’)

Use a select statement to get the data entered.

Output Using STDOUT Instead of SELECT Statement

As we use stdin alternative to the insert statement. Similarly, STDOUT is used in the place of the select statement. The representation is not in the form of a table. For the output purpose, the delimiter used is “|”. This delimiter is automatically placed between the columns in each row.

>> copy school_copy to stdout (DELIMITER ‘|’);

Arise of Errors While Using Delimiters

DELIMITER ‘|’

If you use a delimiter ‘|’ in replacement of CSV, it will cause an error. This will not copy the data from the terminal and causes a syntax error.

Conclusion

‘PostgreSQL Copy from Stdin’ assists in duplicating data of one table to another. In this article, we first gave you an introduction of standard streams, stdin, it’s working, theoretically followed by the brief explanation of the examples. A competitive edge of stdin over insert statement is that, if a row is skipped mistakenly while copying data, we can add it between the existing rows. With the guidance of this tutorial, you will be able to cop the content of tables.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.

PostgreSQL

Show Comments

PostgreSQL Copy from Stdin

Standard input (stdin):

Stdin in PostgreSQL

Copy Data from Stdin to a Table

Copy Data from a Table to Stdin

Output Using STDOUT Instead of SELECT Statement

Arise of Errors While Using Delimiters

DELIMITER ‘|’

Conclusion

About the author

Aqsa Yasin

Other stories

prctl in C example Usage

The basics of PyQt5

Press ESC to close

Standard input (stdin):

Stdin in PostgreSQL

Copy Data from Stdin to a Table

Copy Data from a Table to Stdin

Output Using STDOUT Instead of SELECT Statement

Arise of Errors While Using Delimiters DELIMITER ‘|’

Conclusion

About the author

Aqsa Yasin

Share Article:

You might also like

How To Install PostgreSQL on MacOS

PostgreSQL Built-in Range Data Types

PostgreSQL DateTime Data Types

Other stories

prctl in C example Usage

The basics of PyQt5

Arise of Errors While Using Delimiters

DELIMITER ‘|’