How to get started with PostgreSQL similar to MySQL

Question

I am not getting a clue to:

1. simply login to postgreSQL
2. Create a database
3. Add a table
4. Insert a record
5. Delete , update etc

These things are normally very very easy using mysql . Can someone help me setup following alternative for postgresql

a) Reset default password -- Very Clean description , I do not find same level of clarity for PostgreSQL (Any documentation link is highly appreciated)

b) We know the superuser for mysql is "root" what is the same for PostgreSQL

c) from command line how to ( PostgreSQL ones ?):

 mysql -uroot -proot  
   create database testdb; 
   use testdb;
   create table test(id int(11) auto_increment, name varchar(20), primary key(id));
   insert into test(name) values("testing one"),("testing two"),("testing three"),("testing four");
   select * from test;
   update test set name=concat(name,now()) where id =3;
   delete from test where id =4;
   drop table if exists test;
   drop database if exists testdb;

EDIT MAC OS # Default password reset

sudo mate /Library/PostgreSQL/9.2/data/pg_hba.conf

replaced (md5 with trust)

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

with

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

save

executed the Reload Configuration.app

 login to postgresql without password : 
  $ psql -U postgres
  postgres# ALTER USER postgres WITH PASSWORD 'new password';
  \q  

-revert back all the changes in pg_hba.conf (replace trust with md5) and save

-reload configuration

Now I can login to postgresql with new password

psql -U postgres

Password for user postgres:[my new password]

Answer 1

To login:

psql -d postgres_dbname -U postgres

Create Database:

create database  testuser;

\c testuser;  /*use testuse as mysql*/

Create Table:

create table employee (Name char(20));

Insert :

 insert into employee VALUES ('XAS');

Update Link

Delete Link

Reset Password : See Here & See Here Too

Answer 2

Simply login to postgreSQL

psql -U postgres -W template1
-U = username
postgres is root 
-W = ask for password
tempalte1 = default database

Create a database

-- Create the database from within postgresql
create database my_database_name
-- Connect to the database
\c my_database_name

-- Create the database without logging in
createdb -U postgres -W my_database_name

1. Add a table
2. Insert a record
3. Delete , update etc

All the above from 3 to 5 are like in MySQL

For resetting postgres forgotten password this link is a good reference.

Answer 3

postgresql is a completely different system than mysql; so do not assume things will be like mysql. They are completely different animals entirely; some of your SQL statements might not work, especially if you are using a MySQL proprietary command.

1. To login to postgresql, use the psql command shell
2. CREATE DATABASE
3. CREATE TABLE
4. INSERT
5. For all other basic SQL commands, consider going through the tutorial

User access control is something more fine grained and detailed in postgresql. There are users and roles. A user is simply a role that has the ability to login (like MySQL), but in postgresql you can have roles (an account) that cannot login.

What access a role has is defined in pg_hba.conf. This file defines if a role can login at all, by what means are they authenticated, from where they can login and what database they have access to.

ALTER USER is used to reset credentials.

The "root user" for postgresql is typically postgres; and this is a system user that is created during the install process. For Windows, the binary installer will ask if you want to launch the service as this user as well.

Answer 4

Please take a look at this PostgreSQL error 'Could not connect to server: No such file or directory'

Try to install postgresApp, this solved my problem which was the same of yours.