PostgreSQL equivalent query which has order by clause alone

Most of what these pragmas do for SQLite can be best accomplished by using an UNLOGGED table or a TEMPORARY table.

This also makes it very obvious that these are ephemeral tables, that aren't crash or restart safe. It also highlights that there are very real tradeoffs for blazing speed versus data durability.

Going off of the PRAGMA documentation for SQLite, I've attempted to translate them into equivalent PostgreSQL GUC variables.

pragma synchronous = OFF; is roughly equvalent to setting synchronous_commit off. If you're using a TEMPORARY or UNLOGGED table, this setting will have no real effect because those types of tables aren't actually written into the WAL anyway.

pragma journal_mode = OFF; shuts off the WAL, in PostgreSQL you can only do that with UNLOGGED or TEMPORARY tables.

pragma temp_store = MEMORY; temporary tables in PostgreSQL can write to disk, but setting temp_buffers should let you keep more of the tables in memory for better performance.

pragma count_changes = OFF; is deprecated in SQLite, and doesn't really make sense in PostgreSQL.

Robert Haas has a great blog posting about Temporary and Unlogged Tables.

Whatever you do, be sure to not put any of your tablespaces in RAM. The dangers are spelled out in the following two blog posts.

PostgreSQL no tablespaces on ramdisks

Cannot recover from the loss of a tablespace

If any of the columns is null the result of the concatenation using || is also null.

You can use concat() or even concat_ws() ("concat With Separator") to make Postgres treat null values like empty strings:

select concat_ws('||', column2, column3, column4)
from tablename 
order by column1 asc;

Note that concat_ws() will not append the separator if a value is "missing". If you do need it, you have to use concat()

select concat(column2, '||', column3, '||', column4)
from tablename 
order by column1 asc;

Details in the manual:

9.4. String Functions and Operators

Regarding the last part of your question, you can use a select statement for the \copy command. For details please see the manual.