Run PostgreSQL queries from the command line

Question

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

User · Answer

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  -

User · Answer

I have no doubt on  Grant answer  But I face few issues sometimes such as if the column name is similar to any reserved keyword of postgresql such as natural in this case similar SQL is difficult to run from the command line as   natural   will be needed in Query field  So my approach is to write the SQL in separate file and run the SQL file from command line  This has another advantage too  If you have to change the query for a large script you do not need to touch the script file or command  Only change the SQL file like this  psql -h localhost -d database -U postgres -p 5432 -a -q -f  path to the file sql

User · Answer

Open a command prompt and go to the directory where Postgres installed  In my case my Postgres path is  D  TOOLS Postgresql-9 4 1-3  After that move to the bin directory of Postgres So command  prompt shows as  D  TOOLS Postgresql-9 4 1-3 bin   Now my goal is to select  UserName  from the users table using  UserId  value So the database query is  Select u  UserName  from users u Where u  UserId  1     The same query is written as below for psql command prompt of postgres   D  TOOLS Postgresql-9 4 1-3 bin psql -U postgres -d DatabaseName -h localhost - t -c  Select u   UserName   from users u Where u   UserId   1

User · Answer

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  lt Name of DB gt   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

User · Answer

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

User · Answer

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

User · Answer

If your DB is password protected  then the solution would be   PGPASSWORD password  psql -U username -d dbname -c  select   from my table

[database] Run PostgreSQL queries from the command line

Examples related to database

Examples related to postgresql