Thread: Getting Started: Tutorials/Books?

--- "Eric M. Cherry" <cherry_eric@sbcglobal.net> wrote:
> I am getting started with only basic computer-using skills and essentially
> no programming knowledge.  I am in need of a tutorial and/or a reference
> book that will help me learn to use pgAdmin III.

I haven't seen a tutorial for pgAdmin III.  However, I haven't really look either.
However for general SQL practice:

http://en.wikipedia.org/wiki/SQL  -- general information
http://sqlzoo.net/ -- a nice tutorial that actually let you enter your own queries to help you
learn through practice


> The tutorial that exists in the help file appears to focus on using the psql
> program from within a command shell.  I have failed to accomplish even the
> most basic commands in this manner; the system tells me that user "emc" does
> not exist, I do not appear able to create that user, and I cannot see a way
> to switch to a valid user.  Meanwhile, using pgAdmin III, I appear to be
> able to create new databases and tables.

We (us on this mailing list) can assist you with getting connected to your database using psql.
But first you will need to provide to with your pg_hba.conf file and the command you are using and
the error message you receive when it fails.

Just remember, when you reply to this or any other list email, always reply-all so that everyone
on the list can offer a helping hand.  And also, the list prefers that we do not "top post" when
we reply to an email from the list.  Always cut out the specific portion of text that you want to
comment on and add your reply below it.


> I am assuming that my gross lack of knowledge is what keeps me from using
> the command shell while I can make use of the pgAdmin III interface.  The
> interface is clearly picking up the slack for me, which I appreciate.

PgAdmin is a good tool to use.  However, in my opinion it should only be used as a tool of
convenience for a Database Administrator.   Eventually, when you become more experienced with
PostgreSQL, you will eventually start building your own front-end for users to use the underling
database.  PgAdmin is not really suited for this particular task.

Also, becoming familiar with the SQL Data Definition Language (DDL) and other SQL statements used
in PSQL will expose you to features that you probably wouldn't learn about if you limited youself
many to any various DBA front-ends in-addition to PgAdmin.

> This does not help me get beyond the wall of incomprehension at the next
> stage, though.  I can spawn a database, and I can create a table.  I do not
> grasp the basics of table mechanics from the programming side.  (I have, on
> paper, the table I want to create; I do not know how to make this happen in
> the database.)

Check out the one-line PostgreSQL manual:
http://www.postgresql.org/docs/8.1/interactive/index.html

--the index is a great place to start.  Reading the main heading and then working your way to the
sub-heading will help you to know where first you can turn when you have a question or problem
that you want an answer to.

but more specifically to creating a table:
http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html

> Is there a comprehensive tutorial available for this, or can you recommend a
> book that can give me the background knowledge I need to begin to make
> progress?

There are quiet a few of generalized SQL tutorials from the WIKI page that I previous sighted.
Note that these links are at the bottom of that page.

Here is a link of books listed by the PostgreSQL community:
http://techdocs.postgresql.org/#books
http://techdocs.postgresql.org/techdocs/bookreviews.php#sql

However, I must admit that I've learned the most just by reading the mail posted on various
Postgresql Mailing lists.  Sometimes there is alot to read, but you'll "learn things to never knew
you never knew."  :-)

There is also a Postgresql list for SQL questions you might also like to check out.  The general
list is very good also, yet there is sometime alot to read.


Having said all that,  Welcome to the PostgreSQL community!

Regards,

Richard Broersma Jr.

-----Original Message-----
From: Richard Broersma Jr [mailto:rabroersma@yahoo.com]
Sent: Saturday, September 02, 2006 12:37 AM
To: Eric M. Cherry; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Getting Started: Tutorials/Books?

>Just remember, when you reply to this or any other list email, always
>reply-all so that everyone on the list can offer a helping hand.  And also,
>the list prefers that we do not "top post" when we reply to an email from
>the list.  Always cut out the specific portion of text that you want to
>comment on and add your reply below it.

Understood, and thanks!

>Having said all that,  Welcome to the PostgreSQL community!

>Regards,

>Richard Broersma Jr.

The feedback you offered is spectacular -- thank-you so much!

Talk with you soon, I expect ~

   - emc

emc wrote:

> The tutorial that exists in the help file appears to focus on using
> the psql program from within a command shell.  I have failed to
> accomplish even the most basic commands in this manner; the system
> tells me that user "emc" does not exist, I do not appear able to
> create that user, and I cannot see a way to switch to a valid user.
> Meanwhile, using pgAdmin III, I appear to be able to create new
> databases and tables.

Richard replied:

> We (us on this mailing list) can assist you with getting connected
> to your database using psql. But first you will need to provide to
> with your pg_hba.conf file and the command you are using and the
> error message you receive when it fails.

I found the pg_hba.conf file in c:\Program Files\postgreSQL\8.1\data.  It
appears to contain a vast amount of text following "#" symbols (which I
gather means that the text is there for my information only, ignored by the
computer).  The only line of text that is not prefaced with "#" is this
(this is referenced as IPv4):

host     all     all     127.0.0.1/32     trust

A similar line of text below this one, referenced as IPv6, is this:

#host     all     all     ::1/128          trust

So that's the pg_hba.conf file information.  What I'm doing is this:

1.  In Windows, through the Start menu, I select "Start service"
2.  I wait for "Start service" to open and close a command shell (usually
too fast to see more than a flicker)
3.  In Windows, through the Start menu, I select "Command prompt"
4.  A command shell opens, giving me the prompt:

     C:\Program Files\PostgreSQL\8.1\bin>

5.  I type:

     C:\Program Files\PostgreSQL\8.1\bin>createdb mydb [enter]

6.  The system yields the following error:

     createdb: could not connect to database postgres: FATAL:  role "emc"
does not exist

7.  I am left back at the prompt.
8.  Not to be deterred, I type:

     C:\Program Files\PostgreSQL\8.1\bin>createuser emc

9.  The system gives me a prompt:

     Shall the new role be a superuser? (y/n)

10.  I type "y" [enter]
11.  The system yields an error message:

     createuser: could not connect to database postgres: FATAL:  role "emc"
does not exist

12.  I open Outlook and email the Novice List.

I think that it's noteworthy that pgAdmin III lets me perform the tasks of
making new databases without trouble.  This tells me that the tasks can be
done on my computer, but that I'm leaving out some basic step.  (To give you
an idea of how basic, I spent ten minutes getting the error that the port
wasn't listening.  I finally stumbled to the trick that I had to "start
service" first.  That gives you an idea of the sort of basic step I'm apt to
miss out of sheer ignorance.)

   - emc

On Sat, 2 Sep 2006 08:37:45 -0500
"Eric M. Cherry" <cherry_eric@sbcglobal.net> wrote:

> emc wrote:
>
> Richard replied:
>

> 4.  A command shell opens, giving me the prompt:
>
>      C:\Program Files\PostgreSQL\8.1\bin>
>
> 5.  I type:
>
>      C:\Program Files\PostgreSQL\8.1\bin>createdb mydb [enter]
>
> 6.  The system yields the following error:
>
>      createdb: could not connect to database postgres: FATAL:  role
> "emc" does not exist
>
> 7.  I am left back at the prompt.
> 8.  Not to be deterred, I type:
>
>      C:\Program Files\PostgreSQL\8.1\bin>createuser emc
>
> 9.  The system gives me a prompt:
>
>      Shall the new role be a superuser? (y/n)
>
> 10.  I type "y" [enter]
> 11.  The system yields an error message:
>
>      createuser: could not connect to database postgres: FATAL:  role
> "emc" does not exist
>
> 12.  I open Outlook and email the Novice List.
>
> I think that it's noteworthy that pgAdmin III lets me perform the
> tasks of making new databases without trouble.  This tells me that
> the tasks can be done on my computer, but that I'm leaving out some
> basic step.  (To give you an idea of how basic, I spent ten minutes
> getting the error that the port wasn't listening.  I finally stumbled
> to the trick that I had to "start service" first.  That gives you an
> idea of the sort of basic step I'm apt to miss out of sheer
> ignorance.)
>
Eric,

Good analysis overall.

PostgreSQL is telling you that it doesn't know who 'emc' is.  You see
postgreSQL user accounts (called roles) are entirely separate from the
Operating System (in your case windows) users.  Try
connecting (or createdb or createuser) with the '-U postgres' flag set
and see what happens.

John Purser

<emc posted a problem>

John answered:

> PostgreSQL is telling you that it doesn't know who 'emc' is.  You see
> postgreSQL user accounts (called roles) are entirely separate from the
> Operating System (in your case windows) users.  Try
> connecting (or createdb or createuser) with the '-U postgres' flag set
> and see what happens.

> John Purser

emc:

I did "createuser -U postgres", and the system led me through creating a
user "emc".  It worked beautifully; I'm logged in and creating databases.

Thanks again -- you guys rock!

   - emc