Re: [GENERAL] Retrieve the server's time zone

Tom Lane schrieb am 14.11.2017 um 15:36: >> is there any way (short of writing a function in an untrusted PL) >> to determine the actual time zone (or time) of the server OS? > > AFAIK that would only be true if some part of your client stack > is issuing a SET TIMEZONE command. (libpq will do

[GENERAL] Retrieve the server's time zone

Hello, is there any way (short of writing a function in an untrusted PL) to determine the actual time zone (or time) of the server OS? "show timezone" always returns the client's time zone. localtimestamp also converts the server's time to the client time zone (the one defined by

Re: [GENERAL] idle in transaction, why

Rob Sargent schrieb am 06.11.2017 um 23:09: > Gosh I wish I could learn to proof-read my posts. > My support crew graciously set > > idle_transaction_timeout = 1 > > Now to ponder if I need zero or some large number. The unit of that setting is milliseconds (if no unit is specified). zero

[GENERAL] Why does a SELECT query cause "dirtied" buffers?

When using explain (analyze, buffers) things like this can show up: Buffers: shared hit=137 read=6545 dirtied=46 Given the description in the manual: The number of blocks dirtied indicates the number of previously unmodified blocks that were changed by this query I am a bit

Re: [GENERAL] Monitoring Tool for PostgreSQL

Fabricio Pedroso Jorge schrieb am 18.10.2017 um 19:37: is there a "official" monitoring tool for PostgreSQL databases? For example, i come from Oracle Database, and there, we have Enterprise Manager to monitor and administrer the product... is there such a similar tool for PostgreSQL? There is

Re: [GENERAL] Making subscribers read only in Postgres 10 logical replication

rverghese schrieb am 11.10.2017 um 20:38: > You mean at the user permissions level? Yes, I could, but would mean doing so > table by table, which is not our current structure. I guess there is nothing > at the database level. Not at the database level, but at the schema level: You can revoke

[GENERAL] Postgres 10 manual breaks links with anchors

I don't know if this is intentional, but the Postgres 10 manual started to use lowercase IDs as anchors in the manual. So, if I have e.g.: the following URL open in my browser: https://www.postgresql.org/docs/current/static/sql-createindex.html#sql-createindex-concurrently I cannot simply

Re: [GENERAL] Non-overlapping updates blocking each other

Melvin Davidson schrieb am 14.10.2017 um 17:32: More than likely, the optimizer has determined that a table scan is best, in which case it will use a table lock. That means one updates will be blocking each other. Since when does Postgres lock the whole table during an update? -- Sent

Re: [GENERAL] Multiple Schemas vs. Multiple Databases

Melvin Davidson schrieb am 13.10.2017 um 21:42: If that is what you need to do, then definitely use multiple schemas. In PostgreSQL, the only way to do cross db queries / DML, is with the dblink extension, and from personal use, it is a PIA to use. dblink is not the only way to do that.

Re: [GENERAL] "Shared strings"-style table

Seamus Abshere schrieb am 13.10.2017 um 18:43: On Fri, Oct 13, 2017 at 8:49 AM, Seamus Abshere wrote: Theoretically / blue sky, could there be a table or column type that transparently handles "shared strings" like this, reducing size on disk at the cost of lookup overhead for all queries? (I

[GENERAL] Why does increasing the precision of a numeric column rewrites the table?

When increasing the length constraint on a varchar column, Postgres is smart enough to not rewrite the table. I expected the same thing to be true when increasing the size of a numeric column. However this does not seem to be the case: Consider the following table: create table foo

Re: [GENERAL] Automatically check for anti-patterns in SQL queries

Joy Arulraj schrieb am 08.10.2017 um 13:50: > Hi folks -- We developed a static analysis tool, called SQLCheck, for > automatically identifying anti-patterns in SQL queries. > > https://github.com/jarulraj/sqlcheck > > Our goal is to provide hints to the developers about potential > performance

Re: [GENERAL] Performance appending to an array column

Paul A Jungwirth schrieb am 21.09.2017 um 23:05: but maybe I could write my own extension to load regular files into Postgres arrays, sort of getting the best of both worlds. There is a foreign data wrapper for that: https://github.com/adunstan/file_text_array_fdw but it's pretty old and

Re: [GENERAL] What's the best way in postgres to use ANY() with LIKE '%'?

Ryan Murphy schrieb am 05.09.2017 um 16:19: > Thanks, I saw that page earlier; what I'm looking for is kind of the > opposite - instead of comparing a single value to see if it matches > any of a list of patterns, I'm trying to take a list of values and > see if any of them match a given pattern.

Re: [GENERAL] clustered index benchmark comparing Postgresql vs Mariadb

유상지 schrieb am 31.08.2017 um 04:03: > Cluster secondary indexes were faster than those without cluster indexes in > pg, but slower than mariadb. There is no such thing as a "clustered index" in Postgres. The Postgres "cluster" command physically sorts the rows of a table according to the sort

[GENERAL] _page_cost parameter with values < 1

Hello, recently I have seen a Postgres configuration with the following values: seq_page_cost = 0.5 random_page_cost = 0.6 Is there any advantage (or maybe disadvantage) compared to using e.g. 1.0 and 1.2? Regards Thomas -- Sent via pgsql-general mailing list

Re: [GENERAL] What is exactly a schema?

marcelo schrieb am 14.07.2017 um 13:59: > Could I select a specific schema in the connection string? Say, by example > database=mydb.schemanumbertwo ? The JDBC driver does indeed support that: jdbc:postgresql://localhost/somedatabase?currentSchema=some_schema I think in the backround it

[GENERAL] Re: Feature proposal, DBURL: psql pgsql://joe:p4zzw...@example.org:2345/dbname

Hans Schou schrieb am 05.07.2017 um 14:27: > The dburl (or dburi) has become common to use by many systems > connecting to a database. The feature is that one can pass all > parameters in a string, which has similar pattern as http-URI do. > > Especially when using psql in a script, having the

Re: [GENERAL] Download 9.6.3 Binaries

Andreas Kretschmer schrieb am 23.06.2017 um 20:58: I expected to find binaries for 9.6.3 at https://www.enterprisedb.com/download-postgresql-binaries but I only see 9.6.2. Am I looking at the wrong place? Yeah, use the community version from postgresql.org ;-) Regards, Andreas There are

Re: [GENERAL] sub-select with multiple records, columns

Israel Brewster schrieb am 19.06.2017 um 22:17: SELECT ... (SELECT array_agg(to_json(row(notedate,username,note))) FROM sabrenotes INNER JOIN users ON author=users.id WHERE ticket=sabretickets.id ) notes FROM tickets WHERE ... The only problem with this query is that the notes aren't sorted. Of

Re: [GENERAL] pg_catalog tables don't have constraints?

kbran...@pwhome.com schrieb am 09.06.2017 um 20:57: Neil Anderson wrote: I've been exploring the pg_catalog tables and pointed a couple of tools at it to extract an ER diagram for a blog post. At first I thought it was a bug in the drawing tool but it appears that

Re: [GENERAL] dump to pg

Nicolas Paris schrieb am 31.05.2017 um 16:43: Hi, I have dumps from oracle and microsoft sql server (no more details). Is it possible to load them "directly" into postgres (without oracle/mssql license)? dump -> csv -> postgtres or something ? If those are binary dumps (e.g. a DataPump dump

Re: [GENERAL] 9.5 "chained equality" behavior

Joshua Ma schrieb am 30.05.2017 um 22:56: We're going to fix usages of this to instead do (a = b && a = c) instead of (a = b = c). That has to be (a = b AND a = c) The && operator is not valid for booleans -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

Adrian Klaver schrieb am 28.05.2017 um 17:51: After I finally found the EnterpriseDB Beta binaries (they are pretty well hidden) I tested with those, and everything works fine there. For those following along, where would that be? Here they are:

Re: [GENERAL] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

Tom Lane schrieb am 26.05.2017 um 20:18: I just tried pg_upgrade from the Postgres 10 Beta1 (from the BigSQL distribution) to upgrade a 9.6 cluster. pg_upgrade --check fails with the following messages: could not load library "$libdir/pgxml": ERROR: could not load library

Re: [GENERAL] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

Adrian Klaver schrieb am 26.05.2017 um 23:41: If that's coming from port/dynloader/win32.c, as I think it must be because the non-conformant-to-message-style-guidelines phrase "unknown error" appears nowhere else in our tree, then that's an error code that FormatMessage doesn't recognize.

Re: [GENERAL] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

Adrian Klaver schrieb am 26.05.2017 um 23:16: could not load library "$libdir/pgxml": ERROR: could not load library "d:/etc/postgres-10/pgsql/lib/postgresql/pgxml.dll": unknown error 126 Apparently BigSQL forgot to include contrib/xml2 in their distribution; you should ping them

Re: [GENERAL] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

Tom Lane schrieb am 26.05.2017 um 20:18: I just tried pg_upgrade from the Postgres 10 Beta1 (from the BigSQL distribution) to upgrade a 9.6 cluster. pg_upgrade --check fails with the following messages: could not load library "$libdir/pgxml": ERROR: could not load library

[GENERAL] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

I just tried pg_upgrade from the Postgres 10 Beta1 (from the BigSQL distribution) to upgrade a 9.6 cluster. pg_upgrade --check fails with the following messages: Performing Consistency Checks - Checking cluster versions ok Checking

Re: [GENERAL] logical replication in PG10 BETA

Igor Neyman schrieb am 22.05.2017 um 21:33: > Does built-in logical replication work on Windows in PG10 BETA release? > > I can’t make it working so far. > > I created Publication on “source” PG server, but when I’m trying to CREATE > SUBSCRIPTION… on “destination” server, I’m getting: > >

Re: [GENERAL] storing large files in database - performance

John R Pierce schrieb am 16.05.2017 um 16:44: > On 5/16/2017 7:35 AM, Thomas Kellerer wrote: >> When my (JDBC based) SQL client and the database server are on the same >> computer... > > node.js is Javascript, not java w/ jdbc I know that. I mentioned JDBC so that it's c

Re: [GENERAL] storing large files in database - performance

Eric Hill schrieb am 16.05.2017 um 14:25: > I have a node.js application using PostgreSQL to store uploaded > files. The column in which I am storing the file contents is of type > “bytea” with “Storage” type set to “EXTENDED”. Storing a 12.5 MB file > is taking 10 seconds, and storing a 25MB file

Re: [GENERAL] Insert performance and disk usage in JSON vs JSONB

Ronny Abraham schrieb am 15.05.2017 um 19:25: 4. Insert 10,000 rows to JSON, execution time (sec): 122.855001211 5. Insert 10,000 rows to JSONB, execution time (sec): 122.128999233 What’s interesting is that inserting to JSONB is slightly faster than inserting to JSON. A difference in

Re: [GENERAL] Top posting....

Tom Lane schrieb am 11.05.2017 um 19:43: > Bottom posting without trimming is just an awful combination: > whatever you do, don't do that. Amen to that. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] Migration Query

PAWAN SHARMA schrieb am 08.05.2017 um 17:13: > Does one any having list of bottlenecks and workarounds while migrating data > from Oracle to Postgresql. like what are thing which we can migrate from > Oracle database to Postgresql and what we can't? One thing I have seen regularly is the wrong

Re: [GENERAL] browser interface to forums please?

vinny schrieb am 04.04.2017 um 12:01: > I'd love to have an RSS feed that contains only new questions, so I can just > watch the popup > on my screen the way I do with the rest of the world, and not have to deal > with replies to topics that I don't care about anyway. You can read them as a

[GENERAL] Suggestion to improve select pg_reload_conf()

I would like to suggest an improvement to the select pg_reload_conf() function. Currently this will only return true or false indicating if reloading was successful. I think it would be a "nice-to-have" if the function would also return the GUCs that have been changed, similar to what is being

Re: [GENERAL] Do I need to COMMIT an analyze statement?

David G. Johnston schrieb am 31.03.2017 um 16:49: On Friday, March 31, 2017, Thomas Kellerer <spam_ea...@gmx.net <mailto:spam_ea...@gmx.net>> wrote: Hello, something I have always wondered: If I run with autocommit turned OFF, do I need to commit an ANALY

[GENERAL] Do I need to COMMIT an analyze statement?

Hello, something I have always wondered: If I run with autocommit turned OFF, do I need to commit an ANALYZE statement, or is that "non-transactional"? Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] Trigger based logging alternative to table_log

Jeff Janes schrieb am 27.03.2017 um 19:07: I have some code which uses table_log (http://pgfoundry.org/projects/tablelog/) to keep a log of changes to selected tables. I don't use the restore part, just the logging part. It creates a new table for each table being logged, with several

Re: [GENERAL] Combining INSERT with DELETE RETURNING

Alexander Farber schrieb am 24.03.2017 um 16:06: > the doc https://www.postgresql.org/docs/9.6/static/sql-delete.html states: > > "The syntax of the RETURNING list is identical to that of the output list of > SELECT." > > So I am trying to rewrite the INSERT .. ON CONFLICT DO NOTHING followed

Re: [GENERAL] Incremental / Level -1 backup in PG

Rakesh Kumar schrieb am 22.03.2017 um 01:27: > PG does not have a concept of incremental backup. Postgres doesn't, but external tools can. e.g. Barman can do incremental backups: https://blog.2ndquadrant.com/incremental-backup-barman-1-4-0/ -- Sent via pgsql-general mailing list

Re: [GENERAL] CenOS 5/Postgresql 9.6

Steve Crawford schrieb am 17.03.2017 um 20:15: I'm aware of all those dates. Also that 9.6 has been out for six-months, and that RHEL/CentOS 5 are listed among the currently supported versions at https://yum.postgresql.org/. The question remains - does anyone know where I might find packages so

Re: [GENERAL] CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string

Alexander Farber schrieb am 02.03.2017 um 21:45: I am calling this stored function - CREATE OR REPLACE FUNCTION words_get_board( in_gid integer ) RETURNS TABLE ( out_bid integer, out_letters varchar[15][15], out_values

Re: [GENERAL] Problems with Greatest

Teddy Schmitz schrieb am 16.02.2017 um 05:38: > As a quick follow up I just did an explain on the query, > > > Aggregate (cost=258007258.87..258007258.88 rows=1 width=8) > -> Nested Loop (cost=0.00..184292254.83 rows=14743000807 width=16) > -> Seq Scan on t1 (cost=0.00..3796.41 rows=263141

[GENERAL] Documentation inconsistency (at least to me)

I wonder why regexp_split_to_array() is listed under "String functions and operators" [1] but string_to_array() is listed under "Array functions and operators" [2] I find that a bit inconsistent - I would expect to find both in the same chapter. I would suggest to put both into "String

[GENERAL] Re: Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

Nikolai Zhubr schrieb am 13.02.2017 um 23:03: Maybe I should have been more specific. What I need is debugging/profiling pure communication side of server operation, implying huge lots of requests and replies going over the wire to and from the server within some continued (valid) session, but

Re: [GENERAL] Loose indexscan and partial indexes

Peter J. Holzer schrieb am 10.02.2017 um 14:02: > So it's doing a sequential scan on the initial select in the recursive > CTE, but using the index on the subsequent selects. > > But why? If it uses the index on > SELECT MIN(periodizitaet) FROM facttable_imf_ifs WHERE periodizitaet > 'x' >

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

[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

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

[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

[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

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

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.

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

[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

[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] 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 <spam_ea...@gmx.net> 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 dow

[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

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

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

[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

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

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

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

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

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 >wrote: pg_dump -t '"Statuses"' postgres ​To elaborate - your shell was removing the double-quotes. You need make it so after shell

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

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

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

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.

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?

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

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

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

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

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

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?

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

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 =

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("..."); >>

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

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] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

Tom Lane schrieb am 29.09.2016 um 23:10: > Thomas Kellerer <spam_ea...@gmx.net> 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 rewriteVi

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

[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] 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()

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.

[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

[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

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

[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

[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] 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:

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:

  1   2   3   4   5   6   7   8   9   >