How to execute SQL statement from command line?

Question

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)

Comments

[TonyB]

Documentation

[steeldriver]

You don't SELECT from a database; you SELECT from a table - I usually start with use dbname; then show tables;

[j450n]

Right. Actually that's a type-o. I meant from table.

[Vignesh Raja]

This might help someone stackoverflow.com/a/56042488/4593057

[WinEunuuchs2Unix]

Sorry we answered at the exact same time. Had I seen yours I wouldn't have posted mine. You might need to install postgres SQL to keep track of all your great answers :)

[Sergiy Kolodyazhnyy]

@WinEunuuchs2Unix Hehe, I already have both Postgres and Sqlite3 installed.

[Sergiy Kolodyazhnyy]

@WinEunuuchs2Unix You answer is good, so no worries there. Besides, gives a different perspective. It's always good to have variety of answers.

[j450n]

Thanks Sergiy. I am entering from the psql command line as a superuser. I'm still not getting a return of the data, just the next command line.

[Sergiy Kolodyazhnyy]

@j450n Can you provide the exact command you're trying ? Any errors reported ?

[j450n]

Sure. From my user command, I use sudo -u postgres psql and enter my sudo password. I then get the prompt as "postgres=#" for my command line structure. From there I use \c dbname to enter the database, which then gives me the "dbname=#" prompt. I then do a \d to get a list of tables to find the table I want. I then hit "Q" to go back to the dbname=# prompt. Then I try "SELECT * FROM tablename;" and I get an error that says:ERROR: syntax error at or near "tablename" LINE 1: SELECT * FROM tablename. I try it without the semi colon and I just get the next command prompt

[Sergiy Kolodyazhnyy]

@j450n Are you also entering the square brackets as well ? Because they're not supposed to be there. As for ; it's a statement terminator, so if you don't provide it, psql will expect a longer, multi-line statement, which in the end you have to terminate with ; anyway and only then hit Enter.

[j450n]

@SergiyKolodyazhnyy No. I'm not using brackets. I just don't know how to enter the code into here to make it with the gray background. Well, when I terminate the statement with a semi colon it gives me the error, so I'm not sure what to do

[Sergiy Kolodyazhnyy]

@j450n Well something is definitely not typed right, if it says syntax error. How about just SELECT 2+2; ?

[j450n]

@SergiyKolodyazhnyy That returns 4 in a column with 1 row

[j450n]

crypto_test=# SELECT 2+2; ?column? ---------- 4 (1 row)

[j450n]

crypto_test=# SELECT * FROM 425147; ERROR: syntax error at or near "425147" LINE 1: SELECT * FROM 425147; ^

[j450n]

A "\d" gives: List of relations Schema | Name | Type | Owner --------+--------+-------+---------- public | 425147 | table | postgres , so that is the right table name

[Sergiy Kolodyazhnyy]

@j450n Very odd. Try to put the name in single quotes, perhaps the query treats it as integer, while it needs a string

[Sergiy Kolodyazhnyy]

@j450s Yes, apparently by SQL standard, table names can't have names that start with digits, but it's fine if you enclose it in quotes

[j450n]

@SergiyKolodyazhnyy Thanks for your help! That did it! It needed double quotes though. Mark this as solved. Thanks again!

[Sergiy Kolodyazhnyy]

@j450n Very well. Just go ahead and click the grey checkmark next to the answer. That marks an answer as accepted, which indicates question being solved as well