select query not returning expected result

Question

I have a table which is having 30 columns. And I have executed the below query (using postgres 9.1, executed via pgAdmin)

select column2 || '|' || column3 || '|' || column4 
from tablename 
order by column1 asc

I am getting 5 rows with empty result. and I noticed that column3 having values as 'Data1|Data2' while I execute select * from table name.

Any idea?

Table structure is simple. All are character varying except column1 (numeric) and data having simple texts except column3. Column3 having data with delimer ("|). i.e., 12345|23333

How can I achieve the same in this?

psql -d mydb-U user1 -c "\copy tablename to 
    '/home/user/table_data.csv' delimiter '|' csv header";

Answer 1

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.

Answer 2

Regarding the second part of your question, and your comment on a_horse_with_no_name's answer, try:

psql -d mydb -U user -c 
    "select concat(column2, '|', column3, '|' , column4) 
    from mytable order by column1 asc limit 1" 
    -to '/path/test/range1.csv' -delimiter '|' -csv header

(line breaks added for readability)

If that doesn't work just do:

psql -d mydb -U user -c 
    "select concat(column2, '|', column3, '|' , column4) 
    from mytable order by column1 asc limit 1"

Does the output look like you intend? If so, do it again, but use an i/o redirect:

psql -d mydb -U user -c 
    "select concat(column2, '|', column3, '|' , column4) 
    from mytable order by column1 asc limit 1" > /path/test/range1.csv

...assuming /path/test/ exists as a path.

Also, did you intend to do something like this?

psql -d mydb -U user -c 
    "select column2, column3, column4 
    from mytable order by column1 asc limit 1" 
    -to '/path/test/range1.csv' -delimiter '|' -csv header