Как скопировать таблицу в postgresql

7

Как скопировать таблицу в postgresql

COPY moves data between PostgreSQL tables and standard file-system files. COPY TO copies the contents of a table to a file, while COPY FROM copies data from a file to a table (appending the data to whatever is in the table already). COPY TO can also copy the results of a SELECT query.

If a column list is specified, COPY TO copies only the data in the specified columns to the file. For COPY FROM , each field in the file is inserted, in order, into the specified column. Table columns not specified in the COPY FROM column list will receive their default values.

COPY with a file name instructs the PostgreSQL server to directly read from or write to a file. The file must be accessible by the PostgreSQL user (the user ID the server runs as) and the name must be specified from the viewpoint of the server. When PROGRAM is specified, the server executes the given command and reads from the standard output of the program, or writes to the standard input of the program. The command must be specified from the viewpoint of the server, and be executable by the PostgreSQL user. When STDIN or STDOUT is specified, data is transmitted via the connection between the client and the server.

Each backend running COPY will report its progress in the pg_stat_progress_copy view. See Section 28.4.6 for details.

Parameters

The name (optionally schema-qualified) of an existing table.

An optional list of columns to be copied. If no column list is specified, all columns of the table except generated columns will be copied.

A SELECT , VALUES , INSERT , UPDATE , or DELETE command whose results are to be copied. Note that parentheses are required around the query.

For INSERT , UPDATE and DELETE queries a RETURNING clause must be provided, and the target relation must not have a conditional rule, nor an ALSO rule, nor an INSTEAD rule that expands to multiple statements.

The path name of the input or output file. An input file name can be an absolute or relative path, but an output file name must be an absolute path. Windows users might need to use an E» string and double any backslashes used in the path name.

A command to execute. In COPY FROM , the input is read from standard output of the command, and in COPY TO , the output is written to the standard input of the command.

Note that the command is invoked by the shell, so if you need to pass any arguments to shell command that come from an untrusted source, you must be careful to strip or escape any special characters that might have a special meaning for the shell. For security reasons, it is best to use a fixed command string, or at least avoid passing any user input in it.

Specifies that input comes from the client application.

Specifies that output goes to the client application.

Specifies whether the selected option should be turned on or off. You can write TRUE , ON , or 1 to enable the option, and FALSE , OFF , or 0 to disable it. The boolean value can also be omitted, in which case TRUE is assumed.

Selects the data format to be read or written: text , csv (Comma Separated Values), or binary . The default is text .

Requests copying the data with rows already frozen, just as they would be after running the VACUUM FREEZE command. This is intended as a performance option for initial data loading. Rows will be frozen only if the table being loaded has been created or truncated in the current subtransaction, there are no cursors open and there are no older snapshots held by this transaction. It is currently not possible to perform a COPY FREEZE on a partitioned table.

Note that all other sessions will immediately be able to see the data once it has been successfully loaded. This violates the normal rules of MVCC visibility and users specifying should be aware of the potential problems this might cause.

Specifies the character that separates columns within each row (line) of the file. The default is a tab character in text format, a comma in CSV format. This must be a single one-byte character. This option is not allowed when using binary format.

Specifies the string that represents a null value. The default is \N (backslash-N) in text format, and an unquoted empty string in CSV format. You might prefer an empty string even in text format for cases where you don’t want to distinguish nulls from empty strings. This option is not allowed when using binary format.

When using COPY FROM , any data item that matches this string will be stored as a null value, so you should make sure that you use the same string as you used with COPY TO .

Specifies that the file contains a header line with the names of each column in the file. On output, the first line contains the column names from the table. On input, the first line is discarded when this option is set to true (or equivalent Boolean value). If this option is set to MATCH , the number and names of the columns in the header line must match the actual column names of the table, in order; otherwise an error is raised. This option is not allowed when using binary format. The MATCH option is only valid for COPY FROM commands.

Specifies the quoting character to be used when a data value is quoted. The default is double-quote. This must be a single one-byte character. This option is allowed only when using CSV format.

Specifies the character that should appear before a data character that matches the QUOTE value. The default is the same as the QUOTE value (so that the quoting character is doubled if it appears in the data). This must be a single one-byte character. This option is allowed only when using CSV format.

Forces quoting to be used for all non- NULL values in each specified column. NULL output is never quoted. If * is specified, non- NULL values will be quoted in all columns. This option is allowed only in COPY TO , and only when using CSV format.

Do not match the specified columns’ values against the null string. In the default case where the null string is empty, this means that empty values will be read as zero-length strings rather than nulls, even when they are not quoted. This option is allowed only in COPY FROM , and only when using CSV format.

Match the specified columns’ values against the null string, even if it has been quoted, and if a match is found set the value to NULL . In the default case where the null string is empty, this converts a quoted empty string into NULL. This option is allowed only in COPY FROM , and only when using CSV format.

Specifies that the file is encoded in the encoding_name . If this option is omitted, the current client encoding is used. See the Notes below for more details.

The optional WHERE clause has the general form

where condition is any expression that evaluates to a result of type boolean . Any row that does not satisfy this condition will not be inserted to the table. A row satisfies the condition if it returns true when the actual row values are substituted for any variable references.

Currently, subqueries are not allowed in WHERE expressions, and the evaluation does not see any changes made by the COPY itself (this matters when the expression contains calls to VOLATILE functions).

Outputs

On successful completion, a COPY command returns a command tag of the form

The count is the number of rows copied.

psql will print this command tag only if the command was not COPY . TO STDOUT , or the equivalent psql meta-command \copy . to stdout . This is to prevent confusing the command tag with the data that was just printed.

Notes

COPY TO can be used only with plain tables, not views, and does not copy rows from child tables or child partitions. For example, COPY table TO copies the same rows as SELECT * FROM ONLY table . The syntax COPY (SELECT * FROM table ) TO . can be used to dump all of the rows in an inheritance hierarchy, partitioned table, or view.

COPY FROM can be used with plain, foreign, or partitioned tables or with views that have INSTEAD OF INSERT triggers.

You must have select privilege on the table whose values are read by COPY TO , and insert privilege on the table into which values are inserted by COPY FROM . It is sufficient to have column privileges on the column(s) listed in the command.

If row-level security is enabled for the table, the relevant SELECT policies will apply to COPY table TO statements. Currently, COPY FROM is not supported for tables with row-level security. Use equivalent INSERT statements instead.

Files named in a COPY command are read or written directly by the server, not by the client application. Therefore, they must reside on or be accessible to the database server machine, not the client. They must be accessible to and readable or writable by the PostgreSQL user (the user ID the server runs as), not the client. Similarly, the command specified with PROGRAM is executed directly by the server, not by the client application, must be executable by the PostgreSQL user. COPY naming a file or command is only allowed to database superusers or users who are granted one of the roles pg_read_server_files , pg_write_server_files , or pg_execute_server_program , since it allows reading or writing any file or running a program that the server has privileges to access.

Do not confuse COPY with the psql instruction \copy . \copy invokes COPY FROM STDIN or COPY TO STDOUT , and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \copy is used.

It is recommended that the file name used in COPY always be specified as an absolute path. This is enforced by the server in the case of COPY TO , but for COPY FROM you do have the option of reading from a file specified by a relative path. The path will be interpreted relative to the working directory of the server process (normally the cluster’s data directory), not the client’s working directory.

Executing a command with PROGRAM might be restricted by the operating system’s access control mechanisms, such as SELinux.

COPY FROM will invoke any triggers and check constraints on the destination table. However, it will not invoke rules.

For identity columns, the COPY FROM command will always write the column values provided in the input data, like the INSERT option OVERRIDING SYSTEM VALUE .

COPY input and output is affected by DateStyle . To ensure portability to other PostgreSQL installations that might use non-default DateStyle settings, DateStyle should be set to ISO before using COPY TO . It is also a good idea to avoid dumping data with IntervalStyle set to sql_standard , because negative interval values might be misinterpreted by a server that has a different setting for IntervalStyle .

Input data is interpreted according to ENCODING option or the current client encoding, and output data is encoded in ENCODING or the current client encoding, even if the data does not pass through the client but is read from or written to a file directly by the server.

COPY stops operation at the first error. This should not lead to problems in the event of a COPY TO , but the target table will already have received earlier rows in a COPY FROM . These rows will not be visible or accessible, but they still occupy disk space. This might amount to a considerable amount of wasted disk space if the failure happened well into a large copy operation. You might wish to invoke VACUUM to recover the wasted space.

FORCE_NULL and FORCE_NOT_NULL can be used simultaneously on the same column. This results in converting quoted null strings to null values and unquoted null strings to empty strings.

File Formats

Text Format

When the text format is used, the data read or written is a text file with one line per table row. Columns in a row are separated by the delimiter character. The column values themselves are strings generated by the output function, or acceptable to the input function, of each attribute’s data type. The specified null string is used in place of columns that are null. COPY FROM will raise an error if any line of the input file contains more or fewer columns than are expected.

End of data can be represented by a single line containing just backslash-period ( \. ). An end-of-data marker is not necessary when reading from a file, since the end of file serves perfectly well; it is needed only when copying data to or from client applications using pre-3.0 client protocol.

Backslash characters ( \ ) can be used in the COPY data to quote data characters that might otherwise be taken as row or column delimiters. In particular, the following characters must be preceded by a backslash if they appear as part of a column value: backslash itself, newline, carriage return, and the current delimiter character.

The specified null string is sent by COPY TO without adding any backslashes; conversely, COPY FROM matches the input against the null string before removing backslashes. Therefore, a null string such as \N cannot be confused with the actual data value \N (which would be represented as \\N ).

The following special backslash sequences are recognized by COPY FROM :

Presently, COPY TO will never emit an octal or hex-digits backslash sequence, but it does use the other sequences listed above for those control characters.

Any other backslashed character that is not mentioned in the above table will be taken to represent itself. However, beware of adding backslashes unnecessarily, since that might accidentally produce a string matching the end-of-data marker ( \. ) or the null string ( \N by default). These strings will be recognized before any other backslash processing is done.

It is strongly recommended that applications generating COPY data convert data newlines and carriage returns to the \n and \r sequences respectively. At present it is possible to represent a data carriage return by a backslash and carriage return, and to represent a data newline by a backslash and newline. However, these representations might not be accepted in future releases. They are also highly vulnerable to corruption if the COPY file is transferred across different machines (for example, from Unix to Windows or vice versa).

All backslash sequences are interpreted after encoding conversion. The bytes specified with the octal and hex-digit backslash sequences must form valid characters in the database encoding.

COPY TO will terminate each row with a Unix-style newline ( “ \n ” ). Servers running on Microsoft Windows instead output carriage return/newline ( “ \r\n ” ), but only for COPY to a server file; for consistency across platforms, COPY TO STDOUT always sends “ \n ” regardless of server platform. COPY FROM can handle lines ending with newlines, carriage returns, or carriage return/newlines. To reduce the risk of error due to un-backslashed newlines or carriage returns that were meant as data, COPY FROM will complain if the line endings in the input are not all alike.

CSV Format

This format option is used for importing and exporting the Comma Separated Value ( CSV ) file format used by many other programs, such as spreadsheets. Instead of the escaping rules used by PostgreSQL ‘s standard text format, it produces and recognizes the common CSV escaping mechanism.

The values in each record are separated by the DELIMITER character. If the value contains the delimiter character, the QUOTE character, the NULL string, a carriage return, or line feed character, then the whole value is prefixed and suffixed by the QUOTE character, and any occurrence within the value of a QUOTE character or the ESCAPE character is preceded by the escape character. You can also use FORCE_QUOTE to force quotes when outputting non- NULL values in specific columns.

The CSV format has no standard way to distinguish a NULL value from an empty string. PostgreSQL ‘s COPY handles this by quoting. A NULL is output as the NULL parameter string and is not quoted, while a non- NULL value matching the NULL parameter string is quoted. For example, with the default settings, a NULL is written as an unquoted empty string, while an empty string data value is written with double quotes ( «» ). Reading values follows similar rules. You can use FORCE_NOT_NULL to prevent NULL input comparisons for specific columns. You can also use FORCE_NULL to convert quoted null string data values to NULL .

Because backslash is not a special character in the CSV format, \. , the end-of-data marker, could also appear as a data value. To avoid any misinterpretation, a \. data value appearing as a lone entry on a line is automatically quoted on output, and on input, if quoted, is not interpreted as the end-of-data marker. If you are loading a file created by another application that has a single unquoted column and might have a value of \. , you might need to quote that value in the input file.

In CSV format, all characters are significant. A quoted value surrounded by white space, or any characters other than DELIMITER , will include those characters. This can cause errors if you import data from a system that pads CSV lines with white space out to some fixed width. If such a situation arises you might need to preprocess the CSV file to remove the trailing white space, before importing the data into PostgreSQL .

CSV format will both recognize and produce CSV files with quoted values containing embedded carriage returns and line feeds. Thus the files are not strictly one line per table row like text-format files.

Many programs produce strange and occasionally perverse CSV files, so the file format is more a convention than a standard. Thus you might encounter some files that cannot be imported using this mechanism, and COPY might produce files that other programs cannot process.

Binary Format

The binary format option causes all data to be stored/read as binary format rather than as text. It is somewhat faster than the text and CSV formats, but a binary-format file is less portable across machine architectures and PostgreSQL versions. Also, the binary format is very data type specific; for example it will not work to output binary data from a smallint column and read it into an integer column, even though that would work fine in text format.

The binary file format consists of a file header, zero or more tuples containing the row data, and a file trailer. Headers and data are in network byte order.

PostgreSQL releases before 7.4 used a different binary file format.

File Header

The file header consists of 15 bytes of fixed fields, followed by a variable-length header extension area. The fixed fields are:

11-byte sequence PGCOPY\n\377\r\n\0 — note that the zero byte is a required part of the signature. (The signature is designed to allow easy identification of files that have been munged by a non-8-bit-clean transfer. This signature will be changed by end-of-line-translation filters, dropped zero bytes, dropped high bits, or parity changes.)

If 1, OIDs are included in the data; if 0, not. Oid system columns are not supported in PostgreSQL anymore, but the format still contains the indicator.

32-bit integer, length in bytes of remainder of header, not including self. Currently, this is zero, and the first tuple follows immediately. Future changes to the format might allow additional data to be present in the header. A reader should silently skip over any header extension data it does not know what to do with.

The header extension area is envisioned to contain a sequence of self-identifying chunks. The flags field is not intended to tell readers what is in the extension area. Specific design of header extension contents is left for a later release.

This design allows for both backwards-compatible header additions (add header extension chunks, or set low-order flag bits) and non-backwards-compatible changes (set high-order flag bits to signal such changes, and add supporting data to the extension area if needed).

Tuples

Each tuple begins with a 16-bit integer count of the number of fields in the tuple. (Presently, all tuples in a table will have the same count, but that might not always be true.) Then, repeated for each field in the tuple, there is a 32-bit length word followed by that many bytes of field data. (The length word does not include itself, and can be zero.) As a special case, -1 indicates a NULL field value. No value bytes follow in the NULL case.

There is no alignment padding or any other extra data between fields.

Presently, all data values in a binary-format file are assumed to be in binary format (format code one). It is anticipated that a future extension might add a header field that allows per-column format codes to be specified.

To determine the appropriate binary format for the actual tuple data you should consult the PostgreSQL source, in particular the *send and *recv functions for each column’s data type (typically these functions are found in the src/backend/utils/adt/ directory of the source distribution).

If OIDs are included in the file, the OID field immediately follows the field-count word. It is a normal field except that it’s not included in the field-count. Note that oid system columns are not supported in current versions of PostgreSQL .

File Trailer

The file trailer consists of a 16-bit integer word containing -1. This is easily distinguished from a tuple’s field-count word.

A reader should report an error if a field-count word is neither -1 nor the expected number of columns. This provides an extra check against somehow getting out of sync with the data.

Examples

The following example copies a table to the client using the vertical bar ( | ) as the field delimiter:

To copy data from a file into the country table:

To copy into a file just the countries whose names start with ‘A’:

To copy into a compressed file, you can pipe the output through an external compression program:

Here is a sample of data suitable for copying into a table from STDIN :

Note that the white space on each line is actually a tab character.

The following is the same data, output in binary format. The data is shown after filtering through the Unix utility od -c . The table has three columns; the first has type char(2) , the second has type text , and the third has type integer . All the rows have a null value in the third column.

Compatibility

There is no COPY statement in the SQL standard.

The following syntax was used before PostgreSQL version 9.0 and is still supported:

Note that in this syntax, BINARY and CSV are treated as independent keywords, not as arguments of a FORMAT option.

The following syntax was used before PostgreSQL version 7.3 and is still supported:

See Also

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Как скопировать таблицу в PostgreSQL?

База данных

Многие пользователи просят дублировать таблицу без ее повторного создания и добавления данных в PostgreSQL. Здесь можно использовать команды дублирования. Давайте посмотрим на это, открыв графический интерфейс pgAdmin из меню «Пуск» на рабочем столе Windows 10. Дважды укажите пароль вашего сервера по запросу. После этого вы получите графический пользовательский интерфейс pgAdmin для PostgreSQL. В базах данных вы можете исследовать многие вещи. Вы найдете базу данных Postgres, которая уже была определена и создана PostgreSQL в процессе установки и настройки. Итак, вам не нужно создавать новую базу данных.

Пример 1

Давайте рассмотрим наш первый пример для дублирования таблицы, уже определенной в Postgres. Изучив базу данных Postgres, вы найдете вариант Таблицы. Создайте новую таблицу «test» с записью в ней нескольких столбцов. Вы можете найти эту таблицу под опциями таблицы после ее изучения, как показано на изображении ниже.

Нажмите на значок инструмента запросов, чтобы открыть его. Когда он откроется, напишите в него запрос SELECT, чтобы получить «тестовые» записи только что созданной таблицы в соответствии с приведенной ниже командой. Нажмите на значок «Выполнить», чтобы выполнить эту команду. В выходных данных показаны три различных «тестовых» столбца таблицы с их записями, например, ID, Fname и Lname.

Пришло время создать дублирующую таблицу «Dup_test» для таблицы «test». Итак, сначала откройте новую вкладку инструмента запроса и напишите команду, указанную ниже. В этом запросе есть подчасть для выборки всех записей таблицы «test» с помощью оператора SELECT. Команда CREATE TABLE использовалась для создания новой таблицы «Dup_test», аналогичной таблице «test». Оператор SELECT извлекал все данные и копировал их в таблицу «Dup_test». Выполните запрос, используя значок «Выполнить» на верхней панели задач. После выполнения этого запроса PostgreSQL показывает сообщение об успешном завершении в области вывода в разделе сообщений.

Когда вы исследуете список таблиц, он показывает вам две таблицы, например, dup_test и test.

Когда мы проверяем только что созданную таблицу «dup_test» с помощью запроса SELECT в области инструментов запроса, мы обнаружили, что она содержит те же данные и структуру, что и таблица «test». Итак, запись и структура таблицы test полностью продублированы в таблице dup_test.

Пример 2

Пользователь также может создать дублирующую таблицу в PostgreSQL с помощью другой команды. Это дублирование будет выполнено без дублирования данных таблицы. Следовательно, мы будем использовать ключевое слово «no data» после оператора select в соответствии с приведенным ниже запросом. Запрос создавал новую таблицу с именем «duplicate» с помощью оператора CREATE TABLE и копировал структуру таблицы «test» с помощью оператора SELECT. Оператор «без данных» будет использоваться для предотвращения копирования данных из таблицы «test» в таблицу «duplicate». После выполнения запрос был успешным, как показано ниже, и таблица была успешно продублирована.

Вы можете найти эту таблицу в разделе «Таблицы» PostgreSQL, как показано ниже.

Проверив записи новой дублированной таблицы с именем «duplicate» с помощью запроса SELECT, как показано ниже, мы обнаружили, что структура таблицы такая же, как у таблицы «test». В этой таблице нет записей из-за использования в запросе оператора «без данных». Следовательно, запрос был успешным.

Пример 3

Еще один быстрый и простой способ дублировать таблицу — использовать оператор «AS TABLE» в команде CREATE TABLE PostgreSQL. В этом случае мы увидим, как этот запрос работает волшебным образом. Итак, мы открыли инструмент запроса по его значку. Затем мы должны написать в нем следующий запрос. Мы создали таблицу с именем «новая» как копию таблицы «test» с помощью предложения «AS TABLE» в нашем запросе. Попробуйте выполнить команду в области запроса оболочки командной строки PostgreSQL, чтобы увидеть результаты. Щелкните значок «Выполнить» на панели задач графического пользовательского интерфейса pgAdmin или нажмите клавишу «Ввод» на клавиатуре, если вы работаете в командной оболочке SQL для выполнения этого запроса. Вы увидите, что запрос работает правильно в соответствии с выводом, показанным в области вывода моментального снимка, например сообщениями. Это означает, что таблица «test» была успешно продублирована,

Вы можете увидеть вновь созданную таблицу «новая» в списке таблиц в базе данных Postgres.

После извлечения содержимого таблицы «новая» инструментом запроса с помощью команды SELECT он показывает те же данные, что и таблица «test» вместе со структурой, например, имена столбцов.

Пример 4

Приведем еще один простой пример, иллюстрирующий концепцию дублирования. На этот раз мы создали таблицу «new» в базе данных Postgres графического пользовательского интерфейса pgAdmin. Эта таблица содержит 10 записей в четырех столбцах, например ID, Имя, Город и Возраст. Давайте посмотрим на записи таблицы «новая» с помощью инструмента запроса. Мы попробовали следующую команду в области запроса, чтобы получить «новый» порядок таблицы по столбцу идентификатора. Выходные данные этой команды показывают 10 записей для некоторых пользователей.

Чтобы создать повторяющуюся таблицу, откройте новую вкладку для инструмента запроса. Мы использовали приведенную ниже команду для создания новой таблицы «дубликат» в качестве «новой» таблицы, показанной выше. Мы использовали предложение «WITH NO DATA» в этом запросе, чтобы не копировать содержимое таблицы. Вместо этого этот запрос будет делать только копию структуры таблицы «новой». Поэтому после написания этого запроса в области запроса нажмите кнопку «Выполнить» на панели задач pgAdmin. Запрос будет выполнен, и сообщение об успешном завершении повторяющейся таблицы будет показано в области вывода инструмента запроса, как показано на снимке ниже.

После копирования и создания таблицы давайте посмотрим на вновь созданную дублированную таблицу, например, «duplicate». Итак, мы получили содержимое таблицы «дубликат» при использовании запроса SELECT в области запроса, упорядоченной по столбцу «ID». Мы видели, что структура «дубликата» таблицы совпадает с «новой» таблицей. Эта таблица не копировала записи таблицы «новая», использующая предложение «WITH NO DATA».

Заключение

Мы обсудили различные команды PostgreSQL для дублирования таблицы. Мы видели, как дублировать структуру таблицы с данными и без них. Все эти команды одинаково эффективны для использования в оболочке командной строки PostgreSQL.

Copy a table (including indexes) in postgres

I have a postgres table. I need to delete some data from it. I was going to create a temporary table, copy the data in, recreate the indexes and the delete the rows I need. I can’t delete data from the original table, because this original table is the source of data. In one case I need to get some results that depends on deleting X, in another case, I’ll need to delete Y. So I need all the original data to always be around and available.

However it seems a bit silly to recreate the table and copy it again and recreate the indexes. Is there anyway in postgres to tell it «I want a complete separate copy of this table, including structure, data and indexes»?

Unfortunately PostgreSQL does not have a «CREATE TABLE .. LIKE X INCLUDING INDEXES’

6 Answers 6

New PostgreSQL ( since 8.3 according to docs ) can use «INCLUDING INDEXES»:

As you can see I’m testing on 8.3.

Now, let’s create table:

And see how it looks:

Now we can copy the structure:

And check the structure:

If you are using PostgreSQL pre-8.3, you can simply use pg_dump with option «-t» to specify 1 table, change table name in dump, and load it again:

And now the table is:

The other way to create a new table from the first is to use

Note that Postgresql has a patch out to fix tablespace issues if the second method is used

There are many answers on the web, one of them can be found here.

I ended up doing something like this:

This will copy the schema and the data including indexes, but not including triggers and constraints. Note that indexes are shared with original table so when adding new row to either table the counter will increment.

I have a postgres table. I need to delete some data from it.

. won’t work for some reason. (Care to share that reason?)

I was going to create a temporary table, copy the data in, recreate the indexes and the delete the rows I need.

Look into pg_dump and pg_restore. Using pg_dump with some clever options and perhaps editing the output before pg_restoring might do the trick.

Since you are doing «what if»-type analysis on the data, I wonder if might you be better off using views.

You could define a view for each scenario you want to test based on the negation of what you want to exclude. I.e., define a view based on what you want to INclude. E.g., if you want a «window» on the data where you «deleted» the rows where X=Y, then you would create a view as rows where (X != Y).

Views are stored in the database (in the System Catalog) as their defining query. Every time you query the view the database server looks up the underlying query that defines it and executes that (ANDed with any other conditions you used). There are several benefits to this approach:

1. You never duplicate any portion of your data.
2. The indexes already in use for the base table (your original, «real» table) will be used (as seen fit by the query optimizer) when you query each view/scenario. There is no need to redefine or copy them.
3. Since a view is a «window» (NOT a shapshot) on the «real» data in the base table, you can add/update/delete on your base table and simply re-query the view scenarios with no need to recreate anything as the data changes over time.

There is a trade-off, of course. Since a view is a virtual table and not a «real» (base) table, you’re actually executing a (perhaps complex) query every time you access it. This may slow things down a bit. But it may not. It depends on many issues (size and nature of the data, quality of the statistics in the System Catalog, speed of the hardware, usage load, and much more). You won’t know until you try it. If (and only if) you actually find that the performance is unacceptably slow, then you might look at other options. (Materialized views, copies of tables, . anything that trades space for time.)

Как скопировать таблицу в postgresql

COPY — копировать данные между файлом и таблицей

Синтаксис

Описание

COPY перемещает данные между таблицами PostgreSQL и обычными файлами в файловой системе. COPY TO копирует содержимое таблицы в файл, а COPY FROM — из файла в таблицу (добавляет данные к тем, что уже содержались в таблице). COPY TO может также скопировать результаты запроса SELECT .

Если указывается список столбцов, COPY TO копирует в файл только данные указанных столбцов, а COPY FROM вставляет каждое поле из файла в соответствующий ему по порядку столбец из указанного списка. В случае отсутствия в этом списке каких-либо столбцов таблицы при COPY FROM они получают значения по умолчанию.

COPY с именем файла указывает серверу PostgreSQL читать или записывать непосредственно этот файл. Заданный файл должен быть доступен пользователю PostgreSQL (тому пользователю, от имени которого работает сервер), и путь к файлу должен задаваться с точки зрения сервера. Когда указывается параметр PROGRAM , сервер выполняет заданную команду и читает данные из стандартного вывода программы, либо записывает их в стандартный ввод. Команда должна определяться с точки зрения сервера и быть доступной для исполнения пользователю PostgreSQL . Когда указывается STDIN или STDOUT , данные передаются через соединение клиента с сервером.

Каждый процесс, выполняющий операцию COPY , будет выдавать информацию о ходе её выполнения, отображаемую в представлении pg_stat_progress_copy . За подробностями обратитесь к Подразделу 28.4.6.

Параметры

Имя существующей таблицы (возможно, дополненное схемой). имя_столбца

Необязательный список столбцов, данные которых будут копироваться. Если этот список отсутствует, копируются все столбцы таблицы, за исключением генерируемых. запрос

Команда SELECT , VALUES , INSERT , UPDATE или DELETE , результаты которой будут скопированы. Заметьте, что запрос должен заключаться в скобки.

Для запросов INSERT , UPDATE и DELETE должно задаваться предложение RETURNING и в целевом отношении не должно быть условного правила, правила ALSO или правила INSTEAD , разворачивающегося в несколько операторов. имя_файла

Путь входного или выходного файла. Путь входного файла может быть абсолютным или относительным, но путь выходного должен быть только абсолютным. Пользователям Windows следует использовать формат E» и продублировать каждую обратную черту в пути файла. PROGRAM

Выполняемая команда. COPY FROM читает стандартный вывод команды, а COPY TO записывает в её стандартный ввод.

Заметьте, что команда запускается через командную оболочку, так что если требуется передать этой команде какие-либо аргументы, поступающие из недоверенного источника, необходимо аккуратно избавиться от всех спецсимволов, имеющих особое значение в оболочке, либо экранировать их. По соображениям безопасности лучше ограничиться фиксированной строкой команды или как минимум не позволять пользователям вводить в неё произвольное содержимое. STDIN

Указывает, что данные будут поступать из клиентского приложения. STDOUT

Указывает, что данные будут выдаваться клиентскому приложению. boolean

Включает или отключает заданный параметр. Для включения параметра можно написать TRUE , ON или 1 , а для отключения — FALSE , OFF или 0 . Значение boolean можно опустить, в этом случае подразумевается TRUE . FORMAT

Выбирает формат чтения или записи данных: text (текстовый), csv (значения, разделённые запятыми, Comma Separated Values) или binary (двоичный). По умолчанию выбирается формат text . FREEZE

Запросы копируют данные с уже замороженными строками, как после выполнения команды VACUUM FREEZE . Это позволяет увеличить производительность при начальном добавлении данных. Строки будут замораживаться, только если загружаемая таблица была создана или опустошена в текущей подтранзакции, с ней не связаны открытые курсоры и в данной транзакции нет других снимков. Выполнять COPY FREEZE с секционированной таблицей в настоящее время нельзя.

Заметьте, что все другие сеансы будут немедленно видеть данные, как только они будут успешно загружены. Это нарушает принятые правила видимости MVCC, так что пользователи, включающие этот режим, должны понимать, какие проблемы это может вызвать. DELIMITER

Задаёт символ, разделяющий столбцы в строках файла. По умолчанию это символ табуляции в текстовом формате и запятая в формате CSV . Задаваемый символ должен быть однобайтовым. Для формата binary этот параметр не допускается. NULL

Определяет строку, задающую значение NULL. По умолчанию в текстовом формате это \N (обратная косая черта и N), а в формате CSV — пустая строка без кавычек. Пустую строку можно использовать и в текстовом формате, если не требуется различать пустые строки и NULL. Для формата binary этот параметр не допускается.

Примечание

При выполнении COPY FROM любые значения, совпадающие с этой строкой, сохраняются как значение NULL, так что при переносе данных важно убедиться в том, что это та же строка, что применялась в COPY TO .

Указывает, что файл содержит строку заголовка с именами столбцов. При выводе первая строка файла будет содержать имена столбцов таблицы, а при вводе первая строка просто игнорируется. Этот параметр допускается только для формата CSV . QUOTE

Указывает символ кавычек, используемый для заключения данных в кавычки. По умолчанию это символ двойных кавычек. Задаваемый символ должен быть однобайтовым. Этот параметр поддерживается только для формата CSV . ESCAPE

Задаёт символ, который будет выводиться перед символом данных, совпавшим со значением QUOTE . По умолчанию это тот же символ, что и QUOTE (то есть, при появлении в данных кавычек, они дублируются). Задаваемый символ должен быть однобайтовым. Этот параметр допускается только для режима CSV . FORCE_QUOTE

Принудительно заключает в кавычки все значения не NULL в указанных столбцах. Выводимое значение NULL никогда не заключается в кавычки. Если указано * , в кавычки будут заключаться значения не NULL во всех столбцах. Этот параметр принимает только команда COPY TO и только для формата CSV . FORCE_NOT_NULL

Не сопоставлять значения в указанных столбцах с маркером NULL. По умолчанию, когда маркер пуст, это означает, что пустые значения будут считаны как строки нулевой длины, а не NULL, даже когда они не заключены в кавычки. Этот параметр допускается только в команде COPY FROM и только для формата CSV . FORCE_NULL

Сопоставлять значения в указанных столбцах с маркером NULL, даже если они заключены в кавычки, и в случае совпадения устанавливать значение NULL . По умолчанию, когда этот маркер пуст, пустая строка в кавычках будет преобразовываться в NULL. Этот параметр допускается только в команде COPY FROM и только для формата CSV . ENCODING

Указывает, что файл имеет кодировку имя_кодировки . Если этот параметр опущен, выбирается текущая кодировка клиента. Подробнее об этом говорится ниже, в примечаниях. WHERE

Необязательное предложение WHERE имеет общую форму

, где условие — любое выражение, выдающее результат типа boolean . Строки, не удовлетворяющие этому условию, добавляться в таблицу не будут. Строка удовлетворяет условию, если оно возвращает true при подстановке вместо ссылок на переменные фактических значений из этой строки.

В настоящее время выражения WHERE не могут включать подзапросы, а при вычислении выражений не видны изменения, которые вносит сама команда COPY (это играет роль, когда в них вызываются функции с характеристикой VOLATILE ).

Выводимая информация

В случае успешного завершения, COPY возвращает метку команды в виде

Здесь число — количество скопированных записей.

Примечание

psql выводит эту метку, только если выполнялась не команда COPY . TO STDOUT или её аналог в psql , метакоманда \copy . to stdout . Это сделано для того, чтобы метка команды не смешалась с данными, выведенными перед ней.

Замечания

Команду COPY TO можно использовать только с простыми таблицами, не представлениями, и при этом она не копирует строки из дочерних таблиц или секций. То есть, COPY таблица TO копирует те же строки, что выдаёт запрос SELECT * FROM ONLY таблица . Для выгрузки всех строк представления или таблицы с учётом иерархии наследования или секционирования можно применить COPY (SELECT * FROM таблица ) TO . .

COPY FROM можно применять с обычными, сторонними и секционированными таблицами или представлениями, в которых установлены триггеры INSTEAD OF INSERT .

В таблице, данные которой читает команда COPY TO , требуется иметь право на выборку данных, а в таблице, куда вставляет значения COPY FROM , требуется право на добавление. При этом, если в команде перечисляются избранные столбцы, достаточно иметь права только для них.

Если для таблицы включена защита на уровне строк, соответствующие политики SELECT будут применяться и к операторам COPY таблица TO . Операторы COPY FROM для таблиц с защитой строк в настоящее время не поддерживаются. Вместо них следует использовать равнозначные операторы INSERT .

Файлы, указанные в команде COPY , читаются или записываются непосредственно сервером, не клиентским приложением. Поэтому они должны располагаться на сервере или быть доступными серверу, а не клиенту. Они должны быть доступны на чтение или запись пользователю PostgreSQL (пользователю, от имени которого работает сервер), не клиенту. Аналогично, команда, указанная параметром PROGRAM , выполняется непосредственно сервером, а не клиентским приложением, и должна быть доступна на выполнение пользователю PostgreSQL . Выполнять COPY с указанием файла или внешней команды разрешено только суперпользователям базы данных или членам ролей pg_read_server_files , pg_write_server_files или pg_execute_server_program , так как это позволяет читать/записывать любые файлы и запускать любые программы, к которым имеет доступ сервер.

Не путайте команду COPY с реализованной в psql метакомандой \copy . Метакоманда \copy вызывает COPY FROM STDIN или COPY TO STDOUT , а затем работает с данными в файле, доступном клиенту psql . Таким образом, когда применяется команда \copy , доступность файла и права доступа зависят от клиента, а не от сервера.

Путь файла, указываемый в COPY , рекомендуется всегда задавать как абсолютный, а не относительный. Это обязательное условие для команды COPY TO , но COPY FROM позволяет прочитать файл, заданный и относительным путём. Такой путь будет интерпретироваться относительно рабочего каталога серверного процесса (обычно это каталог данных кластера), а не рабочего каталога клиента.

Выполнение команды в PROGRAM может быть ограничено и другими работающими в ОС механизмами контроля доступа, например SELinux.

COPY FROM вызывает все триггеры и обрабатывает все ограничения-проверки в целевой таблице. Однако правила при загрузке данных не вызываются.

Для столбцов идентификации команда COPY FROM всегда переносит значения, содержащиеся во входных данных, как команда INSERT с указанием OVERRIDING SYSTEM VALUE .

При вводе и выводе данных COPY учитывается DateStyle . Для обеспечения переносимости на другие инсталляции PostgreSQL , в которых могут использоваться нестандартные значения DateStyle , значение DateStyle следует установить равным ISO до вызова COPY TO . Также рекомендуется не выгружать данные с IntervalStyle равным sql_standard , так как сервер с другим значением IntervalStyle может неправильно воспринимать отрицательные интервалы в таких данных.

Входные данные интерпретируются согласно кодировке, заданной параметром ENCODING , или текущей кодировке клиента, а выходные кодируются в кодировке ENCODING или текущей кодировке клиента, даже если данные не проходят через клиента, а считываются или записываются в файл непосредственно сервером.

COPY прекращает операцию при первой ошибке. Это не должно приводить к проблемам в случае с COPY TO , но после COPY FROM в целевой таблице остаются ранее полученные строки. Эти строки не будут видимыми и доступными, но будут занимать место на диске. Если сбой происходит при копировании большого объёма данных, это может приводить к значительным потерям дискового пространства. При желании вернуть потерянный объём, это можно сделать с помощью команды VACUUM .

FORCE_NULL и FORCE_NOT_NULL можно применить одновременно к одному столбцу. В результате NULL-значения в кавычках будут преобразованы в NULL, а NULL-значения без кавычек — в пустые строки.

Форматы файлов

Текстовый формат

Когда применяется формат text, читаемые или записываемые данные представляют собой текстовый файл, строка в котором соответствует строке таблицы. Столбцы в строке разделяются символом-разделителем. Значения самих столбцов — текстовые строки, выдаваемые функцией вывода, либо воспринимаемые функцией ввода, соответствующей типу данных столбца. Заданный маркер NULL выводится и считывается вместо столбцов со значением NULL. COPY FROM выдаёт ошибку, если в любой из строк во входном файле оказывается больше или меньше столбцов, чем ожидается.

Конец данных может обозначаться одной строкой, содержащей только обратную косую и точку ( \. ). Маркер конца данных не требуется при чтении из файла, так как его роль вполне выполняет конец файла; он необходим только при передаче данных в/из клиентского приложения по протоколу обмена до версии 3.0.

Символы обратной косой черты ( \ ) в данных COPY позволяют экранировать символы данных, которые без них считались бы разделителями строк или столбцов. В частности, предваряться обратной косой должны следующие символы, когда они оказываются в значении столбца: сама обратная косая черта, перевод строки, возврат каретки и текущий разделитель.

Маркер NULL передаётся команде COPY TO как есть, без добавления обратной косой; COPY FROM , со своей стороны, ищет во вводимых данных маркеры NULL до удаления обратных косых. Таким образом, маркер NULL, например такой как \N , отличается от значения \N в данных (оно должно представляться в виде \\N ).

Команда COPY FROM распознаёт следующие спецпоследовательности:

В настоящее время COPY TO никогда не выводит спецпоследовательности с восьмеричными или шестнадцатеричными кодами, однако выводит другие вышеперечисленные спецпоследовательности вместо управляющих символов.

Любой другой символ после обратной косой, отсутствующий в приведённой выше таблице, будет представлять себя. Однако опасайтесь излишнего добавления обратных косых, так как это может привести к случайному образованию строки, обозначающей маркер конца данных ( \. ) или маркер NULL ( \N по умолчанию). Эти строки будут восприняты прежде, чем обработаются спецпоследовательности с обратной косой.

В приложениях, генерирующих данные для COPY , настоятельно рекомендуется преобразовать символы новой строки и возврата каретки в последовательности \n и \r , соответственно. В настоящее время можно представить возврат каретки в данных как обратная косая и возврат каретки, а перевод строки как обратная косая и перевод строки, однако это может не поддерживаться в будущих версиях. Такие символы также подвержены искажениям, если файл с выводом COPY переносится между разными системами (например, с Unix в Windows и наоборот).

Все последовательности с обратной косой чертой обрабатываются после преобразования кодировки. Байты, заданные в таких последовательностях восьмеричными или шестнадцатеричными цифрами, должны представлять допустимые символы в кодировке базы данных.

COPY TO завершает каждую строку символом новой строки в стиле Unix ( « \n » ). Серверы, работающие в Microsoft Windows, вместо этого выводят символы возврат каретки/новая строка ( « \r\n » ), но только при выводе COPY в файл на сервере; для согласованности на разных платформах, COPY TO STDOUT всегда передаёт « \n » , вне зависимости от платформы сервера. COPY FROM может воспринимать строки, завершающиеся символами новая строка, перевод каретки, либо возврат каретки+новая строка. Чтобы уменьшить риск ошибки из-за неэкранированных символов новой строки и возврата каретки, которые должны были быть данными, COPY FROM сигнализирует о проблеме, если концы строк во входных данных различаются.

Формат CSV

Этот формат применяется для импорта и экспорта данных в виде списка значений, разделённых запятыми ( CSV ), с которым могут работать многие другие программы, например электронные таблицы. Вместо правил экранирования значений, введённых в PostgreSQL для текстового формата, этот формат использует стандартный механизм экранирования CSV.

Значения в каждой записи разделяются символами DELIMITER . Если значение содержит символ разделителя, символ QUOTE , маркер NULL , символ возврата каретки или перевода строки, то всё значение дополнятся спереди и сзади символами QUOTE , а любое вхождение символа QUOTE или спецсимвола ( ESCAPE ) в данных предваряется спецсимволом. С указанием FORCE_QUOTE в кавычки будут принудительно заключаться любые значения не NULL в указанных столбцах.

В формате CSV отсутствует стандартный способ отличить значение NULL от пустой строки. В PostgreSQL команда COPY решает это с помощью кавычек. Значение NULL выводится в виде строки, задаваемой параметром NULL , и не заключается в кавычки, тогда как значение не NULL , со строкой, задаваемой параметром NULL , заключается. Например, с параметрами по умолчанию NULL записывается в виде пустой строки без кавычек, тогда как пустая строка записывается в двойных кавычках ( «» ). При чтении значений действуют похожие правила. Указание FORCE_NOT_NULL позволяет избежать сравнений на NULL во входных данных в заданных столбцах, а FORCE_NULL — преобразовывать в NULL маркеры NULL, даже заключённые в кавычки.

Так как обратная косая черта не является спецсимволом в формате CSV , маркер конца данных \. может быть и значением данных. Во избежание ошибок интерпретации данные \. , выводимые в виде единственного элемента строки, автоматически заключаются в кавычки при выводе, а при вводе этот маркер, заключённый в кавычки, не воспринимается как маркер конца данных. При загрузке файла, созданного другой программой, в котором в единственном столбце без кавычек оказалось значение \. , потребуется дополнительно заключить это значение в кавычки.

Примечание

В формате CSV все символы являются значимыми. Заключённое в кавычки значение, дополненное пробелами или любыми другими символами, кроме DELIMITER , будет включать и эти символы. Это может приводить к ошибкам при импорте данных из системы, дополняющей строки CSV пробельными символами до некоторой фиксированной ширины. В случае возникновения такой проблемы необходимо обработать файл CSV и удалить из него замыкающие пробельные символы, прежде чем загружать данные из него в PostgreSQL .

Примечание

Обработчик формата CSV воспринимает и генерирует файлы CSV со значениями в кавычках, которые могут содержать символы возврата каретки и перевода строки. Таким образом, число строк в этих файлах не строго равно числу строк в таблице, как в файлах текстового формата.

Примечание

Многие программы генерируют странные и иногда неприемлемые файлы CSV, так что этот формат используется скорее по соглашению, чем по стандарту. Поэтому вам могут встретиться файлы, которые невозможно импортировать, используя этот механизм, а COPY может сформировать такие файлы, что их не смогут обработать другие программы.

Двоичный формат

При выборе формата binary все данные сохраняются/считываются в двоичном, а не текстовом виде. Иногда этот формат обрабатывается быстрее, чем текстовый и CSV , но он может оказаться непереносимым между разными машинными архитектурами и версиями PostgreSQL . Кроме того, двоичный формат сильно зависит от типов данных; например, он не позволяет вывести данные из столбца smallint , а затем прочитать их в столбец integer , хотя с текстовым форматом это вполне возможно.

Формат binary включает заголовок файла, ноль или более записей, содержащих данные строк, и окончание файла. Для заголовков и данных принят сетевой порядок байт.

Примечание

В PostgreSQL до версии 7.4 использовался другой двоичный формат.

Заголовок файла

Заголовок файла содержит 15 байт фиксированных полей, за которыми следует область расширения заголовка переменной длины. Фиксированные поля:

Последовательность из 11 байт PGCOPY\n\377\r\n\0 — заметьте, что нулевой байт является обязательной частью сигнатуры. (Эта сигнатура позволяет легко выявить файлы, испорченные при передаче, не сохраняющей все 8 бит данных. Она изменится при прохождении через фильтры, меняющие концы строк, отбрасывающие нулевые байты или старшие биты, либо добавляющие чётность.) Поле флагов

При 1 в данные включается OID, при 0 — не включается. Системные столбцы oid в PostgreSQL больше не поддерживаются, но этот индикатор всё ещё сохраняется.

Целое 32-битное число, определяющее длину в байтах остального заголовка, не включая само это значение. В настоящее время содержит 0, и сразу за ним следует первая запись. При будущих изменениях формата в заголовок могут быть добавлены дополнительные данные. Обработчик должен просто пропускать все расширенные данные заголовка, о которых ему ничего не известно.

Область расширения заголовка предусмотрена для размещения последовательности самоопределяемых блоков. Поле флагов не должно содержать указаний о том, что содержится в области расширения. Точное содержимое области расширения может быть определено в будущих версиях.

При таком подходе возможно как обратно-совместимое дополнение заголовка (добавить блоки расширения заголовка или установить младшие биты флагов), так и не обратно-совместимое (установить старшие биты флагов, сигнализирующие о подобном изменении, и добавить вспомогательные данные в область расширения, если это потребуется).

Записи

Каждая запись начинается с 16-битного целого числа, определяющего количество полей в записи. (В настоящее время во всех записях должно быть одинаковое число полей, но так может быть не всегда.) Затем, для каждого поля в записи указывается 32-битная длина поля, за которой следует это количество байт с данными поля. (Значение длины не включает свой размер, и может быть равно нулю.) В качестве особого варианта, -1 обозначает, что в поле содержится NULL. В случае с NULL за длиной не следуют байты данных.

Выравнивание или какие-либо дополнительные данные между полями не вставляются.

В настоящее время предполагается, что все значения данных в файле двоичного формата содержатся в двоичном формате (формате под кодом 1). Возможно, в будущем расширении в заголовок будет добавлено поле, позволяющее задавать другие коды форматов для разных столбцов.

Чтобы определить подходящий двоичный формат для фактических данных, обратитесь к исходному коду PostgreSQL , в частности, к функциям *send и *recv для типов данных каждого столбца (обычно эти функции находятся в каталоге src/backend/utils/adt/ в дереве исходного кода).

Если в файл включается OID, поле OID следует немедленно за числом, определяющим количество полей. Это поле не отличается от других ничем, кроме того, что оно не учитывается в количестве полей. Заметьте, что в текущих версиях PostgreSQL системные столбцы oid не поддерживаются.

Окончание файла

Окончание файла состоит из 16-битного целого, содержащего -1. Это позволяет легко отличить его от счётчика полей в записи.

Обработчик, читающий файл, должен выдать ошибку, если число полей в записи не равно -1 или ожидаемому числу столбцов. Это обеспечивает дополнительную проверку синхронизации данных.

Примеры

В следующем примере таблица передаётся клиенту с разделителем полей «вертикальная черта» ( | ):

Копирование данных из файла в таблицу country :

Копирование в файл только данных стран, название которых начинается с ‘A’:

Для копирования данных в сжатый файл можно направить вывод через внешнюю программу сжатия:

Пример данных, подходящих для копирования в таблицу из STDIN :

Примечание: пробелы в каждой строке на самом деле обозначают символы табуляции.

Ниже приведены те же данные, но выведенные в двоичном формате. Данные показаны после обработки Unix-утилитой od -c . Таблица содержит три столбца; первый имеет тип char(2) , второй — text , а третий — integer . Последний столбец во всех строках содержит NULL.

Совместимость

Оператор COPY отсутствует в стандарте SQL.

До версии PostgreSQL 9.0 использовался и по-прежнему поддерживается следующий синтаксис:

Заметьте, что в этом синтаксисе ключевые слова BINARY и CSV обрабатываются как независимые, а не как аргументы параметра FORMAT .

До версии PostgreSQL 7.3 использовался и по-прежнему поддерживается следующий синтаксис: