Thread: Copying data from one table to another - how to specify fields?
Hello, I'm using CentOS 5.5 with PostgreSQL 8.4.7 and am migrating my site from phpBB 3 to Drupal 7. I would like to copy these fields from one table: select user_id, username, user_email, user_regdate, user_lastvisit from phpbb_users where user_id > 50; into the other (already existing) table: select uid, name, mail, created, access from drupal_users; I've read http://www.postgresql.org/docs/8.4/static/sql-selectinto.html and few more docs but don't understand, how could I specify the fields correspondence (i.e. phpbb_users.user_id -> drupal_users.uid) Also the target table exists already, has some records already and has more fields than specified in my select statement above. Thank you Alex
On Wed, Mar 09, 2011 at 02:47:46PM +0100, Alexander Farber wrote:
> I would like to copy these fields from one table:
>
> select user_id, username, user_email, user_regdate, user_lastvisit
> from phpbb_users where user_id > 50;
>
> into the other (already existing) table:
>
> select uid, name, mail, created, access from drupal_users;
>
INSERT INTO drupal users (uid, name, mail, created, access)
SELECT user_id, username, user_email, user_regdate, user_lastvist
FROM phpbb_users where user_id > 50;
This is sort of like an example on
http://www.postgresql.org/docs/9.0/interactive/sql-insert.html, but
that example uses SELCT *. Perhaps an additional example would have
helped? (This is basic SQL, though, and I'm not sure the keyword
manual is the best place for such an example.)
A
--
Andrew Sullivan
ajs@crankycanuck.ca
On Mar 9, 2011, at 08:47 , Alexander Farber wrote:
> Hello,
>
> I'm using CentOS 5.5 with PostgreSQL 8.4.7 and
> am migrating my site from phpBB 3 to Drupal 7.
>
> I would like to copy these fields from one table:
>
> select user_id, username, user_email, user_regdate, user_lastvisit
> from phpbb_users where user_id > 50;
>
> into the other (already existing) table:
>
> select uid, name, mail, created, access from drupal_users;
INSERT INTO drupal_users (uid, name, mail, created, access)
SELECT user_id, username, user_email, user_regdate, user_lastvisit
FROM phpbb_users
WHERE user_id > 50;
<http://www.postgresql.org/docs/9.0/interactive/sql-insert.html>
Michael Glaesemann
michael.glaesemann@myyearbook.com
Oh it is called "INSERT INTO", thank you!
I still have a problem though:
# select uid, name, mail, created, access from drupal_users;
uid | name | mail | created | access
-----+------+----------------------------+------------+------------
0 | | | 0 | 0
1 | Alex | Alexander.Farber@gmail.com | 1299512207 | 1299751991
(2 rows)
# INSERT INTO drupal_users (uid, name, mail, created, access)
SELECT user_id, username, user_email, user_regdate, user_lastvisit
FROM phpbb_users
WHERE user_id > 50 and length(username) > 0;
ERROR: duplicate key value violates unique constraint "drupal_users_name_key"
I don't understand, what is wrong with "name" here and
how to find the troublemaking record in my 4700 lines table
# \d+ drupal_users_name_key;
Index "public.drupal_users_name_key"
Column | Type | Storage | Description
--------+-----------------------+----------+-------------
name | character varying(60) | extended |
unique, btree, for table "public.drupal_users"
Using PostgreSQL 8.4.7
Thank you
Alex
On 09/03/2011 14:21, Alexander Farber wrote:
> Oh it is called "INSERT INTO", thank you!
>
> I still have a problem though:
>
> # select uid, name, mail, created, access from drupal_users;
> uid | name | mail | created | access
> -----+------+----------------------------+------------+------------
> 0 | | | 0 | 0
> 1 | Alex | Alexander.Farber@gmail.com | 1299512207 | 1299751991
> (2 rows)
>
> # INSERT INTO drupal_users (uid, name, mail, created, access)
> SELECT user_id, username, user_email, user_regdate, user_lastvisit
> FROM phpbb_users
> WHERE user_id> 50 and length(username)> 0;
> ERROR: duplicate key value violates unique constraint "drupal_users_name_key"
>
> I don't understand, what is wrong with "name" here and
> how to find the troublemaking record in my 4700 lines table
Is "drupal_users" a table you created yourself? In a vanilla
installation of Drupal, the users table is called just "users".
Anyway, to answer your question, you're trying to insert into
drupal_users a value that already exists there, and which is subject to
a constraint which allows only one instance of that value. To find the
offending value you could do something like this (not tested):
select * from phpbb_users where exists
(select 1 from drupal_users
where drupal_users.name = phpbb_users.name);
Hope this helps,
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
Hello Raymond and others, thank you for looking at my problem! It is a core Drupal 7 table, with a prefix "drupal_" (you can set it while installing Drupal 7 in adv. options). I have only 2 records in that target table: # select uid, name, mail, created, access from drupal_users; uid | name | mail | created | access -----+------+----------------------------+------------+------------ 0 | | | 0 | 0 1 | Alex | Alexander.Farber@gmail.com | 1299512207 | 1299753672 (2 rows) I've tried you suggestion and the conflicting record is 'Alex': # select username, user_id from phpbb_users where exists (select 1 from drupal_users where drupal_users.name = phpbb_users.username); username | user_id ----------+--------- Alex | 2 (1 row) But isn't that record excluded by the conditions below? # INSERT INTO drupal_users (uid, name, mail, created, access) SELECT user_id, username, user_email, user_regdate, user_lastvisit FROM phpbb_users WHERE user_id > 50 and length(username) > 0 and username <> 'Alex'; ERROR: duplicate key value violates unique constraint "drupal_users_name_key"
I've also tried renaming 'Alex' to a temp. value: # update drupal_users set name='Alex_1972' where name='Alex'; UPDATE 1 # INSERT INTO drupal_users (uid, name, mail, created, access) SELECT user_id, username, user_email, user_regdate, user_lastvisit FROM phpbb_users WHERE user_id > 50 and length(username) > 0 and username <> 'Alex'; ERROR: duplicate key value violates unique constraint "drupal_users_name_key" # select username, user_id from phpbb_users where exists (select 1 from drupal_users where drupal_users.name = phpbb_users.username); username | user_id ----------+--------- (0 rows) so there must be a duplicated username in the source table phpbb_users, but how could I find that record? Thank you Alex
Oh ok, found it: # select one.username, one.user_id, two.user_id from phpbb_users one, phpbb_users two where two.username=one.username and two.user_id <> one.user_id; username | user_id | user_id ----------+---------+--------- Вячеслав | 7564 | 421 Вячеслав | 421 | 7564 (2 rows) Sorry!
2 Possibilities (assuming there is a single record with name == 'Alex' in the drupal_users table; not counting uid 0) 1. There is a record with username = 'Alex' in the phpbb_users table 2. Username is not UNIQUE within phpbb_users Write a select statement to extract username from phpbb_user for BOTH these conditions. Decide how you want to modify those records so that can be imported into drupal_users. David J. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alexander Farber Sent: Wednesday, March 09, 2011 9:21 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Copying data from one table to another - how to specify fields? Oh it is called "INSERT INTO", thank you! I still have a problem though: # select uid, name, mail, created, access from drupal_users; uid | name | mail | created | access -----+------+----------------------------+------------+------------ 0 | | | 0 | 0 1 | Alex | Alexander.Farber@gmail.com | 1299512207 | 1299751991 (2 rows) # INSERT INTO drupal_users (uid, name, mail, created, access) SELECT user_id, username, user_email, user_regdate, user_lastvisit FROM phpbb_users WHERE user_id > 50 and length(username) > 0; ERROR: duplicate key value violates unique constraint "drupal_users_name_key" I don't understand, what is wrong with "name" here and how to find the troublemaking record in my 4700 lines table
SELECT username, count(username) FROM phpbb_users GROUP BY username HAVING count(username) > 1; If anything shows up then (phpbb_users .username) is not a unique field but you are trying to insert it into one that is (drupal_users.uid) -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alexander Farber Sent: Wednesday, March 09, 2011 9:48 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Copying data from one table to another - how to specify fields? Hello Raymond and others, thank you for looking at my problem! It is a core Drupal 7 table, with a prefix "drupal_" (you can set it while installing Drupal 7 in adv. options). I have only 2 records in that target table: # select uid, name, mail, created, access from drupal_users; uid | name | mail | created | access -----+------+----------------------------+------------+------------ 0 | | | 0 | 0 1 | Alex | Alexander.Farber@gmail.com | 1299512207 | 1299753672 (2 rows) I've tried you suggestion and the conflicting record is 'Alex': # select username, user_id from phpbb_users where exists (select 1 from drupal_users where drupal_users.name = phpbb_users.username); username | user_id ----------+--------- Alex | 2 (1 row) But isn't that record excluded by the conditions below? # INSERT INTO drupal_users (uid, name, mail, created, access) SELECT user_id, username, user_email, user_regdate, user_lastvisit FROM phpbb_users WHERE user_id > 50 and length(username) > 0 and username <> 'Alex'; ERROR: duplicate key value violates unique constraint "drupal_users_name_key" -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Thank you all for the replies! On Wed, Mar 9, 2011 at 7:01 PM, David Johnston <polobo@yahoo.com> wrote: > SELECT username, count(username) FROM phpbb_users > GROUP BY username > HAVING count(username) > 1; > > If anything shows up then (phpbb_users .username) is not a unique field but > you are trying to insert it into one that is (drupal_users.uid) >