How to execute SQL statement from command line? Announcing the arrival of Valued Associate...
How to find all the available tools in macOS terminal?
Why is "Consequences inflicted." not a sentence?
How to deal with a team lead who never gives me credit?
Is there a service that would inform me whenever a new direct route is scheduled from a given airport?
What causes the vertical darker bands in my photo?
What is the longest distance a 13th-level monk can jump while attacking on the same turn?
Best practices for giving outside developer SSH access?
Do you forfeit tax refunds/credits if you aren't required to and don't file by April 15?
Did Xerox really develop the first LAN?
Why are there no cargo aircraft with "flying wing" design?
How to motivate offshore teams and trust them to deliver?
Why did the IBM 650 use bi-quinary?
Gastric acid as a weapon
When is phishing education going too far?
How widely used is the term Treppenwitz? Is it something that most Germans know?
Is there a concise way to say "all of the X, one of each"?
What happens to sewage if there is no river near by?
Java 8 stream max() function argument type Comparator vs Comparable
How do I determine if the rules for a long jump or high jump are applicable for Monks?
Single word antonym of "flightless"
What is a Meta algorithm?
Is there a documented rationale why the House Ways and Means chairman can demand tax info?
Why is black pepper both grey and black?
List *all* the tuples!
How to execute SQL statement from command line?
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)What's the difference between <<, <<< and < < in bash?How to setup DB in postgresql 9.2.4 on 12.04.2 LTSPostgreSQL importing from folderHow to execute a number of lines using xargsRun multiple line as a single line command bashBash Scripting for opening ssh, python shell and python commandspostgresql installation damagedPostgreSQL ALTER command - Permission Denied - Environment QuestionGiving postgres database user sudo permissionsHow to install Adventure Works DB on Ubuntu 16.04?How do I change file headers from the command line?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
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.
command-line postgresql sql
add a comment |
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.
command-line postgresql sql
Documentation
– TonyB
Jul 18 '18 at 22:33
1
You don'tSELECT
from a database; youSELECT
from a table - I usually start withuse dbname;
thenshow tables;
– steeldriver
Jul 18 '18 at 22:40
Right. Actually that's a type-o. I meant from table.
– j450n
Jul 19 '18 at 0:07
add a comment |
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.
command-line postgresql sql
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.
command-line postgresql sql
command-line postgresql sql
edited Jul 18 '18 at 22:37
Sergiy Kolodyazhnyy
75.5k9156330
75.5k9156330
asked Jul 18 '18 at 22:12
j450nj450n
1615
1615
Documentation
– TonyB
Jul 18 '18 at 22:33
1
You don'tSELECT
from a database; youSELECT
from a table - I usually start withuse dbname;
thenshow tables;
– steeldriver
Jul 18 '18 at 22:40
Right. Actually that's a type-o. I meant from table.
– j450n
Jul 19 '18 at 0:07
add a comment |
Documentation
– TonyB
Jul 18 '18 at 22:33
1
You don'tSELECT
from a database; youSELECT
from a table - I usually start withuse dbname;
thenshow tables;
– steeldriver
Jul 18 '18 at 22:40
Right. Actually that's a type-o. I meant from table.
– j450n
Jul 19 '18 at 0:07
Documentation
– TonyB
Jul 18 '18 at 22:33
Documentation
– TonyB
Jul 18 '18 at 22:33
1
1
You don't
SELECT
from a database; you SELECT
from a table - I usually start with use dbname;
then show tables;
– steeldriver
Jul 18 '18 at 22:40
You don't
SELECT
from a database; you SELECT
from a table - I usually start with use dbname;
then show tables;
– steeldriver
Jul 18 '18 at 22:40
Right. Actually that's a type-o. I meant from table.
– j450n
Jul 19 '18 at 0:07
Right. Actually that's a type-o. I meant from table.
– j450n
Jul 19 '18 at 0:07
add a comment |
3 Answers
3
active
oldest
votes
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)
add a comment |
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?
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 :)
– WinEunuuchs2Unix
Jul 18 '18 at 23:03
@WinEunuuchs2Unix Hehe, I already have bothPostgres
andSqlite3
installed.
– Sergiy Kolodyazhnyy
Jul 18 '18 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.
– Sergiy Kolodyazhnyy
Jul 18 '18 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 '18 at 0:09
@j450n Can you provide the exact command you're trying ? Any errors reported ?
– Sergiy Kolodyazhnyy
Jul 19 '18 at 0:10
|
show 12 more comments
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:
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "89"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2faskubuntu.com%2fquestions%2f1057393%2fhow-to-execute-sql-statement-from-command-line%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
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)
add a comment |
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)
add a comment |
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)
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)
edited Jul 18 '18 at 22:46
answered Jul 18 '18 at 22:33
WinEunuuchs2UnixWinEunuuchs2Unix
48.1k1194186
48.1k1194186
add a comment |
add a comment |
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?
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 :)
– WinEunuuchs2Unix
Jul 18 '18 at 23:03
@WinEunuuchs2Unix Hehe, I already have bothPostgres
andSqlite3
installed.
– Sergiy Kolodyazhnyy
Jul 18 '18 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.
– Sergiy Kolodyazhnyy
Jul 18 '18 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 '18 at 0:09
@j450n Can you provide the exact command you're trying ? Any errors reported ?
– Sergiy Kolodyazhnyy
Jul 19 '18 at 0:10
|
show 12 more comments
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?
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 :)
– WinEunuuchs2Unix
Jul 18 '18 at 23:03
@WinEunuuchs2Unix Hehe, I already have bothPostgres
andSqlite3
installed.
– Sergiy Kolodyazhnyy
Jul 18 '18 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.
– Sergiy Kolodyazhnyy
Jul 18 '18 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 '18 at 0:09
@j450n Can you provide the exact command you're trying ? Any errors reported ?
– Sergiy Kolodyazhnyy
Jul 19 '18 at 0:10
|
show 12 more comments
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?
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?
edited Jul 19 '18 at 18:05
answered Jul 18 '18 at 22:33
Sergiy KolodyazhnyySergiy Kolodyazhnyy
75.5k9156330
75.5k9156330
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 :)
– WinEunuuchs2Unix
Jul 18 '18 at 23:03
@WinEunuuchs2Unix Hehe, I already have bothPostgres
andSqlite3
installed.
– Sergiy Kolodyazhnyy
Jul 18 '18 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.
– Sergiy Kolodyazhnyy
Jul 18 '18 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 '18 at 0:09
@j450n Can you provide the exact command you're trying ? Any errors reported ?
– Sergiy Kolodyazhnyy
Jul 19 '18 at 0:10
|
show 12 more comments
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 :)
– WinEunuuchs2Unix
Jul 18 '18 at 23:03
@WinEunuuchs2Unix Hehe, I already have bothPostgres
andSqlite3
installed.
– Sergiy Kolodyazhnyy
Jul 18 '18 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.
– Sergiy Kolodyazhnyy
Jul 18 '18 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 '18 at 0:09
@j450n Can you provide the exact command you're trying ? Any errors reported ?
– Sergiy Kolodyazhnyy
Jul 19 '18 at 0:10
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 :)
– WinEunuuchs2Unix
Jul 18 '18 at 23:03
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 :)
– WinEunuuchs2Unix
Jul 18 '18 at 23:03
@WinEunuuchs2Unix Hehe, I already have both
Postgres
and Sqlite3
installed.– Sergiy Kolodyazhnyy
Jul 18 '18 at 23:05
@WinEunuuchs2Unix Hehe, I already have both
Postgres
and Sqlite3
installed.– Sergiy Kolodyazhnyy
Jul 18 '18 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.
– Sergiy Kolodyazhnyy
Jul 18 '18 at 23:06
@WinEunuuchs2Unix You answer is good, so no worries there. Besides, gives a different perspective. It's always good to have variety of answers.
– Sergiy Kolodyazhnyy
Jul 18 '18 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 '18 at 0:09
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 '18 at 0:09
@j450n Can you provide the exact command you're trying ? Any errors reported ?
– Sergiy Kolodyazhnyy
Jul 19 '18 at 0:10
@j450n Can you provide the exact command you're trying ? Any errors reported ?
– Sergiy Kolodyazhnyy
Jul 19 '18 at 0:10
|
show 12 more comments
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:
add a comment |
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:
add a comment |
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:
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:
answered 13 mins ago
GeneGene
1112
1112
add a comment |
add a comment |
Thanks for contributing an answer to Ask Ubuntu!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2faskubuntu.com%2fquestions%2f1057393%2fhow-to-execute-sql-statement-from-command-line%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Documentation
– TonyB
Jul 18 '18 at 22:33
1
You don't
SELECT
from a database; youSELECT
from a table - I usually start withuse dbname;
thenshow tables;
– steeldriver
Jul 18 '18 at 22:40
Right. Actually that's a type-o. I meant from table.
– j450n
Jul 19 '18 at 0:07