473,321 Members | 1,667 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

How to replace characters in dblink

Hi, I wanted to copy a data from table1 in db1 to table2 in db2, which has the same structure, with the PosgreSQL dblink funtion.

The fields for both the tables are: id, first_name, last_name.

However, I would like to replace the id and first_name in table1 to: 3 and 'nil' to insert into table2.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO table2 SELECT * FROM dblink('dbname=db1', 'SELECT 3, nil, last_name FROM table1 WHERE id = 1000') AS t1(id integer, first_name varchar, last_name varchar); 
The id can be replaced but it thought "nil" is a column name, instead of the value that I wanted to replace the first_name with, thus return error.

Anyone knows how to replace characters in dblink or any solution to this problem??
Thanks in advance!
Jun 21 '07 #1
3 4905
michaelb
534 Expert 512MB
If you want to insert a literal string nil you need to enclose it in single quotes: 'nil'
If what you have in mind is a null value, then try NULL instead of nil.
Jun 22 '07 #2
If you want to insert a literal string nil you need to enclose it in single quotes: 'nil'
If what you have in mind is a null value, then try NULL instead of nil.
I had tried to insert single quotes for 'nil' but it confused the dblink function as the dblink function is using single quotes for its parameters.

I had just found the solution seconds ago. I do the replace outside the dblink instead. I should have think of this earlier.

(I don't put NULL because all the fields are NOT NULL fields).

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO table2 SELECT 3, 'nil', last_name FROM dblink('dbname=db1', 'SELECT id, first_name, last_name FROM table1 WHERE id = 1000') AS t1(id integer, first_name varchar, last_name varchar);
Anyway, thanks for taking your time to reply, michaelb. =)
Jun 22 '07 #3
michaelb
534 Expert 512MB
Sorry, I did not pay much attention to the context, it'll teach me something...

A typical way of escaping quote is doubling it, just like you use '' instead of ' in the body of function (unless you use the new syntax with $$).
I am not positive this would work in your case, but it's worth trying.
Thank you for update!
Jun 22 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: WindAndWaves | last post by:
Hi Gurus I am a novice - just so you know. is there a smarter way to write: $keywords = str_replace("'", "",$keywords); $keywords = str_replace('"', '',$keywords); $keywords =...
3
by: JPSutor | last post by:
I want to be able to have a user type in a password to a text box and 1.) Capture the characters entered 2.) Replace typed chars with "*"
1
by: Satish Appasani | last post by:
I have a drop down field on a form. When binding data to the DataValueField property of the drop down, I need to replace "&" with "%". In code behind I tried DataValueField='<%#...
1
by: Dave | last post by:
I get XML data from a data that sometimes has html tags embedded around text content (<P>, <PRE>, <B> etc.). I want to strip out all of their HTML formatting such as the <PRE> tags. How can I do...
1
by: Derek Hart | last post by:
Using VB.NET, I wish to open a text file that will have paragraph marks throughout it. To replace them, I can probably use the Replace command and replace vbcrlf. But can somebody give me sample...
4
by: jpierson | last post by:
Hi, I'm having a few problems with the replace function for replacing characters in a textbox. "C:\" is the string i am tryin to remove ,with it a command I am sending does not work. ...
4
by: cheesywillie | last post by:
How would this be done? For example find every "a" and replace them with a "b". Sorry if i am not being descriptive enough, but i don't know how else to phrase the question. If you need any...
2
by: stelegraph | last post by:
hi! i want to replace these characters: sex_m-designer_bw-size_42 so that i have the argument string: sex=m&designer=bw&size=42 i am working under zope 3. glad for any help you can give...
4
by: chriskaza81 | last post by:
hello i am very new in xsl ,xml so ....forgive me if i make spuped questions.. well i have the string e.g. <script> blabla bla </script> i want to replace in xsl the ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.