Re: ***SPAM*** Re: Can I get the number of results plus the results with a single query?

[Walter Dörwald]

On 16 Aug 2022, at 0:13, Rob Sargent wrote:


On 8/15/22 14:37, Perry Smith wrote:



On Aug 15, 2022, at 08:55, David G. Johnston 
 wrote:


On Monday, August 15, 2022, Perry Smith  
wrote:


I’ve been toying with row_number() and then sort by row_number
descending and pick off the first row as the total number.


Use count as a window function.


I see others are commenting after David’s update so:

Thank you David.

This seems to work for me:

SELECT count(*) OVER (), id, basename, sha1 FROM dateien WHERE
(lower(ext) in ( 'pxd' ) and ftype = 'file') ORDER BY sha1;


This has, e.g. 73, in the first column for all of the rows.

Any comparative timing statistics on that?  Especially on more than 
73 records returned, because with that few just grab them all and get 
size() or length of what ever collection mechanism you're playing 
with.


I tried with a larger table (739951 records):

```
select e.* from email.email e;
```

takes 50 seconds (as displayed by TablePlus).

```
select count(*) over (), e.* from email.email e;
```

takes 58 seconds.

And doing `select count(*) from email.email e;` takes 2-3 seconds.

Note that in this example the records where fetched over the internet 
(i.e. not from a local Postgres installation) and there is no `where` 
condition that must be evaluated repeatedly, so other variants might 
give better numbers.


Servus,
   Walter

Re: Getting the table ID

[Walter Dörwald]

On 19 Jul 2022, at 5:10, Igor Korot wrote:


Hi, guys,

In the database theory each table is identified as 
"schema_name"."table_name".


When I tried to look at how to get the table id inside the PostgreSQL,
I saw that I needed to look at the pg_class table.

SELECT oid FROM pg_class WHERE relname = "table_name";

However that query will give a non-unique table id (see the first 
sentence).


So how do I get the table id based on the "schema_name.table_name"?

There is a pg_namespace table - is this where the schema should come 
from?

If yes - how?
Looking at that table I don't see any unique fields...
Or is this something that is hidden?

In fact I'm trying to run following query:

SELECT c.oid FROM pg_class c, pg_namespace nc WHERE nc.oid =
c.relnamespace AND c.relname = ? AND nc.nspname = ?;

from my ODBC based program, but it returns 0 rows on SQLFetch.

I know PostgreSQL does not use '?' for query parameters
but I thought that since its an ODBC everything should work.

Nevertheless, all bindings were successful, but now rows are returned.

Is this query correct?

Thank you.


That's more or less the same query that I am using:

select
r.oid as oid,
n.nspname || '.' || r.relname as name
from
pg_catalog.pg_namespace n
join
pg_catalog.pg_class r on n.oid = r.relnamespace
where
(r.relkind = 'r') and
(n.nspname not like 'pg_%') and
(n.nspname != 'information_schema') and
(n.nspname = 'email') and
(r.relname = 'emailhistory')

Maybe your problem has to to with uppercase/lowercase schema and/or 
table names?


Servus,
   Walter

Re: Get the table creation DDL

[Walter Dörwald]

On 10 Jul 2022, at 17:40, Igor Korot wrote:


Hi,
Is there a query I can execute that will give me CREATE TABLE() 
command

used to create a table?

Thank you.


I am using the following query for that:

```sql
select
a.attname,
a.attnum,
a.attnotnull,
pg_catalog.format_type(a.atttypid, a.atttypmod) as column_type,
case
		when d.adrelid is not null then pg_catalog.pg_get_expr(d.adbin, 
d.adrelid)

else null
end as default_value
from
pg_catalog.pg_class c
join
	pg_catalog.pg_attribute a on c.oid = a.attrelid and a.attnum > 0 and 
not a.attisdropped

join
pg_catalog.pg_namespace n on c.relnamespace = n.oid
left outer join
	pg_catalog.pg_attrdef d on d.adrelid = a.attrelid and d.adnum = 
a.attnum and a.atthasdef

where
c.relname = %s and
n.nspname = %s
order by
attnum
;
```

and the following Python code to format the `create table` statement:

```python
sql = f"create table {schema}.{name}\n"
sql += f"(\n"
for (last, column) in islast(cursor):
column_term = "" if last else ","
notnull = " not null" if column.attnotnull else ""
	default = f" default {column.default_value}" if column.default_value is 
not None else ""
	sql += f"\t{column.attname} 
{column.column_type}{default}{notnull}{column_term}\n"

sql += f");"
return sql
```

Servus,
   Walter