Ttgjyuk

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.

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)

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?

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.

*) Connect to a database by "c ", 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: