13

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.

4

3 Answers 3

12

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:

17
  • 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 :) Jul 18, 2018 at 23:03
  • @WinEunuuchs2Unix Hehe, I already have both Postgres and Sqlite3 installed. Jul 18, 2018 at 23:05
  • @WinEunuuchs2Unix You answer is good, so no worries there. Besides, gives a different perspective. It's always good to have variety of answers. Jul 18, 2018 at 23:06
  • 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.
    – j450n
    Jul 19, 2018 at 0:09
  • @j450n Can you provide the exact command you're trying ? Any errors reported ? Jul 19, 2018 at 0:10
1

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

enter image description here


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)
0

You're getting that error message because you forgot to include the Schema in you SELECT * FROM dbname; query.

You shouldn't be using \d, you should use \dt with the Schema name (not DB name). for example \dt "MySchema".*

Here's how to connect and see your DB, Schemas, and Tables:

*) Type "\?" for help

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

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

enter image description here

*) Connect to a database by "\c ", for example "\c GeneDB1"

enter image description here

You should see the key prompt change to the new DB, like so: enter image description here

*) 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".

enter image description here

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;":

enter image description here

-) 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".*"

enter image description here

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

enter image description here

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

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .