Postgresql – Wildcard for meta-command in postgresql

copycsvpostgresqlpsql

I am searching if there is a wildcard assigned to the \ meta-commandsSee description – meta-commands in Postgresql.

for example..

postgresql=> \copy scheme.table FROM 'D:\path\to\%.csv' DELIMITER ';' CSV ENCODING 'LATIN1';

Make notice of the % in the example provided – A known declaration of wildcards could be _, %, . or *. But I do not seem to find documentation of wildcards in /meta-commands for Postresql.

Best Answer

First \COPY isn't postgresql. That's the client psql. From the docs,

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.

No, psql does not provide filename completion. However there are other wrapper around COPY FROM STDIN that are not psql and they do. For instance pgloader provides FROM [ ALL FILENAMES | [ FIRST ] FILENAME ] MATCHING

The same clause can also be applied to several files with using the spelling FROM ALL FILENAMES MATCHING and a regular expression. The whole matching clause must follow the following rule:

And on top of that you can always use bash or whatever shell you're using to launch psql. Example with bash filename expansion.

for i in *.csv;
  do psql -c "\copy scheme.table FROM $$D:\path\to\$i.csv$$ DELIMITER ';' CSV ENCODING 'LATIN1';"
done;

Power-shell certainly also provides filename expansion. Google for it if you're on Windows 8+.

Related Solutions

PostgreSQL tree structure and recursive CTE optimization

If you really have to modify these data rarely, then you can simply store the result of the CTE in a table, and run queries against this table. You can define indexes based on your typical queries.
Then TRUNCATE and repopulate (and ANALYZE) as necessary.

On the other hand, if you can put the CTE in separate stored procedures rather than a view, you can easily put your conditions in the CTE part rather then the final SELECT (which is basically what you do querying against tree_view_1), so that much less rows will be involved in the recursion. From the query plan it looks like that PostgreSQL estimates row numbers based on some far-from-true assumptions, probably producing suboptimal plans - this effect can be reduced somewhat with the SP solution.

EDIT I may miss something, but just noticed that in the non-recursive term you don't filter the rows. Possibly you want to include only root nodes there (WHERE parent_id IS NULL) - I'd expect much less rows and recursions this way.

EDIT 2 AS it slowly became clear for me from the comments, I misthought the recursion in the original question going the other way. Here I mean starting from the root nodes and going deeper in the recursion.

PostgreSQL COPY Command – Mapping CSV Columns to Fields

It is absolutely possible - the ever helpful documentation comes to the rescue, again:

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]

Which means you can do something like this:

COPY my_table (mt_id, mt_name, mt_created_by, ...)
    FROM 'filename' [...]

What you cannot do is to refer columns of the CSV file. To overcome this, one can create an intermediate table with the matching number and type of columns, do the COPY into it, then do an INSERT ... SELECT ... to the final destination. Based on an important remark from Patrick7, the intermediate table can be defined as UNLOGGED, saving a lot of WAL overhead when the table is big.

Best Answer

Related Solutions

PostgreSQL tree structure and recursive CTE optimization

PostgreSQL COPY Command – Mapping CSV Columns to Fields

Related Question