mething. You mean to do pure INSERT ON
> > CONFLICT DO or to modify the trigger ?
>
> No I meant that in the external program you use to fetch the data from
> the other table and reorganize the fields. Do your test there and do
> either the INSERT or UPDATE.
>
>
> --
> A
*very* useful.
>
> Don’t both of those examples hi-light flaws in the release procedures?
>
>
> And bug highlight flaws in the development process. We're human, after
> all.
>
> --
> Angular momentum makes the world go 'round.
>
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
d pg_update ran.
>
> Likewise, modification time is when we last ran an ALTER command ran, not
> when VACUUM ran (that's tracked elsewhere) or DML ran.
>
> That's all.
>
> --
> Angular momentum makes the world go 'round.
>
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
s,
>>> newschema.functios etc...
>>>
>>> This does not move data
>>>
>>> On Thu, Dec 5, 2019 at 5:07 AM Sonam Sharma
>>> wrote:
>>>
>>>> Can someone please help in schema copy in same database ?
>>>>
>>>>
t; > -- Benjamin Franklin
> >
> >
>
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin
>
>
>
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
y that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin
>
>
>
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
t; d1=# \c d2
> You are now connected to database "d2" as user "postgres".
> d2=# drop user joe;
> ERROR: role "joe" cannot be dropped because some objects depend on it
> DETAIL: owner of table tt
>
> Unfortunately, we can't see into the catalogs
gt; Angular momentum makes the world go ‘round.
>
>
> You might get away with adding
> group by vendor_key
> if it turns out you’re simply getting many copies of vendor key from that
> inner select.
> Run it alone to see.
>
>
>
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
w returned by a subquery used as an expression
>
> Can someone please enlighten me as to the error of my ways?
>
>
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
>
:)
> >
> > >
> > > Thanks,
> > > ~Ben
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
adrian.kla...@aklaver.com
>
>
>
> This message is intended only for the use of the addressee and may contain
> information that is PRIVILEGED AND CONFIDENTIAL.
>
> If you are not the intended recipient, you are hereby notified that any
>
ubject to the Tech Mahindra policy
> statement, you may review the policy at
> http://www.techmahindra.com/Disclaimer.html <
> http://www.techmahindra.com/Disclaimer.html> externally
> http://tim.techmahindra.com/tim/disclaimer.html <
> http://tim.techmahindra.com/tim/disclaimer.html> internally within
> TechMahindra.
> >
> >
>
> >
> >
> >
>
>
>
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
hindra.com/Disclaimer.html externally
> http://tim.techmahindra.com/tim/disclaimer.html internally within
> TechMahindra.
>
>
> ========
>
>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
> while in service but possibly not in this scenario. Plan B is to drop a lot
> of tables and deal with imports later.
>
> I take the above to mean that a lot of the tables are cruft, correct?
>
> >
> > I appreciate the help.
> >
>
>
> --
> Adrian Klaver
&
blication of this message without the prior written consent of authorized
>> representative of HCL is strictly prohibited. If you have received this
>> email in error please delete it and notify the sender immediately. Before
>> opening any email and/or attachments, please check them for viruses and
>> other defects.
>> ----
ectively. This can also be
> done on the pg_dump end.
>
> >
> > Thanks.
> >
> > Tiff
> >
>
> > >> --
> > >> Adrian Klaver
> > >> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
See https://www.postgresql.org/docs/current/tutorial-fk.html
On Mon, Dec 17, 2018 at 3:32 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Mon, Dec 17, 2018 at 1:20 PM Rich Shepard
> wrote:
> >
> > On Mon, 17 Dec 2018, Melvin Davidson wrote:
> >
>
')
...
...
('Transportation');
Then you can
ALTER TABLE your_table
ADD CONSTRAINT FOREIGN KEY (industry)
REFERENCES fks_for_tables(fks_valies);
On Mon, Dec 17, 2018 at 3:20 PM Rich Shepard
wrote:
> On Mon, 17 Dec 2018, Melvin Davidson wrote:
>
> > Yes, you must drop then add the revise
tell it exactly how it is. ***
> PostgreSQL centered full stack support, consulting and development.
> Advocate: @amplifypostgres || Learn: https://postgresconf.org
> * Unless otherwise stated, opinions are my own. *
>
>
>
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
d go out and find a "problem"
> and then file a complaint --- but then they'd have to recuse themselves
> from dealing with that complaint, so there's an incentive not to.
>
> regards, tom lane
>
>
--
*Melvin Davidson*
*Maj. Database
ef('functionname(list of parameters data
> types)'::regprocedure);
>
> If you want to export you may use the -t and -o switches.
>
> Regards
> Charles
>
>
>
>
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
t
need a way to do so quietly and painlessly.The truth is absolute and cannot
be changed.Perception is not the truth.Flerp!*
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
On Mon, Jul 30, 2018 at 6:21 PM, Andres Freund wrote:
> Hi,
>
> On 2018-07-30 17:21:25 -0400, Melvin Davidson wrote:
> > * >it has never been the case that relhaspkey meant that the table
> > *currently* has a primary key. *
>
> > *Hmmm, I guess it's a lot hard
key after vacuum"rather than just dropping a column that has
existed from version 7.2.So now I guess the policy is break code instead of
fix documention.That meakes sense...NOT!*
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
On Mon, Jul 30, 2018 at 10:31 AM, Tom Lane wrote:
> Melvin Davidson writes:
> > In the release notes for Version 11 Beta, under changes, I see these
> scary
> > remarks:
> > Remove relhaspkey column from system table pg_class (Peter Eisentraut)
> > Applications
a column is fine, but dropping columns that breaks code is
ridiculous.
Please restore that column before the final release!
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
m/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
> This sounds like somebody looking for a solution when they don't yet know
what the problem is.
+1
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
On Thu, Jul 19, 2018 at 8:54 PM, Adrian Klaver
wrote:
> On 07/19/2018 05:43 PM, Melvin Davidson wrote:
>
>>
>>
>>
>
>>
>> > Then again people might use shared, university or library computers
>> Would you please be so kind as to inform us which
On Thu, Jul 19, 2018 at 8:09 PM, Ken Tanzer wrote:
> On Thu, Jul 19, 2018 at 11:35 AM Melvin Davidson
> wrote:
>
>> >> Politely tell them to buy some of the many well written books that are
>> available on these very topics...
>> >Fair enough but what about t
>> Politely tell them to buy some of the many well written books that are
available on these very topics...
>Fair enough but what about those that cant afford it? I think us in the
Western World tend to forget that by >far the majority of users cant afford
a latte from Starbucks let alone a 60.00
y, You may find find some are really not
needed.*
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
SELECT n.nspname as schema,
i.relname as table,
i.indexrelname as index,
of the source schema abd
send me the call to clone schema that you are using.
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
-- Function: public.clone_schema(text, text, boolean)
-- DROP FUNCTION public.clone_schema(
the
problem without testing yourself.
On Mon, Jul 9, 2018 at 2:56 PM, Adrian Klaver
wrote:
> On 07/09/2018 09:49 AM, Melvin Davidson wrote:
>
>
>>
>> On Mon, Jul 9, 2018 at 12:32 PM, DiasCosta > <mailto:diasco...@diascosta.org>> wrote:
>>
>> Hi
on "bilhetica_logic_schema.idx_unq_cod_operador_aml" does not
exist
This is not related to the clone_schema function. It looks like you may
have corruption in your syste catalogs,
Try reindexing your system_catalogs.
REINDEX VERBOSE SYSTEM ;
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
then I will need you to do a
pgdump -F p -t public.t_cpuinfo
and send the output to me.
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
-- Function: public.clone_schema(text, text, boolean)
-- DROP FUN
2018-07-07 4:32 GMT-04:00 DiasCosta :
> Hi Melvin,
>
> Thank you.
>
> Dias Costa
>
> On 04-07-2018 23:38, Melvin Davidson wrote:
>
>
>
> On Wed, Jul 4, 2018 at 2:48 PM, DiasCosta wrote:
>
>> Hi Melvin,
>>
>> I'm new to clone_schema.
>>
ou can use the
attached script and add 'AND a.rolname = 'xxx' to the WHERE clause.
Then as a superuser you can use ALTER TABLE xyz OWNER TO new_owner for each
table found.
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitat
>As I said I'm writing the client application with libpq/ODBC. How will I
get
>the results?
Igor,
You DO NOT need libpq/ODBC . Just use the count option of grep (-c).
If it is greater than zero, then send a mail to yourself and/or attach the
log for review.
--
*Melvin Davidson*
*Maj. Da
gt;
> czw., 5 lip 2018 o 16:02 Melvin Davidson
> napisał(a):
>
>>
>>
>> On Thu, Jul 5, 2018 at 1:49 AM, Łukasz Jarych wrote:
>>
>>> From link function is not working.
>>>
>>
>> There is no " From link" in PostgreSQL,
>> Woul
On Thu, Jul 5, 2018 at 1:49 AM, Łukasz Jarych wrote:
> From link function is not working.
>
There is no " From link" in PostgreSQL,
Would you please be more specific. Please provide a working example.
>The folks that wanted transactional ALTER SEQUENCE might disagree:):
Ah, so you mean the previous version was not working or sufficient?
https://www.postgresql.org/docs/9.6/static/sql-altersequence.html
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration
On Wed, Jul 4, 2018 at 6:48 PM, Adrian Klaver
wrote:
> On 07/04/2018 03:38 PM, Melvin Davidson wrote:
>
>>
>>
>> On Wed, Jul 4, 2018 at 2:48 PM, DiasCosta > <mailto:diasco...@diascosta.org>> wrote:
>>
>> Hi Melvin,
>>
>> I'
chema text,
dest_schema text,
include_recs boolean)
RETURNS void AS
$BODY$
-- Initial code by Emanuel '3manuek'
-- Last revision 2017-04-17 by Melvin Davidson
-- Added SELECT REPLACE for schema views
--
-- This function will clone all sequences, tables, indexes, rules, triggers,
On Wed, Jul 4, 2018 at 2:27 PM, Łukasz Jarych wrote:
> Hi Melvin,
>
>
>
> Best,
> Jacek
>
>
> wt., 3 lip 2018 o 15:34 Melvin Davidson napisał(a):
>
>>
>>
>>
>>> ERROR: BŁĄD: you cannot add to column "TopoToVersion_ID"
>I'm writing a client in C++ with libpq. So I will have to do a lot of
polling .
Can't you just run a cron job?
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
>Unfortunately I'm stuck with 9.1.
Have you thought about just setting *log_statement = 'ddl'* in
postgresql.conf
and just greping the log for CREATE and ALTER?
ma(
source_schema text,
dest_schema text,
include_recs boolean)
RETURNS void AS
$BODY$
-- Initial code by Emanuel '3manuek'
-- Last revision 2018-07-03 by Melvin Davidson
-- Added ' OVERRIDING SYSTEM VALUE' for INSERT records
--
-- This function will clone all sequences, tables, inde
.
>> > > HINT: Maybe you wanted to point to column "
>> "audit_sq.last_value"?
>> > > QUERY: SELECT last_value, max_value, start_value, increment_by,
>> > min_value,
>> > > cache_value, log_cnt, is_cycled, is_called FRO
d its attachments without reading them
> or saving them to disk. Thank you.
>
> What I do NOT want are all the millions (not literally but it feels like
it J) of functions we have.
It sounds like you would best be served by installing pg_extractor. In
essence, it is a super flexible vers
ate,
query,
wait_event_type,
query_start,
current_timestamp - query_start as duration
FROM pg_stat_activity
WHERE pg_backend_pid() <> pid
ORDER BY 1,
datname,
query_start;
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
On Wed, Jun 20, 2018 at 1:28 PM, Francisco Olarte
wrote:
> Melvin:
>
> Maybe old eyes, but ...
>
> On Wed, Jun 20, 2018 at 7:03 PM, Melvin Davidson
> wrote:
> >>Is there a way to be notified on the CREATE TABLE execution?
> > Here is sample code that will
ddl()
OWNER TO postgres;
CREATE EVENT TRIGGER table_created_dropped ON DDL_COMMAND_END
EXECUTE PROCEDURE public.fn_notify_ddl();
ALTER EVENT TRIGGER table_created_dropped
OWNER TO postgres;
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command
alysis)? or do I need some other
> solutions or any extension?
>
>
> Thanks
>
> On Thu, Jun 14, 2018 at 3:36 PM, Melvin Davidson
> wrote:
>
>>
>>
>> On Thu, Jun 14, 2018 at 6:30 PM, Adrian Klaver > > wrote:
>>
>>> On 06/14/2018 02
gt;> Thanks in advance.
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
In addition to Ravi's and Adrian's questions:
What is the hardware configuration?
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
rement is to get the server shutdown time whenever that event
>occurs and insert into a table dynamically!! Is it possible?
Since you have not stated your O/S, we cannot provide a specific solution.
However, I can suggest that you use
a command line script to grab the time from the log and the
org/PostgreSQL-general-
> f1843780.html
>
>
Look in your postgresql log. You should see a line similar to this"
2018-06-04 09:21:27 EDT LOG: database system is shut down
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
so that instead of one job finishing quickly, they both have to take turns
waiting for needed information. That takes
a lot longer,
Try this, Select a table that has a lot of rows, ideally 1M+. Then start a
query with a WHERE clause and see how long
it takes. Then submit the same query fro
e you need an index on this_.tarih, since
it is doing a sequential scan when it should be using an index.*
*Also, your main problem is that when you have two exact same queries
executing at the same time, they will cause contention in*
*the disk, and neither one will make much progress.*
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
On Mon, May 28, 2018 at 11:08 PM, Torsten Förtsch
wrote:
> Hi,
>
> I am absolutely sure a certain value of one of my ENUM types is not used
> in the entire database. Now I am asked to drop that value. Unfortunately,
> there is no ALTER TYPE DROP VALUE.
>
> On my development box I tried
>
>
gt; Best,
> Moohanad
>
> I want to access the information about queries and statistics
*I strongly recommend you install PgBadger, which analyzes the log file to
provide you the information you need.*
*https://sourceforge.net/projects/pgbadger/
<https://sourceforge.net/projects/pgbadger/
n
a table
Another good use for triggers is to maintain customer balance..EG: An
INSERT, UPDATE or DELETE involving a customer payment
(or in the case of banks (deposit or withdrawals) would automatically
maintain the balance in the customer master record.
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
>> Thanks
>>
>> Phil
>>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
*I would start by changing*
* RETURNS substance*
*to*
*RETURNS RECORD*
*Note: you might also conside using RETURNS TABLE(...)*
*https://www
On Sun, May 6, 2018 at 10:33 PM, Melvin Davidson <melvin6...@gmail.com>
wrote:
>
>
> On Sun, May 6, 2018 at 10:19 PM, Igor Korot <ikoro...@gmail.com> wrote:
>
>> Hi, ALL,
>>
>> Is there an easy way to add the schema to the following query:
>>
>
t;
>
>Is there an easy way to add the schema to the following query:
You mean like this?
SELECT u.usename,
*n.nspname AS schema*
FROM pg_class c
*JOIN pg_namespace n ON n.oid = c.relnamespace,*
pg_user u
WHERE u.usesysid = c.relowner
AND relname = ?
--
*Melvin
ement
>> for it
>>
>> pgBadger has always been my goto tool for that:
>> https://github.com/dalibo/pgbadger
>>
>>
> There are some statistic per tables: .. select * from pg_stat_user_tables,
> indexes: select * from pg_stat_user_indexes, and databases: sel
s obviously not what is
actually in production. So if you copy the actual function from the system
that works *
*and use that to frop and rebuild the function on the system that fails,
does that resolve the problem? I suspect there is an error in the failing
system that you have overlooked.*
-
many parameters specified for RAISE *
*https://www.postgresql.org/docs/9.6/static/plpgsql-errors-and-messages.html#PLPGSQL-STATEMENTS-RAISE
<https://www.postgresql.org/docs/9.6/static/plpgsql-errors-and-messages.html#PLPGSQL-STATEMENTS-RAISE>"
Inside the format string, % is replaced by the string representation of the
next optional argument's value"*--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
create the schema/tables that are important to you and you store *
*and retrieve the information that is important to you. When PostgreSQL is
first installed it cannot possibly have any tables/information that you*
*would consider important. *
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
ian Klaver
>> adrian.kla...@aklaver.com
>>
>
>
*Vikas,*
*Presuming the the real "master" will have additional records/rows inserted
in the tables,*
*if you run ANALYZE on the database(s) in both "masters", then execute the
following query *
*on both, whic
lace business logic in the
database or the application is strictly on a case by case basis. *
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
> they are optimisation fences:
> https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f184378
> 0.html
>
>
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist
the host parameter It is not ignoring
your entry, you simply have not entry for the laptop you are connecting
from,So simply add the following:*
*hostall all 192.168.0.2/32
ident * <- note, you may have to change the authentication
method depending on your environment
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
gt;> Data
> >> Editor which help users create, develop and execute queries as well
> >> as edit
> >> and adjust the code to their requirements in a convenient and
> >> user-friendly
> >> interface./
> >
> > (snip...)
> >
> &g
On Mon, Mar 19, 2018 at 1:17 PM, Jimmy Augustine <jimmy.august...@enyx.fr>
wrote:
> Hi,
>
> I used this command and I found the same value in total_size column.
>
> 2018-03-19 18:01 GMT+01:00 Melvin Davidson <melvin6...@gmail.com>:
>
>>
>>
>>
e_pretty(pg_database_size(datname))as
size_pretty,pg_database_size(datname) as size, (SELECT
pg_size_pretty (SUM( pg_database_size(datname))::bigint)FROM
pg_database) AS total, ((pg_database_size(datname) / (SELECT SUM(
pg_database_size(datname)) FROM
pg_database) ) * 100)::numeric(6,3) AS pct FROM pg_database ORDER BY
datname;*
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
ompts,
surround the text with single quotes.)*
*By default, \prompt uses the terminal for input and output. However, if
the -f command line switch was used, \prompt uses standard input and
standard output.*
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
ulation (IE:
pgsql-general@lists.postgresql.org) for comment BEFORE said changes are
implemented.
On Thu, Mar 15, 2018 at 11:23 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Melvin Davidson <melvin6...@gmail.com> writes:
> > Yes, Stephen, I certainly understand making changes to system
On Thu, Mar 15, 2018 at 10:14 PM, Stephen Frost <sfr...@snowman.net> wrote:
> Greetings Melvin,
>
> * Melvin Davidson (melvin6...@gmail.com) wrote:
> > >I guess with your query I can figure out which connection holds a lock,
> > but it seems I cannot correlate
emove/replace another field. Hopefully they will refrain from doing
so in the future, as it breaks queries and applications.*
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
(c.pid = l1.pid and not l1.granted)
LEFT JOIN pg_locks l2 on (l1.relation = l2.relation and l2.granted)
LEFT JOIN pg_stat_activity l ON (l2.pid = l.pid)
LEFT JOIN pg_stat_user_tables t ON (l1.relation = t.relid)
WHERE pg_backend_pid() <> c.pid
ORDER BY datname,
query_start;
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
On Tue, Mar 13, 2018 at 1:47 PM, Melvin Davidson <melvin6...@gmail.com>
wrote:
>
>
>
>> Thank you Melvin, I forgot to mention I've already found your script
>> before I asked here, but I didn’t think it was robust enough (please don't
>> offend :-). Particularly,
n it is failing is
because, once again, the catalogs have been changed. In this case the
structure of sequences.
I am trying to find a workaround. I will get back to you when I do.
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
On Tue, Mar 13, 2018 at 10:02 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Tue, Mar 13, 2018 at 6:47 AM, Melvin Davidson <melvin6...@gmail.com>
> wrote:
>
>> On Mon, Mar 12, 2018 at 2:26 PM, Steven Roth <st...@rothskeller.net>
>> wrote:
tion_name ( arguments )
*Please also note that it is very helpful if you specify PostgreSQL version
and O/S when submitting to this list.*
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
ies all
sequences, tables, indexes, rules, triggers, data(optional), views &
functions from any existing schema to a new schema.Then just call the
function.SAMPLE CALL:SELECT clone_schema('public', 'new_schema', TRUE);Use
FALSE if you do not want to copy data.*
--
*Melvin Davidson*
*Ma
pg_database_size(datname) as size,
(SELECT pg_size_pretty (SUM( pg_database_size(datname))::bigint)
FROM pg_database) AS total,
(pg_database_size(datname) / (SELECT SUM(
pg_database_size(datname))
FROM pg_database) ) *
100::numeric(6,3) AS pct
FROM pg_database d
JOIN pg_authid a ON a.oid = datdba
WHERE datname LIKE '%$DBNAME%'
ORDER BY datname;
_EOF_
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
idsonMaj. Database & Exploration SpecialistUniverse Exploration
Command – UXC*
Employment by invitation only!
-- Function: public.clone_schema(text, text, boolean)
-- DROP FUNCTION public.clone_schema(text, text, boolean);
CREATE OR REPLACE FUNCTION public.clone_schema(
source_schema text,
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
>
> trying to save query as sql file in pg_admin4 but file --> save as not
exists like here:
As previously answered, you have to use the icon(s) for all options in the
query window
FYI,I
-createrole.html>https://www.postgresql.org/docs/current/static/sql-grant.html
<https://www.postgresql.org/docs/current/static/sql-grant.html>*--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
ould you perhaps elaborate on how a surrogate key allows one to insert
> garbage into the table? I'm afraid I don't quite get what you're saying.
>
>
> If your only unique index is a synthetic key, then you can insert the same
> "business data" multiple times with different synthetic keys.
>
>
> --
> Angular momentum makes the world go 'round.
>
* If you are going to go to the trouble of having a surrogate/synthetic
key, then you may as well have a primary key , which is much better. *
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
On Thu, Mar 1, 2018 at 12:22 PM, Ron Johnson <ron.l.john...@cox.net> wrote:
> On 03/01/2018 11:03 AM, Melvin Davidson wrote:
>
>
>
> On Thu, Mar 1, 2018 at 11:51 AM, Ron Johnson <ron.l.john...@cox.net>
> wrote:
>
>> On 03/01/2018 10:37 AM, Vick Khera wrote
| en_US.UTF-8 | en_US.UTF-8 |
> postgres| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>
>
> --
> Angular momentum makes the world go 'round.
>
*>$ pg_dump -Fc PROD > PROD.pgdump >$ pg_dump --globals-only postgres >
globals.sql >$ pg_dump -Fc postgre
,
> Cut the trees and you'll see there is no forest.
>
>
*> it is common practice to denormalize data without a>requirement to be
able to identify a single record *
*You may perceive that to be "common practice", but in reality it is not,
and in fact a bad one. As was previously s
, then all roles and databases
are contained
in the output file created. NOTE: restoring from the dumped file will
require rebuilding all indexes, because indexes are not dumped.
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
not always suitable to just use an integer or serial as the key,but rather
why natural unique (even multi column) keys are better. But this begs the
question, why are "developers" allowed to design database tables? That
should be the job of the DBA! Atthe very minimum, the DBA should be
revi
-
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
As Adrian pointed out, by definition, PK's create a constraint which are
NOT NULLABLE;
Here is the SQL to check for tables with no primary key.
*SELECT n.nspname,c.relname as table,
c.reltuples::bigint FROM pg_class c JOIN pg_namespace n ON (n.oid
=c.relnamespace ) WHERE relkind = 'r' AND relhaspkey =
FALSEORDER BY n.nspname, c.relname;*
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
correction to that.*
*https://www.postgresql.org/docs/10/static/runtime-config-connection.html
<https://www.postgresql.org/docs/10/static/runtime-config-connection.html>by
defaultdb_user_namespace = off *
*However, if set = on, then " you should create users as username@dbname "
which makes role names specific to each database.*
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
s/postgresql-backups-and-everything-you-need-to-know/>*
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
ook like the output that
> pg_dump displays.
>
> ex:
>
> GRANT ALL ON TABLE testing_cdc TO bob;
>
>
> --
> -- PostgreSQL database dump complete
> --
>
> I need a way which my script can isolate the owner's name and set it to a
> variable on its own. Same with gra
pg_class cJOIN pg_namespace n ON (n.oid =
c.relnamespace)JOIN pg_authid o ON (o.oid = c.relowner)WHERE n.nspname
not like 'pg_%' AND n.nspname not like 'inform_%' AND relkind =
'r'ORDER BY 1;*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
1 - 100 of 137 matches
Mail list logo
