Re: [GENERAL] PostgreSQL advocacy

Bruce Momjian schrieb am 22.03.2016 um 16:07: > For me, streaming replication fully solves the high reliability problem > and sharding fully solves the scaling problem. Of course, if you need > both, you have to deploy both, which gives you 100% of two solutions, > rather than Oracle RAC which giv

Re: [GENERAL] what database schema version management system to use?

Alexey Bashtanov schrieb am 06.04.2016 um 12:55: > I am searching for a proper database schema version management system. > > My criteria are the following: > 0) Open-source, supports postgresql > 1) Uses psql to execute changesets (to have no problems with COPY, > transaction management or sophi

[GENERAL] Why is the comparison between timestamp and date so much slower then between two dates

I came across something strange today. Consider the following table: CREATE TABLE price_history ( product_id integer, valid_from date, valid_to date, price integer ); CREATE INDEX i1 ON price_history (product_id, valid_from, valid_to); The table c

[GENERAL] Re: Why is the comparison between timestamp and date so much slower then between two dates

Tom Lane schrieb am 13.04.2016 um 15:45: >> So my question is: why is comparing a timestamp to a date so much slower? > > The date has to be up-converted to a timestamptz (not timestamp). > I think the expensive part of that is determining what timezone > applies, in particular whether DST is acti

[GENERAL] Re: Why is the comparison between timestamp and date so much slower then between two dates

Alban Hertroys schrieb am 13.04.2016 um 16:39: >>> So my question is: why is comparing a timestamp to a date so much slower? > > The reason that the other way around is so much more expensive is that > the database needs to do that conversion twice for every row in the > table. When down-converting

[GENERAL] Re: Why is the comparison between timestamp and date so much slower then between two dates

Mike Sofen schrieb am 14.04.2016 um 14:29: > The general rule in the SQL Server world is that using a function in > a Where clause or join will eliminate usage of an index that would > have been leveraged if the function didn't exist. The reason is that > functions are non-deterministic, so the opt

[GENERAL] Re: Why is the comparison between timestamp and date so much slower then between two dates

Tom Lane schrieb am 14.04.2016 um 15:57: >> So the optimizer _should_ be smart enough to do the conversion only >> once at the beginning of the statement and then use that converted >> value during the execution of the statement without the need >> to re-evaluate it for each row. > > It's not;

[GENERAL] Re: Why is the comparison between timestamp and date so much slower then between two dates

Alban Hertroys schrieb am 14.04.2016 um 21:22: now() (and current_timestamp as well) are defined to return the same value throughout the entire transaction. So the optimizer _should_ be smart enough to do the conversion only once at the beginning of the statement and then use that converted valu

[GENERAL] Re: what's the exact command definition in read committed isolation level?

Jinhua Luo schrieb am 18.04.2016 um 16:47: > For trigger, e.g. written in pl/pgsql, each sql command within the > function may see more new data beyond the (entry) snapshot of outer > command. No it will not see "more data") It runs in the same _transaction_ as the "firing" command and thus sees

[GENERAL] Re: what's the exact command definition in read committed isolation level?

Tom Lane schrieb am 18.04.2016 um 17:16: For trigger, e.g. written in pl/pgsql, each sql command within the function may see more new data beyond the (entry) snapshot of outer command. No it will not see "more data") It runs in the same _transaction_ as the "firing" command and thus sees **

[GENERAL] MVIEW refresh consistently faster then insert ... select

Hello, I have a table that is an aggregation of another table. This aggregation reduces an input of ~14 million rows to ~4 million rows. So far I have used a truncate/insert approach for this: truncate table stock; insert into stock (product_id, warehouse_id, reserved_provisional, re

Re: [GENERAL] MVIEW refresh consistently faster then insert ... select

Kevin Grittner schrieb am 04.05.2016 um 09:06: > On Wed, May 4, 2016 at 1:46 AM, Thomas Kellerer wrote: > >> I have a table that is an aggregation of another table. >> This aggregation reduces an input of ~14 million rows to ~4 >> million rows. > >> The refres

Re: [GENERAL] Debugging code on server?

Guyren Howe schrieb am 04.05.2016 um 18:43: This is a fork from my "Love your database" question. It's a separable concern, so I moved it here. Let's say I want to use Postgres' *amazing* support for lots of languages. I want to use Javascript or PERL or Ruby or something. How do I debug the cod

[GENERAL] 9.6beta, parallel execution and cpu_tuple_cost

Hello, while playing around with the parallel aggregates and seq scan in 9.6beta I noticed that Postgres will stop using parallel plans when cpu_tuple_cost is set to a very small number. When using the defaults and max_parallel_degree = 4, the following (test) query will be executed with 4 wo

Re: [GENERAL] 9.6beta, parallel execution and cpu_tuple_cost

Tom Lane schrieb am 27.05.2016 um 15:48: Thomas Kellerer writes: while playing around with the parallel aggregates and seq scan in 9.6beta I noticed that Postgres will stop using parallel plans when cpu_tuple_cost is set to a very small number. If you don't reduce the parallel-plan

[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 "timezone")

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 th

Re: [GENERAL] standard LOB support

EBIHARA, Yuichiro wrote on 22.06.2007 06:09: It seems like PG JDBC driver CANNOT handle 'bytea' as BLOB nor 'text' as CLOB. getBlob()/setBlob()/getClob()/setClob() can work with only Large Objects (at least with postgresql-8.1-405.jdbc3.jar). org.postgresql.util.PSQLException: Bad Integer Z\27

Re: [GENERAL] standard LOB support

EBIHARA, Yuichiro wrote on 22.06.2007 08:34: Thomas, Thank you for your comment. I found that using getBinaryStream(), setBinaryStream(), getCharacterStream() and setCharacterStream() to handle LOBs across different DBMS is much more portable (and reliably) than using the Clob()/Blob() meth

Re: [GENERAL] standard LOB support

EBIHARA, Yuichiro wrote on 22.06.2007 09:28: Hmm. At least for updating LOBs, "my method" should be "legal". This is a quote from jdbc-3_0-fr-spec.pdf "The setBinaryStream and setObject methods may also be used to set a Blob object as a parameter in a PreparedStatement object. The setAsciiStr

Re: [GENERAL] Moving to postgresql and some ignorant questions

Phoenix Kiula wrote on 14.08.2007 19:46: There are some cases where I would like to bunch queries into a transaction purely for speed purposes, but they're not interdependent for integrity. E.g., BEGIN TRANSACTION; UPDATE1; UPDATE2; UPDATE3; COMMIT; If UPDATE2 fails because it, say, v

Re: [GENERAL] reporting tools

Phoenix Kiula wrote on 23.08.2007 10:42: On 23/08/07, Scott Marlowe <[EMAIL PROTECTED]> wrote: Yeah, I'm not the biggest fan of CR, but it's worked with PostgreSQL for quite some time now. We had it hitting a pg7.2 db back in the day, when hip kids road around in rag top roadsters and wore tshi

Re: [GENERAL] reporting tools

Ned Lilly wrote on 23.08.2007 15:44: This is specifically why we released OpenRPT as open source - it's very lightweight, no Java required. http://sf.net/projects/openrpt I am a Java developer and thus I have no problems in using Java based tools. Especially because I ususally only have a JDB

Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished

Alexander Staubo wrote on 24.08.2007 23:49: So actually the remark shouldn't be that "the multi-threaded architecture is only advantageous on Windows", but more like "the multi-process architecture is disadvantageous on Windows and hence a multi-threaded architecture is preferred (on that partic

Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished

Stephen Ince wrote on 27.08.2007 18:02: Derby and hsqldb are the only free embedded databases for commercial use. Well, there are some more: H2 Database, OneDollarDB (OpenSource version of DaffodilDB), Berkely DB and McKoi are free as well (although McKoi seems to be dead). Then there are a

Re: [GENERAL] replacing Access/ Approach etc

Zenaan Harkness wrote on 08.09.2007 07:03: Hi, a friend of mine on Windows, is attempting to convert to using PostgreSQL (and of course, I'm helping him). The installation gave an option to run as an application, rather than as a service. Turns out, my friends login account has Admin privs, and

Re: [GENERAL] replacing Access/ Approach etc

Shelby Cain wrote on 08.09.2007 20:57: Compared to that, I don't really understand follow your argument as to why installing Postgresql as a service and stopping/starting it through the service control panel such a big deal. Or stopping/starting using a batch file (with "net start pgsql") Thom

Re: [GENERAL] Locking entire database

Panagiotis Pediaditis, 14.09.2007 16:45: Well the problem is I am working on rdf query engine for persistent RDF data. The data is stored/structured in a specific way in the database. When i perform updates in parallel, because there are cross table dependencies, I end up with inconsistencies,

[GENERAL] Preserving view source code

Hello, when I create a view, e.g.: CREATE VIEW my_view AS SELECT col1, col2, col3 FROM mytable; And I later retrieve the view's source using "SELECT definition FROM pg_view", the source I supplied has been altered by Postgres. The formatting has been removed completely and PG a

Re: [GENERAL] Preserving view source code

Richard Huxton wrote on 16.10.2007 18:59: The formatting has been removed completely and PG actually re-wrote the query. For the above example I would get: Is there a way to tell PG _not_ to alter my SQL, so I can retrieve the same (or at least a very similar) version of the original statement

Re: [GENERAL] looking for some real world performance numbers

snacktime wrote on 21.10.2007 08:11: I have a group of otherwise very bright people trying to convince me that a rdbms is not a good place to store relational data Hmm. Those bright people say that a /relational/ database management system is not a good place to store /relational/ data? I

Re: [GENERAL] Using Postgres as a "embedded" / SQL Lite database on Windows

Craig Hawkes, 24.10.2007 05:14: - running as a service What happens in the unlikely event that they already have a version of Postgres installed? As far as I know you can happily install different versions in parallel. You just need to make sure the service name is unique (e.g. by using

Re: [GENERAL] Using Postgres as a "embedded" / SQL Lite database on Windows

Craig Hawkes, 24.10.2007 22:04: If I could reword: Given that we have a large estiblished client base running a Delphi/Paradox solution, and that we would like to replace Paradox with a much better SQL engine, I was looking for comments as to how Postgres maybe suitable. Not sure if I'll

Re: [GENERAL] pg_restore

Tom Lane wrote on 28.10.2007 23:18: Oh, I just twigged that you are using a plain-SQL dump file (that is, you didn't specify -Fc or -Ft to pg_dump). For plain-SQL dumps you should not use pg_restore at all; you feed those to psql. While we are on the topic of pg_dump/pg_restore: Why is it, th

Re: [GENERAL] pg_restore

Tom Lane wrote on 29.10.2007 00:55: Thomas Kellerer <[EMAIL PROTECTED]> writes: Why is it, that pg_dump can use a compressed output directly but pg_dumpall is always using a SQL (i.e. "plain text") output? The custom and tar formats are not designed to support data from more t

Re: [GENERAL] PostgreSQL and AutoCad

Ilan Volow wrote on 30.10.2007 23:01: I'm personally interested in the idea of versioning for a drawing. Instead of storing the entire drawing for each version, one could theoretically just store the vector additions/changes/deletions that happen from one revision to the next. Which could als

Re: [GENERAL] day of week

Anton Andreev wrote on 31.10.2007 15:34: Hi, I have records with date column. Is there a way I can get which day of week this date is? What about the extract() function with the dow parameter? Tho

Re: [GENERAL] Syntax error in a large COPY

Tom Lane wrote on 06.11.2007 21:21: The real point here is that when writing to a mailing list, you should make an effort to conserve other peoples' time. Hundreds of people are going to read what you wrote, possibly thousands when you count in people searching the list archives in the future.

Re: [GENERAL] Syntax error in a large COPY

Tom Lane, 07.11.2007 06:14: Thomas Kellerer <[EMAIL PROTECTED]> writes: If everyone simply top-posted, there would be no need for me to scroll down, just to find a two line answer below a forty line quote - which I personally find more irritating than top-posting. I think you're

Re: [GENERAL] Postgre and XML

x asasaxax wrote on 19.11.2007 12:17: Hi, I´m interested in running xml with postgre. I use postgre version 8.2 and windows xp. I would like to know how can i enable the xml in the postgresql. Did you know if its secure to use this xml function of postgre in commercial applications? How

Re: [GENERAL] replication in Postgres

Alvaro Herrera, 26.11.2007 15:07: EnterpriseDB has no replication solution that I know of. Quote from http://www.enterprisedb.com/products/enterprisedb_replication.do "EnterpriseDB Replication Server replicates data across the enterprise in near real time to meet a wide array of business chal

Re: [GENERAL] PostgresSQL vs Ingress

Peter Childs, 30.11.2007 14:25: I found ingres website but no mention of a database system so I though they were something else that had taken on the name... http://www.ingres.com/downloads/prod-comm-download.php Found this using Google ;) Thomas ---(end of broad

Re: [GENERAL] Hijack!

Joshua D. Drake, 11.12.2007 17:43: O.k. this might be a bit snooty but frankly it is almost 2008. If you are still a top poster, you obviously don't care about the people's content that you are replying to, to have enough wits to not top post. I personally find non-trimmed bottom postings at lo

Re: [GENERAL] index organized tables use case

Richard Huxton, 12.12.2007 16:12: Hmm - I'm not sure it does benefit that much. I mean, if you're going to be accessing XXXA, then XXXB, XXXC etc. in order then it clearly helps to have the table with the same order as your primary key. Otherwise, I'd be doubtful you'd see that much benefit.

Re: [GENERAL] jdbc lob and postgresql

Anu Padki wrote on 12.12.2007 20:09: Hello all, I am contemplating to use postgresql for a product that requires to manipulate many blobs. From the documentation I am not clear if one has to use postgres api to insert/update/select blobs or one can use standard jdbc api? Can I just execute a sta

Re: [GENERAL] jdbc lob and postgresql

Kris Jurka, 13.12.2007 08:24: Using a PreparedStatement with setBinaryStream() to insert the blob and getBinaryStream() to read the BLOB works fine for me. It depends how you want to handle binary data on the server side. get/setBinaryStream only work with the bytea data type. getBlob/setBl

Re: [GENERAL] alter varchar() column length?

Gauthier, Dave, 11.01.2008 15:55: Is there a way to alter a varchar column’s length? Maybe something like “alter table foo alter column xyz varchar(256)”. A quick look into the manual would have revealed the correct syntax: http://www.postgresql.org/docs/8.2/static/sql-altertable.html -

Re: [GENERAL] Adding a New Column Specifically In a Table

Carlos Mennens, 13.10.2010 20:06: OK so I have read the docs and Google to try and find a way to add a new column to an existing table. My problem is I need this new column to be created 3rd rather than just dumping this new column to the end of my table. I can't find anywhere how I can insert m

[GENERAL] Re: Updates, deletes and inserts are very slow. What can I do make them bearable?

Tim Uckun, 21.10.2010 07:05: No, it isn't. This is a three-way join between consolidated_urls, cu, and tu --- the fact that cu is the same underlying table as cu is an alias for consolidated_urls. tu is an alias for trending_urls. There are only two tables in the query. Yes, but consolidate

Re: [GENERAL] Generate a dynamic sequence within a query

Alban Hertroys, 21.10.2010 13:43: I'm currently using WebFOCUS at work and they have a LAST operator, referring to the value a column had in the last returned row. That's pretty good for stuff like this, so I wonder if it wouldn't be beneficial to have something like that in Postgres? Already t

Re: [GENERAL] Implementing replace function

Alexander Farber wrote on 31.10.2010 09:22: Hello Postgres users, to mimic the MySQL-REPLACE statement I need to try to UPDATE a record and if that fails - INSERT it. There is actually an example of this in the PG manual ;) http://www.postgresql.org/docs/current/static/plpgsql-control-struct

[GENERAL] Temporary schemas

Hello, I have created a temporary table using create temporary table foo ( id integer ); and noticed this was created in a schema called "pg_temp_2" My question is: is this always "pg_temp_2"? Or will the name of the "temp schema" change? If it isn't always the same, is there a way I can r

Re: [GENERAL] Temporary schemas

Thom Brown wrote on 01.11.2010 12:33: You can use: SELECT nspname FROM pg_namespace WHERE oid = pg_my_temp_schema(); to get the name of the current temporary schema for your session. Thanks that's what I was looking for. Regards Thomas -- Sent via pgsql-general mailing list (pgsql-gene

Re: [GENERAL] Replication

Jonathan Tripathy wrote on 01.11.2010 20:53: Hi Everyone, I'm looking for the best solution for "Hot Standbys" where once the primary server fails, the standby will take over and act just like the master did. The standby must support INSERTS and UPDATES as well (once the master has failed) Are

Re: [GENERAL] Replication

Jonathan Tripathy wrote on 01.11.2010 21:12: 9.0 has streaming replication and "Hot Standby" http://www.postgresql.org/docs/current/static/hot-standby.html http://www.postgresql.org/docs/current/static/warm-standby.html#STREAMING-REPLICATION But does that not only allow "read-only" things to

Re: [GENERAL] Temporary schemas

Merlin Moncure wrote on 01.11.2010 21:13: On Mon, Nov 1, 2010 at 6:46 AM, Thomas Kellerer wrote: Hello, I have created a temporary table using create temporary table foo ( id integer ); and noticed this was created in a schema called "pg_temp_2" My question is: is this always

Re: [GENERAL] Temporary schemas

Merlin Moncure wrote on 01.11.2010 23:13: On Mon, Nov 1, 2010 at 4:27 PM, Thomas Kellerer wrote: The problem is, that the JDBC driver only returns information about the temp tables, if I specify that schema directly. Have you filed a bug report to jdbc yet? :-D. I thought about it

Re: [GENERAL] 8.4 Data Not Compatible with 9.0.1 Upgrade?

Carlos Mennens, 02.11.2010 22:37: Before I move or rename '/var/lib/postgres/data', what version of PostgreSQL should I be at? 8.4 or 9.0? Actually both, because pg_upgrade needs the binaries of the old *and* new version. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)

Re: [GENERAL] 8.4 Data Not Compatible with 9.0.1 Upgrade?

Adrian Klaver, 02.11.2010 23:23: Before I move or rename '/var/lib/postgres/data', what version of PostgreSQL should I be at? 8.4 or 9.0? Actually both, because pg_upgrade needs the binaries of the old *and* new version. Part of the confusion Carlos is experiencing is that he is caught betwee

Re: [GENERAL] Syntax of: alter table ... add constraint ...

Alexander Farber, 08.11.2010 15:50: And then I realized that I actually want medals smallint default 0 check (medals>= 0) So I've dropped the old constraint with alter table pref_users drop constraint "pref_users_medals_check"; but how can I add the new contraint please? I'm tryin

Re: [GENERAL] Schema tool

Aram Fingal wrote on 11.11.2010 22:45: I was thinking of reporting back to this forum with advantages/disadvantages of each tool, as I see it, but realized that I was rapidly getting too far off topic for a list focused specifically on PostgreSQL. I don't think this woul be off-topic here if yo

Re: [GENERAL] More then 1600 columns?

Peter Bex, 12.11.2010 08:36: What can also work extremely well is storing the data in an array. If you need to access the array based on more meaningful keys you could store key/index pairs in another table. The hstore module would also be a viable alternative - and it's indexable as well. T

Re: [GENERAL] newbie question - delete before insert

Grant Mckenzie wrote on 20.11.2010 07:00: How do people implement insert or upate ( otherwise known as upsert ) behaviour in postgres i.e. insert a row if it's key does not exist in the database else update the existing row? You can simply send the UPDATE, if nothing was updated, it's safe to

[GENERAL] Table name with umlauts

Hi, I'm curious why the following is not working: c:\psql postgres postgres psql (9.0.1) Type "help" for help. postgres=# select version(); version - PostgreSQL 9.0.1, compiled by Visual C++ build 1500, 32-bi

Re: [GENERAL] Table name with umlauts

Tom Lane wrote on 22.11.2010 19:25: Thomas Kellerer writes: I'm curious why the following is not working: postgres=# show client_encoding; client_encoding - UTF8 (1 row) postgres=# create table umlaut_test_ö (id integer); ERROR: invalid byte sequence for enc

Re: [GENERAL] Table name with umlauts

Tom Lane wrote on 22.11.2010 20:36: I had the idea that the Windows version of psql was smart enough to set client_encoding based on the console encoding it finds itself running under, but I might be wrong about that. Or maybe you did something that overrode its default? I changed to "chcp 125

Re: [GENERAL] alter table add column - specify where the column will go?

Alexander Farber, 24.11.2010 08:42: is there a syntax to add a column not at the last place No, because the order of the column is irrelevant (just as there is no order on the rows in a table) Simply select them in the order you like to have. Thomas -- Sent via pgsql-general mailing list (p

Re: [GENERAL] alter table add column - specify where the column will go?

Alexander Farber, 24.11.2010 08:49: Why do you want to do anything like that? Easier to read... login, logout I understand the "easier to read" part. But what do you mean with "login, logout"? Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to y

Re: [GENERAL] alter table add column - specify where the column will go?

Grzegorz Jaśkiewicz, 24.11.2010 10:37: just never use SELECT *, but always call columns by names. You'll avoid having to depend on the order of columns, which is never guaranteed, even if the table on disk is one order, the return columns could be in some other. I always try to convince people

Re: [GENERAL] A cronjob for copying a table from Oracle

Alexander Farber, 10.12.2010 12:02: I'm preparing a PHP-script to be run as a nightly cronjob and will first find the latest qdatetime stored in my local PostgreSQL database and then just "select" in remote Oracle, "insert" into the local PostgreSQL database in a loop. But I wonder if there is m

Re: [GENERAL] A cronjob for copying a table from Oracle

Alexander Farber, 10.12.2010 12:53: On Fri, Dec 10, 2010 at 12:33 PM, Thomas Kellerer wrote: And I'm not sure how to copy the Oracle's strange DATE column best into PostgreSQL, without losing precision? Oracle's DATE includes a time part as well. So simply use a timestamp in

Re: [GENERAL] Constraining overlapping date ranges

Filip Rembiałkowski, 22.12.2010 14:28: INSERT INTO tbl SELECT 1, '2010-01-01', '2010-12-31'; INSERT 0 1 I'm curious why you use this syntax as you have fixed values and could use the "standard" VALUES construct without problems: INSERT INTO tbl VALUES (1, '2010-01-01', '2010-12-31'); Regards

Re: [GENERAL] 2 versions of an entity worth distinct table?

gvim wrote on 27.12.2010 02:47: If a table representing contact details can have 2 but no more than 2 email addresses is it really worth factoring-out email addresses to a separate table. If you are absolutely sure you will never have more than two, then I agree, you don't need to create a 1:N

Re: [GENERAL] Need advise for database structure for non linear data.

Andre Lopes wrote on 03.01.2011 12:11: array( 'name' => 'Don', 'age' => '31' ); array( 'name' => 'Peter', 'age' => '28', 'car' => 'ford', 'km' => '2000' ); In a specific website search I will store only "name" and "age", and in other website I will store "na

Re: [GENERAL] Query to find sum of grouped counts from 2 tables

Satish Burnwal (sburnwal) wrote on 07.01.2011 11:15: I have 2 tables containing the data for same items: STORE1 - Id typeitems - 1 FOOD10 2 FOOD15 3 SOAP20 STORE2 -

Re: [GENERAL] OOO and postgres

Rich Shepard wrote on 07.01.2011 18:56: The data type is VARCHAR(), not character varying[]. character varying is a synonym for varchar, so the definition character varying[] is valid. It defines an array of varchar and is equivalent to varchar[] But I doubt that this is what the OP meant ;)

Re: [GENERAL] How to generate unique invoice numbers for each day

Tomas Vondra wrote on 16.01.2011 23:41: Yes, locking may in some cases lead to deadlocks, that's true. For example creating several invoices (for different days) in a single transaction may lead to a deadlock. But that's a feature, not a bug. Hmm, a single transaction cannot deadlock itself as

Re: [GENERAL] How can I find a schema that a table belong to?

Jerry LeVan, 19.01.2011 17:35: So I guess the question is: Given a bare table name, how can I recover the schema qualified name with whatever the current search path happens to be? SELECT table_schema FROM information_schema.tables WHERE table_name = 'your_table' ; -- Sent via

Re: [GENERAL] How can I find a schema that a table belong to?

Tom Lane, 19.01.2011 19:19: SELECT table_schema FROM information_schema.tables WHERE table_name = 'your_table' ; That's not going to work, at least not in the interesting case where you have more than one candidate table --- that SELECT will list all of 'em. Ah, right. I was a buit too quick

Re: [GENERAL] How can I find a schema that a table belong to?

Tom Lane, 19.01.2011 19:19: Given a bare table name, how can I recover the schema qualified name with whatever the current search path happens to be? SELECT table_schema FROM information_schema.tables WHERE table_name = 'your_table' ; That's not going to work, at least not in the interesting

Re: [GENERAL] Subselect AS and Where clause

Uwe Schroeder, 26.01.2011 08:34: I have a query like this: SELECT a,b,c, (select problem from other_table where id=a) as problem FROM mytable WHERE a=1 So far so good. Actually "problem" always resolves to one record, so it's not the "multiple records returned" problem. What I try to do is thi

Re: [GENERAL] How to extract a value from a record using attnum or attname?

Kevin Grittner wrote on 04.02.2011 23:27: PL/pgSQL seems tantalizingly close to being useful for developing a generalized trigger function for notifying the client of changes. I don't know whether I'm missing something or whether we're missing a potentially useful feature here. Does anyone see

Re: [GENERAL] many schemas or many databases

Szymon Guz, 08.02.2011 09:30: Hi, is there any noticeable difference between a cluster with many databases and a database with many schemas? I've got a quite huge database on Oracle with about 400 logically disjoint schemas. I could import that into PostgreSQL as many different databases, or as

Re: [GENERAL] Schema version control

Royce Ausburn wrote on 10.02.2011 22:38: I'm really interested to hear how you guys manage schema upgrades in the face of branches and upgrading from many different versions of the database. We are quite happy with Liquibase. You can simply run it against a database and tell it to migrate it t

Re: [GENERAL] Schema version control

Bill Moran wrote on 10.02.2011 23:59: The overview: You store your schema and data as XML (this is easy to migrate to, because it includes a tool that makes the XML from a live database) Keep your XML schema files in some RCS. When it's time for a new deployment, you run the dbsteward tool agains

Re: [GENERAL] Schema version control

Bill Moran wrote on 11.02.2011 00:37: Anyway ... based on nothing more than a quick scan of their quickstart page, here are the differences I see: * Liquibase is dependent on you creating "changesets". I'm sure this works, but we took a different approach with dbsteward. dbsteward expects

Re: [GENERAL] Hide db name and user name in process list arguments

Gavrina, Irina, 16.02.2011 15:50: Hi, On Unix systems Postgres process list can beaccessible through‘ps’ utility: ps auxww | grep ^postgres $ ps auxww | grep ^postgres postgres 9600.01.16104 1480 pts/1SN 13:17 0:00 postmaster -i postgres 9630.0

Re: [GENERAL] why is there no TRIGGER ON SELECT ?

Melvin Davidson, 22.02.2011 15:42: I know a function can be used, but the point is to log a table whenever "someone else" does a SELECT on it. It cannot be depended on that a user will include that (or any specific function in a SELECT. iow, when any user does "SELECT ... FROM tablex;" then logg

Re: [GENERAL] select DISTINCT not ordering the returned rows

Ioana Danes, 02.03.2011 21:35: Hi Everyone, I would like to ask for your help finding a temporary solution for my problem. I upgraded postgres from 8.3 to 9.0.3 and I have an issue with the order of the returned rows. The database is free to return rows in any order it thinks is most efficie

Re: [GENERAL] Create unique index or constraint on part of a column

Ruben Blanco wrote on 08.03.2011 00:30: Hi: Is there anyway to create a unique index or constraint on part of a column? Something like this, but something that works ;-) ALTER TABLE invoices ADD CONSTRAINT cons UNIQUE (EXTRACT(YEAR FROM invoice_date), innvoice_number); Thanks for any h

Re: [GENERAL] Install issues

Alex, 22.03.2011 17:33: Using Windows 7 64 bit. Tried to install 8.4 and 9.0and it fails right near the end when it tries to create or read the conf file. If I transfer my postgres 8.4 file over the upgrade takes but the postgres service doesn't exist so no communication occurs. Is there anyw

Re: [GENERAL] Table lock while adding a column and clients are logged in

Sven Haag wrote on 02.04.2011 12:13: if i'm trying to add an additional column to a table in pgadmin while clients are logged in, pgadmin hangs. only if all cients are logged out it returns to the normal state. according to our consultant of the application this behavior doesn't appear in oracle

Re: [GENERAL] Table lock while adding a column and clients are logged in

Alban Hertroys wrote on 03.04.2011 11:31: On 3 Apr 2011, at 11:22, Alban Hertroys wrote: Oracle and SQL server don't "suffer" from this because they do not handle DDL statements transactionally (I could be mistaken about SQL server, I don't know it all that well). I forgot to mention, if you

Re: [GENERAL] Table lock while adding a column and clients are logged in

Alban Hertroys wrote on 03.04.2011 11:17: On 2 Apr 2011, at 12:44, Thomas Kellerer wrote: Even after a plain SELECT you should issue a COMMIT (or ROLLBACK) to end the transaction that was implicitely started with the SELECT. Sorry, but you're wrong about that. A statement that impli

Re: [GENERAL] Table lock while adding a column and clients are logged in

Sven Haag wrote on 03.04.2011 16:13: Original-Nachricht Datum: Sun, 03 Apr 2011 15:37:17 +0200 Von: Thomas Kellerer An: pgsql-general@postgresql.org Betreff: Re: [GENERAL] Table lock while adding a column and clients are logged in Alban Hertroys wrote on 03.04.2011 11:17

Re: [GENERAL] postgreSQL : duplicate DB names

Khangelani Gama, 26.05.2014 09:20: > Hi > > Something wrong happened in our server, for some strange and unknown reason > (s) the server created another DB, > there is two same DB names in one server. > > Name| Owner | Encoding > -+--+-- > a9004| postgres |

Re: [GENERAL] Examples of projects that use Postgres "as API server"

Ben Ellis, 29.06.2014 03:10: > Hi all, > > I've been using Postgres for a small project and I've been very > impressed by its flexibility in defining new types and functions. I > very much like having the ability to define a clean relational model > and then a set of functions that act as the API

Re: [GENERAL] Windows Installation User account - Correct database for us

> When the database is installed a postgreSQL user account is created > which in most cases will be the second user account on the PC. No, not any longer. This has changed with 9.1 or 9.2 (don't remember). The Postgres service that is installed uses the "local network" account. > The programme

[GENERAL] Obsolete ToDo Item?

Hi, I was reading trough the ToDo list in the Postgres Wiki and noticed that one item for Fsync[1] seems to be obsolete: Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options and whether fsync does anything Ideally this requires a separate test program like /contrib/pg_test_fsyn

Re: [GENERAL] copy/dump database to text/csv files

William Nolf wrote on 24.07.2014 21:04: This is probably an easy one for most sql users but I don't use it very often. We have a postgres database that was used for an application we no longer use. However, we would like to copy/dump the tables to files, text or csv so we can post them to shar

<    1   2   3   4   5   6   7   8   9   >