Best options for new PG instance

Hi: I'm going to be requesting a PG DB instance (v9.6.7) from an IT dept in a large corp setting. I was wondering if anyone could comment on the pros/cons of getting this put on a virtual machine vs hard metal ? Locally mounted disk vs nfs ? Thanks !

Can I grant permissions to specific set of uids (linux) ?

Hi: I'd like to grant select, insert, update, delete to a table for a specific set of uids (linux). All others get select only. Can the DB authenticate the current linux user and grant access based on the fact that they are logged in ()IOW, no passwords ?Is this possible ? If so, how ? Than

hardcode password in connect string

Hi: PG v9.5.2 on RHEL I like to use an alias to connect to my favorite DBs but don't like to enter passwords. I used to just disable passwords (working behind a firewall), but this one is different. I see nothing in the interactive connect string where I can enter the password... psql -h theho

Re: hardcode password in connect string

docs/9.0/static/libpq-pgservice.html > > Jim > > > On April 13, 2018 2:43:01 PM EDT, David Gauthier > wrote: >> >> Hi: >> >> PG v9.5.2 on RHEL >> >> I like to use an alias to connect to my favorite DBs but don't like to >> enter passwords

Backup Strategy Advise

Hi: I need some advise on how best to backup a PG DB. PG 9.5.2 on RHEL6 The requirement is to be able to restore the DB after catastrophic failure and lose no more than the last 15 minutes worth of data. Also, we would like to be able to do the backups on-line (no down time). There is no need f

Run external command as part of an sql statement ?

Hi: At the psql prompt, I can do something like... "select \! id -nu" ...to get the uid of whoever's running psql. I want to be able to run a shell command like this from within a stored procedure. Is there a way to do this ? Thanks

Doing a \set through perl DBI ?

Hi: Is there a way to do the equivalent of a "\set foo 1" through perl dbi ? I tried... $dbh->do("\\set foo 1"); and got a syntax error Of course, I'd also have to be able to access the value of foo once its set. I'm guessing the usual way ??? (select :foo) Thanks for any help !

Re: Doing a \set through perl DBI ?

; On Wednesday, June 6, 2018, David Gauthier > wrote: > >> Hi: >> >> Is there a way to do the equivalent of a "\set foo 1" through perl dbi ? >> I tried... >> $dbh->do("\\set foo 1"); >> and got a syntax error >> >> Of

As a table owner, can I grant "grant" ?

Postgres 9.5.2 on linux Given that I am not superuser, but DO have createrole... Can I grant some other role the ability to grant access to a table I created ? For Example: I create a table called foo. As the creator/owner of this table, I seem to have the ability to grant select,insert,update,

timestamp (military) at time zone without the suffix

Hi: I would like to get the utc timestamp, 24-hr clock (military time), without the time zone suffix. Below commands were run nearly at the same time... sqfdev=> select now()::timestamp(0) ; now - 2018-07-11 15:27:12 (1 row) ...then immediately... sqfdev=> select

Re: timestamp (military) at time zone without the suffix

o store the UTC time. How d I do that ? insert into foo (dt) values (localtimestamp(0) at time zone 'utc') ??? On Wed, Jul 11, 2018 at 3:45 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wednesday, July 11, 2018, David Gauthier > wrote: > >> Hi:

Re: timestamp (military) at time zone without the suffix

updating records using "localtimestamp(0)". On Wed, Jul 11, 2018 at 4:11 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wednesday, July 11, 2018, David Gauthier > wrote: > >> OK, the "to_char" gets rid of the timezone extension. But the

sorting/comparing column values in non-alphanumeric sorting ways ?

Hi: I have a table listing tools and tool versions for a number of different tool configurations. Something like this... create table tv (tool text, tcfg1 test, tcfg2 text, tcfg3 text, highestver text); insert into tv (tool,tcfg1mtcfg2,tcfg3) values ('tool_a','1.0.5b','1.0.10','1.0.9'); I want

Need text wrapping inside a column to avoid record wrapping (tabular output of 'select')

Hi: psql (9.6.7, server 9.1.9) on RHEL6 In order to avoid record wrapping in the tabular output of a "select" statement, I need to limit the width of certain columns. For those columns, I would like to have text wrapping so as not to lose any information (IOW, I don't want to simply truncatate

Re: Need text wrapping inside a column to avoid record wrapping (tabular output of 'select')

That does it. Good enough, despite the non-white space wrapping thing. Thanks ! On Wed, Jul 25, 2018 at 12:53 PM, Adrian Klaver wrote: > On 07/25/2018 09:40 AM, David Gauthier wrote: > >> Hi: >> >> psql (9.6.7, server 9.1.9) on RHEL6 >> >> In order to

Can Pg somehow recognize/honor linux groups to control user access ?

Hi: The title says it all. I need to be control who can gain access to a DB based on a linux user group. I can set up a generic role and password, but also want to prevent users who are not in a specific linux group from accessing the DB. For code that works with the DB, this is easy (just chmo

unorthodox use of PG for a customer

Hi Everyone: I'm going to throw this internal customer request out for ideas, even though I think it's a bit crazy. I'm on the brink of telling him it's impractical and/or inadvisable. But maybe someone has a solution. He's writing a script/program that runs on a workstation and needs to write

Re: unorthodox use of PG for a customer

anks for your interest and input everyone ! On Fri, Aug 24, 2018 at 2:39 PM Edson Carlos Ericksson Richter < rich...@simkorp.com.br> wrote: > Em 24/08/2018 15:18, David Gauthier escreveu: > > Hi Everyone: > > > > I'm going to throw this internal customer request o

nested query problem

Hi: I'm having trouble with this query... select sr.sqf_id, sr.status, fse.perl_sub_name, fse.end_datetime from sqf_runs sr, (select perl_sub_name, end_datetime from flow_step_events_view where sqf_id = sr.sqf_id order by 2 limit 1) fse where sr.userid='foo'; ERROR: invalid referenc

Re: nested query problem

atch. On Thu, Sep 6, 2018 at 5:10 PM Paul Jungwirth wrote: > On 09/06/2018 01:59 PM, David Gauthier wrote: > > I'm having trouble with this query... > > > > select sr.sqf_id, sr.status, fse.perl_sub_name, fse.end_datetime > >from > > sqf_

Re: nested query problem

Wow, I take that back. I thought there were many recs with "foo" but there wa sonly one. When I ran this against a value that actually had multiple records, it ran fine. Sorry for that. And Thanks for this query ! On Thu, Sep 6, 2018 at 5:15 PM David Gauthier wrote: > Not

Convert interval to hours

Hi: In perl/DBI, I have code that's getting me an "age" which returns something like... "-17 days -08:29:35". How can I convert that to a number of hours (as a float I would presume) ? Thanks

How can I get and handle the status of sql statements that run in plpgsql ?

Hi: psql (9.6.7, server 9.5.2) on linux How does one get the status of an sql statement executed in plpgsql? If that status is cryptic, how can that be translated to something which someone could understand? Finally, how can I effectively do a start transaction and either rollback or commit bas

Re: How can I get and handle the status of sql statements that run in plpgsql ?

ssage (raise notice) 2) rollback somehow. On Tue, Oct 2, 2018 at 5:27 PM Adrian Klaver wrote: > On 10/2/18 1:47 PM, David Gauthier wrote: > > Hi: > > psql (9.6.7, server 9.5.2) on linux > > > > How does one get the status of an sql statement executed in plpgsql? If

Re: How can I get and handle the status of sql statements that run in plpgsql ?

and rolls back ? On Wed, Oct 3, 2018 at 11:46 AM David Gauthier wrote: > Thanks Adrian and Christopher ! > > So the transaction will be rolled back automatically if there's a > problem. Got it ! > > Question: How do I detect when it is appropriate to raise notice so as

How to reformat output of "age()" function

How can I change the default output of the "age" function to be, for example, in minutes? E.g. dvdb=> select age('2019-09-11 09:00:00','2019-09-09 11:00:00'); age 1 day 22:00:00 (1 row) I want the equivalent of that time delta in minutes. Thanks in Advance

Re: How to reformat output of "age()" function

Thanks a lot! On Wed, Sep 11, 2019 at 12:34 PM Francisco Olarte wrote: > David: > > On Wed, Sep 11, 2019 at 5:39 PM David Gauthier > wrote: > > How can I change the default output of the "age" function to be, for > example, in minutes? > > E.g. > > dv

Web GUI for PG table ?

Hi: We're considering replacing a windows AccessDB based system with PG. Access was chosen because of it's GUI to its tables (looks and behaves like a SS). But performance can be volatile given the fact that the AccessDB front-ends and back-end are at different sites 1000+ miles apart. The belief

Re: Web GUI for PG table ?

Many good visualization options but I need one that runs on the web AND allows insert/update/delete records. On Thu, Sep 12, 2019 at 10:42 AM Adrian Klaver wrote: > On 9/12/19 7:08 AM, David Gauthier wrote: > > Hi: > > > > We're considering replacing a windows Acc

Securing records using linux grou permissions

Hi: psql (9.6.7, server 11.3) on linux What are the possibilities regarding restricting user access to records given this scenario. I have a DB with tables that are organized in a hierarchical way. For example, a "projects" table is the parent of >1 recs in a "domains" table (PK/FK setup), whic

Re: Securing records using linux grou permissions

Ok, thanks. I was hoping there was a way to integrate the user/permissions/groups in linux with the PG permissions functionality. On Tue, Oct 15, 2019 at 12:32 PM Michael Lewis wrote: > It sounds like you want row level security- > https://www.postgresql.org/docs/9.6/ddl-rowsecurity.html > > But

format return of "age" to hh:mm

Hi: How does one reformat the output of the "age" function to always be in terms of hours:mins. E.g. dvdb=> select age('2020-03-05 01:40:32-05','2020-03-01 21:56:05-05'); age - 3 days 03:44:27 (1 row) I want... "75:44" I'm not married to "age" If there's a better way

How can I set all constraints to be deferrable for a DB/schema

Hi: psql (9.6.7, server 11.3) (linux) Is there a way to set all constraints on all tables of a DB and/or schema to be deferrable ? Or do I have to do them one-by-one ?

How can I recreate a view in a new schema such that the view def references tables in the new schema ?

Here's an interesting one for you... psql (9.6.7, server 11.3) on linux I have 2 DBs, differnet servers/instances. I want to take all the metadata and data for a set of tables/views in the public schema of one DB and move it all over to be inside a schema of a second DB/instance. I'm using pg_du

Re: How can I recreate a view in a new schema such that the view def references tables in the new schema ?

th "myschem." in the output of pg_dump, maybe with sed or something. But even after explicitly using "public.", it didn't stick in the view def. On Thu, Mar 26, 2020 at 1:34 PM Adrian Klaver wrote: > On 3/26/20 10:16 AM, David Gauthier wrote: > > Here's an intere

Re: How can I recreate a view in a new schema such that the view def references tables in the new schema ?

ep_events fse WHERE fse.sqf_id = sr.sqf_id)); sqf=> You can see the "public." refs in the create view, but not echoed in the stored view def. On Thu, Mar 26, 2020 at 1:58 PM Adrian Klaver wrote: > On 3/26/20 10:55 AM, David Gauthier wrote: > > Thanks Adrian for th

using a common key value on both sides of a union ?

psql (9.6.7, server 11.3) on linux I want to do something like this (intentionally bad sql but will illustrate the need) select s.name,s.grade from students s where s.class='math' union select 'whole class', class_grade from all_classes where class=s.class Of course it's that "where class=s.clas

Estimated resources for a 500 connections instance (VM)

psql (9.6.0, server 11.3) on linux We've ramped up usage on a PG server (a VM, I have no choice about this) and are approaching the 100 connections limit. We could increase the limit, but I've read that this can lead to a degradation in performance. If we bump it up to 500, what kind of compute r

Re: Estimated resources for a 500 connections instance (VM)

After looking at some of the factors that can affect this, I think it may be important to know that most of the connections will be almost idle (in terms of interacting with the DB). The "users" are perl/dbi scripts which connect to the DB and spend the vast majority of the time doing things other

order by not working in view ?

psql (9.6.7, server 11.3) on linux In the copy/paste below, first 2 lines returned by a select on the view, why didn't it sort on start_datetime correctly ? I would think that the one started on 04-08 would come before the one on 04-09 ? dvdb=> \d sim_phases; Table "dvm.sim_phase

Ned to understand why all the idle connections

Hi: psql (9.6.7, server 11.3) on linux I have what appear to be a log of idle connections to my DB. Query of pg_stat_activity indicates well over half (127/206) are like this... dvdb=# select state_change,wait_event_type,wait_event,state,backend_type from pg_stat_activity where query = '';

Re: Ned to understand why all the idle connections

Thanks! And an example of connection pooling is pgBouncer ? On Thu, Apr 23, 2020 at 2:41 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thursday, April 23, 2020, David Gauthier > wrote: > >> Hi: >> >> psql (9.6.7, server 11.3) on linux >&g

Why is a check constraint not working ?

psql (9.6.7, server 11.3) I have a table... dvdb=# \d+ dvm_events; Table "dvm.dvm_events" Column | Type | Modifiers | Storage | Stats target | Description --+--+---+-

Best way to use trigger to email a report ?

psql (9.6.0, server 11.3) on linux Looking for ideas. I want a trigger to... 1) compose an html report based on DB content 2) email the report to a dist list (dl = value of a table column) If this will involve hybrid coding, I prefer PL/perl. The linux env has both "mail" and "mutt" (if this is

Re: Best way to use trigger to email a report ?

Got it. On Fri, May 8, 2020 at 2:05 PM David G. Johnston wrote: > On Fri, May 8, 2020 at 10:19 AM Christophe Pettus > wrote: > >> If you don't want to periodically poll the table, you can use NOTIFY >> within the trigger to wake up a process that is waiting on NOTIFY. >> > > Kinda. > > "With th

Advise on how to install pl/perl on existing DB.

psql (9.6.0, server 11.3) linux Hi: I'm a PG users who has asked our IT team to install pl/perlu on an existing 9.6.0 instance on linux. They really don't know how to approach this. Could someone point me to a good step-by-step (including ptrs to any downloads they may need) ? Also, when they d

getting daily stats for event counts

Hi: 9.6.0 on linux I have a table which logs the inception of an event with an "incept_datetime" (timestamptz) field. I want to know how many events occurred per day from one date to the next using midnight as a marker for each day reported. The end result should be something like... 2019-06-23

Can't seem to mix an inner and outer join in a query and get it to work right.

9.6.7 on linux This query, which has 2 outer joins, gives me the records that I want... dvdb=# select dvdb-# sqf.sqf_runs.sqf_id, dvdb-# sqf.sqf_runs.submitted_shelvelist as sqf_sl, dvdb-# dvm.workarea_env.p4_changelist as as_cl, dvdb-# dvm.workarea_env.wa_id, dvdb-# dvm.dvm_events

Re: Can't seem to mix an inner and outer join in a query and get it to work right.

se of the outer join) On Mon, Jun 29, 2020 at 9:10 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Monday, June 29, 2020, David Gauthier wrote: > >> >>sqf_id

Need free PG odbc driver for Windows 10

Hi: I need a free odbc driver for PG to be installed on Windows 10 that my user community can easily install. By "easily install" I mean no binaries, no zipped file, etc... just point-click-install (with the usual confirmations and accepting default destinations for the code and such). Devart us

Postgres, High Availability, patching servers sequentially

Hi: version 9.6 on linux Our IT dept configured our DB to be "High Availability" a couple months back. I believe this means there's a backup server and disks that mirror the main and can kick in should main go down. They need to install a patch on the servers which will require server downtime.

Can PAF be used to provide zero downtime while primary and backup servers are being patched?

Our IT dept needs to install a patch on both primary and backup servers for our Postgres Automatic Failover configured DB (version 9.6 on linux). From the standpoint of the DB users, can a strategy be implemented such that they see zero downtime during this process as the 2 servers are taken down

How is PG replication typically used to create a High Availability (HA) config ?

Hi: Our IT dept has created what they call a High Availability DB for our PG DB (9.6.7 on linux). If the primary fails, they promise to promote the backup to be the new primary but leave it at that. But from the perspective of the app, I'm left with 1) detecting an SQL error is a DB connecti

Re: How is PG replication typically used to create a High Availability (HA) config ?

is orchestrating the HA cfg. On Tue, Aug 11, 2020 at 11:46 AM Paul Förster wrote: > Hi David, > > > On 11. Aug, 2020, at 17:12, David Gauthier > wrote: > > > > Hi: > > > > Our IT dept has created what they call a High Availability DB for our PG > DB (9.

Re: How is PG replication typically used to create a High Availability (HA) config ?

e event of a primary/backup swap. -dave On Wed, Aug 12, 2020 at 4:10 AM Paul Förster wrote: > Hi David, > > please don't top-post. > > > On 11. Aug, 2020, at 22:57, David Gauthier > wrote: > > > > Thanks for the response Paul :-) > > > > Our code

passing linux user to PG server as a variable ?

9.6.7 on linux I need to insert the linux username of a user on the client side into a col using an insert statement. I realize that the server knows nothing about who the linux user was on a client, but I was thinking that I might be able to pass that in somehow through a variable. Looking at

Re: passing linux user to PG server as a variable ?

approach, but don't really know where/how to create it in terms of pg sys files, init scripts or env vars like PGOPTIONS. On Mon, Aug 17, 2020 at 4:07 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Aug 17, 2020 at 12:53 PM David Gauthier > wrote: > >&

Re: passing linux user to PG server as a variable ?

t to pass a variable like this to the server which it then could read on the server side? On Mon, Aug 17, 2020 at 5:53 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Monday, August 17, 2020, David Gauthier > wrote: > >> OK, trying to piece together something

What's the best way to translate MS generated translations of user input to what the user actually typed prior to insert or update into PG backend table ?

psql (11.5, server 11.3) on linux I'm using MS-Access as a Windows front-end to a PG DB table through ODBC (PostgreSQL Unicode ODBC Driver). Seems to be working fine except for when users enter "..." as part of a string, MS (in it's infinite wisdom) decides to translate that to what emacs is desc

Re: What's the best way to translate MS generated translations of user input to what the user actually typed prior to insert or update into PG backend table ?

Next question, how does one actually replace the thing ? replace(thestring,0x2026,'...') ... isn't going to fly. Working with binary values in PG isn't at the top of my resume :-) On Fri, Oct 30, 2020 at 12:20 PM Tim Clarke wrote: > > On 30/10/2020 16:03, David Gaut

database aliasing options ?

Hi: version 11.5 on linux. Our IT dept has configured our PG DB as a "High Availability" database. It has a primary and backup server (disks too). Normally both are running but if one goes down, the other is still available for use, effectively keeping the DB up while the failed server is being

Performance hit if I create multiple DBs on same instance

Hi: 11.3 on linux I've come up with a plan to archive data from my main DB which involves creating other DBs on the same server. But even though there will be zero activity on the archive DBs in terms of insert/update/delete, and almost no activity in terms of select, I'm still worried that the

Re: Performance hit if I create multiple DBs on same instance

Ok, thanks. I was also planning on manually running vacuum, reindex and analyze on the main DB after removing the data from the main DB after archiving. Does that sound necessary and reasonable ? On Tue, Nov 24, 2020 at 10:15 AM Adrian Klaver wrote: > On 11/24/20 6:36 AM, David Gauthier wr

ODBC message "server closed the connection unexpectedly" when accessing a PG/11.3 DB

We are using MS-Access as a front-end to work with tables served by a Postrges/11.3 DB on linux as the MS-Access backend through ODBC. I have my users install on their Windows laptops a PG driver for ODBC from here... https://www.postgresql.org/ftp/odbc/versions/msi and selecting psqlodbc-12_02_00

effects of nullifying bytea column on storage

Hi: psql (11.5, server 11.3) on linux I have a table with a bytea column which, of course, contains binary data. After 60 days, I no longer need the binary data but want to retain the rest of the record. Of course it's easy to just update the bytea column to null for the older records. But I can

Re: effects of nullifying bytea column on storage

Doesn't vacuum run automatically (or can it be set to run automatically) ? On Wed, May 11, 2022 at 8:05 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wednesday, May 11, 2022, David Gauthier wrote: > >> Hi: >> psql (11.5, server 11.3) on linux >

escaping double-quotes in varchar array

psql (11.5, server 11.3) on linux Trying to insert a string containing a double-quote into a varchar arr. Here's my attempt dvdb=# create table testarr (arr varchar[]); CREATE TABLE dvdb=# insert into testarr (arr) values ('{"abcdefg"}'); INSERT 0 1 dvdb=# select * from testarr

Drop role cascade ?

psql (11.5, server 11.3) on linux Someone who had privileges in a lot of tables/schemas/DBs left the company. I want to drop that role but "rop role xxx" won't let me because... "xxx cannot be dropped because some objects depend on it". Is there a way to just get rid of this role without revoki

editable spreadsheet style interface

I think I know the answer to this one but I'll ask anyway... Is there a spreadsheet style interface to a PG DB where users can... - lock records - edit records - submit changes (transaction) Is there any after-market tool for PG that does something like this ?

recursion in plpgsql

Hi: I'm trying/failing to write a recursive plpgsql function where the function tries to operate on a hierary of records in a reflexive table. parent-child-grandchild type of recursion. I tried with a cursor, but got a "cursor already in use" error. So that looks like scoping. I know I did this

Re: recursion in plpgsql

with plpsql because that wasn't necessary anymore. On Tue, Nov 6, 2018 at 7:29 PM Tom Lane wrote: > David Gauthier writes: > > I'm trying/failing to write a recursive plpgsql function where the > function > > tries to operate on a hierary of records in a reflexive table.

PG version recommendation

Hi: I'm going to be requesting a PG instance supported by an IT team in a large corp. They will be creating the server as a VM. We will be loading the DB using scripts (perl/dbi) on linux, possibly using bulk loading techniques if that's required. Queries will come from both linux and the web,

Re: PG version recommendation

>>Home-rolled application, or third party? Are you asking about how they do VMs ? They already provide PG v9.6.7 , so I gather they're not averse to supporting PG DBs. On Tue, May 7, 2019 at 4:05 PM Ron wrote: > On 5/7/19 1:52 PM, David Gauthier wrote: > > Hi: > > I&

Need create table statements from metadata

psql (9.6.7, server 9.5.2) on linux. I have 2 DBs, one for dev the other is live. I want to recreate several tables in the dev db using the same metadata found in the live db. But I'm too lazy to manually transcribe everything and that's prone to error anyway. In the past, I would just run pg_d

List tables for a specific schema

psql (9.6.7, server 9.5.2) I created a schema in my DB called "dvm". Then I created a table a-la... create table dvm.foo (col1 tedxt); . I see the schema with \dnS+. But I can't see my table using \d. I can see the dable with \d dvm.foo, so it's in there. The first column of the \d output is

Need a DB layout gui

Hi: I've been poking around https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools to see if there is anything that can read PG metadata in and display it graphically in a gui. You know, the kind of picture that is 1 block per table with 1->many arrows connecting up the primary/

Re: Need a DB layout gui

All very good ideas. Thanks to all for the input. I think I'm leaning toward DBeaver. But they're all good. Thanks ! On Tue, Jun 25, 2019 at 5:02 PM Kevin Brannen wrote: > *From:* David Gauthier > > > I've been poking around > ht

Need a referential constraint to a non-unique record

I need to create a constraint on a column of a table such that it's value is found in another table but may not be unique in that other table. Example... Let's say the DB is about students and the grades they got for 4 subjects... Math, English, Science, History. But instead of creating 4 records

Need efficient way to remove (almost) duplicate rows from a table

v11.5 on linux (I know, I know, IT is working on the upgrade to 15.2) I have a table with millions of records and there are a lot of "almost" duplicates that I want to get rid of in an efficient way. Best to illustrate with a simple example... We'll talk about deleting leaves on a tree where eac

Indexing fragments of a column's value ?

I'm asking about the possibility of indexing portions of a column's value where the column has a static field format. Example, a char(8) which contains all hex values (basically a hex number that's always 8 chars wide, leading zeros if needed). Someone might want to select all recs where the first

Can user specification of a column value be required when querying a view ?

Hi: I have a view that I want to require user specification for a specific column before the query starts (if that makes sense). Example I want the users to be required to provide a value for ssn in the following query... "select * from huge_view *where ssn = '106-91-9930'* " I never want them t

Re: Can user specification of a column value be required when querying a view ?

OK, didn't think so, just checking. Thanks for verifying ! On Mon, Nov 20, 2023 at 4:45 PM Christophe Pettus wrote: > > > > On Nov 20, 2023, at 13:41, David Gauthier wrote: > > I want the users to be required to provide a value for ssn in the > following query... &g

suppress notices from inside a stored a plpgqsl function

Hi: I have a plpgsql function that has this... drop table if exists tmp_diff_blkviews; Even with the "if exists", I still get... NOTICE: table "tmp_diff_blkviews" does not exist, skipping CONTEXT: SQL statement "drop table if exists tmp_diff_blkviews" PL/pgSQL function dvm.blkview_diffs(c

running \copy through perl dbi ?

Hi: I'm trying to run a PG client side "\copy" command from a perl script. I tried using $dbh->do("\\copy ...") but it barffed when it saw the '\'... ERROR: syntax error at or near "\" I can do this with a command line approach, attaching to the DB then run using... -c "\copy ...". But I was

How to \ef a function ?

atletx7-reg017:/home/dgauthie[ 120 ] --> dvdbdev Pager usage is off. psql (11.5, server 11.3) Type "help" for help. dvdb=# \df opid.bef_ins_axi_reqs_set_trig; List of functions Schema | Name| Result data type | Argument data types | Type

Best practices for data buffer cache setting/tuning (v15)

Is there a document which makes recommendations on sizing data buffer cache, tuning options which evict old/unused data in mem, and cache fragmentation avoidance for a v15.3 DB ? Thanks in Advance.

Deleting duplicate rows using ctid ?

I have a table with 4 columns, none of them unique. I want to delete all but one of the duplicate records. I think there's a way to do this with ctid. Examples that pop up in google searches always rely on something like a unique (id) field, like a primary key, (no good in my case) create tabl

v11.5- v15.3 upgrade (linux)

Hi: I'm a PG user in a big corp with an IT dept that administers a PG server/instance that I use. It's an old install, v11.5, and we need to upgrade to v15.3. They want to bring the upgraded DB up on a new linux vm which has OS upgrades of its own. So it's a move AND an upgrade. There are 2 conc

creating a subset DB efficiently ?

Here's the situation - The DB contains data for several projects. - The tables of the DB contain data for all projects (data is not partitioned on project name or anything like that) - The "project" identifier (table column) exists in a few "parent" tables with many child... grandchild,... tab

Re: v11.5- v15.3 upgrade (linux)

On Fri, Mar 8, 2024 at 11:28 AM Yogesh Sharma < yogesh.sha...@catprosystems.com> wrote: > Greetings, > > On 3/6/24 19:19, David Gauthier wrote: > > Hi: > > I'm a PG user in a big corp with an IT dept that administers a PG > > server/instance that I use. It

Re: v11.5- v15.3 upgrade (linux)

Thanks. On Fri, Mar 8, 2024 at 12:12 PM Adrian Klaver wrote: > On 3/8/24 09:09, Adrian Klaver wrote: > > On 3/8/24 08:57, David Gauthier wrote: > >> Thanks for the reply. > >> > >> When you say "dump/restore" do you mean pg_dump then running

How to reference a DB with a period in its name ?

Hi: I have a DB named "thedb", which has a schema called "theschem" which has a table called "thetab". In a perl/DBI script, I am able to work with the table by referencing it as... "thedb.theschem.thetab" as in... "delete from thedb.theschem.thetab where col1=1" No problem (so far...) New DB n

Re: How to reference a DB with a period in its name ?

ll the code has the prefix and I don't want to uproot that (because it's working). Thanks David ! On Fri, Mar 29, 2024 at 5:22 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Fri, Mar 29, 2024 at 2:20 PM David G. Johnston < > david.g.johns...@gmail.co

What linux version to install ?

This is what we are running... Red Hat Enterprise Linux Server release 7.9 (Maipo) In our pandora distrib, I see PG v15.3. I was wondering if there is something even better. Can't seem to pin this down using https://www.postgresql.org/download/linux/redhat/.

\dt shows table but \d says the table doesn't exist ?

psql (15.3, server 14.5) on linux Someone else's DB which I've been asked to look at. \dt gives many tables, here are just 3... public | some_idIds | table | cron_user public | WarningIds | table | cron_user public |

How to get CASE statement to recognize null ?

This is probably an easy one for someone with experience using CASE, but intuitively I can't get it. First... 11.5 on linux. Example... dvdb=# create table foo (col1 varchar, col2 varchar); CREATE TABLE dvdb=# insert into foo (col1,col2) values ('a','x'), (null,'y'); INSERT 0 2

Re: How to get CASE statement to recognize null ?

as varchar)||'-'||sr.nightly_cl_display_suffix END as changelist On Wed, Mar 10, 2021 at 2:46 PM Tom Lane wrote: > David Gauthier writes: > > dvdb=# select > > CASE col1 > > WHEN null THEN 'z' > > ELSE col1 > >E

Need to omit time during weekends from age calculations

Hi: I suspect I'm not the first to ask about this but couldn't find anything after googling for a bit. So here goes I'd like to get the "age" difference between two times which span either all or part of a weekend but exclude any time that transpired during the weekend. Example (please pardo

Re: Need to omit time during weekends from age calculations

ng Sat&Sun is good enough for this particular problem. Thanks Everyone ! On Mon, Jun 7, 2021 at 3:46 PM Pavel Stehule wrote: > > > po 7. 6. 2021 v 21:17 odesílatel Ron napsal: > >> On 6/7/21 2:12 PM, David Gauthier wrote: >> >> Hi: >> >> I suspect I

Need to check each element of an array satisfies a foreign key constraint

Hi: I have a column in a table which is a csv of values and I need to make sure each element of the csv = the PK of that same table. create table projects ( project varchar primary key, children_csv varchar ); insert into projects (project,children_csv) values ('prj1',null), ('prj2',null), (

  1   2   >