Home>

For postgresql beginners, thanks for your teaching.

I want to get the data with postgresql and output it in csv format, but the space data is lost.

Assuming that the table has only one column
Column definition is character (4) not null,
The data stored in the column is""(4 single-byte spaces).

The result obtained with the following sql statement is different from the expected result.

select
'0000'││','││ Column name ││','││'1111'
from
Table name

The above sql result is as follows.
0000,, 1111

The expected acquisition results are as follows.
0000,, 1111

Change the column name of sql to''(4 spaces)
When I executed the query,
The result was as expected.

I can't consider what this difference is ...

Thanks for your teaching.

Verification environment
windows7
postgresql 9.6.8
pgAdmin4

  • Answer # 1

    When combined with

    ||, it is converted to text type, and white space is removed at that time.
    So it is necessary to make it text type before joining.
    However, it is not possible to simply cast, so use pad ().

    select '0000' || ',' || rpad (column name, 4) || ',' || '1111'
    from table name


    By the way, is it necessary to use a fixed length even though it is in CSV format?

  • Answer # 2

    '0000'││',"'││ Column name ││'",'││'1111'

  • Answer # 3

    < code data-language = "SQL">select
    '"' || '0000' ││ '", "' ││ Column name ││ '", "' ││'1111 '||'" '
    from
    Table name


    If you stick all the columns with double quotations,
    There will be no missing characters.

    select
    '"' || '0000' ││ '", "' ││ COALESCE (column name, '') ││ '", "' ││'1111 '||'" '
    from
    Table name


    For example, if the column name is NULL, the space is filled.