0

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";
3

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.

  • @horse, tried the following command. But didn't work 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"; ERROR: syntax error at or near "to" LINE 1: ...mytable order by column1 asc limit 1 to '/path... – Spike Oct 1 '15 at 21:20
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.