Sample Exam Questions

From the objective of OSS-DB Exam Silver
S2.4 Backup and restore (Usage of COPY statement (SQL) and \copy command (psql))

Sample Question


Start psql, and then execute

\copy tablex from '/path/to/filename'

Choose three appropriate descriptions from the following.

  1. The contents of the file /path/to/filename are added to the table tablex.

  2. Not only is it a convenient command for loading data from a file, it is also faster than using SQL INSERT.

  3. Since /path/to/filename is a file on the database server, the user running the database process (typically postgres) must have access privilege.

  4. /path/to/filename is a tab-delimited text file.

  5. You must have database administrator privileges to execute this command.

※This sample exam is different from those that appear in the actual OSS-DB Exam.

Answer and Explanation

PostgreSQL provides a command called COPY for input and output between tables and files (this is not a standard SQL command, but a unique extension).

For example, if you execute COPY table_name FROM 'file_name', it reads the contents of the specified file and loads it into the specified table. This command can be executed not only as SQL but also as a psql meta-command in the form of \copy table_name from 'file_name'.

Even when executed as a meta-command, the SQL COPY is executed internally, so the functionality and usage are very similar, but there is an important difference in file handling.

Since SQL is executed on the server, the file name/path specified must also be on the database server, and the user executing the database process must have access privileges. The file path name should also be specified as an absolute path. Also, for security reasons of accessing files on the server, the execution of the command is restricted to users with specific privileges, such as superusers. On the other hand, psql is a client application, and the specified file is on the client machine. Therefore, it is sufficient if the user executing psql has access privileges, and there is no problem with the path name being a relative path. No special permissions are required for the user to execute, other than access privileges to the table or file being used.

This question is about the meta-command \copy, but let's check each choice.

As stated in choice A, this command adds the contents of the file /path/to/filename to the table tablex.
As stated in choice B, it not only provides a convenient interface for file input and output, but also enables it to be processed very quickly.
Choice C, as explained above, is the case when executed with the SQL COPY command, and the situation is significantly different in the case of the psql \copy meta-command.
Regarding choice D, COPY can handle both tab-delimited and comma-delimited, but by default, it is executed as tab-delimited.
In regards to choice E, as explained above, administrator privileges are required when performing file input and output with the SQL COPY command, and no special privileges are required for the psql \copy meta-command.

Therefore, the correct answers are A, B, and D.