How to execute SQL statement from command line?

I'm a longtime GUI user trying to switch to command line, and I'm not sure how to execute an SQL statement from the Ubuntu command line. I'm using postgres. I can use \c to connect to the database and \d to see the tables in it. I can also see the headers with \d dbname (where dbname is the name of the database). What I can't do is see the actual data.

I tried SELECT * FROM dbname; and I got a "syntax error at or near dbname". I Tried it without the semi colon, and just got a new command line. How do I see my data? Thanks in advance.

Solution 1:

Edit: OP's actual problem apparently is table name that entirely consists of digits. According to SQL-92 standard table names cannot start with a digit, but otherwise can contain digits. For such case, one simply needs to wrap the name in double or single quotes as in SELECT * FROM "12345";

Essentially, what you need is the psql command - the command-line interpreter for Postgres, which comes by default with Postgres installation on Ubuntu. Running psql -U username databasename will allow you to connect to that, and execute SQL queries via that command-line interpreter. If you're asking about running commands while in bash shell, you should be using psql command with -c flag. Something along the lines of

psql -U username -d database.db -c "SELECT * FROM some_table"

For multiline queries you can use heredoc:

$ psql -U admin_serg hello_world <<EOF
> SELECT * FROM foobar;
> EOF                  

 foo |     bar     
-----+-------------
   1 | Hello World
(1 row)

Of course, if you haven't created a particular user with postgres , you might want to do that, or just log in as psql user first, sudo su postgres.

As for syntax error, you probably tried to enter an SQL query directly into the command-line ( in your case, that'd be probably bash shell ). That's not how this works - bash doesn't understand SQL, only its own syntax, hence why psql command-line interpreter exists, just like for other databases (sqlite3 for instance) or there's GUI tools for that(like pgAdmin for postgres or sqlitebrowser for sqlite3).

See also:

• PostgreSQL - query from bash script as database user 'postgres'
• How to create user for a db in postgresql?

Solution 2:

You can issue commands from the terminal, but you can get an open source package with tab completion, colours, etc:

Using the generic program psql use:

$ psql mydb

If you leave off the database name then it will default to your user account name. You already discovered this scheme in the previous section.

In psql, you will be greeted with the following message:

Welcome to psql 8.3.23, the PostgreSQL interactive terminal.

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

mydb=>

The last line printed out by psql is the prompt, and it indicates that psql is listening to you and that you can type SQL queries into a work space maintained by psql. Try out these commands:

mydb=> SELECT version();
                            version
----------------------------------------------------------------
 PostgreSQL 8.3.23 on i586-pc-linux-gnu, compiled by GCC 2.96
(1 row)

mydb=> SELECT current_date;
    date
------------
 2002-08-31
(1 row)

mydb=> SELECT 2 + 2;
 ?column?
----------
        4
(1 row)