psql is the command-line front end to PostgreSQL. This page describes thepsqlcommands that the PostgreSQL interface for Spanner supports. To learn how toconnect withpsql seeConnecting psql to a PostgreSQL-dialect database.

Meta-commands

The PostgreSQL interface supports the followingpsql meta-commands categories:

psql communicates with Spanner throughPGAdapter, which uses the core engine of the Spanner JDBC driver. The driversupports the session management statements described inSession management statements.Therefore, you can use these statements withpsql.

SQL statement batching

psql and PGAdapter support multi-statement SQL batches. To batch statements, you usethepsql -c option. This option allows for one or more SQL or session managementstatements, separated by semicolons (;), to be passed in as a single execution request.A batch can include any supported statements, and can mix DDL, DML, and DQL.

A multi-statement batch is executed within a single implicit transaction block. Implicit transaction blocks are automatically closed at the end of the batch. If any errors occur inside of an implicit transaction block, the entire transaction is rolled back.

ExplicitBEGIN andCOMMIT transaction controls are supported, but an explicit transaction blockcan't contain DDL statements.

Examples

DML

The following example shows how to submit a batch ofINSERT statements.

psql-hlocalhost-p5432-c"INSERT INTO users (id, age, firstname) VALUES (1, 25, 'Alex'); \ INSERT INTO users (id, age, firstname) VALUES (2, 31, 'Dana'); \ INSERT INTO users (id, age, firstname) VALUES (3, 54, 'Izumi');"

The next example shows how to execute the SQL statements in the fileinsert_contacts.sql.

psql-hlocalhost-c"$(cat contacts_insert.sql)"

DDL

This example submits a batch ofALTER TABLE statements.

psql-hlocalhost-p5432test-db-c"ALTER TABLE users ADD col1 integer; \ ALTER TABLE users ADD col2 text; ALTER TABLE users ADD col3 float8;"

COPY command for importing data

Use theCOPY FROM STDIN command to import data from a text or CSV file into a PostgreSQL-dialect database. Although only STDIN is supported, you can import usingCOPYby piping files intopsql.

There are two ways to execute theCOPY command:

• AtomicCOPY

  Data is copied in a single transaction. This is the default. Standard transaction limits of Spanner apply to the transaction. This means that at most80,000 mutations or 100MB of data can be included in oneCOPY operation.

• Non-atomicCOPY

  COPY automatically splits the data over multipletransactions if the file contains more than 80,000 mutations or more than 100MB.

  If an error is encountered during theCOPY and the operation aborts,some rows might already be persisted to the database. No rollback occurs. The transactions are executed in parallel, so data after the row in the importfile that caused the error might be imported to the database beforetheCOPY operation is halted.

Enable non-atomicCOPY

To enable non-atomicCOPY, submit the following command before executing the copyoperation.

SET SPANNER.AUTOCOMMIT_DML_MODE='PARTITIONED_NON_ATOMIC'

Syntax

COPYtable_name [ (column_name [, ...] ) ]    FROM STDIN    [ [ WITH ] (option [, ...] ) ]whereoption is one of:    FORMATformat_name    DELIMITER 'delimiter_character'    NULL 'null_string'    QUOTE 'quote_character'    ESCAPE 'escape_character'    HEADER [boolean]andformat_name is:    {text|csv}anddelimiter_character is:    [!-~] except ' " \andnull_string is:    {a—z|A—Z|0—9|_}+andquote_character is:    [!-~] except ' " \andescape_character is:    [!-~] except ' " \andboolean is:    {TRUE|ON|1|FALSE|OFF|0}

The table must already exist. If no column list is specified, all columns of the table are copied.

The default forFORMAT istext.

delimiter_character must be a one-byte character. The default is the Tab character for text format and a comma for CSV format.

NULL 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.

QUOTE specifies the quoting character to use 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 the CSV format.

ESCAPE specifies the character to appear before a data character that matches theQUOTE value. The default is the same as theQUOTE 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 the CSV format.

HEADER indicates if the first record of the input file is a header (contains column names). The default is TRUE.

Examples

This example imports data from the text-formatted file namedmydata.txt into tablemytable. PGAdaptermust be running. For more information, seeStarting PGAdapter.

catmydata.txt|psql-hlocalhost-c"COPY mytable FROM STDIN;"

In this next example,mydata.csv is in CSV format and its first row is a header withcomma-separated column names.

catmydata.csv|psql-hlocalhost\-c"COPY mytable FROM STDIN WITH (FORMAT csv, ESCAPE '~', HEADER TRUE);"

This next example shows how to start a non-atomicCOPY operation.

catmydata.txt|psql-hlocalhost\-c"SET SPANNER.AUTOCOMMIT_DML_MODE='PARTITIONED_NON_ATOMIC'"-c"COPY mytable FROM STDIN;"

Troubleshooting

The following are some common errors.

Invalid input syntax

The following error occurs:

Invalid input syntax for type <type>:"<table_name>"

This error can occur when the input file has a header row with column names andtheHEADER option was not specified.

Invalid COPY data

The following error occurs:

Invalid COPY data: Row length mismatched. Expected <number> columns, but only found <number>

This error occurs when a row in the input file doesn't include a value (or null) for every column in the table. One cause could be a malformed CSV file, or a mismatchbetween the specified delimiter option (or default delimiter) and the actual delimiter in the file.

What's next

• Learn how toconnect to a PostgreSQL-dialect database withpsql.
• Learn aboutPGAdapter.