By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,333 Members | 1,962 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,333 IT Pros & Developers. It's quick & easy.

How to replace characters in dblink

P: 6
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
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 534
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

P: 6
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

Expert 100+
P: 534
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

Post your reply

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