Re: CURRENTE_DATE

[Ray O'Donnell]

On 22/10/2024 12:31, Rossana Ocampos wrote:


*Hello ,*

I am new with PostgreSQL and I have a bug. I have created a function 
that has an input variable of type date , in case it does not receive 
value , it has to assume by default the current date.


I have defined it as follows variable  DATE DEFAULT CURRENT_DATE, but 
I get the following error.


*_El error _*

ERROR: invalid input syntax for type date: “CURRENT_DATE” LINE 1: 
...extsupplydate ('1085018'::bigint, '5278'::bigint, 'CURRENT_D... ^ 
ERROR: invalid input syntax for type date: “CURRENT_DATE” SQL status: 
22007 Characters: 78




I think you just need to leave off the quotes, as current_date is a 
function:


  insert into  values ( ... , current_date, );

Also, you don't need to quote the bigint values.


HTH,

Ray.



Please could you help me, thank you very much.

Rossana




--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Issue Installing PostgreSQL 17 on Ubuntu 22.04 (Jammy)

[Ray O'Donnell]

On 26/08/2024 12:33, Jyoti Saxena wrote:


Hi Ray,

I appreciate the clarification regarding the release status of 
PostgreSQL 17. Understanding that it is currently in the beta phase, I 
am eager to integrate this version into my development and testing 
workflows.


Could you please assist me by providing the following information:

  * Instructions on how to access and install the beta version of
PostgreSQL 17 via the PostgreSQL APT repository for Ubuntu.
  * Details on where I can find the binary installation files for
PostgreSQL 17 beta for Windows.



Hi Jyoti,

I don't use Windows myself so I can't help you with that, but if you 
look at my other email to the list, there's a link to instructions on 
the wiki.


HTH,

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Issue Installing PostgreSQL 17 on Ubuntu 22.04 (Jammy)

[Ray O'Donnell]

On 26/08/2024 11:43, Ray O'Donnell wrote:

On 26/08/2024 07:52, Jyoti Saxena wrote:


Hi,

I’m encountering an issue while trying to install PostgreSQL 17 on my 
Ubuntu 22.04 (Jammy) system. I followed the installation instructions 
from the official PostgreSQL website 
https://www.postgresql.org/download/linux/ubuntu/.




I don't know what's currently available in the PG apt repo, but 
PostgreSQL 17 hasn't been released yet. Maybe you need to ask for a 
beta package or something?


PS - there are more instructions at https://apt.postgresql.org, 
including a link to this page on the wiki which will hopefully help:


    https://wiki.postgresql.org/wiki/Apt/FAQ#Development_snapshots

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Issue Installing PostgreSQL 17 on Ubuntu 22.04 (Jammy)

[Ray O'Donnell]

On 26/08/2024 07:52, Jyoti Saxena wrote:


Hi,

I’m encountering an issue while trying to install PostgreSQL 17 on my 
Ubuntu 22.04 (Jammy) system. I followed the installation instructions 
from the official PostgreSQL website 
https://www.postgresql.org/download/linux/ubuntu/.




I don't know what's currently available in the PG apt repo, but 
PostgreSQL 17 hasn't been released yet. Maybe you need to ask for a beta 
package or something?


Ray.



--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Code does Not Read in FY 2025 Data

[Ray O'Donnell]

On 16/07/2024 15:59, Anthony Apollis wrote:

I am using Postgres and SQL Server.
Can you test the data pls.


Adrian has already pointed out a possible cause of the issue you are 
seeing - you should test further based on this.


Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Finding error in long input file

[Ray O'Donnell]

On 9 July 2024 23:14:33 Rich Shepard  wrote:


I've a file with 488 rows to be input into a table. I run the script using
psql with the `\i' option.

After psql found a few missing commas I thought the script was clean. But,
psql returned this error:
bustrac=# \i scripts/insert-addrs.sql
psql:scripts/insert-addrs.sql:488: ERROR:  syntax error at or near ";"
LINE 488: ...2832,1,default,'85250 Red House Rd','Paisley','OR','97636');
^ Am
I correct that some rows were inserted into the table before this error was
generated somewhere prior to the end of the file? If so, how do I determine
which rows were inserted and what is the recommended procedure to locate
just where that error is?



Did you run the entire thing inside a transaction? If so, then it will have 
been rolled back after the error, and no rows will have been inserted.


Ray.






I've learned from experience that using the old coding approach of dividing
the file in half, then half again and again until the row with the error is
found produces multiple rows with different PKs. Not what I want to clean
up.

A clue stick will be much appreciated.

TIA,

Rich

Re: Updating 457 rows in a table

[Ray O'Donnell]

On 19/05/2024 17:54, Rich Shepard wrote:

Searching the postgresql doc for UPDATE the examples I find show updating
one or a few rows in a table. I have 457 rows to update in a table.

I could write a .sql script with 457 lines, each updating one row of the
table. My web search for `sql: update table rows from a file of column
values' finds pages for single row updates and updating a table from 
another

table, but neither is what I want.

I want to change a column value in a table based on the value of a 
different

column in that same table.

Specifically, in the 'people' table I want to change the column 'active'
from false to true for 457 specific person_id row numbers.

Is there a way to do this without manually writing 457 'update ...' 
rows in

a .sql file?


Could you create a table with just person_id values whose rows are to be 
updated? Then you could do something like this:


update people set active = true where exists (
  select 1 from temporary_table where person_id = people.person_id
);


That's just off the top of my head and might not be correct, but that's 
the way I'd be thinking.


Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Left join syntax error

[Ray O'Donnell]

On 18/05/2024 16:01, Rich Shepard wrote:

On Sat, 18 May 2024, Ray O'Donnell wrote:

You need to include the alias for the table also - see "...from 
companies as c..." in Shammat's example.


Ray,

That didn't work:
bustrac-# FROM people as p, companies as c
bustrac-# LEFT JOIN companies as c ON c.company_nbr = p.company_nbr;
ERROR:  table name "c" specified more than once

so I tried only the alias on the join line:
bustrac-# LEFT JOIN c ON c.company_nbr = p.company_nbr;
ERROR:  relation "c" does not exist
LINE 3: LEFT JOIN c ON c.company_nbr = p.company_nbr;
  ^
and that didn't work either.


Hi Rich,

Look again at Shammat's example! -

SELECT p.lname, p.fname, p.job_title, p.company_nbr, p.email, 
c.company_name

FROM people as p
  LEFT JOIN companies as c ON c.company_nbr = p.company_nbr

NB - "... from people as p left join companies as c on " - i.e. the 
companies table (or its alias c) is only specified once.


HTH,

Ray.







Thanks,

Rich





--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Left join syntax error

[Ray O'Donnell]

On 18/05/2024 15:46, Rich Shepard wrote:

On Sat, 18 May 2024, Shammat wrote:


Don't put the second table in the FROM part

SELECT p.lname, p.fname, p.job_title, p.company_nbr, p.email, 
c.company_name

FROM people as p
 LEFT JOIN companies as c ON c.company_nbr = p.company_nbr


Shammat,

I tried this with this result:

ERROR:  missing FROM-clause entry for table "c"
LINE 3: LEFT JOIN companies ON c.company_nbr = p.company_nbr;



You need to include the alias for the table also - see "...from 
companies as c..." in Shammat's example.


Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Windows service randomly stops with no indication why

[Ray O'Donnell]

On 08/03/2024 14:17, Jay Madren wrote:
Running PostgreSQL 15.6 on Windows Server 2022. The database service 
randomly just stops and the Windows Service auto-restart options don't 
kick in. The stop is unexpected (not a controlled shut down) because 
after restarting the service the postgresql log states that 
the database system was interrupted, not properly shut down, and 
automatic recovery in progress. There's nothing to indicate exactly 
when it went down, other than the timestamp of the last log entry 
before restarting it.


There's also nothing I could find in the Windows Event Logs other than 
when the service is started each time.


This is occurring at random times, day or night, around 0 to 2 times 
each day.


Any clue as to what's happening or how to investigate this further?



I haven't used Windows in a very long time, but from (dim and distant) 
memory PostgreSQL on Windows maintains its own log files, separate to 
the Windows event log - you may find more clues there.


Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Testing - Please ignore/delete this message

[Ray O'Donnell]

On 02/01/2024 13:12, Arbol One wrote:

Did it work?



No idea - I ignored it...! :-)

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: pgBackRest on old installation

[Ray O'Donnell]

On 21/11/2023 11:59, Achilleas Mantzios - cloud wrote:

On 11/21/23 08:36, KK CHN wrote:

Thank you.  Its worked out well. But a basic doubt ? is storing the DB 
superuser password in .pgpass is advisable ? What other options do we 
have ?


The .pgpass file is stored in the user's home directory, and from memory 
I think it won't get used at all if the permissions allow anyone other 
than the user to read it, so it's safer than it sounds!


Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Question about the new PostgreSQL 16 availability on Ubuntu

[Ray O'Donnell]

On 25/10/2023 16:47, John W Higgins wrote:
As to the wiki (and/or any other references), small number of people, 
doing way too many things - some stuff doesn't get updated as quickly as 
one would like.


...not to mention that they are volunteers, doing it gratis in their own 
limited free time, which the rest of us very much appreciate.


Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Ubuntu 18 + PHP 8.2 + PDO: can't find drivers

[Ray O'Donnell]

On 23/09/2023 19:56, Chris Kelly wrote:

On Ubuntu "18.04.6 LTS (Bionic Beaver)":

# apt-cache search pgsql | grep 8
pike7.8-pg - PostgreSQL modules for Pike
pike8.0-pg - PostgreSQL modules for Pike


# apt-cache search pgsql | grep php
php-pgsql - PostgreSQL module for PHP [default]
php7.2-pgsql - PostgreSQL module for PHP
php-db - Database Abstraction Layer
php-mdb2-driver-pgsql - pgsql MDB2 driver


This is for an existing site with an existing db dump and an existing 
codebase. I can't use PHP7 due to composer requirements.


I tried to add the ondrej repo but got "The following signatures 
couldn't be verified because the public key is not available: NO_PUBKEY 
467B942D3A79BD29".


Yes, you need to add the key first - I think the instructions are on the 
website - not in a position to check just now.



Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Ubuntu 18 + PHP 8.2 + PDO: can't find drivers

[Ray O'Donnell]

On 22 September 2023 21:40:38 Chris Kelly  wrote:

The need has mostly passed (I used another computer with Ubuntu 20) but are 
there clear, *working* instructions for how to connect to Postgress on 
Ubuntu 18 via PDO? I don't see drivers that would work. This is for a 
Drupal site.


PS - depending on your Ubuntu version, you might need to take packages from 
deb.sury.org in order to get PHP 8.2. I use them all the time, they're very 
good.


Ray

Re: Ubuntu 18 + PHP 8.2 + PDO: can't find drivers

[Ray O'Donnell]

On 22 September 2023 21:40:38 Chris Kelly  wrote:

The need has mostly passed (I used another computer with Ubuntu 20) but are 
there clear, *working* instructions for how to connect to Postgress on 
Ubuntu 18 via PDO? I don't see drivers that would work. This is for a 
Drupal site.


From memory (not at my laptop now) you need to install the php8.2-pgsql 
package (or maybe it's php8.2-pdo-pgsql). It should just work then... 
Drupal will notice the driver and offer Postgresql as an option on 
installation. Don't forget to restart Apache after installing that package


Ray.

Re: Upgrade problem

[Ray O'Donnell]

On 11/09/2023 17:33, Graeme wrote:

On 11/09/2023 17:13, Adrian Klaver wrote:

On 9/11/23 09:04, Graeme wrote:
Preparing to upgrade my small cluster from Mageia 8/Pg 9 to Mageia 
9/Pg 15. I'm at the point of running pg_upgrade but have received 
anerror message:


You are going to have to be more specific on the Postgres version. 
Prior to Postgres 10 major version changes where two digits. So for 
Postgres 9.X.x that meant 9.0.x --> 9.6.x

Don't have access to that version without re-booting; probably 9.5


select version();

?


Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Creating a TABLESPACE

[Ray O'Donnell]

On 16/08/2023 21:24, Ron wrote:
Speakers of English as a second language are to be tolerated and 
assisted, not mocked and insulted.


+1

I actually found it rather amusing. :-)

Ray.




On 8/16/23 12:27, Amn Ojee Uw wrote:


Sorry, did not mean it that way.
Thanks for the advice.

On 8/16/23 11:37 a.m., Ron wrote:
Friendly tip: whatever else you do, don't write "Hello children!" in 
English to adults.  It's *highly insulting*.


On 8/16/23 05:10, Amn Ojee Uw wrote:


Hello children!



--
Born in Arizona, moved to Babylonia.


--
Born in Arizona, moved to Babylonia.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: syntax pb

[Ray O'Donnell]

On 30/05/2023 14:45, Marc Millas wrote:


and , now, if I want to insert that:
Insert into t2 (a, b, c, d)
Select distinct test1.t, 'abc' as b, NULL as c, NULL as d
 From t1 test1;

I get:
ERROR:  column "d" is of type numeric but expression is of type text
LINE 2: Select distinct test1.t, 'abc' as b, NULL as c, NULL as d

HINT:  You will need to rewrite or cast the expression.


I'm guessing you'll need to cast the NULLs:

   select distinct test1.t, 'abc', null::text, null::numeric ...


I don't think you need the aliases.

Ray.





Can someone give a short SQL syntax hint ?

thanks,



Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com 



--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Converting row elements into a arrays?

[Ray O'Donnell]

On 02/03/2023 21:01, Ray O'Donnell wrote:

On 02/03/2023 20:58, Ron wrote:

Postgresql 12.13

Given the sample below, I'm looking for how to generate this output. 
It's like GROUP BY, but generating an array instead of an aggreate 
number.

  f1 | f2_array
+-
1 | {1,2,3}
   2 | {1,2,3,4}
   3 | {1,2}


Something like this (off the top of my head)? -

    select f1, array_agg(f2) as f2_array group by f1;


  ... from foo ...

R.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Converting row elements into a arrays?

[Ray O'Donnell]

On 02/03/2023 20:58, Ron wrote:

Postgresql 12.13

Given the sample below, I'm looking for how to generate this output.  
It's like GROUP BY, but generating an array instead of an aggreate number.

  f1 | f2_array
+-
1 | {1,2,3}
   2 | {1,2,3,4}
   3 | {1,2}


Something like this (off the top of my head)? -

   select f1, array_agg(f2) as f2_array group by f1;

Hope that helps (and that it's right!).

Ray.





The ultimate goal is to somehow use pg_index.indkey to get column names 
from pg_attribute.


create table foo (f1 int, f2 int);
insert into foo values (1, 1);
insert into foo values (1, 2);
insert into foo values (1, 3);
insert into foo values (2, 1);
insert into foo values (2, 2);
insert into foo values (2, 3);
insert into foo values (2, 4);
insert into foo values (3, 1);
insert into foo values (3, 2);

select * from foo order by f1, f2;
  f1 | f2
+
   1 |  1
   1 |  2
   1 |  3
   2 |  1
   2 |  2
   2 |  3
   2 |  4
   3 |  1
   3 |  2
(9 rows)





--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: posgres question / answer

[Ray O'Donnell]

On 30/11/2022 22:39, Rizwan Shaukat wrote:


Hi Team,

im new to postgres, trying to learn postgres n i hv many questions in
my mind, need to discuss in community, pls add access for questions.


Hi there,

This is a mailing list - emails to it go to everyone on the list. Ask away!

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: restore question

[Ray O'Donnell]

On 19/07/2022 11:11, Ronald Haynes wrote:

Hi folks,  I am a fairly novice postgresql user.



I have a backup file for a couple of small postgresql databases.    The 
backup file was created using postgresql 12.2, my Mac now has a 
postgresql 13.x or 14.x versions.   I would like to restore the setup 
from the backup file.




That looks like a dump file generated by pg_dumpall, so restoring it 
should be as simple as:


   psql -f backup-file.sql

You'll possibly need other connection parameters as required by psql, 
such as -U (the PostgreSQL user to use for connecting) or others.


HTH,

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Get the table creation DDL

[Ray O'Donnell]

On 10/07/2022 17:47, Igor Korot wrote:

Hi, Michael,

On Sun, Jul 10, 2022 at 11:13 AM Michael Nolan  wrote:




On Sun, Jul 10, 2022 at 10:28 AM Igor Korot  wrote:


Hi,
Is there a query I can execute that will give me CREATE TABLE() command
used to create a table?


So there is no "query" per se?
Also how do I pass the table name?



If you connect to the database with psql including the -E option, then do

\d 

It will show you the SQL used to generate the output... this may help.

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: PostgreSQL 12.2 on Linux ubuntu 20.4 / s390

[Ray O'Donnell]

On 15/06/2022 11:58, Ian Dauncey wrote:
According to the apt list for PostgreSQL there is nothing for the s390 
system for version 13 and 14.


That is more than likely your answer, so I'm not knowledgable on 
these things, but perhaps the packagers will be able to say more. I'd be 
guided by that page in any case.


Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: PostgreSQL 12.2 on Linux ubuntu 20.4 / s390

[Ray O'Donnell]

On 15 June 2022 08:31:42 Ian Dauncey  wrote:

Morning all



Currently we are running PostgreSQL 12.2 on Ubuntu 20.04.1 LTS (Kernel: 
Linux 5.4.0-42-generic - Architecture: s390x).




We are looking at upgrading to a later version of PostgreSQL



My question is :- What is the latest available version of PostgreSQL that 
will run on the above Architecture.


Here you go:

   https://wiki.postgresql.org/wiki/Apt

Ray.







Regards

Ian





Disclaimer

The information contained in this communication from the sender is 
confidential. It is intended solely for use by the recipient and others 
authorized to receive it. If you are not the recipient, you are hereby 
notified that any disclosure, copying, distribution or taking action in 
relation of the contents of this information is strictly prohibited and may 
be unlawful.


This email has been scanned for viruses and malware, and may have been 
automatically archived by Mimecast, a leader in email security and cyber 
resilience. Mimecast integrates email defenses with brand protection, 
security awareness training, web security, compliance and other essential 
capabilities. Mimecast helps protect large and small organizations from 
malicious activity, human error and technology failure; and to lead the 
movement toward building a more resilient world. To find out more, visit 
our website.

Re: newbie db design question

[Ray O'Donnell]

On 11 June 2022 08:05:41 Rino Mardo  wrote:

hello!

really noob question here. i have this experimental database design:

create table products (
product_id serial primary key,
description text,
supplier_id) ;

create table supplier (
supplier_id serial primary key,
description text) ;


the products table should be linked to the supplier table via "supplier_id" 
column. i can't find out what would be the data type of supplier_id in 
table products to do that. i tried


Hi there,

"serial" isn't a real type - it's just a shortcut for "integer not null 
default nextval()". Read about it here:


https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL

So the supplier_id column in the first table should be of type "integer".



supplier_id serial primary key references supplier

but it won't allow multiple primary key.

how then to proceed?



You need a foreign key add this to the first table:

constraint  foreign key (supplier_id) references 
supplier(supplier_id)


I hope this helps.

Ray.





regards,

Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)

[Ray O'Donnell]

On 06/04/2022 18:20, Laura Smith wrote:

On Wednesday, April 6th, 2022 at 17:41, W.P.  wrote:

That was not a "command". PG was working ok, DB on encrypted (LUKS
on LVM2, ext4) USB drive.


I mean, why ?

If there was ever a "setting yourself up to fail" scenario, it would
be this.  Just because you can do it, doesn't mean you should.


That was my thought too, but not being an expert I didn't want to jump 
straight in... The OP might have a good reason for doing this, but 
trying to run a database off a USB stick strikes me as asking for trouble.



I suspect you will have to file this under "learnt the hard way"


Heh - I have a lot of stuff filed there myself. :-)

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Robust ways for checking allowed values in a column

[Ray O'Donnell]

On 25/01/2022 13:55, Shaozhong SHI wrote:

I tried the following:

select form from mytable  where form ~ 
'^Canal$|^Drain$|^Foreshore$|^inlandRiver$|^Lake$|^lockOrFlightOfLocks$|^Marsh$|^Researvoir$|^Sea$|^tidalRiver$|^Transfer$'


 I used ^ and $ to ensure checking of allowed values.

However, 'Backyard' was selected.

Why is that?


Sounds like a candidate for a foreign key relationship.

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: List all tables from a specific database

[Ray O'Donnell]

On 14/01/2022 12:51, Flaviu2 wrote:

Yes, I ran

*\c mydb1*

but I didn't see any SQL script underhood :(


Ah, OK. As I understand it, connecting to a database isn't something you 
can do in SQL: it's a function of the client, and how you do it depends 
on the client. - So in psql, it's a command-line argument; connecting 
from PHP with PDO, you use a connection string; and so on.


Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: List all tables from a specific database

[Ray O'Donnell]

On 14/01/2022 12:42, Flaviu2 wrote:

Yes, I started psql with -E, and now I see the SQL underhood. Thank you.

Now I need to find how to get and how to select a specific database, I 
have no experience in Postgre SQL.


Well, connecting from the command line with psql, you just specify it:

  psql [...options...] my_database

Or if you're already in psql, use the \c command:

   \c my_database

The Postgres manual is excellent, if enormous:

https://www.postgresql.org/docs/current/index.html

A good place to start is the "Tutorial" section.

HTH,

Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: List all tables from a specific database

[Ray O'Donnell]

On 14/01/2022 10:39, Flaviu2 wrote:
I work on a project that use Postgre SQL, and I have 0 experience in 
this regard. I hope to get help here. I need two SQL scripts for Postgre:


1. Get all databases, which I aquired already:

*SELECT datname FROM pg_database WHERE datistemplate = false*

This one is functional, it's ok.

2. Now, I need to find all tables *under a specific* database. This one 
I don't know how to achieve it. Can you help me here ? It is possible ?


If it's any help, running psql with the -E switch will show you the SQL 
which psql generates... so then (within psql) issuing the \dt command to 
list tables will show you the SQL used.


Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Can we use sql language to create a function in Postgres?

[Ray O'Donnell]

On 05/01/2022 11:08, Ray O'Donnell wrote:

On 05/01/2022 11:04, Shaozhong SHI wrote:
I was given an Oracle script.  Can we use sql language to create a 
function in Postgres?


Yes, absolutely:

create function my_function(...)
as
$$
   select.
$$
language sql;


Should have added the link to the docs:

https://www.postgresql.org/docs/14/server-programming.html

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Can we use sql language to create a function in Postgres?

[Ray O'Donnell]

On 05/01/2022 11:04, Shaozhong SHI wrote:
I was given an Oracle script.  Can we use sql language to create a 
function in Postgres?


Yes, absolutely:

create function my_function(...)
as
$$
  select.
$$
language sql;


Is this what you mean?


Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: How best to turn select result into options like 'a|b|c''

[Ray O'Donnell]

On 20 December 2021 22:10:19 Shaozhong SHI  wrote:

Is there a way to turn select result into something like 'a|b|c' .
Regards,
David


On my phone so hard to reply properly, but if a, b and c are in separate 
rows then you need the array_agg() and array_to_string() functions.


Ray.

Re: How is JSON stored in PG ?

[Ray O'Donnell]

On 16/11/2021 15:54, David Gauthier wrote:


Pros and Cons for using JSON ?


If it helps, here's my current use-case. I'm currently working on a 
project wherein data is captured from students for eventual submission 
to the relevant government department. The exact set of data required 
changes every so often, and it's not used anywhere else in the 
application, so I'm going to store it in a JSONB column, with a schema 
stored (also in JSONB) in a separate table which can be used to validate 
the data. I'll have a version number on the schema, and this will be 
kept in the student record also.


Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Pg_hba.conf problem after unexpected IP change

[Ray O'Donnell]

On 14/11/2021 20:16, Adrian Klaver wrote:


Actually that was already done:

https://www.postgresql.org/message-id/CAOR%2BmccGARtFsb7c%2BF%3DRUk832%3DdaaJ8%2ByQW8eBYa1Krypr4O6Q%40mail.gmail.com 



The problems seems to have stemmed from this(per OP):

"Well, it turns out that at one point when I was using my virtual 
machines, suddenly and unexpectedly, the IP of one of the virtual 
machines changed."


Something to do with the networking between host and VM's I presume.



Ah, OK - I missed that. Serves me right for jumping in late. :-)

Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Pg_hba.conf problem after unexpected IP change

[Ray O'Donnell]

On 14/11/2021 19:46, Yessica Brinkmann wrote:

Hello.
Thank you very much for your answer.
The configuration actually works for me.After restarting my computer, I 
went to test again and it was already working. I assume that perhaps 
because they are virtual machines it was not only enough to restart the 
server, I think you also had to restart the computer.
It's what I think, or I don't know what else could have happened, but 
now it's working for me.


Restarting the entire computer shouldn't be necessary... On the Debian 
servers I look after, it's enough to do:


systemctl restart postgresql

(or whatever the name of the particular service is).

Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Get COUNT results from two different columns

[Ray O'Donnell]

On 23/09/2021 14:37, Clive Swan wrote:

Greetings,

I have two separate queries that work individually, returning a count 
from each column.


I want to subtract New(COUNT) from Old(Count)

I get an error when trying to run UNION?


At a quick guess, you'll need to move the subtraction outside the two 
subqueries - stuff inside either query can't see anything in the other one.


Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: database design with temporary tables

[Ray O'Donnell]

On 29/08/2021 17:53, Adrian Klaver wrote:

On 8/29/21 9:38 AM, ourdiaspora wrote:


‐‐‐ Original Message ‐‐‐

On Sunday, August 29th, 2021 at 5:30 PM, Adrian Klaver 
 wrote:



On 8/29/21 9:24 AM, Adrian Klaver wrote:


Whoops, unfinished thought. What I was going to ask is:

The above is not clear to me. Are you asking about the Postgres

documentation?



Yes, wanted to know relevant parts because often the first problem is 
to know which part of the (extensive) documentation to read...




 From what I see it is basic operations; CREATE TABLE, SELECT, INSERT, 
UPDATE and COPY.


As I said in my previous post create a fairly detailed design flow first 
and from that you will see what operations you need to do and what part 
of the docs you need to look at.


+100 to this. Also, it's not clear from your description what is 
happening where - how much functionality is in the database, and how 
much at the web server. Are your users connecting directly to the 
database server? - or do they connect only to the web server, which then 
uses its own account on the database server? Get this clear in your own 
head and much of the rest will follow.


Ray.



--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: database design with temporary tables

[Ray O'Donnell]

On 29/08/2021 17:36, ourdiaspora wrote:

‐‐‐ Original Message ‐‐‐

On Sunday, August 29th, 2021 at 5:24 PM, Adrian Klaver
 wrote:



Presumably not. Temporary tables only live at most for the length
of a

session. It would be a really bad idea to hold sessions open for
24

hours.


Is there an alternative scenario, such as the user is able to create
a new table with saves the session data for a maximum time (such as
24 hours), even up to a certain time if the web browser crashes for
example?


I'd save a timestamp with the session data, and then run a cron job 
which deletes sessions older than whatever lifetime you want.


Ray.



--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: PHP: query with parameter for SET

[Ray O'Donnell]

On 27/07/2021 18:20, Pavel Stehule wrote:
When you use client side prepared statements, then it can be possible. 
But I don't know how it works in PHP.


Server side prepared statements are not possible in this case. "set" 
statement has not execution plan. But you  You can use Adrian's 
proposal, and you can prepare the query 'select set_config($1, false)'


Thanks Pavel - that makes sense.

Ray.



--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

PHP: query with parameter for SET

[Ray O'Donnell]

Hi everyone,

Using PHP (or indeed maybe more generally), is it possible to issue a 
SET TIME ZONE statement with a parameter for the new runtime setting?


In a PHP application I'm issuing a SET TIME ZONE command just after the 
database connection is created, and for security I'd like to pass the 
time zone value in a parameter, as it's coming from a configuration file 
- something like this:


$pdo = new PDO(/* DSN goes here */);

$statement = $pdo->prepare('set time zone :tz');
$statement->execute([':tz' => 'Europe/Dublin']);

However, in the logs I see:

ERROR:  syntax error at or near "to" at character 15
STATEMENT:  set time zone $1

Is this possible? I've also tried the "set timezone ..." variant with 
similar results. I'm wondering too if the limitation (if such it is) is 
in PHP or whether it's a Postgres thing.


Thanks,

Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Overlapping timestamptz ranges with priority

[Ray O'Donnell]

On 03/07/2021 21:13, Adrian Klaver wrote:

On 7/3/21 12:16 PM, Ray O'Donnell wrote:
Yes, you're right - I realised that after I sent my last email. The 
inner loop in the function should have matched overlapping bookings by 
aircraft registration:


 -- For each booking, check whether there are any with
 -- a higher priority and whose times overlap it.
 for m_overlapping in
   select booking_id, booking_time from bookings
   where booking_id < m_rec.booking_id
   and booking_time && m_rec.booking_time
 loop
   -- Snip away any overlapping (obscured) time.
   m_visible_time := m_visible_time - m_overlapping.booking_time;
 end loop;


Was the above supposed to show the change?


Whoops, sorry, here it is:

for m_overlapping_time in
select booking_id, booking_time from bookings
where aircraft_reg = m_rec.aircraft_reg
  and booking_id < m_rec.booking_id
  and booking_time && m_rec.booking_time
loop
[... etc ...]

select booking_id, aircraft_reg, booking_time from bookings order by 
aircraft_reg, lower(booking_time);




Pretty sure lower() is not needed, if I'm following this correctly:

https://www.postgresql.org/docs/12/functions-range.html

"The simple comparison operators <, >, <=, and >= compare the lower 
bounds first, and only if those are equal, compare the upper bounds. 
These comparisons are not usually very useful for ranges, but are 
provided to allow B-tree indexes to be constructed on ranges."


Ah, good - thanks for pointing that out.

In the case where the lower bound  is the same I'm thinking using 
lower() will result in different ordering under different circumstances:


I see what you mean. It shouldn't matter for our use case; ordering on 
the aircraft registration and time is what counts for us, and the output 
of the function ought to produce well-ordered booking times for each 
aircraft. The other columns are used for display purposes only.


I need to play with it a bit more: for example, if a long, 
lower-priority booking is behind a short, higher-priority one such 
that the long one extends both before and after the short one, then 
the range-difference operator will give me an error about a 
non-contiguous result. However, I think I'm heading in the right 
direction now.


Great. Good luck going forward.


Thanks again for your help - much appreciated!

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Overlapping timestamptz ranges with priority

[Ray O'Donnell]

On 03/07/2021 18:59, Adrian Klaver wrote:


I'm not sure this is doing what you think it is;


[...]


  select * from get_visible_bookings();
  booking_id | aircraft_reg | type_code | booking_time | 
owner_uid | owner_name
+--+---+-+---+ 


   1 | A1ZX | type1 | ["2021-07-03 10:00:00-07","2021-07-03 
14:00:00-07"] | 1 | aklaver
   2 | A1ZX | type1 | ("2021-07-03 14:00:00-07","2021-07-03 
16:00:00-07"] | 1 | aklaver
   3 | A1ZX | type1 | ["2021-07-04 09:00:00-07","2021-07-04 
12:00:00-07"] | 1 | aklaver
   4 | B2CA | type2 | ["2021-07-03 09:00:00-07","2021-07-03 
10:00:00-07") | 2 | wilbur
(4 rows)


The booking_id for aircraft B2CA with booking_time of  ["2021-07-03 
11:00:00-07","2021-07-03 14:00:00-07"] is not accounted for. There is a 
step missing that accounts for bookings being assigned to a particular 
aircraft.


Yes, you're right - I realised that after I sent my last email. The 
inner loop in the function should have matched overlapping bookings by 
aircraft registration:


-- For each booking, check whether there are any with
-- a higher priority and whose times overlap it.
for m_overlapping in
  select booking_id, booking_time from bookings
  where booking_id < m_rec.booking_id
  and booking_time && m_rec.booking_time
loop
  -- Snip away any overlapping (obscured) time.
  m_visible_time := m_visible_time - m_overlapping.booking_time;
end loop;

When this is corrected, I get what I'm looking for (trying it here with 
your data):


set time zone 'America/Los_Angeles';
SET

select booking_id, aircraft_reg, booking_time from bookings order by 
aircraft_reg, lower(booking_time);


 booking_id | aircraft_reg |booking_time 


+--+-
 25 | A1ZX | ["2021-07-03 10:00:00-07","2021-07-03 
14:00:00-07")
 26 | A1ZX | ["2021-07-03 12:00:00-07","2021-07-03 
16:00:00-07")
 27 | A1ZX | ["2021-07-04 09:00:00-07","2021-07-04 
12:00:00-07")
 28 | B2CA | ["2021-07-03 09:00:00-07","2021-07-03 
12:00:00-07"]
 29 | B2CA | ["2021-07-03 11:00:00-07","2021-07-03 
14:00:00-07"]

(5 rows)

select booking_id, aircraft_reg, booking_time from 
get_visible_bookings() order by aircraft_reg, lower(booking_time);


 booking_id | aircraft_reg |booking_time 


+--+-
 25 | A1ZX | ["2021-07-03 10:00:00-07","2021-07-03 
14:00:00-07")
 26 | A1ZX | ["2021-07-03 14:00:00-07","2021-07-03 
16:00:00-07")
 27 | A1ZX | ["2021-07-04 09:00:00-07","2021-07-04 
12:00:00-07")
 28 | B2CA | ["2021-07-03 09:00:00-07","2021-07-03 
12:00:00-07"]
 29 | B2CA | ("2021-07-03 12:00:00-07","2021-07-03 
14:00:00-07"]

(5 rows)


gfc_booking6_dev=# set time zone 'America/Los_Angeles';
SET
gfc_booking6_dev=# select booking_id, aircraft_reg, booking_time from 
bookings order by aircraft_reg, lower(booking_time);
 booking_id | aircraft_reg |booking_time 


+--+-
 25 | A1ZX | ["2021-07-03 10:00:00-07","2021-07-03 
14:00:00-07")
 26 | A1ZX | ["2021-07-03 12:00:00-07","2021-07-03 
16:00:00-07")
 27 | A1ZX | ["2021-07-04 09:00:00-07","2021-07-04 
12:00:00-07")
 28 | B2CA | ["2021-07-03 09:00:00-07","2021-07-03 
12:00:00-07"]
 29 | B2CA | ["2021-07-03 11:00:00-07","2021-07-03 
14:00:00-07"]

(5 rows)


I need to play with it a bit more: for example, if a long, 
lower-priority booking is behind a short, higher-priority one such that 
the long one extends both before and after the short one, then the 
range-difference operator will give me an error about a non-contiguous 
result. However, I think I'm heading in the right direction now.


Thanks,

Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Overlapping timestamptz ranges with priority

[Ray O'Donnell]

On 27/06/2021 23:41, Ray O'Donnell wrote:

Hi all,

I'm playing with timestamptz ranges for a hobby project. I have a
table with a tstzrange column, in which the timestamps can overlap;
where they do, rows with a higher priority (derived from a bigint
primary key column) should be picked.

What I'd like to do is present a view which shows timestamp ranges at
 the front of the queue, as it were; where ranges overlap, these may
be segments of a range from a particular row. I'm having trouble with
this and would appreciate suggestions.


I've come up with a way of doing it using a function... it's not going 
to be very efficient if the number of rows gets large, due to nested 
loops, but as the system generally keeps only a limited number of 
bookings (no more that a few hundred), I think it'll do - certainly as a 
first run at it.


Firstly, the table structure (as it now stands) on which the function 
will operate:


CREATE TABLE bookings
(
booking_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
aircraft_reg text NOT NULL,
type_code text NOT NULL,
booking_time tstzrange NOT NULL,
owner_uid text NOT NULL,
owner_name text NOT NULL,

CONSTRAINT bookings_pk PRIMARY KEY (booking_id),

(... foregin keys etc...)
);


And here's the function:

create or replace function get_visible_bookings()
returns setof bookings
language plpgsql
as
$$
declare
  m_rec bookings;
  m_overlapping record;
  m_visible_time tstzrange;
begin
  -- Loop through all bookings on the system, ordered on booking ID.
  -- The booking ID also give the queue priority of the booking:
  -- bookings with a lower ID have a higher priority.
  for m_rec in
select * from bookings order by booking_id
  loop
m_visible_time := m_rec.booking_time;

-- For each booking, check whether there are any with
-- a higher priority and whose times overlap it.
for m_overlapping in
  select booking_id, booking_time from bookings
  where booking_id < m_rec.booking_id
  and booking_time && m_rec.booking_time
loop
  -- Snip away any overlapping (obscured) time.
  m_visible_time := m_visible_time - m_overlapping.booking_time;
end loop;

-- If any of the current booking's time is still visible,
-- then return the row with what's left of the time.
if not isempty(m_visible_time) then
return next row(m_rec.booking_id, m_rec.aircraft_reg,
  m_rec.type_code, m_visible_time,
  m_rec.owner_uid, m_rec.owner_name);
end if;
  end loop;

  return;
end;
$$;



--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Overlapping timestamptz ranges with priority

[Ray O'Donnell]

On 29/06/2021 22:49, Adrian Klaver wrote:

If I'm following correctly then:

1) Under old system there where preset two hour slots over a day period, 
where the period was say 8:00 to 16:00


2) You now want to allow user defined two hour slots over the same 
period, where a slot can't start before 8:00 or end after 16:00.


3) First to file gets the slot.

4) Because of turn around considerations a user can't pick up the 
remaining hour of an overlap. As example in the case of time slots of 
10:00 - 12:00 and 11:00 - 13:00 for a particular plane where the second 
slot was the later one filed the user can't get 12:00 - 13:00.


Sorry for the slow response - I'm only getting to spend intermittent 
time on this project.


Your description isn't far off; however (with reference to your points 
1-4 above):


1. Under the old system, one-hour slots were baked in at a fundamental 
level, though a user could book multiple consecutive 1-hour slots together.


2. I'd now like the database to be agnostic with regard to the slot 
duration. A booking should be defined only by timestamps defining its 
beginning and end; the booking could also span multiple days. The 
concept of a "slot" (a slot being the minimum unit of time which a user 
can book, be it an hour or anything else) will be imposed only at the 
application level... This would allow it to be changed easily if 
required (in other words, slots are a sort of artifial grid view which 
the application imposes on the raw bookings coming from the database).


3. Yes, first to book available time gets it. Anyone else booking for 
the same time is in a queue behind the first in line. Similarly, someone 
making a booking whose time partially overlaps an existing booking will 
effectively finish up with two bookings - one active (first in line for 
the free time) and one queued (behind the already-booked time).


4. Effectively, yes - see my explanation of queued bookings in (3) above.

Thanks for the time and thought you're giving this!

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Overlapping timestamptz ranges with priority

[Ray O'Donnell]

On 29/06/2021 22:10, Adrian Klaver wrote:


The queued bookings are for a particular aircraft or a particular time 
slot?


They're for an aircraft. On the old system, they could only be for a 
slot - so if someone had booked, say, a two-hour slot, then anyone 
queued behind them could only queue for the same two hours. I'd like to 
make the new system more flexible, hence the potentially overlapping ranges.


Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Overlapping timestamptz ranges with priority

[Ray O'Donnell]

On 29/06/2021 20:43, Adrian Klaver wrote:


An ounce of prevention is worth a pound of cure:

1) Install btree_gist
create extension btree_gist ;

2) create table bookings (
     booking_id bigint not null,
     aircraft_id integer,
     booking_time_start timestamptz,
     booking_time_end timestamptz,

     constraint bookings_pk primary key (booking_id),
     constraint timestamp_exclude EXCLUDE USING gist
     (aircraft_id WITH =,
  tstzrange(booking_time_start, booking_time_end, '[]') WITH &&)


[...]


This way the overlap is prevented and you don't have to deal with it later.


Fair point The idea of using overlapping ranges was to allow for 
queued bookings, which is something we permit. In the old system (which 
this one is to replace) queued bookings are kept in a separate table. My 
idea was to have them in a single table, which would seem more elegant - 
but by golly it's harder! Maybe I should rethink my approach.


Thanks,

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Overlapping timestamptz ranges with priority

[Ray O'Donnell]

On 28/06/2021 00:52, Adrian Klaver wrote:

On 6/27/21 3:41 PM, Ray O'Donnell wrote:



Here's a slightly simplified example:


create table bookings (
 booking_id bigint not null,
 booking_time tstzrange not null,

 constraint bookings_pk primary key (booking_id)
);


It seems to me this is missing some reference to what is being booked 
e.g. room number.


Yes, indeed - I left out everything except what was immediately relevant 
to my problem. The real table is actually for booking aircraft - it's 
for the local flying club of which I'm a member - so there are columns 
for aircraft registration, member details, etc.


Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Overlapping timestamptz ranges with priority

[Ray O'Donnell]

Hi all,

I'm playing with timestamptz ranges for a hobby project. I have a table 
with a tstzrange column, in which the timestamps can overlap; where they 
do, rows with a higher priority (derived from a bigint primary key 
column) should be picked.


What I'd like to do is present a view which shows timestamp ranges at 
the front of the queue, as it were; where ranges overlap, these may be 
segments of a range from a particular row. I'm having trouble with this 
and would appreciate suggestions.


Here's a slightly simplified example:


create table bookings (
booking_id bigint not null,
booking_time tstzrange not null,

constraint bookings_pk primary key (booking_id)
);

insert into bookings (booking_id, booking_time) values
(1, tstzrange('2021-06-20 12:00+01', '2021-06-20 14:00+01', '[)')),
(2, tstzrange('2021-06-20 13:00+01', '2021-06-20 16:00+01', '[)'));


And what I'd like to be able to do is pull out the following:


 booking_id |slot_time
+-
  1 | ["2021-06-20 12:00:00+01","2021-06-20 14:00:00+01")
  2 | ["2021-06-20 14:00:00+01","2021-06-20 16:00:00+01")


As you can see, where the two slots overlap (between 13:00 and 14:00), 
the booking with the higher priority (lower booking ID) takes precedence.


The closest I've got to it is this:


select a.booking_id, a.booking_time - b.booking_time from bookings a
inner join bookings b on (a.booking_id < b.booking_id)
union
select d.booking_id, d.booking_time - c.booking_time from bookings d
inner join bookings c on (d.booking_id > c.booking_id)


This gives me:


 booking_id |  ?column?
+-
  1 | ["2021-06-20 12:00:00+01","2021-06-20 13:00:00+01")
  2 | ["2021-06-20 14:00:00+01","2021-06-20 16:00:00+01")


...which is missing the bit where they overlap; and anyway, when I add 
in more bookings, it gives me nonsense results. :-)


Any pointers will be greatly appreciated!

Many thanks in advance,

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: pgTAP installation

[Ray O'Donnell]

On 20/06/2021 14:24, Vijaykumar Jain wrote:


This was a wrong suggestion. sorry.
i'll try to reproduce what resulted in functions not getting loaded
in your case.


ok i guess you have a typo there. the function name is *no_plan()*, but 
you called *noplan() *


Aaaargh - you're right, that's what the problem was. Thanks very much, 
and sorry for the noise.


Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

pgTAP installation

[Ray O'Donnell]

Hi all,

I'm just tipping my toe into the water with pgTAP for the first time, 
and must be doing something wrong - I can't seem to get it working.


I'm on Kubuntu Hirsute, and I've installed both PostgreSQL 13 and pgTAP 
from apt.postgresql.org:


   sudo apt install postgresql-13
   sudo apt install postgresql-13-pgtap

Then I connected to my database and installed pgTAP there:

create extension pgtap;

And I can see it there:

gfc_booking6_dev=# \dx
 List of installed extensions
  Name   | Version |   Schema   | Description
-+-++--
 pgtap   | 1.1.0   | public | Unit testing for PostgreSQL
 plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)


However, when I try to use it, I get:

gfc_booking6_dev=# select * from noplan();
ERROR:  function noplan() does not exist
LINE 1: select * from noplan();
  ^
HINT:  No function matches the given name and argument types. You might 
need to add explicit type casts.


Am I missing a step somewhere?

Many thanks in advance,

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: SELECT in VCHAR column for strings with TAB

[Ray O'Donnell]

On 10/06/2021 14:30, Matthias Apitz wrote:


Hello,

I want to search in a VCHAR column for a string with two TAB at the end.
I tried some things w/o any luck, like:

select * from acq_vardata where name=concat('Test202112', 9, 9);
select * from acq_vardata where name=concat('Test202112', '\t\t');


Maybe use a regular expression?

https://www.postgresql.org/docs/13/functions-matching.html


Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Hello - About how to install PgAdmin4 on Debian 10

[Ray O'Donnell]

On 27/03/2021 14:59, Ray O'Donnell wrote:

If you can, include the relevant bits from the pgAdmin log, which 
according to [2] should be here:


  ~/.pgadmin/pgadmin4.log


A quick find + grep also found me these:

~/.local/share/pgadmin4.startup.log
~/.local/share/pgadmin/pgadmin4/.pgAdmin4.startup.log

...though the second one is much older.

Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Hello - About how to install PgAdmin4 on Debian 10

[Ray O'Donnell]

Hello Carlos,

This sounds like a different issue, and you would have more luck asking 
about it on the pgadmin-support list [1], which the developers follow.


If you can, include the relevant bits from the pgAdmin log, which 
according to [2] should be here:


  ~/.pgadmin/pgadmin4.log

Ray.


[1] https://www.pgadmin.org/support/list/

[2] https://www.pgadmin.org/faq/#8



--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Hello - About how to install PgAdmin4 on Debian 10

[Ray O'Donnell]

On 24/03/2021 20:17, Carlos Montenegro wrote:

Yes Ray,
I followed those instructions, but it seems the repository location 
changed. It is not available, but thank you so much for your support.


It seems to be available for me:

$ sudo apt update

[]

Hit:10 https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/buster 
pgadmin4 InRelease


[]

Sorry if it's a silly question, but are you sure that you added the 
pgAdmin repo (at ftp.postgresql.org) as opposed to the main PostgreSQL 
repo (at apt.postgresql.org)?


Best,

Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Hello - About how to install PgAdmin4 on Debian 10

[Ray O'Donnell]

On 24/03/2021 19:08, Carlos Montenegro wrote:

Hello dear Ray.
Thanks for your answer.

Yes, I see pgadmin4 and then tried, but any success.
How did you make the installation? Any advice?


Hi Carlos,

I had forgotten - pgAdmin has its own apt repository, which you need to add:

https://www.pgadmin.org/download/pgadmin-4-apt/

Once you've done that, then "apt update" and "apt install..." should do 
the job.


I hope this helps.

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Hello - About how to install PgAdmin4 on Debian 10

[Ray O'Donnell]

On 24/03/2021 17:17, Carlos Montenegro wrote:

Hello dear community.
your help !

I am trying to install PgAdmin4 on Debian 10 following the instructions 
on here:

https://wiki.postgresql.org/wiki/Apt 

but when execute:
 > sudo apt-get install pgadmin4 or
 > sudo apt-get install pgadmin4 pgadmin4-apache2

the system says: The package pgadmin4 does not have any candidate for 
installation.


On my laptop (Debian Buster), "apt-cache search pgadmin4" finds the 
following:


pgadmin4-desktop
pgadmin4-server
pgadmin4-web
pgadmin4

I don't see "pgadmin4-apache2" there. I have pgadmin4-server and 
pgadmin4-desktop installed and working fine.


Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Copy & Re-copy of DB

[Ray O'Donnell]

On 21/01/2021 13:13, sivapostg...@yahoo.com wrote:

Hello,

I'm from SQL Server now developing my current project using PG.

In our earlier project(s), we used to copy the database from one server 
to another, work in another server, and re-copy it in the original 
server.   All happened easily with just transferring the two files (mdf 
& ldf) to our required server.


Want to replicate the same in PG.  Is there any way to accomplish the 
same in PG ?


create database  template 

Would that do the job? You could also use pg_dump.

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: format return of "age" to hh:mm

[Ray O'Donnell]

On 05/03/2020 15:50, David Gauthier wrote:
> Hi:
> 
> How does one reformat the output of the "age" function to always be in
> terms of hours:mins.

Hi there,

age() returns an interval, so without having tried it I'm guessing you
could use to_char() to format it whatever way you want.

Ray.

-- 
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Upgrade to 12.2 using same data directory

[Ray O'Donnell]

On 25/02/2020 12:52, Mihalidesová Jana wrote:
> I think it’s strange to change the data directory $PATH every time I
> upgrade the software, when the data are the same. I have a specific
> naming convention not using a db version.

There's a lot to recommend keeping an untouched copy of your old data,
in case something goes wrong

Ray.

-- 
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: POLL: Adding transaction status to default psql prompt

[Ray O'Donnell]

On 06/02/2020 02:54, Vik Fearing wrote:
> Hello,
> 
> I proposed a patch to add %x to PROMPT1 and PROMPT2 by default in psql.
> 
> The effect of this is:
> 
> - nothing at all when not in a transaction,
> - adding a '*' when in a transaction or a '!' when in an aborted
>   transaction.
> 
> Before making a change to a long-time default, a poll in this group was
> requested.
> 
> Please answer +1 if you want or don't mind seeing transaction status by
> default in psql or -1 if you would prefer to keep the current default.

+1


-- 
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: How to transfer databases form one server to other

[Ray O'Donnell]

On 27/01/2020 12:40, Andrus wrote:
> Hi!
> 
>>> Postgres version 12 pg_dump probably cannot installed in old server
>>> (Debian Squeeze 9).
> 
>> I have it installed on my Debian 9 laptop, from the PostgreSQL apt repo:
> 
>>  https://apt.postgresql.org
> 
> Oled server uses Debian Sqeeze whose version is 6.
> I mistakenly typed Debian Squeeze 9, I'm sorry.

No problem! :-)

R.

-- 
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: How to transfer databases form one server to other

[Ray O'Donnell]

On 26/01/2020 22:47, Andrus wrote:
> Hi!
> 
>> Before you do any of this I would check the Release Notes for the
>> first release of each major release. Prior to version 10 that would be
>> X.X.x where X is a major release. For 10+ that is X.x.  I would also
>> test the upgrade before doing it on your production setup.
> 
> I want to create test transfer first, check applications work and after
> that final transfer.
> 
>> Best practice if you are going the dump/restore route is to use the
>> pg_dump binary from the new server(12) to dump the old server(9.1)
> 
> Postgres version 12 pg_dump probably cannot installed in old server
> (Debian Squeeze 9).

I have it installed on my Debian 9 laptop, from the PostgreSQL apt repo:

  https://apt.postgresql.org

Ray.

-- 
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: What am I doing wrong here?

[Ray O'Donnell]

On 26/12/2019 13:36, stan wrote:
>   IF _bom_name_key is NULL
>   THEN
> WITH inserted AS (
>   INSERT into project_bom 
>   (project_key, bom_name)
>   VALUES
>   (NEW.project_key , 'Main') 
>   RETURNING 
>   project_bom_key
>   ) 
> /* Syntax error flagged at this line */
> _bom_name_key  = ( SELECT 
>   project_bom_key 
>  FROM inserted )
> ;
>   ELSE
>   NEW.project_bom_key = _bom_name_key;
>   END IF;
>   END IF;


You need to use the SELECT INTO syntax:

  with inserted as (
 
  )
  select project_bom_key into _bom_name_key
  from inserted
  (etc)

Likewise, while I don't think there's anything wrong with the earlier
assignment -

  _bom_name_key := (select);

- to my eye the SELECT INTO looks more natural:

  select project_bom_key
  into _bom_name_key
  from ... (etc).

It's a PL/pgSQL construct - full details here:

https://www.postgresql.org/docs/12/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

I hope that this helps.

Ray.


-- 
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: server will not start (Debian)

[Ray O'Donnell]

On 10/12/2019 16:11, Adrian Klaver wrote:
> On 12/10/19 7:32 AM, stan wrote:
>> On Tue, Dec 10, 2019 at 08:55:02AM -0500, Justin wrote:
>>> Hi Stan
>>>
>>> Check security make sure V12 postgres  has the correct credentials
>>>
>> OK,
>>
>> postgres@stantest:/var/run/postgresql$ ls -ld
>> drwxrwsr-x 2 postgres postgres 40 Dec 10 08:35 .
>>
>> Looks correct to me.
>>
>> This sundirectory 12-main.pg_stat_tmp keeps getting deleted.
>>
>> If I create it by hand as user psotgres, the DB starts up when I run:
>>
>> /usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/main -c
>> config_file=/etc/postgresql/12/main/postgresql.conf
>>
>> But tring to start it with /etc/ini.d/postgresql start fails
>>
> 
> How did you install Postgres 12?
> 
> Why are you using the file in init.d/ instead of pg_ctlcluster?

Or indeed systemctl? :-)

Ray.

-- 
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: SELECT returnig a constant

[Ray O'Donnell]

On 15/10/2019 15:01, stan wrote:

Thanks, as you can see from my SOLVED reply, I go that part figured out.
Now I am trying to figure out how to complete this. The SELECT returns more
than 1 row, and when I put that in the VALUES clause this does not work.


Please reply to the list, rather than to me - reply-all should do the 
trick. :-)


You're using cross-joins, which returns the cartesian product of the 
tables concerned, without any WHERE clause so if the tables are big 
you'll return a LOT of data.


Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: SELECT returnig a constant

[Ray O'Donnell]

On 15/10/2019 14:28, stan wrote:

I used to be able to return a constant value in a SELECT statement in
ORACLE. I need to populate a table for testing, and I was going to do so
like this:

SELECT
 employee.id ,
project.proj_no ,
work_type.type  ,
'rate' 1
FROM employee
CROSS JOIN project
CROSS JOIN work_type;

This statement works correctly, till I add the last "'rate' 1 line, then it
returns a syntax error.


I don't think you can use a number as a column name. Give it a different 
name and it should work:


SELECT  , 'rate' my_constant_name FROM ...

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Phone number type extension

[Ray O'Donnell]

On 28/09/2019 11:13, Morris de Oryx wrote:
For clarification, what do you mean by "phone number"? I'm not being 
dense, I'm wondering if you're looking for a type that handles only 
numbers from one country, or that can deal with the rules for a variety 
of countries.


That was my first thought also. For example, here in Ireland we have 
different numbers of digits in both area code and local number depending 
on where in the country you are - and in fact, we can't always agree on 
where the area code ends and the local number begins! :-)


Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: backing up the data from a single table?

[Ray O'Donnell]

On 13/09/2019 15:13, stan wrote:

My development methodology is to create scripts that init the db, and load
test data, as I make changes.

Now, I am starting to move toward a test instance with real data. The end
user has provided "real" test data, n spreadsheets. I have the inputing of
data from these working OK. (takes a bit of hand work). What I would like
to do, is  continue to init the "test" database. To make that easy, I would
like to be able to "export" individual tables, so I can use the scripts to
reload them. I prefer not to have to convert these to my load script
format.

Is there a way to "export" a single table, that can be easily re
"imported"?


Yes, absolutely:

  pg_dump -t  (etc)

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: PGAdmin4.11.1 on Ubuntu 18.04

[Ray O'Donnell]

On 01/08/2019 16:32, Tony Shelver wrote:
The article at stackoverflow is here 



Thanks for the link! The solution given in one of the comments there 
worked for me (Debian 9):


- Install pip3 if needed: sudo apt install python3-pip

- pip3 install psycopg2-binary (doesn't need pg_config)

- Edit ~/.config/pgadmin/pgadmin4.conf and set 
PythonPath="/home/rod/.local/python3.5;/home/rod/.local/python3.5/site-packages"


I then needed to kill the pgAdmin4 server so that it picked up the new 
setting.


Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Why does the pg_dumpall command have a database option?

[Ray O'Donnell]

On 21/06/2019 14:32, Espresso Beanies wrote:

Will it still dump all of the databases or just the one it connects to?

On Thu, Jun 20, 2019 at 4:13 PM Adrian Klaver > wrote:


On 6/20/19 1:03 PM, Espresso Beanies wrote:
 > I'm trying to see if someone could answer to me why the pg_dumpall
 > command has a database option when it's designed to dump all the
 > databases in a PostgreSQL server instance. I'm only asking
because when
 > I create a .pgpass file and try to use the pg_dumpall command,
I'm still
 > required to specify a specific database even though the command
itself
 > should be targeting all databases. Can anyone explain this to me
a bit
 > better?
 >
 > Thanks,

Because pg_dumpall is a client and needs to connect to a database to
kick start the process/fetch global information:


It'll dump all the databases.

To expand on what Adrian said - PostgreSQL doesn't have a concept of 
connecting just to the server - you always have to connect to a specific 
database as well, no matter what you're actually doing. This is what the 
"postgres" database is mostly used for.


HTH,

Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Connection refused (0x0000274D/10061)

[Ray O'Donnell]

On 09/06/2019 20:49, Sourav Majumdar wrote:

Hello,
Thanks for your quick response. I am new to pgsql, didnot configured it 
, can you please give a guidance for that? I will be highly oblised if 
you can assist me.

I am using windows 8.1, 64 bit.
ruby 2.3.3p222 (2016-11-21 revision 56859) [i386-mingw32]
Rails 5.2.3
Trying to setup ruby on rails on my local host


Hi,

PostgreSQL's default port is 5432, and if the Postgres instance on your 
computer is the only one there then that's what it'll be listening on.


I don't know anything about Ruby or how you connect to PG from it, but 
you need to tell it to use port 5432.


Alternatively, if you really want to use port 3000, then you need to 
edit postgresql.conf and change the "port" setting there. You'll need to 
restart PG for this to take effect.


Either way, you need to connect to the port on which PG is listening.

Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Connection refused (0x0000274D/10061)

[Ray O'Donnell]

On 09/06/2019 19:51, Sourav Majumdar wrote:


could not connect to server: Connection refused (0x274D/10061)
Is the server running on host "localhost" (::1) and accepting TCP/IP
connections on port 5432? could not connect to server: Connection
refused (0x274D/10061) Is the server running on host "localhost"
(127.0.0.1) and accepting TCP/IP connections on port 5432?


Hi there,

From the above, you have the server listening on port 3000 (you would 
have set this in postgresql.conf - did you?), but the client is trying 
to connect on port 5432 (the default).


You therefore need to tell the client to connect to port 3000 - if you 
are using psql, you need the -p option:


   psql -p 3000 (... etc ...)

HTH,

Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Converting yes or no to one letter strings.

[Ray O'Donnell]

On 4 June 2019 23:30:33 Lou  wrote:

Hi everyone,
Is it possible to convert a boolean yes or no field to hold a one letter 
string? For example, the strings: 's' 'f' 'p' 'e'
To start off, I just need to convert true to 's'. false will have to be 
manually changed to 'f' or 'p' or 'e' as appropriate.

Lou

select case when (boolean variable) then (whatever) else (whatever else) end;

Re: duplicate key value violates unique constraint "chinese_price_infos_pkey"

[Ray O'Donnell]

On 06/05/2019 12:10, Arup Rakshit wrote:

Hi,

Thanks for your reply. It is automatic, my app don’t creates ID, it
delegates it to the DB. I am using Ruby on Rails app, where we use
Postgresql.


Well, I'm only throwing out wild guesses, but another possibility is 
that rows were loaded manually into that table which had different 
values for the id column; this would lead to what you see.


Anyway, I'd fix the sequence value first with setval(), and then observe 
for a while; if you're still getting id collisions, then something is 
definitely inserting id values independently of the sequence. Remember, 
the sequence is only used as a default for that column; if you insert a 
value into the column, the sequence isn't used and so its current value 
doesn't change.


Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: duplicate key value violates unique constraint "chinese_price_infos_pkey"

[Ray O'Donnell]

On 06/05/2019 12:05, Arup Rakshit wrote:

Every time I try to insert I get the error:

docking_dev=# INSERT INTO "chinese_price_infos" ("item_code",
"price_cents", "unit", "description", "company_id", "created_at",
"updated_at") VALUES ('01GS10001', 6000, 'Lift', 'Shore Crane
Rental', '9ae3f8b8-8f3f-491c-918a-efd8f5100a5e', '2019-05-06
10:49:03.894725', '2019-05-06 10:49:03.894725'); ERROR:  duplicate
key value violates unique constraint "chinese_price_infos_pkey" 
DETAIL:  Key (id)=(71165) already exists. docking_dev=# INSERT INTO

"chinese_price_infos" ("item_code", "price_cents", "unit",
"description", "company_id", "created_at", "updated_at") VALUES
('01GS10001', 6000, 'Lift', 'Shore Crane Rental',
'9ae3f8b8-8f3f-491c-918a-efd8f5100a5e', '2019-05-06 10:49:03.894725',
'2019-05-06 10:49:03.894725'); ERROR:  duplicate key value violates
unique constraint "chinese_price_infos_pkey" DETAIL:  Key
(id)=(71166) already exists.

Then I found:

docking_dev=# SELECT MAX(id) FROM chinese_price_infos; max  
128520 (1 row)


docking_dev=# SELECT nextval('chinese_price_infos_id_seq'); nextval 
- 71164 (1 row)


Not sure how it is out of sync. How can I fix this permanently. I ran
vacuum analyze verbose; still same error.



You can fix it by using setval() to set the sequence manually to 
something higher than the highest current id value in the table. 
However, it sounds as if something in the application code may be 
causing problems For example, is something generating id values 
without reference to the sequence?


Ray.



--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: ERROR: operator does not exist: timestamp without time zone + integer

[Ray O'Donnell]

On 30/04/2019 16:00, Andrew Gierth wrote:

"Adrian" == Adrian Klaver  writes:


  Adrian> Or cast to a date:

  Adrian> test=> select date_trunc('day', localtimestamp)::date + 7;

yeesh. that's a very long-winded way to write current_date + 7


Well, current_date is different: current_date returns a date, so you 
only have to do:


   select current_date + 7;

The original question (if I remember correctly; have zapped it now) was 
about adding an integer to a timestamp, hence the need to truncate it to 
a date first as in Adrian's example above.


Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Resetting identity columns

[Ray O'Donnell]

On 22/04/2019 18:49, Tom Lane wrote:

"Ray O'Donnell"  writes:

In general, then, is it not possible to use an expression thus? -



  [...] ALTER COLUMN [...] RESTART WITH 


No.  In general, PG's utility commands (everything except SELECT/
INSERT/UPDATE/DELETE) don't do expression evaluation.  Partly this
is laziness or lack of round tuits, but in some cases there are
also interesting semantic problems.


Fair enough. Thanks Tom, Adrian & Thomas for the help!

Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Resetting identity columns

[Ray O'Donnell]

On 22/04/2019 17:02, Adrian Klaver wrote:


do language plpgsql $$
declare m_max_id bigint;
begin
select max(id) + 1 from identity_test into m_max_id;
EXECUTE 'alter table identity_test alter column id restart with ' || 
m_max_id;

end;
$$;


Thanks a million Adrian - EXECUTE did the job, and I finished up 
wrapping it in a function as I used it in a number of places in the 
larger migration script:


create function reset_identity(
p_table text,
p_column text
)
returns text
as
$$
declare
m_max_id bigint;
begin
execute 'select max(' || quote_ident(p_column) || ') + 1 from '
   || quote_ident(p_table) into m_max_id;
execute 'alter table ' || quote_ident(p_table)
   || ' alter column ' || quote_ident(p_column)
   || ' restart with ' || m_max_id;

return 'New identity value for ' || p_table || '.' || p_column
   || ': ' || m_max_id;
end;
$$
language plpgsql;


In general, then, is it not possible to use an expression thus? -

[...] ALTER COLUMN [...] RESTART WITH 

Thanks,

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Resetting identity columns

[Ray O'Donnell]

Hi all,

I'm probably doing something silly I'm migrating data from one 
database table to another, where the old table used a SERIAL primary key 
and the new one uses GENERATED BY DEFAULT AS IDENTITY. Having loaded the 
data into the new table, I need to reset the underlying sequence so that 
it picks up from the highest existing value.


I'm using PostgreSQL 11.2 on Debian 9.

I've tried:

=# alter table orders alter column order_id restart with (
select max(order_id) + 1 from orders);

ERROR:  syntax error at or near "("
LINE 1: ...r table orders alter column order_id restart with (select ma...


I also tried it with a DO block:

=# do language plpgsql $$
$# declare m_max_id bigint;
$# begin
$# select max(order_id) + 1 from orders into m_max_id;
$# alter table orders alter column order_id restart with m_max_id;
$# end;
$# $$;

ERROR:  syntax error at or near "m_max_id"
LINE 5: ...er table orders alter column order_id restart with m_max_id;


What am I missing?

I should add that this is part of a larger migration script; otherwise I 
could just do it by hand the command line.


Thanks in advance,

Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Performance comparison between Pgsql 10.5 and Pgsql 11.2

[Ray O'Donnell]

On 01/03/2019 15:01, Nicola Contu wrote:

Hello,
is there any reason why I am getting worse results using pgsql11.2 in 
writing comparing it with pgsql 10.6?


I have two Instances, both just restored, so no bloats.
Running read queries I have pretty much same results, a little bit 
better on pg11- Running writes the difference is in favour of 10.


Did you run ANALYZE on the databases after restoring?

Ray.



--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: pgexpress 4.60 vita voom

[Ray O'Donnell]

On 30/01/2019 14:19, Ray O'Donnell wrote:

On 30/01/2019 11:22, ceuro wrote:

Hello everybody, this is my first post.
I need to update to vita voom pgexpress 4.60 in order to upgrade to
postgresql 9.2:
I've tried to contact vita voom to collect (I've also already payed) the
last release of the driver but nobody answer me:
Is there anybody that could help me in finding the driver?
What you suggest to use instead of pgexpress to connect new release of
postgresql 9.2?
(of course for me it should be easier to use pgexpress as I already 
use an

old release to connect postgresql 8.2)


Hi there,

It's a proprietary driver, so you'll have to pursue Vita Voom, if they 
still exist.


Actually, I just had a quick look at the Vita Voom website, and the last 
news item on it is dated October 2012 so that's not a good sign.


Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: pgexpress 4.60 vita voom

[Ray O'Donnell]

On 30/01/2019 11:22, ceuro wrote:

Hello everybody, this is my first post.
I need to update to vita voom pgexpress 4.60 in order to upgrade to
postgresql 9.2:
I've tried to contact vita voom to collect (I've also already payed) the
last release of the driver but nobody answer me:
Is there anybody that could help me in finding the driver?
What you suggest to use instead of pgexpress to connect new release of
postgresql 9.2?
(of course for me it should be easier to use pgexpress as I already use an
old release to connect postgresql 8.2)


Hi there,

It's a proprietary driver, so you'll have to pursue Vita Voom, if they 
still exist.


I used to use it years ago for Delphi stuff, but I got tired of having 
to buy it all over again whenever I upgraded PostgreSQL, so I moved 
elsewhere. I've no problem paying for good software, but I didn't like 
having to do it over and over again.


A separate issue is that PostgreSQL 9.2 is very old indeed, and is no 
longer supported.


Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Multiple postgresql clusters with same version and separate binaries

[Ray O'Donnell]

On 04/01/2019 14:21, Erika Knihti-Van Driessche wrote:

Hi,

I have 10+ postgresql clusters (v 9.6) running on one machine - each 
having their own data directory and port. They all share same binaries 
though, and this is now giving me some headache.. I cannot shutdown all 
clusters at the same time, so upgrading them is quite impossible.


I know that running several versions of postgres is possible on one 
machine - I suppose this would also work out with same version running 
on different locations, like /db1/PostgreSQL/10, /db2/PostgreSQL/10.. 
etc..? Has anyone any experience with this kind of problem?


What OS are you running? The Debian packages from apt.postgresql.org 
allows multiple versions to co-exist on the same machine (though 
naturally on different ports), and multiple clusters within each version 
(again on different ports).


Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: initialize and use variable in query

[Ray O'Donnell]

On 29/12/2018 15:40, Glenn Schultz wrote:

All,

I need to initialize a variable and then use it in query.  
Ultimately this will part of a recursive CTE but for now I just need to 
work this out.  I followed the docs and thought I needed something like 
this.  But does not work-maybe I have misunderstood.  Is this possible?


Hi there,

What does "does not work" mean? What error do you get?



SET max_parallel_workers_per_gather = 8;
SET random_page_cost = 1;
SET enable_partitionwise_aggregate = on;
Do $$
Declare startdate date;
BEGIN
startdate := (select max(fctrdt) from fnmloan);
END $$;


A couple of things off the top of my head:

(i) I think you need "language plpgsql" (or whatever) after the DO block.

(ii) That assignment in the DO should probably be:

  select max(fctrdt) into startdate from fnmloan;


I hope this helps.

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie