The execution of the command for checking the version of PostgreSQL doesn’t depend on the version of the PostgreSQL itself. It is already become the default parameter and argument provided. To be able to see all of the available parameter and argument which can be used by executing the ‘psql’ command, just type ‘psql –help’. Apr 23, 2018 Here’s how to view your PostgreSQL version: SHOW serverversion; Run that command into a query pane in PgAdmin or other admin tool, and PostgreSQL will return its own server version. The default is PostgreSQL 10, so running the following yum command installs the latest stable version: $ sudo yum install @postgresql Installing and use PostgreSQL on RHEL 8 using application streams (click to enlarge) How to initialize new PostgreSQL database cluster. In this post, I am sharing basic commands to check the PostgreSQL version information. PostgreSQL Database Administrator can use this command to find the version number, release version name of PostgreSQL. Earlier we discussed about how to install PostgreSQL database on Linux from source. In this article, let us review top 15 practical postgreSQL DBA command examples. If you are a mySQL administrator, check-out our 15 examples of mysqladmin command article that we discussed a while back. By issuing the SHOW serverversion; statement above, we’re asking Postgres to retrieve the current parameter value of serverversion, which of course is the version of PostgreSQL currently running. Viewing the Client Version. In a similar vein, we can also issue a short SQL statement from a PostgreSQL prompt to query the client version of psql.
Active4 months ago
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
Sergiy KolodyazhnyySELECT * 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.79.4k1111 gold badges171171 silver badges354354 bronze badges
j450nj450n
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 Kolodyazhnyy79.4k1111 gold badges171171 silver badges354354 bronze badges
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:
WinEunuuchs2UnixWinEunuuchs2Unix58.5k1818 gold badges119119 silver badges232232 bronze badges
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.
![Postgres Version Command Postgres Version Command](/uploads/1/2/6/8/126871277/642089086.png)
*) 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;':
Postgres Command Line
-) 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'.*'
Postgres Version Check Command
*) Now you can do your queries. For example:
*) Here is what the above DB, Schema, and Tables look like in pgAdmin:
![Postgres Version Command Postgres Version Command](/uploads/1/2/6/8/126871277/663681148.png)
GeneGene