Copying data from one table to another - how to specify fields?

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&gt;

Michael Glaesemann
michael.glaesemann@myyearbook.com

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

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!