|
Yes, I did. But I haven't found any SQL to tell me the tables for a specific database only.
|
|
|
|
|
You can get Postgres to tell you how to do that using the -E (display hidden queries) option to psql. So for example
$ psql -E -l
********* QUERY **********
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
<hr />
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+-----------+-------------+-------------+-----------------------
template0 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | ebacon | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 |
(3 rows) Likewise for the
\dt<dt> command inside psql. Then all you need to do is to figure out how to combine both queries into one.<br />
<div class="signature">Keep Calm and Carry On</div></dt>
|
|
|
|
|
Thank you. That query retrieve all databases. And for all tables from a database only ?
|
|
|
|
|
try
psql -E test -c "\dt". You can then take that output and combine with the previous one to get a listing of all tables across all databases.<br />
<div class="signature">Keep Calm and Carry On</div>
|
|
|
|
|
"get a listing of all tables across all databases"
That I already did. But doesn't help me, because I need the tables below a specific database, not all tables from all databases.
|
|
|
|
|
|
Thank you Richard. I have tried before, and now, the SQL from that page:
SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';
And as result I got all tables, from ALL databases.
Untitled6 — ImgBB[^]
|
|
|
|
|
But that is exactly what that command is supposed to do. You need to follow the instructions from the top of the page.
|
|
|
|
|
I am pretty close. And what SQL command should I use to select the database, and after that to call SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema' ?
I have tried:
USE database my_database
but seem to not work:
ERROR: syntax error at or near "USE";
Error while executing the query
You know, I need to all this programatically, using SQL commands, not using Postgre tools.
modified 12-Jan-22 12:30pm.
|
|
|
|
|
Sorry, I do not have a system available to test this. However, I do not see any instruction concerning a USE statement in the tutorial on that page.
|
|
|
|
|
1. Using SQL Query
To show the list of tables with the corresponding schema name, run this statement:
SELECT * FROM information_schema.tables;
or in a particular schema:
SELECT * FROM information_schema.tables WHERE table_schema = 'schema_name';
2. Using psql
To list all tables:
In all schemas:
\dt *.*
In a particular schema:
\dt schema_name.*
|
|
|
|
|
I have installed a WampServer, and when I wanted to try it, seem that localhost is not available:
Untitled4 — ImgBB[^]
I noticed that mysql and mariadb services are running:
Untitled2 — ImgBB[^]
I only started localhost from here:
Untitled3 — ImgBB[^]
Also, other options from that menu are not working either: PHPMyAdmin, etc. What I should do to use MySQL database ? In fact, I only need to setup a ODBC source for MySQL database from WampServer, that's all.
modified 7-Jan-22 2:58am.
|
|
|
|
|
The web server is "Apache" - make sure you start that, then the web-based admin things should work (assuming it is configured correctly).
|
|
|
|
|
Ohh, I tried that before, but didn't start it:
Untitled5 — ImgBB[^]
I'll check the log to see why.
|
|
|
|
|
How can I get the number of the tables from a sqlite3 database ?
I downloaded from here: SQLite Sample Database And Its Diagram (in PDF format)[^]
chinook.db
But when I try to get all tables from this database:
SELECT * FROM sqlite_schema WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
I got:
no such table: sqlite_schema
I also tried to create a database on my own:
SQLite::Database db("C:\\Temp\\mydb.bd3", SQLite::OPEN_READWRITE | SQLite::OPEN_CREATE);
db.exec(_T("CREATE TABLE sqlite_schema(type TEXT, name TEXT, tbl_name TEXT, rootpage INTEGER, sql TEXT)"));
db.exec(_T("PRAGMA foreign_keys = ON"));
and
db.exec(_T("DROP TABLE IF EXISTS master"));
db.exec(_T("CREATE TABLE master(masterid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT)"));
db.exec(_T("DROP TABLE IF EXISTS app"));
db.exec(_T("CREATE TABLE app (appid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT)"));
db.exec(_T("DROP TABLE IF EXISTS device"));
db.exec(_T("CREATE TABLE device (deviceid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, masterid INTEGER, appid INTEGER, name TEXT, FOREIGN KEY(masterid) REFERENCES master(masterid), FOREIGN KEY(appid) REFERENCES app(appid))"));
but when I try the same SQL:
SELECT * FROM sqlite_schema WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
I got the same error:
no such table: sqlite_schema
How can I assure to retrieve all tables from a db3 (db) file ?
|
|
|
|
|
The name has been changed to "sqlite_schema" as of version 3.33.0. The older name "sqlite_master" is still accepted as an alias for backwards compatibility. The FAQ is for version 3.33.0. You must be using an older version of SQLite.
Assuming you're not using 3.33.0 or later, try using the sqlite_master table instead.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks a lot. That was the issue.
I have a small request: can you share which structure has this table, sqlite_master ?
I need a database_id, table_id, or appropriate.
|
|
|
|
|
|
Hello all
i have stored procedure running fine in MS sql 2012 but when i run it in ms sql 2018 i got this error message:
The data types datetime and time are incompatible in the add operator.
this is the line of the error :
(CONVERT(datetime, shfts.Date) + SftpStartTime) as SftStartDateTime,
case when ((CONVERT(datetime, shfts.Date) +SftpEndTime)
> (CONVERT(datetime, shfts.Date) + SftpStartTime) )
|
|
|
|
|
I think you'll have to use the DATEADD function instead of just the addition operator.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
Your problem is occurring because you cannot add time to a date using columns or variables - although you can do it with literals - but only if you convert the date to a datetimeThis is valid - but doesn't help you I know, just putting it out there
select convert(datetime, shftsDate) + '16:12:00' SftStartDateTime is obviously just a time so you can't use the DateAdd function - if you try you will get another error Quote: Argument data type time is invalid for argument 2 of dateadd function.
Converting that time into "seconds from midnight" could be an option, but that is a real PITA - Google for it and see how awful the solutions are 
The solution is to ensure that both operands of are of the same type. E.g.
select convert(datetime, @shftsDate) + convert(datetime, @SftpStartTime);
|
|
|
|
|
|
Check your database compatibility level - it's likely that you had it set to 100 on your SQL Server 2012 instance, but it's set to 110 or higher on your SQL Server 2016/2019 instance.
(NB: There is no SQL Server 2018 version.)
As a temporary workaround, you may be able to set the compatibility level back to 100. But this will prevent you from using any features introduced after SQL Server 2008.
The correct fix would be to change your code to use DATEADD instead:
DATEADD(second, DATEDIFF(second, 0, SftpStartTime), shfts.Date) As SFtStartDateTime,
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Is this app like 3rd party cookies?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
