Обсуждение: Significant trailing spaces

PostGreSql 7.4.3

I noticed that there was a change in the way that 7.4 handled trailing spaces in CHAR/VARCHARS,
so upgraded from 7.2.4,
I still seem to get the same problem - putting a CHAR(250) variable (with trailing spaces) into a VARCHAR(100) gives me a VARCHAR with a length 100 - ie padded with spaces. Is this still correct?

Have I misunderstood, and are trailing spaces handled in the same way in both 7.2.4 and 7.4.3 (or is there some flag I need to set to get the new functionality?)

Regards, Steve Tucknott ReTSol Ltd DDI: 01903 828769

Steve Tucknott <steve@retsol.co.uk> writes:
> I still seem to get the same problem - putting a CHAR(250) variable
> (with trailing spaces) into a VARCHAR(100) gives me a VARCHAR with a
> length 100 - ie padded with spaces. Is this still correct?

This is per SQL specification.  SQL92 section 9.2 "Store assignment"
defines assignment to a varchar thusly:

    [ T is the target variable, V is the value being assigned ]

            d) If the data type of T is variable-length character string
              and the length in characters M of V is not greater than the
              maximum length in characters of T, then the value of T is set
              to V and the length in characters of T is set to M.

            e) If the data type of T is variable-length character string and
              the length in characters M of V is greater than the maximum
              length in characters L of T, then,

              Case:

              i) If the rightmost M-L characters of V are all <space>s, then
                 the value of T is set to the first L characters of V and
                 the length in characters of T is set to L.

             ii) If one or more of the rightmost M-L characters of V are
                 not <space>s, then an exception condition is raised: data
                 exception-string data, right truncation.

If you don't want the spaces, consider using the trim() or rtrim()
function.

            regards, tom lane

The only thing I see mentioned in the docs is that prior to 7.2:

insert char( 250 ) into a column of char ( 100 ) would succeed without
generating an error or warning that 150 characters were chopped off.

In 7.2: insert char( 250 ) into a column of char ( 100 ) crashes with an
error.

        insert char (250) into a CAST(column as char (100)) succeeds and
silently chops off the last 150 characters.

The 150 characters chopped could be any type of character data (trailing
space etc.).

I am looking at
http://www.postgresql.org/docs/7.4/static/datatype-character.html.

If you are looking at something different please post a link or copy and
paste if it is not long.

Mike

On Mon, 2004-08-02 at 09:08, Steve Tucknott wrote:
> PostGreSql 7.4.3
>
> I noticed that there was a change in the way that 7.4 handled trailing
> spaces in CHAR/VARCHARS,
> so upgraded from 7.2.4,
> I still seem to get the same problem - putting a CHAR(250) variable
> (with trailing spaces) into a VARCHAR(100) gives me a VARCHAR with a
> length 100 - ie padded with spaces. Is this still correct?
>
> Have I misunderstood, and are trailing spaces handled in the same way
> in both 7.2.4 and 7.4.3 (or is there some flag I need to set to get
> the new functionality?)
>
>
> Regards,
>
> Steve Tucknott
>
> ReTSol Ltd
>
> DDI: 01903 828769

Mike,
This is from the 7.4 release notes - in my install the file is called HISTORY and is located directly under the postgre directory (not to be confused with the other history file that gives the BERKELEY background info)

.....
     * Trailing spaces are now trimmed when converting from type char(n)
       to varchar(n) or text. This is what most people always expected to
       happen anyway.
...

The passage is at line 372, under the 'Migration to version 7.4' heading.


On Tue, 2004-08-03 at 04:47, mike g wrote:

The only thing I see mentioned in the docs is that prior to 7.2:

insert char( 250 ) into a column of char ( 100 ) would succeed without
generating an error or warning that 150 characters were chopped off.

In 7.2: insert char( 250 ) into a column of char ( 100 ) crashes with an
error.

        insert char (250) into a CAST(column as char (100)) succeeds and
silently chops off the last 150 characters.

The 150 characters chopped could be any type of character data (trailing
space etc.).

I am looking at
http://www.postgresql.org/docs/7.4/static/datatype-character.html.

If you are looking at something different please post a link or copy and
paste if it is not long.

Mike

On Mon, 2004-08-02 at 09:08, Steve Tucknott wrote:
> PostGreSql 7.4.3 
> 
> I noticed that there was a change in the way that 7.4 handled trailing
> spaces in CHAR/VARCHARS, 
> so upgraded from 7.2.4, 
> I still seem to get the same problem - putting a CHAR(250) variable
> (with trailing spaces) into a VARCHAR(100) gives me a VARCHAR with a
> length 100 - ie padded with spaces. Is this still correct? 
> 
> Have I misunderstood, and are trailing spaces handled in the same way
> in both 7.2.4 and 7.4.3 (or is there some flag I need to set to get
> the new functionality?) 
> 
> 
> Regards,
> 
> Steve Tucknott
> 
> ReTSol Ltd
> 
> DDI: 01903 828769

Regards, Steve Tucknott ReTSol Ltd DDI: 01903 828769

I am getting started with Postgres and cannot copy a database that was
converted to a .csv file into my postgres database titled TEST_DB.

TEST_DB=> \d
        List of relations
 Schema |  Name   | Type  | Owner
--------+---------+-------+-------
 public | matters | table | floog
(1 row)

I have completed the copy command trying to move data from the .csv file
into TEST_DB like so:

COPY matters FROM /home/floog/TEST_DB.csv;

But I don't think the data transferred properly because when I try to
look at any column or row I get the following:

TEST_DB=> select * from matters;
 client_1_lastname | client_1_firstname | client_1_address |
client_1_phone | client_2_lastname | client_2_firstname |
client_2_address |
client_2_phone | carrier_name | claim_rep | claim_num | responsible |
supervisory | second | client_num | matter_num | file_name |
adverse_1_lastname | adverse_1_firstname | adverse_2_lastname |
adverse_2_firstname | adverse_3_lastname | adverse_3_firstname |
other_party_lastname | other_party_firstname | case_type | billing_type
| date_of_loss | judicial_district | date_open | date_close

-------------------+--------------------+------------------+----------------+-------------------+--------------------+------------------+----------------+---------

-----+-----------+-----------+-------------+-------------+--------+------------+------------+-----------+--------------------+---------------------+------------

--------+---------------------+--------------------+---------------------+----------------------+-----------------------+-----------+--------------+--------------+-
------------------+-----------+------------
(0 rows)

I can open up and view the .csv file using an OpenOffice spreadsheet.
I'm thinking maybe I have to copy one column at a time from the .csv
file to the postgres database.
How do I copy something like column A, B, C, etc. from the .csv file to
TEST_DB client_1_lastname | client_1_firstname | client_1_address | etc.
etc.

Thank you for your time and patience.

Mike

On Tue, 2004-08-03 at 13:15, mike wrote:
> I am getting started with Postgres and cannot copy a database that was
> converted to a .csv file into my postgres database titled TEST_DB.

...

> COPY matters FROM /home/floog/TEST_DB.csv;
>
> But I don't think the data transferred properly because when I try to
> look at any column or row I get the following:

...[empty table]...

If there were rows in the input and COPY produced an empty table, there
was presumably an error message.  It would be helpful to list that in
your problem report.

> I can open up and view the .csv file using an OpenOffice spreadsheet.
> I'm thinking maybe I have to copy one column at a time from the .csv
> file to the postgres database.
> How do I copy something like column A, B, C, etc. from the .csv file to
> TEST_DB client_1_lastname | client_1_firstname | client_1_address | etc.
> etc.

If the file is comma-separated with quoted values, you need to get rid
of the quotes and change the commas to tabs (in case there are commas in
the data).  You can do this in OpenOffice by setting the parameters
correctly when you save a csv file.  If you wish, you can choose a
different delimiter, so long as it does not appear in the data.  The
fields in the csv file must appear in the same order as the columns are
listed in the database by "\d matters", without omitting any columns, or
else you must specify a field list.  The filename must be in single
quotes.

  COPY matters [(field1, field2,...)] FROM '/home/floog/TEST_DB.csv'
[DELIMITER 'x'];

(You need the DELIMITER clause if you don't use tab as a delimiter.)

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
     "Love is patient, love is kind. It does not envy, it
      does not boast, it is not proud. It is not rude, it is
      not self seeking, it is not easily angered, it keeps
      no record of wrongs. Love does not delight in evil but
      rejoices with the truth. It always protects, always
      trusts, always hopes, always perseveres."
                                I Corinthians 13:4-7

Oliver,

Thank you for your quick reply.
I appreciate the guidance.
I'll try your suggestion and also do some more reading in the docs.
about delimiters.

Mike


Quoting Oliver Elphick <olly@lfix.co.uk>:

> On Tue, 2004-08-03 at 13:15, mike wrote:
> > I am getting started with Postgres and cannot copy a database that
> was
> > converted to a .csv file into my postgres database titled TEST_DB.
>
> ...
>
> > COPY matters FROM /home/floog/TEST_DB.csv;
> >
> > But I don't think the data transferred properly because when I try
> to
> > look at any column or row I get the following:
>
> ...[empty table]...
>
> If there were rows in the input and COPY produced an empty table,
> there
> was presumably an error message.  It would be helpful to list that
> in
> your problem report.
>
> > I can open up and view the .csv file using an OpenOffice
> spreadsheet.
> > I'm thinking maybe I have to copy one column at a time from the
> .csv
> > file to the postgres database.
> > How do I copy something like column A, B, C, etc. from the .csv
> file to
> > TEST_DB client_1_lastname | client_1_firstname | client_1_address |
> etc.
> > etc.
>
> If the file is comma-separated with quoted values, you need to get
> rid
> of the quotes and change the commas to tabs (in case there are commas
> in
> the data).  You can do this in OpenOffice by setting the parameters
> correctly when you save a csv file.  If you wish, you can choose a
> different delimiter, so long as it does not appear in the data.  The
> fields in the csv file must appear in the same order as the columns
> are
> listed in the database by "\d matters", without omitting any columns,
> or
> else you must specify a field list.  The filename must be in single
> quotes.
>
>   COPY matters [(field1, field2,...)] FROM '/home/floog/TEST_DB.csv'
> [DELIMITER 'x'];
>
> (You need the DELIMITER clause if you don't use tab as a delimiter.)
>
> --
> Oliver Elphick
> olly@lfix.co.uk
> Isle of Wight
> http://www.lfix.co.uk/oliver
> GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543
> 10EA
>                  ========================================
>      "Love is patient, love is kind. It does not envy, it
>       does not boast, it is not proud. It is not rude, it is
>       not self seeking, it is not easily angered, it keeps
>       no record of wrongs. Love does not delight in evil but
>       rejoices with the truth. It always protects, always
>       trusts, always hopes, always perseveres."
>                                 I Corinthians 13:4-7
>
>