sql: support COPY FROM ... WHERE ....

otan commented 4 years ago

PostgreSQL 12 introduced a COPY FROM ... WHERE .. clause: https://www.postgresql.org/docs/current/sql-copy.html

WHERE
The optional WHERE clause has the general form

WHERE condition
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).

I imagine this has knock on effects for bulk-io / IMPORT PGCOPY.

Jira issue: CRDB-3739

Epic CRDB-18320

mwang1026 commented 3 years ago

@otan wondering why this was tagged bulk? separately, looks like it was implemented -- can we close if so?

otan commented 3 years ago

Import takes in copy clauses which need is why I tagged it

lacasaprivata2 commented 3 years ago

 async with tx.copy("COPY ACCOUNT (id, handle, avatar_id, metadata, status, privacy, indexer, timestamp, phone, name) FROM STDIN") as stream:
        for i in batch:
          await stream.write_row((
            i.id,
            i.handle,
            i.avatar_id,
            i.metadata,
            i.status,
            i.privacy,
            i.indexer,
            i.timestamp,
            i.phone,
            i.name
          ))

Not sure if this was implemented, this still does not work..... https://github.com/psycopg/psycopg/issues/78

cockroachdb / cockroach

sql: support COPY FROM ... WHERE .... #54580