First steps

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
Line 20: Line 20:
 
         \q to quit
 
         \q to quit
  
Create a database
+
Create a schema called test in the default database called postgres
 
   postgres=# CREATE SCHEMA test;
 
   postgres=# CREATE SCHEMA test;
  
Create a user
+
Create a role (user) with password
 
   postgres=# CREATE USER xxx PASSWORD 'yyy';
 
   postgres=# CREATE USER xxx PASSWORD 'yyy';
  
Grant privileges
+
Grant privileges on new schema to new role
 
   postgres=# GRANT ALL ON ALL TABLES IN SCHEMA test TO xxx;
 
   postgres=# GRANT ALL ON ALL TABLES IN SCHEMA test TO xxx;
  
Line 32: Line 32:
 
   postgres=# \q
 
   postgres=# \q
  
Became a standard user
+
Became a standard user.<br/> 
 +
The default authentication mode is set to 'ident' which means a given Linux user xxx can only connect as the postgres user xxx.
 
   # su - xxx
 
   # su - xxx
  
Login
+
Login from xxx user in shell to default postgres db
   xxx$ psql test
+
   xxx$ psql -d postgres
   psql: FATAL:  database "test" does not exist
+
   psql (9.2.4)
???
+
  Type "help" for help.
  
Create a table.
+
Create a table test in schema test
 +
  postgres=>CREATE TABLE test.test (coltest varchar(20));
 +
  CREATE TABLE
  
First SELECT from a table.
+
Insert a single record into new table
 +
  postgres=>insert into test.test (coltest) values ('It works!');
 +
  INSERT 0 1
  
Where to continue?
+
First SELECT from a table
 +
  postgres=>SELECT * from test.test;
 +
    coltest 
 +
  -----------
 +
  It works!
 +
  (1 row)
 +
 
 +
Drop test table
 +
  postgresql=>DROP TABLE test.test;
 +
  DROP TABLE

Revision as of 15:09, 18 July 2013

Hello,

please add info or links:

First steps after installation

First connect/login as root

 # su - postgres
 $ psql
 psql (9.2.4)
 Type "help" for help.
 postgres=#  help
 You are using psql, the command-line interface to PostgreSQL.
 Type:  \copyright for distribution terms
        \h for help with SQL commands
        \? for help with psql commands
        \g or terminate with semicolon to execute query
        \q to quit

Create a schema called test in the default database called postgres

 postgres=# CREATE SCHEMA test;

Create a role (user) with password

 postgres=# CREATE USER xxx PASSWORD 'yyy';

Grant privileges on new schema to new role

 postgres=# GRANT ALL ON ALL TABLES IN SCHEMA test TO xxx;

Disconnect

 postgres=# \q

Became a standard user.
The default authentication mode is set to 'ident' which means a given Linux user xxx can only connect as the postgres user xxx.

 # su - xxx

Login from xxx user in shell to default postgres db

 xxx$ psql -d postgres
 psql (9.2.4)
 Type "help" for help.

Create a table test in schema test

 postgres=>CREATE TABLE test.test (coltest varchar(20));
 CREATE TABLE

Insert a single record into new table

 postgres=>insert into test.test (coltest) values ('It works!');
 INSERT 0 1

First SELECT from a table

 postgres=>SELECT * from test.test;
   coltest  
 -----------
  It works!
 (1 row)

Drop test table

 postgresql=>DROP TABLE test.test;
 DROP TABLE
Personal tools