Re: here does postgres take its timezone information from?

Thomas Munro writes: > FreeBSD users already have the choice between zoneinfo from base or > the misc/zoneinfo port if for some reason they want to control tzdata > updates separately. PostgreSQL and FreeBSD both track tzdata closely, > and both pushed a commit for version 2019c into their stable

CASE(?) to write in a different column based on a string pattern

Hi,     I need to create a CASE (I think) statement to check for a string pattern, and based on its value, write a substring in a different column (alias). I'm trying to create a COPY statement to port a table into antoher database, which has a table with another format (that's why the aliases)

Re: CASE(?) to write in a different column based on a string pattern

On Wed, 13 Nov 2019 at 16:24, Moreno Andreo wrote: > |foo |bar |baz | > 1234 > 5678 > 9012 > (hoping text formatting is ok... 1234 should go in column foo, 568 in > bar and 9012 in baz) > > Is it possible? Simplest way in plain SQL would be individual ca

Re: CASE(?) to write in a different column based on a string pattern

So what you are doing is transforming the table format from vertical to horizontal. I think you will want to use a union to join the table to itself along with the case statement to produce the output you are looking for. On Wed, Nov 13, 2019 at 10:37 AM Geoff Winkless wrote: > On Wed, 13 Nov 2

Re: CASE(?) to write in a different column based on a string pattern

Il 13/11/19 17:48, Andrew Kerber ha scritto: So what you are doing is transforming the table format from vertical to horizontal.  I think you will want to use a union to join the table to itself along with the case statement to produce the output you are looking for. Not precisely, the string

Re: CASE(?) to write in a different column based on a string pattern

Il 13/11/19 17:36, Geoff Winkless ha scritto: Simplest way in plain SQL would be individual case statements for each column, I think. SELECT pattern, CASE WHEN pattern LIKE 'foo%' THEN SUBSTR(pattern, 4) ELSE '' END AS foo CASE WHEN pattern LIKE 'bar%' THEN SUBSTR(pattern, 4) ELSE '' END

terminated by signal 11: Segmentation fault

Hi All, This is the second time I get this error since Postgresql 12 was officially released. My version: PostgreSQL 12.0 (Ubuntu 12.0-2.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit I am running a Warehouse Management system in a Live environm

Last autovacuum time - what is it?

Hello! I need to ask about last_autovacuum column in *pg_stat_all_tables.* I'm not sure is this a starting time, or when the daemon finished. I make an "accident" with changing many rows in a huge table. I didn't want to start autovacuum with this operation. They reported slowing, but I'm not

Re: terminated by signal 11: Segmentation fault

Gerrit Fouche writes: > This is the second time I get this error since Postgresql 12 was officially > released. My version: > PostgreSQL 12.0 (Ubuntu 12.0-2.pgdg18.04+1) on x86_64-pc-linux-gnu, > compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit Given that this failed in an UPDATE, I

Re: Last autovacuum time - what is it?

Durumdara writes: > I need to ask about last_autovacuum column in *pg_stat_all_tables.* > I'm not sure is this a starting time, or when the daemon finished. Looks like it's the time that the vacuum operation finished. regards, tom lane

`pg_ls_dir` can query some directories, but not others

Copying here a question I asked on StackOverflow: https://stackoverflow.com/questions/58846076 === On my system, `/home` and `/etc` have exactly the same permissions: ``` $ ls -ld /home /etc drwxr-xr-x 67 root root 4096 Nov 13 15:59 /etc drwxr-xr-x 3 root roo

Re: `pg_ls_dir` can query some directories, but not others

On 11/13/19 2:32 PM, Brennan Vincent wrote: Copying here a question I asked on StackOverflow: https://stackoverflow.com/questions/58846076 === On my system, `/home` and `/etc` have exactly the same permissions: ``` $ ls -ld /home /etc drwxr-xr-x 67 root root

ERROR: COPY escape must be a single one-byte character (multi-delimiter appears to work on Postgres 9.0 but does not on Postgres 9.2)

Hello, I have a Talend enterprise job that loads data into a PostgreSQL database via the COPY command. When migrating to a new server this command fails with the following error message: org.postgresql.util.PSQLException:ERROR: COPY escape must be a single one-byte character The thing is, I looke

Re: ERROR: COPY escape must be a single one-byte character (multi-delimiter appears to work on Postgres 9.0 but does not on Postgres 9.2)

On 11/13/19 4:40 PM, Brandon Ragland wrote: Hello, I have a Talend enterprise job that loads data into a PostgreSQL database via the COPY command. When migrating to a new server this command fails with the following error message: org.postgresql.util.PSQLException:ERROR: COPY escape must be a

Re: `pg_ls_dir` can query some directories, but not others

> On Nov 13, 2019, at 6:33 PM, Adrian Klaver wrote: > > On 11/13/19 2:32 PM, Brennan Vincent wrote: >> Copying here a question I asked on StackOverflow: >> https://stackoverflow.com/questions/58846076 >> === >> On my system, `/home` and `/etc` have exactly t

Re: Wall shiping replica failed to recover database with error: invalid contrecord length 1956 at FED/38FFE208

Hello. At Wed, 2 Oct 2019 19:24:02 -0400, Stephen Frost wrote in > Greetings, > > * Tom Lane (t...@sss.pgh.pa.us) wrote: > > Stephen Frost writes: > > > * Aleš Zelený (zeleny.a...@gmail.com) wrote: > > >> But recovery on replica failed to proceed WAL file > > >> 00010FED0039 with

root page 3 of index "pg_class_oid_index" has level 0, expected 1

Hi, During major version upgrade from PostgreSQL v9.4 to v9.6 I facing following error: Performing Consistency Checks - Checking cluster versions ok connection to database failed: FATAL: root page 3 of index "pg_class_oid_index" has

Re: root page 3 of index "pg_class_oid_index" has level 0, expected 1

Abhijit Gharami wrote: > During major version upgrade from PostgreSQL v9.4 to v9.6 I facing following > error: > > Performing Consistency Checks > - > Checking cluster versions ok > > connection to database failed: FATAL: root page 3

Re: terminated by signal 11: Segmentation fault

Hi Tom, Thank you, we have that same scenario. Regards Gerrit On Wed, 13 Nov 2019, 20:57 Tom Lane, wrote: > Gerrit Fouche writes: > > This is the second time I get this error since Postgresql 12 was > officially > > released. My version: > > PostgreSQL 12.0 (Ubuntu 12.0-2.pgdg18.04+1) on x86_64

Re: root page 3 of index "pg_class_oid_index" has level 0, expected 1

On Thu, Nov 14, 2019 at 08:26:48AM +0100, Laurenz Albe wrote: > If you have data corruption, you shouldn't use pg_upgrade to upgrade. > pg_dumpall / psql is the way to go. Please refer to this wiki page: https://wiki.postgresql.org/wiki/Corruption If you have a cluster in such a state, you have r