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 (AT) retsol (DOT) co.uk> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

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/s...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:
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

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/s...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:


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
-----+-----------+-----------+-------------+-------------+--------+------------+------------+-----------+--------------------+---------------------+------------
--------+---------------------+--------------------+---------------------+----------------------+-----------------------+-----------+--------------+--------------+-
------------------+-----------+------------
(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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Tue, 2004-08-03 at 13:15, mike wrote:
....

....[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.

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 (AT) lfix (DOT) 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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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 (AT) lfix (DOT) co.uk>:






---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)