Postgres Pro
Downloads
Home   >   mailing lists

Thread: Two questions in a row

Two questions in a row

From
Renato Moutinho
Date:
Hello people,

  I have two questions I would like to know if
anyone can help me with:

1. How do you remove one of two identical rows
from a pgsql table ?

2. How can I restrict connection to a certain
database (locally) on a user basis ? I mean,
I want to permit connection to mydb1 from user
user1 only and connection to mydb2 from user
user2 only..

P.S.: Excuse me for the newbiness on any of
these 2 questions.. :-)


Thanks in advance,

--
Renato Moutinho Silva


__________________________________________________
Do You Yahoo!?
Send instant messages & get email alerts with Yahoo! Messenger.
http://im.yahoo.com/

Re: Two questions in a row

From
Gilles DAROLD
Date:
Renato Moutinho wrote:

> Hello people,
>
>   I have two questions I would like to know if
> anyone can help me with:
>
> 1. How do you remove one of two identical rows
> from a pgsql table ?

DELETE FROM t1 WHERE .... wil do the stuff for you. If you don't know
the value into the duplicate field just export the database with pg_dump
create
a unique index onto this field and reimport all your data. Duplicate
data will
not be inserted.

> 2. How can I restrict connection to a certain
> database (locally) on a user basis ? I mean,
> I want to permit connection to mydb1 from user
> user1 only and connection to mydb2 from user
> user2 only..

See pg_hba.conf into your data directory, all is here...

> P.S.: Excuse me for the newbiness on any of
> these 2 questions.. :-)
>
> Thanks in advance,
>
> --
> Renato Moutinho Silva
>
> __________________________________________________
> Do You Yahoo!?
> Send instant messages & get email alerts with Yahoo! Messenger.
> http://im.yahoo.com/

Re: Two questions in a row

From
Gilles DAROLD
Date:
> > 2. How can I restrict connection to a certain
> > database (locally) on a user basis ? I mean,
> > I want to permit connection to mydb1 from user
> > user1 only and connection to mydb2 from user
> > user2 only..
>
> See pg_hba.conf into your data directory, all is here...

Sorry, use of GRANT will help you more...

Gilles

RE: Two questions in a row

From
"Andrew Snow"
Date:
> > 1. How do you remove one of two identical rows
> > from a pgsql table ?
>
> DELETE FROM t1 WHERE .... wil do the stuff for you. If you don't know
> the value into the duplicate field just export the database with pg_dump
> create
> a unique index onto this field and reimport all your data. Duplicate
> data will
> not be inserted.

Another way could be to
SELECT oid FROM table WHERE ...;

and then delete based on that. However I believe it is possible to have two
rows have the same oid but usually that isn't the case.

Re: Two questions in a row

From
Mike Mascari
Date:
Andrew Snow wrote:
>
> > > 1. How do you remove one of two identical rows
> > > from a pgsql table ?
> >
> > DELETE FROM t1 WHERE .... wil do the stuff for you. If you don't know
> > the value into the duplicate field just export the database with pg_dump
> > create
> > a unique index onto this field and reimport all your data. Duplicate
> > data will
> > not be inserted.
>
> Another way could be to
> SELECT oid FROM table WHERE ...;
>
> and then delete based on that. However I believe it is possible to have two
> rows have the same oid but usually that isn't the case.

Its not possible to have two rows with the same oid (unless
you've wrapped around the maximum oid by creating at least 4.2
billion records). So you can eliminate duplicates with the query:

DELETE FROM foo WHERE EXISTS
(SELECT f.key FROM foo f WHERE f.key = foo.key AND f.oid <
foo.oid);

Of course, an index on foo.key will dramatically speed up the
above operation.

Hope that helps,

Mike Mascari