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.
'0000'││','││ Column name ││','││'1111'
The above sql result is as follows.
The expected acquisition results are as follows.
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.
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.
- postgresql - group by aggregating with half-width space removed
- date format mysql postgresql
- fedora 23 lvm2 format insufficient root disk space expansion method
- ruby - rails "+" is converted to half-width space
- arraylist - about the problem that a half-width space is created in the output array
- cannot process csv file with half-width space in file name in windows bat file
- when connecting to postgresql, half-width alphanumeric characters cannot be entered
- postgresql - creating sql to format iot data
- for postgresql table built in rds, csv import from pgadmin is not possible
- postgresql - table properties cannot be referenced in pgadmin4
- about postgresql version of pgadmin4
- postgresql - [pgadmin 4] user password cannot be changed
- varchar cannot be specified when creating a postgresql (pgadmin4) table
- postgresql - behavior after selecting querytool in pgadmin (46)
- postgresql - i think that there is a character string readmemd in the database "dummy" where postgres is running
- postgresql - sequence is not displayed in sequences of pgadmin4
- postgresql - collation order and character classification of postgre10 database created with pgadmin4
- postgresql - [django/postgres] is it a specification that extra _id is added to the column name when a foreign key is set to mod