|
|
Thank you so much.
Simply changed "rank" to "ranklist" and it has resolved the issue.
|
|
|
|
|
Perhaps you should get together with your classmate / colleague:
I need to generate rank in mysql v5.7.31[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I am familiar with SQL Server Activity Monitor. What all I know about it, have been learned by my own experinced. I decided to learn all details about it, and googled many keywords but most of results were a brief. I did not find a comprehensive article or document in detail.
Can anyone send me a well documented link?
For example the task states vary in different articles; Or what head blocker value is (the concept is clear but not the value; I thouth it is SPID but is not)?
SignatureNotFoundException
|
|
|
|
|
After some extra research I found a complete list of state from different sources:
Running - The session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. For more information, see Using Multiple Active Result Sets (MARS).
Background - The session is running a background task, such as deadlock detection.
Rollback - The session has a transaction rollback in the process.
Pending - The session is waiting for a worker thread to become available.
Runnable - The session's task is in the runnable queue of a scheduler while waiting to get a time quantum.
Spinloop - The session's task is waiting for a spinlock to become free.
Suspended - The session is waiting for an event, such as I/O, to complete.
Sleeping - A session in the sleeping state means a client connection without an active query.
Dormant - SQL Server is resetting the session. Same as Sleeping, except Dormant also indicates that the SPID has been reset after completing an RPC event. (Replication SPIDs show "DORMANT" when waiting.)
For more information about other columns see:
SQL Server Activity Monitor
I hope it helps others.
SignatureNotFoundException
|
|
|
|
|
I need to get all database names, using SQL script only. I have tried:
SELECT datname FROM pg_database WHERE datistemplate = false and seem to work. I got:
mydb1
mydb2
postgres
mybd1 and mydb2 have been created by me. So far, so good. Now, I need to find all tables under every database, using SQL script only. Is it possible ?
|
|
|
|
|
|
Thank you Victor. Obviosly, I don't understand something: if I do:
create database mydb3
and then I want to create a table under that db, how can I do that ?
Because any (dummy) table I create, seem to be any no database inside ...
P.S. I saw (and tried) that link ... thanks anyway.
|
|
|
|
|
|
Hello Victor. As far as I understood, "use ..." is not valid command in Postgre SQL ... I have tried that.
|
|
|
|
|
_Flaviu wrote: As far as I understood, "use ..." is not valid command in Postgre SQL
I know.
But did you try the
\connect
|
|
|
|
|
That command is a Postgre SQL cmd line command, I cannot run it as SQL script, I have tried that.
Thank you.
|
|
|
|
|
If you are not using psql then how are you running your SQL?
|
|
|
|
|
Is about a C++ ODBC class which can run SQL scripts only.
|
|
|
|
|
|
SELECT TABLE_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_CATALOG=$dbname AND T.TABLE_SCHEMA='public';
|
|
|
|
|
Get create table statements for a database under a schema.
<pre lang="SQL"> SELECT TABLE_NAME, 'CREATE TABLE '||TABLE_NAME|| ' ('||STRING_AGG(CONCAT( C1, C2, C3, C4, C5, C6 ),', ')||')' AS QUERY FROM ( SELECT C.TABLE_NAME, '"'||C.COLUMN_NAME||'"' || ' ' || CASE WHEN DATA_TYPE='ARRAY' THEN LTRIM(UDT_NAME,'_')||'[]' ELSE DATA_TYPE END AS C1, CASE WHEN CHARACTER_MAXIMUM_LENGTH > 0 THEN '(' || CHARACTER_MAXIMUM_LENGTH || ')' END AS C2, CASE WHEN NUMERIC_PRECISION > 0 AND NUMERIC_SCALE < 1 THEN NULL END AS C3, CASE WHEN NUMERIC_PRECISION > 0 AND NUMERIC_SCALE > 0 THEN NULL END AS C4, CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' END AS C5, CASE WHEN COLUMN_DEFAULT IS NOT NULL AND COLUMN_DEFAULT NOT LIKE 'nextval%' THEN ' DEFAULT' END || ' ' || REPLACE(COLUMN_DEFAULT, '::CHARACTER VARYING', '') AS C6 FROM INFORMATION_SCHEMA.COLUMNS C, INFORMATION_SCHEMA.TABLES T WHERE C.TABLE_CATALOG='tpch' AND T.TABLE_CATALOG='tpch' AND T.TABLE_SCHEMA='public' AND C.TABLE_NAME=T.TABLE_NAME AND C.TABLE_SCHEMA='public' AND T.TABLE_TYPE IN ('BASE TABLE') ORDER BY C.TABLE_NAME, C.ORDINAL_POSITION ) AS STRING_COLUMNS GROUP BY TABLE_NAME
|
|
|
|
|
|
Yes, I have tried:
SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog' AND
schemaname != 'information_schema';
but this script doesn't take into account a specific database. I don't understand how is relation between database and table, in Postgre SQL ...
|
|
|
|
|
That is not what the tutorial suggests. The summary states:
Quote: Summary: in this tutorial, you will learn how to show tables in PostgreSQL using psql tool and pg_catalog schema.
|
|
|
|
|
I realize that all available tables are below existing connection, which is the default database. And the default database must be choosen at the connection time. But yes, all databases could be listed in any connection.
Thank you for all your support.
modified 16-Jan-22 5:05am.
|
|
|
|
|
How can I list all tables from a specific database in PostGre SQL ?
Lets say I have 3 databases, and I have several tables in every one each of them. How can I retrieve the table names for every database I have, using SQL command ?
With following command:
SELECT datname FROM pg_database WHERE datistemplate = false
I got:
db_name
post_flaviu
postgres
test
Now, how can I find all tables under eevry db from above ?
modified 12-Jan-22 7:16am.
|
|
|
|
|
Feeding "postgresql list tables in database" into your search engine will produce a zillion useful answers.
Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012
|
|
|
|
|
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>
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
