Thread: Re: deleting an identical record
> In a table I entered by accident two times the same record, if I now > list the table I see two lines with exactly the same contents. How can > I delete one of those two records? I can't find a select criterium which > differs for both. > create table t (a int, b text); insert into t values (1, 'a'); insert into t values (1, 'a'); insert into t values (1, 'a'); insert into t values (1, 'a'); select oid, * from t;
On Thu, 2002-02-21 at 03:44, Lee Harr wrote: > > In a table I entered by accident two times the same record, if I now > > list the table I see two lines with exactly the same contents. How can > > I delete one of those two records? I can't find a select criterium which > > differs for both. > > > > > create table t (a int, b text); > > insert into t values (1, 'a'); > insert into t values (1, 'a'); > insert into t values (1, 'a'); > insert into t values (1, 'a'); > > select oid, * from t; Aren't the oids different? DELETE FROM t WHERE oid <> xxx -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "My sheep hear my voice, and I know them, and they follow me; And I give unto them eternal life; and they shall never perish, neither shall any man pluck them out of my hand." John 10:27,28
Oliver Elphick wrote: > > On Thu, 2002-02-21 at 03:44, Lee Harr wrote: > > > In a table I entered by accident two times the same record, if I now > > > list the table I see two lines with exactly the same contents. How can > > > I delete one of those two records? I can't find a select criterium which > > > differs for both. > > > > > > > > > create table t (a int, b text); > > > > insert into t values (1, 'a'); > > insert into t values (1, 'a'); > > insert into t values (1, 'a'); > > insert into t values (1, 'a'); > > > > select oid, * from t; > > Aren't the oids different? > > DELETE FROM t WHERE oid <> xxx > OUCH !!! There will be only one row left in the table. Be carefull.
On Thu, 2002-02-21 at 09:25, Jean-Luc Lachance wrote: > Oliver Elphick wrote: > > > create table t (a int, b text); > > > > > > insert into t values (1, 'a'); > > > insert into t values (1, 'a'); > > > insert into t values (1, 'a'); > > > insert into t values (1, 'a'); > > > > > > select oid, * from t; > > > > Aren't the oids different? > > > > DELETE FROM t WHERE oid <> xxx > > > OUCH !!! There will be only one row left in the table. Be carefull. True. But in my defence, that is valid in the context of this particular example. (Nevertheless, I was right only by accident and your warning is very necessary.) -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "My sheep hear my voice, and I know them, and they follow me; And I give unto them eternal life; and they shall never perish, neither shall any man pluck them out of my hand." John 10:27,28
Lee Harr wrote: > > In a table I entered by accident two times the same record, if I now > > list the table I see two lines with exactly the same contents. How can > > I delete one of those two records? I can't find a select criterium which > > differs for both. > > > > create table t (a int, b text); > > insert into t values (1, 'a'); > insert into t values (1, 'a'); > insert into t values (1, 'a'); > insert into t values (1, 'a'); > > select oid, * from t; thanks, this works indeed, what's the meaning of the comma behind oid?
Hugo Coolens <hugo.coolens@skynet.be> writes: > Lee Harr wrote: > > > select oid, * from t; > > thanks, this works indeed, what's the meaning of the comma behind oid? If you want oid as a result column in a select, you need to specify it explicitly--it's not part of '*'. -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863
>> create table t (a int, b text); >> >> insert into t values (1, 'a'); >> insert into t values (1, 'a'); >> insert into t values (1, 'a'); >> insert into t values (1, 'a'); >> >> select oid, * from t; > > thanks, this works indeed, what's the meaning of the comma behind oid? > select a, b, sqrt(a::numeric), 'a: '||a::text||' B: '||b::text as myfield from t; SELECT chooses fields from a table. When you select *, you choose all fields (except for oid...). You can choose functions and operate on fields too. The ::type notation is to coerce the value in to the right type for the particular function or operator. So. The comma means "these are separate items in a list of fields."