[GENERAL] Trying to create DB / user to import some data

[mdr]

I am new to Postgres but not to DBMS.
Running on Postgres 9.1 Ubuntu 13.04.

I log in as Postgres user: psql -h localhost -U postgres
and then I create a db and user and allow grants to the user like this:

create user import_dbms_user with password 'import_dbms';
create database import_dbms_db;
grant all privileges on database import_dbms_db to import_dbms_user;

\du shows:
 import_dbms_user || {}
 postgres | Superuser, Create role, Create DB, Replication | {}

In the pg_hba.conf I set as:
# local DATABASE USER METHOD [OPTIONS]
local import_dbms_db import_dbms_user md5
and restart Postgres.

However when I try to run psql from the command line:
psql -h localhost -U import_dbms_user -WI enter password when prompted
Password for user import_dbms_user: 
psql: FATAL:  database "import_dbms_user" does not exist

But I get the error as above.

Trying to understand what I may be doing wrong.

Do I need to assign some kind of login role to import_dbms_user or such?

i assumed in the pg_hba.conf the line above is all I needed to log in
locally?
local import_dbms_db import_dbms_user md5

And instead of local I would enter IP address of machine from which I would
want to log in to the server?
192.168.1.10 import_dbms_db import_dbms_user md5

I am running on a VM and am trying to run some python scripts with psycopg2
to load some data.

So that script cannot log in either. Do I need to create any special role /
priviledge for that user to login from that script as well?

Thanks for your help!

Mono






--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Trying-to-create-DB-user-to-import-some-data-tp5772568.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Trying to create DB / user to import some data

[mdr]

Hi Steven -

Thanks. Worked great. I assumed it would assume the dbname to be
import_dbms_db as import_dbms_user was granted admin privileges on it.

Also I do have import_dbms_user and import_dbms_db in my pg_hba.conf as:
local import_dbms_db import_dbms_user md5

I still need that - correct? And I will need to have another entry
specifying the ip addr of the machine to connect from if I am connecting
from a different machine?

So to connect from Python scripts (using psycopg2) do I need to specify the
database to connect to as well?

And if that python script is running from a different machine the ip addr of
that machine needs to be in the pg_hba.conf?

I ask as this is not connecting for me right now but I will check on the
psycopg forum as well.

Just wanted to confirm.

Thank you for your help.

Mono



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Trying-to-create-DB-user-to-import-some-data-tp5772568p5772622.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Trying to create DB / user to import some data

[mdr]

Thanks for your help Adrian. Works great.

I had a few other questions on creating primary keys after create table
using alter table.

I will post them separately so this thread is closed.

Thank you both again.

Mono



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Trying-to-create-DB-user-to-import-some-data-tp5772568p5772628.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Creating Primary Key after CREATE TABLE: Is Sequence created?

[mdr]

I had a question on creating PK with alter table, after table is created. 

I understand I create a PK id during create table by stating id as follows:
id serial primary key

It implicitly creates index and the sequence testing_id_seq to be associated
with the id field.
I can list the sequence with \ds.

...
However if I create a primary key with alter table primary key as in:
import_dbms_db=> alter table testing ADD CONSTRAINT pkid PRIMARY KEY (id);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pkid" for
table "testing"
ALTER TABLE
import_dbms_db=> \ds
No relations found.

It does not create a sequence.

So I am assuming I have to create the sequence and associate it with the
column separately as well?

Is there a way to create primary key with the alter table to allow the
sequence to be created automatically and associated with the primary key?

I did not find anything on this so just wanted to confirm - so I write my
scripts accordingly.

Also during creating indexes (primary, secondary or foreign) am I allowed to
create indexes with same name but on different tables? Or do index names
have to be different across tables? probably good programming practice as
well to have different index names across tables even if allowed?

Thank you for your help and suggestions.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Creating-Primary-Key-after-CREATE-TABLE-Is-Sequence-created-tp5772633.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Creating Primary Key after CREATE TABLE: Is Sequence created?

[mdr]

Hi David and John -

Thank you for your answers on the SERIAL, SEQUENCE and thus PRIMARY KEY.

I did not realize there was a column of type SERIAL that creates a SEQUENCE.

However, now I have a different question.

Is it possible to create a column of type SQL:2011 types (INTEGER or such)
and then connect a SEQUENCE to it and make that column a PRIMARY KEY -
without creating a column of type SERIAL?

It seems column of type SERIAL is specific to Postgres and will make my
script Postgres dependent?

Just in case I decide to go to MySQL. Most likely not, but just wanted to
manage to SQL:2011.

Thank you again.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Creating-Primary-Key-after-CREATE-TABLE-Is-Sequence-created-tp5772633p5772642.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general