Run PostgreSQL queries from the command line

I inserted a data into a table....I wanna see now whole table with rows and columns and data. How I can display it through command?

psql -U username -d mydatabase -c 'SELECT * FROM mytable'

If you're new to postgresql and unfamiliar with using the command line tool psql then there is some confusing behaviour you should be aware of when you've entered an interactive session.

For example, initiate an interactive session:

psql -U username mydatabase 
mydatabase=#

At this point you can enter a query directly but you must remember to terminate the query with a semicolon ;

For example:

mydatabase=# SELECT * FROM mytable;

If you forget the semicolon then when you hit enter you will get nothing on your return line because psql will be assuming that you have not finished entering your query. This can lead to all kinds of confusion. For example, if you re-enter the same query you will have most likely create a syntax error.

As an experiment, try typing any garble you want at the psql prompt then hit enter. psql will silently provide you with a new line. If you enter a semicolon on that new line and then hit enter, then you will receive the ERROR:

mydatabase=# asdfs 
mydatabase=# ;  
ERROR:  syntax error at or near "asdfs"
LINE 1: asdfs
    ^

The rule of thumb is: If you received no response from psql but you were expecting at least SOMETHING, then you forgot the semicolon ;

SELECT * FROM my_table;

where my_table is the name of your table.

EDIT:

psql -c "SELECT * FROM my_table"

or just psql and then type your queries.

If your DB is password protected, then the solution would be:

PGPASSWORD=password  psql -U username -d dbname -c "select * from my_table"

Open "SQL Shell (psql)" from your Applications (Mac).

Click enter for the default settings. Enter the password when prompted.

*) Type \? for help

*) Type \conninfo to see which user you are connected as.

*) Type \l to see the list of Databases.

*) Connect to a database by \c <Name of DB>, for example \c GeneDB1

You should see the key prompt change to the new DB, like so:

*) Now that you're in a given DB, you want to know the Schemas for that DB. The best command to do this is \dn.

Other commands that also work (but not as good) are select schema_name from information_schema.schemata; and select nspname from pg_catalog.pg_namespace;:

-) Now that you have the Schemas, you want to know the tables in those Schemas. For that, you can use the dt command. For example \dt "GeneSchema1".*

*) Now you can do your queries. For example:

*) Here is what the above DB, Schema, and Tables look like in pgAdmin:

I also noticed that the query

SELECT * FROM tablename;

gives an error on the psql command prompt and

SELECT * FROM "tablename";

runs fine, really strange, so don't forget the double quotes. I always liked databases :-(