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

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(dot)Farber(at)gmail(dot)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(at)iol(dot)ie