Обсуждение: Populating a Table
Hello, I'm sure I'll be flagged as a moron for asking this question, so be it. I have some tables of info with no primary keys. As a result the field I want to key on is repeated. My plan is to do aDISTINCT select from the un-keyed table into another table that does have constraints. Here is what I thought would work: INSERT INTO table2 SELECT DISTINCT(key), name, field1, field2 FROM table1; When I issue this statement it tells me that you cannot put in duplicate values into my key field. Why does it do that? If I issue the following statement I get exactly what I want: SELECT DISTINCT(phone) from table1; Am I using the DISTINCT function incorrectly? Is it taking all of the field and using them to create a distinct row? Any help would be appreciated. Chris.
try INSERT INTO table2 SELECT DISTINCT ON key key, name, field1, field2 FROM table1; sherwin Chris@pcguru.com writes: >Hello, > >I'm sure I'll be flagged as a moron for asking this question, so be it. > >I have some tables of info with no primary keys. As a result the field I >want to key on is repeated. My plan is to do a DISTINCT select from the >un-keyed table into another table that does have constraints. > >Here is what I thought would work: > >INSERT INTO table2 > SELECT DISTINCT(key), name, field1, field2 > FROM table1; > >When I issue this statement it tells me that you cannot put in duplicate >values into my key field. Why does it do that? If I issue the following >statement I get exactly what I want: > >SELECT DISTINCT(phone) from table1; > >Am I using the DISTINCT function incorrectly? Is it taking all of the >field and using them to create a distinct row? > >Any help would be appreciated. > >Chris. >
Hey Sherwin, You da man! Your statement worked great, I just had to put parentheses around key: i.e. DISTINCT ON (key) Thank you very much. Chris. >>> "pgsql-admin" <pgsql-admin@fc.emc.com.ph> 10/29/00 09:42PM >>> try INSERT INTO table2 SELECT DISTINCT ON key key, name, field1, field2 FROM table1; sherwin Chris@pcguru.com writes: >Hello, > >I'm sure I'll be flagged as a moron for asking this question, so be it. > >I have some tables of info with no primary keys. As a result the field I >want to key on is repeated. My plan is to do a DISTINCT select from the >un-keyed table into another table that does have constraints. > >Here is what I thought would work: > >INSERT INTO table2 > SELECT DISTINCT(key), name, field1, field2 > FROM table1; > >When I issue this statement it tells me that you cannot put in duplicate >values into my key field. Why does it do that? If I issue the following >statement I get exactly what I want: > >SELECT DISTINCT(phone) from table1; > >Am I using the DISTINCT function incorrectly? Is it taking all of the >field and using them to create a distinct row? > >Any help would be appreciated. > >Chris. >