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.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