Thread: pqAdmin3

pqAdmin3

From
Philip Pinkerton
Date:
When trying to connect to database via the pgAdmin3 GUI it asks for a
password. I use the same passworrd as I did when I connect to the DB via
command line but I get Ident error?

how do I set, re-set the password so I can use  both the commandline and
pgAdmin to access edit by DB?

Thanks


Importing a tab delimited text file - How?

From
Ken Tozier
Date:
I've been looking though the PostgreSQL documentation but can't seem to
find a command for importing files. I read the documentation related to
large objects but this isn't what I'm looking for as I don't want to
import the entire file into a single field, I want to import it as a
table. I'm sure there's a way to do it but I can't seem to find the
magic command.

Could someone point me to (or provide) an example?

Thanks,

Ken


Re: Importing a tab delimited text file - How?

From
Robby Russell
Date:
On Sun, 2004-10-24 at 19:25 -0400, Ken Tozier wrote:
> I've been looking though the PostgreSQL documentation but can't seem to
> find a command for importing files. I read the documentation related to
> large objects but this isn't what I'm looking for as I don't want to
> import the entire file into a single field, I want to import it as a
> table. I'm sure there's a way to do it but I can't seem to find the
> magic command.
>
> Could someone point me to (or provide) an example?
>
> Thanks,
>

\h COPY

The COPY command will help you with this.

-Robby


--
/***************************************
* Robby Russell | Owner.Developer.Geek
* PLANET ARGON  | www.planetargon.com
* Portland, OR  | robby@planetargon.com
* 503.351.4730  | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
****************************************/


Attachment

Re: Importing a tab delimited text file - How?

From
Doug McNaught
Date:
Ken Tozier <kentozier@comcast.net> writes:

> I've been looking though the PostgreSQL documentation but can't seem
> to find a command for importing files. I read the documentation
> related to large objects but this isn't what I'm looking for as I
> don't want to import the entire file into a single field, I want to
> import it as a table. I'm sure there's a way to do it but I can't seem
> to find the magic command.
>
> Could someone point me to (or provide) an example?

You want the SQL COPY statement, or the \copy command in 'psql'.

-Doug

Re: Importing a tab delimited text file - How?

From
Ken Tozier
Date:
Doug, Robby,

Thanks. That did the trick.

Ken


On Oct 24, 2004, at 8:02 PM, Doug McNaught wrote:

> Ken Tozier <kentozier@comcast.net> writes:
>
>> I've been looking though the PostgreSQL documentation but can't seem
>> to find a command for importing files. I read the documentation
>> related to large objects but this isn't what I'm looking for as I
>> don't want to import the entire file into a single field, I want to
>> import it as a table. I'm sure there's a way to do it but I can't seem
>> to find the magic command.
>>
>> Could someone point me to (or provide) an example?
>
> You want the SQL COPY statement, or the \copy command in 'psql'.
>
> -Doug
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if
> your
>       joining column's datatypes do not match
>


Complex query need help with OR condition.

From
Ken Tozier
Date:
I'm working on a query which works as expected when I leave out one of
the "OR" tests but when the "OR" is included, I get hundreds of
duplicate hits from a table that only contains 39 items. Is there a way
to write the following so that the "WHERE" clause tests for two
possible conditions?

Thanks for any help,

Ken


Here's the working query:

SELECT a.paginator, a.doc_name, (b.time - a.time) as elapsed_time FROM
pm_events as a, pm_events as b
    WHERE a.event_code='pmcd'
    AND b.event_code='pmcl'
    AND a.doc_name=b.doc_name
    AND a.paginator=b.paginator
    AND a.time < b.time

When I add the OR clause things go haywire:

SELECT a.paginator, a.doc_name, (b.time - pm_events.time) as
elapsed_time FROM pm_events as a, pm_events as b
    WHERE a.event_code='pmcd'
    OR a.event_code='pmop'
    AND b.event_code='pmcl'
    AND a.doc_name=b.doc_name
    AND a.paginator=b.paginator
    AND a.time < b.time

Have also tried the following in the WHERE clause to no avail:

    WHERE a.event_code IN {'pmcd', 'pmop'}
    WHERE a.event_code=('pmcd' | 'pmop')


Re: Complex query need help with OR condition.

From
Tom Lane
Date:
Ken Tozier <kentozier@comcast.net> writes:
> When I add the OR clause things go haywire:

> SELECT a.paginator, a.doc_name, (b.time - pm_events.time) as
> elapsed_time FROM pm_events as a, pm_events as b
>     WHERE a.event_code='pmcd'
>     OR a.event_code='pmop'
>     AND b.event_code='pmcl'
>     AND a.doc_name=b.doc_name
>     AND a.paginator=b.paginator
>     AND a.time < b.time

I think you need some parentheses, or at least a bit of thought about
what the OR is binding to.

            regards, tom lane

Re: Complex query need help with OR condition.

From
Ken Tozier
Date:
On Oct 25, 2004, at 12:35 AM, Tom Lane wrote:

> Ken Tozier <kentozier@comcast.net> writes:
>> When I add the OR clause things go haywire:
>
>> SELECT a.paginator, a.doc_name, (b.time - pm_events.time) as
>> elapsed_time FROM pm_events as a, pm_events as b
>>     WHERE a.event_code='pmcd'
>>     OR a.event_code='pmop'
>>     AND b.event_code='pmcl'
>>     AND a.doc_name=b.doc_name
>>     AND a.paginator=b.paginator
>>     AND a.time < b.time
>
> I think you need some parentheses, or at least a bit of thought about
> what the OR is binding to.

The table stores "file open", "file create" and "file close" events
(pmop, pmcd, pmcl) What I'm trying to do is bind "open" and "create"
events to the next sequential close event for each document a
particular paginator works on. I also tried parentheses after the where
clause like so:

WHERE (a.event_code='pmcd' OR a.event_code='pmop')

But it only seems to recognize the first comparison ignoring whatever
comes after the OR


Re: pqAdmin3

From
Jeff Davis
Date:
Perhaps the current authentication scheme you have set up is different
for local and remote host. It might be that pgAdmin is connecting from a
different machine? And if you're using ident authentication it might not
work from a remote machine the same as for local.

Please give more details, such as the contents of pg_hba.conf and the
way you're trying to connect from both psql and pgAdmin.

Regards,
    Jeff Davis

On Sun, 2004-10-24 at 18:10 -0300, Philip Pinkerton wrote:
> When trying to connect to database via the pgAdmin3 GUI it asks for a
> password. I use the same passworrd as I did when I connect to the DB via
> command line but I get Ident error?
>
> how do I set, re-set the password so I can use  both the commandline and
> pgAdmin to access edit by DB?
>
> Thanks
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Jeff Davis <jdavis-pgsql@empires.org>


Re: Complex query need help with OR condition.

From
Jaime Casanova
Date:
 --- Ken Tozier <kentozier@comcast.net> escribió:
> I'm working on a query which works as expected when
> I leave out one of
> the "OR" tests but when the "OR" is included, I get
> hundreds of
> duplicate hits from a table that only contains 39
> items. Is there a way
> to write the following so that the "WHERE" clause
> tests for two
> possible conditions?
>
> Thanks for any help,
>
> Ken
>
>
> Here's the working query:
>
> SELECT a.paginator, a.doc_name, (b.time - a.time) as
> elapsed_time FROM
> pm_events as a, pm_events as b
>     WHERE a.event_code='pmcd'
>     AND b.event_code='pmcl'
>     AND a.doc_name=b.doc_name
>     AND a.paginator=b.paginator
>     AND a.time < b.time
>
> When I add the OR clause things go haywire:
>
> SELECT a.paginator, a.doc_name, (b.time -
> pm_events.time) as
> elapsed_time FROM pm_events as a, pm_events as b
>     WHERE a.event_code='pmcd'
>     OR a.event_code='pmop'
>     AND b.event_code='pmcl'
>     AND a.doc_name=b.doc_name
>     AND a.paginator=b.paginator
>     AND a.time < b.time
>
> Have also tried the following in the WHERE clause to
> no avail:
>
>     WHERE a.event_code IN {'pmcd', 'pmop'}
>     WHERE a.event_code=('pmcd' | 'pmop')
>
>


the query with the OR clause says:

SELECT a.paginator, a.doc_name, (b.time -
pm_events.time)
+++++++++

meanwhile the other one says:
SELECT a.paginator, a.doc_name, (b.time - a.time)
                                          +

Which pm_events table will the planner use a or b?? i
think in the second query you are confusing the
planner forcing a cartesian product.

regards,
Jaime Casanova

_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com