[SQL] how to preserve \n in select statement
I have a table that has a few text value types, and I enter a bunch of text with '\n' representing a newline. When I select the records from that table, postgresql 7.3 represents those \n as newlines and actually outputs the a newline rather than as a \n as entered. I want to be able to get my \n text out of the select statement in the exact same manner it was inserted. I found a workaround where I can use the copy to command to copy a table to a text file, and the command preserves the \n characters. The problem with that is the copy to command overwrites the output file every time it is written to. That stinks because then I can't run multiple queries and direct the output to a file all at once. (I would prefer to set the output file as \o '/path/to/oufile.txt' and have all queries dump their results there) Is there a way to make the select statement not interpret newline escape characters? Matt Van Mater _ Have fun customizing MSN Messenger learn how here! http://www.msnmessenger-download.com/tracking/reach_customize ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] testing for null value in integer field?
How does one check for an unset value in an integer field? I've tried such things as: select . where intnumber = '' select .. where intnumber = ? select . where intnumber = NULL Thanks. -- Until later, Geoffrey [EMAIL PROTECTED] Building secure systems inspite of Microsoft ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] testing for null value in integer field?
Geoffrey wrote: How does one check for an unset value in an integer field? I've tried such things as: select . where intnumber = '' select .. where intnumber = ? select . where intnumber = NULL Thanks. It is actually WHERE intnumber IS NULL. You don't use operator = to compare with NULLs, you use IS. HTH. Wei ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] testing for null value in integer field?
select . where intnumber IS NULL Geoffrey wrote: How does one check for an unset value in an integer field? I've tried such things as: select . where intnumber = '' select .. where intnumber = ? select . where intnumber = NULL Thanks. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] testing for null value in integer field?
Wei Weng wrote: Geoffrey wrote: How does one check for an unset value in an integer field? I've tried such things as: select . where intnumber = '' select .. where intnumber = ? select . where intnumber = NULL Thanks. It is actually WHERE intnumber IS NULL. You don't use operator = to compare with NULLs, you use IS. Thank you muchly -- Until later, Geoffrey [EMAIL PROTECTED] Building secure systems inspite of Microsoft ---(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
Re: [SQL] how to preserve \n in select statement
I have been entering the data by having a command file that inserts the rows one by one. (yes I know a copy command would probably be faster for importing bulk data, but I prefer to insert each line individually) IE: psql -U myusername mydatabase -f '/path/to/commandfile' Inside the commandfile I have lines like: INSERT INTO tablename (col1, col2, col3) VALUES ($val1, $val2, $val3); How do I escape the data from a select statement? Something like: SELECT \* FROM tablename; ? Original Message Follows From: Guy Fraser <[EMAIL PROTECTED]> To: Matt Van Mater <[EMAIL PROTECTED]> Subject: Re: [SQL] how to preserve \n in select statement Date: Fri, 19 Dec 2003 08:43:59 -0700 What are you using to enter and retrieve the data? Either escape the data before you put it in the database or when you retieve it, whichever gives you the results you are looking for. Matt Van Mater wrote: I have a table that has a few text value types, and I enter a bunch of text with '\n' representing a newline. When I select the records from that table, postgresql 7.3 represents those \n as newlines and actually outputs the a newline rather than as a \n as entered. I want to be able to get my \n text out of the select statement in the exact same manner it was inserted. I found a workaround where I can use the copy to command to copy a table to a text file, and the command preserves the \n characters. The problem with that is the copy to command overwrites the output file every time it is written to. That stinks because then I can't run multiple queries and direct the output to a file all at once. (I would prefer to set the output file as \o '/path/to/oufile.txt' and have all queries dump their results there) Is there a way to make the select statement not interpret newline escape characters? Matt Van Mater _ Check your PC for viruses with the FREE McAfee online computer scan. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] how to preserve \n in select statement
On Fri, Dec 19, 2003 at 10:06:28 -0500, Matt Van Mater <[EMAIL PROTECTED]> wrote: > I have a table that has a few text value types, and I enter a bunch of text > with '\n' representing a newline. When I select the records from that > table, postgresql 7.3 represents those \n as newlines and actually outputs > the a newline rather than as a \n as entered. I want to be able to get my > \n text out of the select statement in the exact same manner it was > inserted. If you really want to store \n so that something else will interpret \n as a newline, then use '\\n' in the string constant. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] how to preserve \n in select statement
I just don't see why pgsql can't return my data exactly as I entered it. It wouldn't be hard for me to manipulate it before entry and add another escape character, but that's not really the point. The point is why is postgresql changing the data I insert into a field, and not giving me away to get it back in its original form? I wouldn't have a problem if I was notified during an insert that my escape characters would be modified, or even if I was given an error message and the insert failed. One complaint about MYSQL is that it often does 'the next best thing' and doesn't notify the user that their command or input has been altered in some way. It seems like this is the same scenario with pgsql. I think this behavior stems from a security problem psql had a while back where escape characters were being interpreted, and this may be another instance of that functionality. Original Message Follows From: Bruno Wolff III <[EMAIL PROTECTED]> To: Matt Van Mater <[EMAIL PROTECTED]> CC: [EMAIL PROTECTED] Subject: Re: [SQL] how to preserve \n in select statement Date: Fri, 19 Dec 2003 15:26:07 -0600 On Fri, Dec 19, 2003 at 10:06:28 -0500, Matt Van Mater <[EMAIL PROTECTED]> wrote: > I have a table that has a few text value types, and I enter a bunch of text > with '\n' representing a newline. When I select the records from that > table, postgresql 7.3 represents those \n as newlines and actually outputs > the a newline rather than as a \n as entered. I want to be able to get my > \n text out of the select statement in the exact same manner it was > inserted. If you really want to store \n so that something else will interpret \n as a newline, then use '\\n' in the string constant. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) _ Tired of slow downloads? Compare online deals from your local high-speed providers now. https://broadband.msn.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] how to preserve \n in select statement
"Matt Van Mater" <[EMAIL PROTECTED]> writes: > I just don't see why pgsql can't return my data exactly as I entered > it. Because you are using an input syntax that requires that quotes and backslashes be escaped. There are other input methods available that don't require this, but they have disadvantages of their own. In particular, you have to separate data from SQL command if you want a no-escape-processing behavior for data. > I think this behavior stems from a security problem psql had a while > back where escape characters were being interpreted, and this may be > another instance of that functionality. Matt, you have no idea what you are talking about. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
