\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 |

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/.

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

he 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.com> wrote: > >> On

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

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

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

4 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's an old install, v11.5, and

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,...

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

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

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.

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

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

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

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

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

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

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

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

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

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 >>

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

Re: storing zipped SQLite inside PG ?

can get them to upgrade the DB itself. Thank You too David ! On Tue, Dec 21, 2021 at 10:14 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tuesday, December 21, 2021, David Gauthier > wrote: > >> >> OK, you get the picture. I'm all ears :-) An

storing zipped SQLite inside PG ?

Hi: I need a sanity check (brainstorming) before I jump into coding something that might have a better solution known to a community like this one. Here's the situation... To begin with... PG 11.5 on linux. Some code (nature unimportant to this discussion) generates a lot (a lot) of data and

performance expectations for table(s) with 2B recs

11.5 on linux server = VM provided by our IT dept (IOW, can be grown if needed) DB itself is on NFS So far, the tables I have in my DB have relatively low numbers of records (most are < 10K, all are < 10M). Things have been running great in terms of performance. But a project is being

Insert binary file into bytea where PG server does not have access to the file ?

11.5 on linux Big corp with an IT dept providing us with PG DBs running in instances on their servers. (We/I amd not DBA).We on the client side, the "users" of these DBs, want to load binary files into bytea type columns. But the files we want to load are on disks that the server does not

Wildcarding json keys in json query

PG 11.5 on linux Let's say I store a jsonb in a column called test_results that looks like this... { ports : { port_abc:{min: 5, max: 7, mean: 6}, port_def:{min: 5, max: 9, mean: 7}, port_ghi:{min: 6, max: 10, mean: 8} } } And I want to to get all the port names where the mean

How is JSON stored in PG ?

Hi: psql (11.5, server 11.3) on linux I'm considering using JSON as a datatype for something I'm working on. The reasons are... 1) the 'metadata' (if you want to call it that) in JSON is very flexible. Doesn't require an alter table or anything like that to change. 2) The customers for this

what is pg_stat_activity.query for idle connections ?

11.3 on linux I have a DB with a worrisome number of connections with pg_stat_activity.query = '/* DBD::Pg ping test v3.5.3 */', all of them with state='idle'. I have code that uses perl's ping method to ping the DB and I suspect this is the source. Is '/* DBD::Pg ping test v3.5.3 */' the last

Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def.

This stored procedure ... create or replace function validate_proj_csv (proj_csv varchar) returns int language plpgsql as $$ -- This function used in a check constraint in the public.projects table to ensure that -- all projects in column sibling_project_csv are valid projects. DECLARE

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

il.com> wrote: > On Tuesday, July 13, 2021, David Gauthier > wrote: >> >> >> I suppose I could write a stored procedure to do this and call it in a >> check constraint. But I was wondering if there is something more elegant. >> >> > You cannot use a chec

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),

Re: Need to omit time during weekends from age calculations

ng Sat 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'm n

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

Re: How to get CASE statement to recognize null ?

)||'-'||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 > >END as col1, > >

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

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

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

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

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

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 Gauthier wrote: > >

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

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

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 that

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

ow 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: > >> Looking at psql command line options, I s

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: How is PG replication typically used to create a High Availability (HA) config ?

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 is actually

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 >

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

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

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.

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

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

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 | sqf_sl | as

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-#

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...

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

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

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

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

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 >

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 = '';

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

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

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

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

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

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

t;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 interesting one for you...

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

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 ?

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

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 > >

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),

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 AccessDB

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

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

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

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

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 > https://wiki.post

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

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 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

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'm g

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: 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. > > pare

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

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 ?

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

turns 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

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

(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 >

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

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

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: &

Re: nested query problem

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_runs sr, > &

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

Re: unorthodox use of PG for a customer

t 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 out for ideas, even > > though I think it's a bit crazy. I'm on

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

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

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

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

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

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

ng "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 times >> st

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

e 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: >> >> I

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=>

  1   2   >