Re: [GENERAL] Problem JDBC, AutoCommit ON and SELECT FOR UPDATE

Philippe EMERIAUD wrote on 06.03.2015 17:27: Hi all, We have an application based on DB2 database, We are testing this same application on PostgreSQL database. By default we are in autocommit on mode. On DB2 (and Oracle), the query SELECT FOR UPDATE locks the row until the resultset is closed.

Re: [GENERAL] Basic Question on Point In Time Recovery

Robert Inder schrieb am 12.03.2015 um 12:52: > Postgres on the standby machine is continually reading those files. > But that is all it will do. "pg_dump" just says "The database is starting up". > > Could/should I have something configured differently? > > Or Is this something that has changed w

Re: [GENERAL] Name spacing functions and stored procedures

Tim Uckun schrieb am 19.03.2015 um 11:03: > I guess I see schemas as ways to group data not functions. A schema is just a namespace. The only "link" between data and a schema is that data can only live in tables and a table is associated with a namespace. Even if you use it to "group data", yo

Re: [GENERAL] Errors using JDBC batchUpdate with plpgsql function

Hannes Erven schrieb am 04.05.2015 um 12:31: > Hi, > > >> String sql = "select test_user_result_insert_func(?, ?, ?);"; > > You can't call functions via JDBC like that. That's perfectly valid - unless the function uses out parameters or ref cursors. I am however unsure about batche

Re: [GENERAL] count distinct and group by

Geoff Winkless schrieb am 07.05.2015 um 12:39: > in Postgres (unlike MySQL) you can't order a list of values by a column you > haven't selected.​ Of course you can, just not when you are aggregating. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to you

Re: [GENERAL]

Sachin Srivastava schrieb am 18.05.2015 um 12:04: > Kindly confirm, which year this 9.1.2 was released and when 9.1.15 was > released. That information is part of the release notes: http://www.postgresql.org/docs/9.1/static/release-9-1-2.html http://www.postgresql.org/docs/9.1/static/release-9-

[GENERAL] Documentation bug?

Hello all, I just noticed that you can do something like this (using 9.4.1): select array[1,2,3] - 3 which is doing the same thing as: select array_remove(array[1,2,3],3) but the minus is not documented as an array operator: http://www.postgresql.org/docs/current/static/functions-array.

Re: [GENERAL] Documentation bug?

Maxim Boguk schrieb am 19.05.2015 um 13:33: > Hello all, > > I just noticed that you can do something like this (using 9.4.1): > >select array[1,2,3] - 3 > > which is doing the same thing as: > >select array_remove(array[1,2,3],3) > > but the minus is not docume

Re: [GENERAL] date with month and year

Brian Dunavant wrote on 21.05.2015 21:51: It's probably worth noting that both the Ruby 'best practice' AND Postgres have a failure case when dealing with future dates precisely because they are storing the data as UTC with a time zone. This is one case where storing the data WITHOUT TIME ZONE w

[GENERAL] Re: Allowing postgresql to accept 0xff syntax for data types that it makes sense for?

Bill Moran wrote on 22.05.2015 18:57: Arrgh ... it's good that you're bringing this up, but you're making me realize that there's more to figure out than I originally thought ... My focus had been on it being used for BYTEA columns, but there _are_ plenty of places in the code that do things like

[GENERAL] Re: Allowing postgresql to accept 0xff syntax for data types that it makes sense for?

Tom Lane wrote on 21.05.2015 19:57: One large concern about doing anything like this is whether future versions of the SQL standard might blindside us with some not-terribly-compatible interpretation of that syntax. If we do something that is also in Oracle or DB2 or one of the other big boys, t

Re: [GENERAL] odbc to emulate mysql for end programs

Mimiko schrieb am 02.06.2015 um 13:16: > 1) mysql widelly uses case-insensitive naming for > schemas,tables,columns. So does Postgres. FOO, foo and Foo are all the same name > But postgres use case-sensitive when doulbe-quoting Which is what the SQL standard requires (and this was required *lo

Re: [GENERAL] Select query regarding info

> Could you please provide below information. > > How to change standard_conforming_strings value of postgresql.conf? I would not change that option. You should rather stick to standard conforming strings and fix your query. That can be done through a simple (and automated) search & replace.

Re: [GENERAL] Synchronous replication and read consistency

Kevin Grittner schrieb am 29.07.2015 um 23:10: No, it means that if the primary is hit by a meteor and you promote the standby, the data will not have been lost. The time between the successful return of the commit on the primary and the time at which the change becomes visible on the standby is

Re: [GENERAL] PostgreSQL Developer Best Practices

Melvin Davidson schrieb am 22.08.2015 um 21:40: > Thank you for pointing out "run with standard_conforming_strings = ON".. > However, that is NOT the problem. > What is occurring is that the developers are sending strings like 'Mr. > M\'vey', > which, if we set standard_conforming_strings = ON, wo

Re: [GENERAL] PostgreSQL Developer Best Practices

Melvin Davidson schrieb am 22.08.2015 um 17:15: > I've attached a file with a few starters that although are numbered, > are in no special order. > 2. End ALL queries with a semi-colon (;) >EG: SELECT some_column FROM a_table; > >Although autocommit is on by default, it is always a go

Re: [GENERAL] Serial initial and incremental value

FarjadFarid(ChkNet) schrieb am 02.09.2015 um 11:05: > How can I obtain the initial and incremental value of a serial entity? You can use: select * from sequence_name; where "sequence_name" is the name of the sequence associated with your column. Thomas -- Sent via pgsql-general mai

Re: [GENERAL] get first and last row in one sql as two columns

Tom Smith schrieb am 03.09.2015 um 14:11: >> >> SELECT first_value(col1) over (order by col1), >>last_value(col1) over (order by col1) >> FROM table; >> > The window function works for me (with adding limit 1 in the end to output > only one row > needed instead of many dupl

Re: [GENERAL] Listen/notify, equivalents on other DBs

Mark Morgan Lloyd schrieb am 25.09.2015 um 23:41: I'm trying to get support for PostgreSQL's listen/notify into a development environment, but since it supports multiple database backends: can anybody comment on how many other servers have a comparable facility? Minimal research has allowed me t

Re: [GENERAL] Issues with german locale on CentOS 5,6,7

Peter Geoghegan schrieb am 07.10.2015 um 11:33: > On Wed, Oct 7, 2015 at 2:20 AM, Bernd Helmle wrote: >> The last day we've encountered an issue what i think is somewhat severe if >> you want to do either OS upgrades with CentOS or even binary upgrades with >> an existing PostgreSQL instance to a

Re: [GENERAL] Best practices for aggregate table design

droberts schrieb am 06.10.2015 um 20:53: > Okay, so is it safe to say I should use loosely use these guidelines when > deciding whether to model an attribute as a dimension > (type=[inbound,outbound]) vs. bundling with a measure (total_inbound) ? > > If you know the number of values for a dimensio

Re: [GENERAL] PSQL Tools

jwienc...@comcast.net schrieb am 18.10.2015 um 16:04: Is anyone aware of any tools like TOAD that are available for Postgresql? https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to y

Re: [GENERAL] Recursive Arrays 101

Alban Hertroys schrieb am 25.10.2015 um 22:07: > WITH RECURSIVE taxons AS ( > -- Hierarchical root nodes > SELECT N AS id, Taxon, Rank, 1 AS level, '' || N AS Path -- A useful > addition explained further down > FROM t > WHERE ParentID IS NULL > > -- Child nodes > UNION

Re: [GENERAL] Configure Different Databases on One Server

Jim Longwill schrieb am 29.10.2015 um 22:43: Ok. The reason I wanted to experiment with autovacuum disable for 'ddev2' database is that it is a mostly read-only database -- to support querying from our web apps. I.e. there are only 1 or 2 small tables ever updated all day on it, and only about

Re: [GENERAL] Is there bigintarray?

Igor Bossenko schrieb am 02.11.2015 um 14:20: What is the current plans for bigintarray? Igor The following works for me: create table foo ( bia bigint[] ); -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www

Re: [GENERAL] Query regarding

M Tarkeshwar Rao schrieb am 04.11.2015 um 10:21: > Hi all, > > We have started to convert some oracle sql scripts and converting them to > postgres, but facing some issues to create table. > > I have some common doubts in create table script ie. > > ALTER TABLE employee > DROP PRIMARY KEY CA

Re: [GENERAL] is there any difference DROP PRIMARY KEY in oracle and postgres?

M Tarkeshwar Rao schrieb am 06.11.2015 um 04:52: > one thing in oracle is there any difference between “DROP PRIMARY > KEY” used directly in oracle to drop primary key, or “DROP CONSTRAINT > CDRAUDITPOINT_pk”, as first syntax is not available in postgres and > we need to give primary key name as co

Re: [GENERAL] Does PostgreSQL ever create indexes on its own?

Doiron, Daniel schrieb am 12.11.2015 um 23:21: I’m troubleshooting a schema and found this: Indexes: "pk_patient_diagnoses" PRIMARY KEY, btree (id) "index_4341548" UNIQUE, btree (id) "idx_patient_diagnoses_deleted" btree (deleted) "idx_patient_diagnoses_diagnosis_type_id" btr

Re: [GENERAL] Does PostgreSQL ever create indexes on its own?

Albe Laurenz schrieb am 13.11.2015 um 11:23: >> My questions is whether these “index_*” indexes could have been created by >> postgresql or whether I >> have an errant developer using some kinda third-party tool? > > These indexes were *not* created by PostgreSQL. > We are not Oracle. Well, Orac

Re: [GENERAL] How SQL SELECT * statement works in Postgres?

Sachin Srivastava schrieb am 05.06.2016 um 11:16: Kindly inform to me How PostgreSQL Processes SQL Statements internally? How SQL SELECT * statement works in Postgres? Check out Bruce's presentations: htt

[GENERAL] pgAdmin 4 beta not working on Windows 10

Dave Page schrieb am 10.06.2016 um 16:48: I'm pleased to announce that the release of pgAdmin 4 v1.0 Beta 1 for testing. You can find more details on the website: Announcement: https://www.pgadmin.org/ Documentation: https://www.pgadmin.org/docs4/dev/index.html Downloads: https://www.pgadmin.o

[GENERAL] Re: Moving from PHP to Java: A result was returned when none was expected.

Alexander Farber schrieb am 15.06.2016 um 15:56: > Good afternoon, > > at PostgreSQL 9.5.3 I have a stored function (full source code below) > returning void, which I successfully call with PHP: > > function skipGame($dbh, $uid, $gid) { > $sth = $dbh->prepare('SELECT words_skip_game(?, ?

[GENERAL] 9.6 beta2 win-x64 download links still point to beta1

Hello, the Beta2 downloads on http://www.enterprisedb.com/products-services-training/pgdownload http://www.enterprisedb.com/products-services-training/pgbindownload still lead to Beta1 for the Windows 64bit builds. All others properly link to beta1 Thomas -- Sent via pgsql-general ma

Re: [GENERAL] 9.6 beta2 win-x64 download links still point to beta1

Bruce Momjian schrieb am 28.06.2016 um 05:36: On Fri, Jun 24, 2016 at 07:44:17AM +0200, Thomas Kellerer wrote: the Beta2 downloads on http://www.enterprisedb.com/products-services-training/pgdownload http://www.enterprisedb.com/products-services-training/pgbindownload still lead to

[GENERAL] Re: Best way to insert a row with two columns having the value based on the same argument generated by another postgres function.

Silk Parrot schrieb am 07.07.2016 um 08:56: > Hi, > >I am trying to build a user database. The steps for creating a new user > are: > > 1. Use gen_salt to create a salt. > 2. Compute the hash based on the salt and password and store both the hash > and the salt into a new row. > > > The

[GENERAL] Re: Clustered index to preserve data locality in a multitenant application?

Nicolas Grilly schrieb am 30.08.2016 um 13:12: > We rely on clustered indexes to preserve data locality for each > tenant. Primary keys start with the tenant ID. This way, rows > belonging to the same tenant are stored next to each other. Because > all requests hit only one tenant, this is a great

Re: [GENERAL] Chante domain type - Postgres 9.2

Rakesh Kumar schrieb am 26.09.2016 um 15:08: >>You sound like you think that varchar(50) is somehow cheaper than text. > > The biggest impediment to text cols in other RDBMS is no index allowed. > If PG has an elegant solution to that, then yes I see the point made by the > original poster. Don

Re: [GENERAL] isnull() function in pgAdmin3

dudedoe01 schrieb am 27.09.2016 um 19:04: > I am trying to emulate the isnull() function used in MySQL into postreSQL. I > have tried different ways such is null but it's not producing the results > desired. I am doing a data migration from MySQL into postgreSQL and need > help with the isnull() in

[GENERAL] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

Hello, for some reason pg_upgrade failed on Windows 10 for me, with an error message that one specifc _vm file couldn't be copied. When I try to copy that file manually everything works fine. After running a "vacuum full" on the table in question the upgrade goes through. One thing I noticed

Re: [GENERAL] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

Adrian Klaver schrieb am 29.09.2016 um 22:55: After running a "vacuum full" on the table in question the upgrade goes through. Assuming you did that on old cluster? Yes, correct. I did that on the 9.5 cluster Where both clusters installed the same way? Yes. I always download the ZIP Arch

Re: [GENERAL] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

Tom Lane schrieb am 29.09.2016 um 23:10: > Thomas Kellerer writes: >> for some reason pg_upgrade failed on Windows 10 for me, with an error >> message that one specifc _vm file couldn't be copied. > > Hmm ... a _vm file would go through rewriteVisibilityMap(), which

Re: [GENERAL] postgres failed to start from services manager on windows 2008 r2

PHANIKUMAR G schrieb am 01.10.2016 um 17:30: We then tried to start the service with the following changes. a. we changed the "Log on as" type to "This account" b. changed the account name to ".\" c. And entered the password for this account and saved. After that we are able to

Re: [GENERAL] import_bytea function

Stephen Davies schrieb am 07.10.2016 um 09:12: > I am trying to use the import_bytea function described in various list posts > (PG version 9.3.14) in a jsp. > > I get an error saying that only the super user can use server-side > lo_import(). > > If I change the Java connection to use user pos

Re: [GENERAL] import_bytea function

Stephen Davies schrieb am 07.10.2016 um 10:46: >> You can store the contents of a file in a bytea using plain JDBC no >> lo_import() required >> >> String sql = "insert into images (id, image_data) values (?,?)"; >> Connection con = ; >> File uploaded = new File("..."); >> Inpu

Re: [GENERAL] import_bytea function

Stephen Davies schrieb am 08.10.2016 um 02:57: A follow-up question. Once the bytea column is populated, how best to display the content in a web page? I have : byte [] imgB; ResultSet rs = st1.executeQuery("select pic from part where pno='" + p + "'"); if(rs.next()){ imgB = r

Re: [GENERAL] import_bytea function

Jan de Visser schrieb am 08.10.2016 um 16:11: You need to stream the data. Working from memory here, and it's been a long time, but it's something like rs = conn.executeQuery("SELECT byeta_column FROM foo WHERE bar = ?"); Blob b = (Blob) rs.getObject(1); No. getBytes() works fine with the JDB

Re: [GENERAL] import_bytea function

Stephen Davies schrieb am 08.10.2016 um 02:57: I will have to regenerate that code to get the exact error message text but it basically said that the parameter substitution was invalid. A follow-up question. Once the bytea column is populated, how best to display the content in a web page? I

Re: [GENERAL] Getting the currently used sequence for a SERIAL column

> Is this to be regarded as internal API or is it safe to use this to > find the correct sequence? I think it's safe to use. > Furthermore, what's stored in the column seems to be a string of the > format "nextval('sequencename'::regclass)". Is there a function to > parse this, to return just t

Re: [GENERAL] Getting the currently used sequence for a SERIAL column

Tom Lane schrieb am 18.10.2016 um 15:20: >> Furthermore, what's stored in the column seems to be a string of the >> format "nextval('sequencename'::regclass)". Is there a function to >> parse this, to return just the sequence name, or will the sequence >> name always be without for instance a schem

Re: [GENERAL] Getting the currently used sequence for a SERIAL column

Tom Lane schrieb am 18.10.2016 um 16:11: I thought pg_depend only stores the dependency if the the sequence was assigned an owning column (through OWNED BY). No, there will be regular expression dependencies as well. That 'a' dependency is the one that pg_get_serial_sequence() looks for, but t

Re: [GENERAL] Getting the currently used sequence for a SERIAL column

Hanne Moa schrieb am 19.10.2016 um 09:06: >> regression=# create table t1 (f1 serial); >> CREATE TABLE >> regression=# select * from pg_depend where objid = 't1_f1_seq'::regclass or >> refobjid = 't1_f1_seq'::regclass; >> classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype

Re: [GENERAL] Getting the currently used sequence for a SERIAL column

Hanne Moa schrieb am 19.10.2016 um 10:31: >> You can use the following statement to find the sequences that a table uses: >> > Thanks. This assumes that there is only one nextval per table though. > While this holds for the database we need this on right now, it's not a > sufficiently generic solut

Re: [GENERAL] Drop user cascade

Alex Ignatov (postgrespro) schrieb am 19.10.2016 um 12:26: > Hello! > > Why we can DROP TABLE CASCADE, DROP VIEW CASCADE, DROP SEQUENCE CASCADE but > we can’t DROP USER/ROLE CASCADE? > > Why do Postgres have no such functionality as DROP USER CASCADE? Is there any > reasons in that absence? Yo

Re: [GENERAL] Master - slave replication?

Bjørn T Johansen schrieb am 26.10.2016 um 13:17: > I have a database that I would like to replicate in case of hardware failure > on this server. So I gather I just need a streaming replication (warm > standby?) and I found a howto describing how to set it up, that looks like > this: > > 1. Edit

Re: [GENERAL] Strict min and max aggregate functions

Jeff Janes schrieb am 19.11.2016 um 22:12: I need "strict" MIN and MAX aggregate functions, meaning they return NULL upon any NULL input, and behave like the built-in aggregates if none of the input values are NULL. This doesn't seem like an outlandish thing to want, and I'm surprised I can't fi

Re: [GENERAL] Backup "Best Practices"

Israel Brewster schrieb am 28.11.2016 um 23:50: pg_archivecleanup -n /mnt/server/archiverdir 00010010.0020.backup Ok, but where does that "00010010.0020.backup" come from? I mean, I can tell it's a WAL segment file name (plus a backup label), but I don'

Re: [GENERAL] About the MONEY type

Tobia Conforto schrieb am 30.11.2016 um 12:15: > I think MONEY is a great datatype, at least in theory. I personally find it pretty useless to be honest - especially because the currency symbol depends on the client. So if I store a money value in the database, some clients see CHF, some see Kč

[GENERAL] pg_dump and quoted identifiers

Inspired by this question: http://dba.stackexchange.com/q/158044/1822 I tried that for myself, and it seems that pg_dump indeed can not parse quoted identifiers: psql (9.6.1) Type "help" for help. postgres=# create table "Statuses" (id integer); CREATE TABLE postgres=# \

Re: [GENERAL] pg_dump and quoted identifiers

David G. Johnston schrieb am 13.12.2016 um 18:05: On Tue, Dec 13, 2016 at 9:43 AM, Pavel Stehule mailto:pavel.steh...@gmail.com>>wrote: pg_dump -t '"Statuses"' postgres ​To elaborate - your shell was removing the double-quotes. You need make it so after shell processing the double-quotes

Re: [GENERAL] pg_dump and quoted identifiers

Tom Lane schrieb am 13.12.2016 um 18:02: -bash-4.1$ pg_dump -d postgres -t '"Statuses"' pg_dump: no matching tables were found -bash-4.1$ pg_dump -d postgres -t 'public."Statuses"' pg_dump: no matching tables were found These cases work for me. Maybe your shell is doing some

Re: [GENERAL] pg_dump and quoted identifiers

Tom Lane schrieb am 13.12.2016 um 19:35: >>> These cases work for me. Maybe your shell is doing something weird >>> with the quotes? > >> Hmm, that's the default bash from CentOS 6 (don't know the exact version) > > I'm using bash from current RHEL6, should be the same. > > I'm suspicious that

Re: [GENERAL] pg_upgrade 9.0 to 9.6

Mikhail schrieb am 13.12.2016 um 10:57: > Should i check all the production environments for the problems, > mentioned in all interim versions release notes, is it enough only to > check the last minor upgrade release note (9.6 --> 9.6.1) or there is > another quick way to check if i should apply s

Re: [GENERAL] pg_dump and quoted identifiers

Adrian Klaver schrieb am 14.12.2016 um 15:32: >>> I'm suspicious that you're not actually typing plain-ASCII single and >>> double quotes, but some fancy curly quote character. >> >> Definitely not. I typed this manually on the command line using Putty > > So you are reaching the Bash shell via Pu

Re: [GENERAL] pg_dump and quoted identifiers

Tom Lane schrieb am 15.12.2016 um 16:20: >> Still doesn't work: >> -bash-4.1$ pg_dump -d postgres -t "\"Statuses\"" >> pg_dump: no matching tables were found > > Hmm. It might shed some light if you put "echo" in front of that > to see what gets printed: > > $ echo pg_dump -d postgres -t "\"

Re: [GENERAL] Er Data Modeller for PostgreSQL

Stephen Davies schrieb am 23.12.2016 um 10:08: I'm looking for an ER Data Modeller tool for postgresql. I use Navicat Premium for postgresql and the tool has a modeller but I would like to display a database modeller that belonging to a tables of an schema under a database. If I use Navicat for

Re: [GENERAL] How to convert MS SQL functions to pgSQL functions

Yogi Yang 007 schrieb am 31.12.2016 um 11:06: Hello, I am stuck while trying to convert/port a MS SQL server database to pgSQL. Here is the code of the function in MS SQL server: CREATE FUNCTION [dbo].[AccountGroupHierarchy] -- Description: ( @groupId numeric(18,0) ) RETURNS @table_variable

[GENERAL] What's the benefit (or usage scenario) of a "typed table"?

I recently stumbled over "typed tables" in Postgres (there were several questions containing this on stackoverflow recently) create type some_type as (id integer, data text); create table some_table of some_type; I wonder what the benefit of a typed table is and when this would be useful

Re: [GENERAL] What's the benefit (or usage scenario) of a "typed table"?

David G. Johnston schrieb am 31.12.2016 um 16:51: I wonder what the benefit of a typed table is and when this would be useful? But I'd say if you want a table with said structure you should plan on droppign the original type after you've altered all references to it to point to the new implici

Re: [GENERAL] R: Postgres 9.6.1 big slowdown by upgrading 8.4.22

FWIW you still haven't explained how the upgrade was performed. That might be a very important piece of information, because the 9.4 cluster might have hint bits set and/or the data may be mostly frozen, but the 9.6 cluster may not have that yet, resulting in higher CPU usage. We upgraded the da

[GENERAL] Is it possible to specify an ESCAPE for a wildcard search using LIKE ANY (...)

I recently stumbled over the need to use a wildcard escape character for a condition that makes use of LIKE ANY, something like: select * from some_table where name like any (array['foo_bar%', 'bar_foo%']) escape '/'; so that the underscore wouldn't be treated as a wildard (I can't real

[GENERAL] Re: Is it possible to specify an ESCAPE for a wildcard search using LIKE ANY (...)

Tom Lane schrieb am 17.01.2017 um 13:41: > Thomas Kellerer writes: >> So my question is: Is there any way to specify an alternate wildcard escape >> when using LIKE ANY (..)? > > No, not with ESCAPE. [ manfully resists temptation to run down SQL > committee's

[GENERAL] Re: Is it possible to specify an ESCAPE for a wildcard search using LIKE ANY (...)

Karsten Hilbert schrieb am 17.01.2017 um 14:42: >> I recently stumbled over the need to use a wildcard escape character for a >> condition that makes use of LIKE ANY, something like: >> >>select * >>from some_table >>where name like any (array['foo_bar%', 'bar_foo%']) escape '/'; >> >>

[GENERAL] Combining count() and row_number() as window functions

I was playing around with a query that essentially looked something like this: select row_number() over (order by foo_date) as rn, count(*) over () as total_count, f.* from foo f; (The actual query limits the output based on the row_number() for pagination purpos

Re: [GENERAL] PgPool or alternatives

Simon Windsor schrieb am 21.01.2017 um 21:26: I was wondering if there is another option that will allow me to spool all ALTER|CREATE|DELETE|DROP|INSERT|UPDATE commands to all nodes, and SELECTs to any of the connected nodes. The apllication can actually handle separate READ|WRITE nodes from how

Re: [GENERAL] Doubts regarding postgres Security

Stephen Frost schrieb am 21.01.2017 um 22:37: So, there is no solution for my first question, we need if users enter the wrong password more than 5 times than their account gets locked and then only DBA will unlock this account. I understood the question and there is an answer- use PAM. What

Re: [GENERAL] Searching array for multiple items

Alex Magnum schrieb am 25.01.2017 um 09:29: > I can search an array with 1 = ANY('{1,3,4,7}'::int[]) > > I need to check for one or multiple items in the array. > > e.g.'1,7,3' = ANY('{1,3,4,7}'::int[] > > I do need to check if > a) all items exist in the array You can use the contains (or is

[GENERAL] How does Postgres estimate the memory needed for sorting/aggregating

There was a question on dba.stackexchange recently: http://dba.stackexchange.com/a/162117/1822 That question (and the answer) deals with performance difference of a query caused by the _declared_ length of a VARCHAR column in SQL Server (everything else being equal - especially the actual d

[GENERAL] Re: How does Postgres estimate the memory needed for sorting/aggregating

Tomas Vondra schrieb am 25.01.2017 um 22:46: I guess this is based on the column statistics stored in pg_stats, but I am not sure: It is based on the average length of values in that column, yes. Thanks for confirming that. I assume this is taken from pg_stats.avg_width ? I'm not sure wha

Re: [GENERAL] Data Modeling Tools - Version specific to Postgres

Greg Slawek schrieb am 01.02.2017 um 19:35: Can anyone recommend a data modeling tool (preferably Mac OSX compatible)? I would like to be sure it can export version specific SQL code (ie 9.1 vs 9.4) I have used Toad Data Modeler years ago on Windows, which was pretty good at sticking to the di

[GENERAL] Result of timestamp - timestamp

Hello, I just stumbled about a report that has been running for a long time now and that relied on the fact that the interval "timestamp - timestamp" always returns an interval with days, hours, minutes. But never a "justified" interval with years, months, days and so on. The query usees "extr

Re: [GENERAL] Result of timestamp - timestamp

Adrian Klaver schrieb am 05.02.2017 um 22:40: >> I just stumbled about a report that has been running for a long time now >> and that relied on the fact that the interval "timestamp - timestamp" >> always returns an interval with days, hours, minutes. But never a >> "justified" interval with years,

[GENERAL] Re: Poor performance on 9.4.4 when matching multiple columns from left side of LATERAL join

Steven Grimm schrieb am 14.11.2015 um 07:25: We have a table, call it "multi_id", that contains columns with IDs of various kinds of objects in my system, and another table that's a generic owner/key/value store for object attributes (think configuration settings, and I'll refer to this table a

Re: [GENERAL] update inside function does not use the index

Johannes schrieb am 16.11.2015 um 14:56: > I have problems with a self written function, which does not use the > index, which takes very long (500 ms per update). > > The pl/pgsql function iterates over a select resultset with a cursor. > In every loop I execute an update with a where LIKE condit

Re: [GENERAL] SQL conversion tool

Sachin Srivastava schrieb am 18.11.2015 um 10:41: > Please inform which is the best tool for SQL conversion because I have to > migration Oracle database into PostgreSQL. Ora2Pg works quite well http://ora2pg.darold.net/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) T

Re: [GENERAL] JSONB performance enhancement for 9.6

Tom Smith schrieb am 29.11.2015 um 03:27: Hello: Is there a plan for 9.6 to resolve the issue of very slow query/retrieval of jsonb fields when there are large number (maybe several thousands) of top level keys. Currently, if I save a large json document with top level keys of thousands and qu

Re: [GENERAL] Secret Santa List

Lou Duchez schrieb am 23.12.2015 um 04:49: I have a company with four employees who participate in a Secret Santa program, where each buys a gift for an employee chosen at random. (For now, I do not mind if an employee ends up buying a gift for himself.) How can I make this work with an SQL stat

Re: [GENERAL] to_timestamp alternatives

gkhan schrieb am 31.12.2015 um 22:34: Thanks very much for both of your replies. I had tried something similar and gotten an error, so I am probably making a stupid mistake. If I try this, it works: SELECT ('09.03.2014'||' '||lpad('3:00:00',8,'0'),'DD.MM. HH24:MI:SS')::timestamp but i

Re: [GENERAL] How do I implement a .XSD in Postgres?

ERR ORR schrieb am 01.01.2016 um 11:07: I need to import some DB schemas which are defined in .XSD (a XML derivative) format. I've googled about this but have found no satisfactory answer. Perhaps I just don't know what exactly to ask Google. So please: - What tool can I use in order to import

Re: [GENERAL] ERROR for '@' for function

Sachin Srivastava schrieb am 20.01.2016 um 12:42: > How to handle this below situation, I am getting error for function, my > Postgres version is 9.4 > is > > ERROR: syntax error at or near "@" > LINE 67: autonumbersett...@repos.birchstreet.net >

Re: [GENERAL] 9.5 new features

Melvin Davidson schrieb am 23.01.2016 um 16:27: > http://www.postgresql.org/docs/9.5/interactive/brin-intro.html > > 62.1. Introduction > ... > "A block range is a group of pages that are physically adjacent in the table; > for each block range, some summary info is stored by the index." > > Fro

Re: [GENERAL] Tutorial on How to Compile PostgreSQL 9.5 for Windows 64bit

Igal @ Lucee.org schrieb am 25.01.2016 um 19:46: > I have posted a video tutorial on How to Compile PostgreSQL 9.5 for Windows > 64bit > https://www.youtube.com/watch?v=-BJmuZT5IPE > > It was quite difficult for me to figure it out, so hopefully it will make > life easier for > the next guy (or

Re: [GENERAL] comparison between Postgresql and Microsoft SQL Server

Heine Ferreira schrieb am 02.02.2016 um 09:04: > I know there is a manual but it is quite a large and heavy document. > Does anyone know of a document that compares and shows the differences between > SQL Server and Postgres? > That will be a substantial shortcut for me. I am maintaining an (brief

Re: [GENERAL] Replacement for Oracle Text

Daniel Westermann schrieb am 19.02.2016 um 11:53: > if I'd need to implement/replace Oracle Text > (ww.oracle.com/technetwork/testcontent/index-098492.html). > What choices do I have in PostgreSQL (9.5+) ? Postgres also has a full text search (which I find much easier to use than Oracle's): h

Re: [GENERAL] Replacement for Oracle Text

Daniel Westermann schrieb am 19.02.2016 um 12:41: if I'd need to implement/replace Oracle Text (ww.oracle.com/technetwork/testcontent/index-098492.html). > What choices do I have in PostgreSQL (9.5+) ? > >>Postgres also has a full text search (which I find much easier to use than

Re: [GENERAL] bpchar, text and indexes

Victor Yegorov schrieb am 22.02.2016 um 16:45: > Test setup: > > PostgreSQL 9.4.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 > 20120313 (Red Hat 4.4.7-16), 64-bit > > create table t(t_id int4, sn_c char(20)); > insert into t select id, > chr((random()*26)::int4+65)||chr((random()*

[GENERAL] Confusing deadlock report

Hello, we have a strange (at least to me) deadlock situation which does not seem to fall into the "usual" deadlock category. The error as reported in the Postgres log file is this: 2016-03-12 13:51:29.305 CET [23912]: [1-1] user=arthur,db=prod,app=[unknown] ERROR: deadlock detected 2016-03-1

Re: [GENERAL] Confusing deadlock report

Tom Lane schrieb am 16.03.2016 um 14:45: >> 2016-03-12 13:51:29.305 CET [23912]: [2-1] user=arthur,db=prod,app=[unknown] >> DETAIL: Process 23912 waits for ShareLock on transaction; blocked by process >> 24342. >> Process 24342 waits for ShareLock on transaction 39632974; blocked >> by

Re: [GENERAL] Confusing deadlock report

Albe Laurenz schrieb am 16.03.2016 um 13:20: >> The error as reported in the Postgres log file is this: >> >> 2016-03-12 13:51:29.305 CET [23912]: [1-1] user=arthur,db=prod,app=[unknown] >> ERROR: deadlock detected >> 2016-03-12 13:51:29.305 CET [23912]: [2-1] user=arthur,db=prod,app=[unknown] >>

Re: [GENERAL] Confusing deadlock report

Albe Laurenz schrieb am 16.03.2016 um 14:38: >>> waits for ShareLock on transaction; blocked by process 24342. Process 24342 waits for ShareLock on transaction 39632974; blocked by process 23912. Process 23912: UPDATE alpha SET some_flag = $1 WHERE (id = $2)

Re: [GENERAL] PostgreSQL advocacy

Mark Morgan Lloyd schrieb am 21.03.2016 um 14:44: > I was discussing this sort of thing elsewhere in the context of MS's > apparent challenge to Oracle and IBM, and the dominant feeling > appeared to be that actual use of things like Oracle RAC was > vanishingly uncommon. Which surprised me, and wh

  1   2   3   4   5   6   7   8   9   >