Installing PostgreSQL creates a default database and user account, both called ‘postgres.’ To log into the ‘postgres’ user account type the following command in the terminal: sudo –i –u postgres This example shows the command in a Debian-based distribution, Ubuntu. 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. Determining the PostgreSQL version. To determine which PostgreSQL version is installed on your server, log in to your account using SSH, and then type the following command at the command line: psql -version. Alternatively, you can log in to PostgreSQL from the command line using the psql program, and then type the following query to view.

I'm in a corporate environment (running Debian Linux) and didn't install it myself. I access the databases using Navicat or phpPgAdmin (if that helps). I also don't have shell access to the server running the database.

AcumenusHighly IrregularHighly Irregular

14 Answers

Highly IrregularHighly Irregular

I believe this is what you are looking for,

Server version:

Client version:

chhantyal

Server version:

If having more than one installation of PostgreSQL, or if getting the 'postgres: command not found' error:

If locate doesn't help, try find:

Although postmaster can also be used instead of postgres, using postgres is preferable because postmaster is a deprecated alias of postgres.

Client version:

As relevant, login as postgres.

If having more than one installation of PostgreSQL:

Server version:

If more curious, try => SHOW all;.

Client version:

For what it's worth, a shell command can be executed within psql to show the client version of the psql executable in the path. Note that the running psql can potentially be different from the one in the path.

AcumenusAcumenusBhavesh Odedravipin cpvipin cp

If you're using CLI and you're a postgresuser, then you can do this:

Possible output:

simhumilecosimhumileco

The accepted answer is great, but if you need to interact programmatically with PostgreSQL version maybe it's better to do:

It will return server version as an integer. This is how server version is tested in PostgreSQL source, e.g.:

More info here and here.

Michel MilezziMichel MilezziMD. Khairul Basar

Using pgadmin4 it can be seen by double clicking Servers > server_name_here > Properties tab > Version:

Version 3.5:

Version 4.1, 4.5:

jmunschjmunsch

A simple way is to check the version by typing psql --version in terminal

Alex TrnAlex TrnVikas HardiaVikas Hardia

The pg_config command will report the directory where the PostgreSQL programs are installed (--bindir), the location of C include files (--includedir) and object code libraries (--libdir), and the version of PostgreSQL (--version):

DonatoDonato

If you have shell access to the server (the question mentions op does not have, but in case you have,) on a debian/ubuntu system

which will output the installed version,

where the Installed: <version> is the installed postgres package version.

All Іѕ VаиітyAll Іѕ Vаиітy

Don’t know how reliable this is, but you can get two tokens of version fully automatically:

So you can build paths to binaries:

Just replace 9.2 with this command.

Alexey PetrenkoAlexey Petrenko

If Select version() returns with Memo try using the command this way:

or

Gidil

protected by eyllanescMar 21 '18 at 6:20

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged postgresql or ask your own 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.

Sergiy Kolodyazhnyy

3 Answers

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

For multiline queries you can use heredoc:

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:

Sergiy KolodyazhnyySergiy Kolodyazhnyy

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:

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:

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:

WinEunuuchs2UnixWinEunuuchs2Unix

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:

Postgres Command Line Tools

*) 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:

Postgres Version Command In Windows 10

Postgresql Version Command

Not the answer you're looking for? Browse other questions tagged command-linepostgresqlsql or ask your own question.