Re: [GENERAL] Return 30% of results?

Nick wrote on 18.09.2009 23:31: Is there a way to return a percentage of the rows found? I tried window functions but get an error... ERROR: syntax error at or near "OVER" SELECT id, cume_dist FROM ( SELECT id, cume_dist() OVER (ORDER BY id) FROM employees ) s WHERE cume_dist < 0.3 Works f

[GENERAL] Re: The password specified does not meet the local or domain policy during install.

David Chell wrote on 26.09.2009 06:31: This is a clean install, I’ve never installed postgresql on the is machine before, I have successfully installed it on another machine which is a Windows Server 2008 x64, but whenever I try to install it in the Windows XP (32) laptop I get the following

Re: [GENERAL] sync structures

John wrote on 28.09.2009 18:24: Thanks that will help. After all this time I'm surprized that someone hasn't provide an easy way to get this done. It's has to be every developers problem. Have a look at my SQL Workbench. It has a built-in command to generate a diff between two databases. T

Re: [GENERAL] Query to find contiguous ranges on a column

Peter Hunsberger wrote on 13.10.2009 23:23: I need a query to find the contiguous ranges within this column, in this case returning the result set: start, end 2, 5 11, 19 23, 23 32, 37 I have one solution that joins the table against itself and does (among other things) a subselect looking "not

Re: [GENERAL] Forms generator ?

Stuart Adams wrote on 28.10.2009 17:59: Looking for a forms generator for a web based UI for entering/modifiying/viewing a table's records. Any recommendations ??? Thanks, Stuart I haven't used this (yet), but once:Radix seems to be what you are looking for http://www.oncetechnologies

Re: [GENERAL] Correlated Subquery and calculated column non-functional

The Frog wrote on 30.10.2009 11:07: select product.manufacturer, product.brand, SUM(sales.qtysold * sales.unitprice) as turnover, (select count(*) from cube_sales.sales as Q WHERE SUM(sales.qtysold * sales.unitprice) > turnover) + 1 as rank from cube_sales.

Re: [GENERAL] sudoku in an sql statement

Merlin Moncure wrote on 31.10.2009 14:32: an oracle guy wrote an sql statement that solves a sudoku puzzle...using an oracle specific feature. Still, it's pretty neat, and an absolute gem of lateral thinking. http://technology.amis.nl/blog/6404/oracle-rdbms-11gr2-solving-a-sudoku-using-recursiv

[GENERAL] Windowing functions - future plans

Hi, I was wondering if there are plans to extend the window framing clause to allow the number of rows in the frame to be specified. Something like: ROWS BETWEEN 5 PRECEDING AND 5 FOLLOWING I did find a comment on the TODO list regarding the framing clause ("Implement full support for window

Re: [GENERAL] Store images on database ou in external files?

Andre Lopes wrote on 01.11.2009 15:20: I need to choose a way to store image files. This database will take care of an Add Classifieds website with low ammount of Ads, maximum of 20 new ads per month. It is good idea for this kind of website to store image files in the database? This is a

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

Merlin Moncure wrote on 12.09.2013 18:37: By the way, for in-place major version upgrade (not dumping DB and import again), MySQL is doing a better job in here. http://www.postgresql.org/docs/9.3/static/pgupgrade.html pgupgrade has nothing to do with this: that's just a tool that does in plac

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

Patrick Dung wrote on 12.09.2013 18:11: For PostgreSQL, it seems I can't find the list (it just say see the Appendix E / release notes). I think it is a plus for PostgreSQL if it has few incompatibilities between major versions. There is such a list in the release notes: http://www.postgresql.

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

Patrick Dung wrote on 13.09.2013 18:17: The problem of pg_upgrade is that it needed to hold two set of databases data in the server. This is not be desirable (very slow) or possible (space limitation) for database with huge data. For example, if the old version is already using over 50% of the

Re: [GENERAL] need elegant way to store and query tables with variable headers

Gauthier, Dave wrote on 08.10.2013 20:27: Someone is asking me for a way to architect a model which will store basic table data (columns with names and rows), but the number and name of the columns are both variables. I'll call these "data-tables" here. sounds like the hstore extension could

Re: [GENERAL] Unique - first

Robert James wrote on 27.10.2013 14:04: I have a table (x,y,z) - I'd like to take the rows with unique x values - but, when more than one row have the same x value, I want the one with the minimal z value. How can I do that? I can imagine doing it with window functions, but also that regular SQL

Re: [GENERAL] Work table

Robert James wrote on 27.10.2013 20:47: I'm using Postgres for data analysis (interactive and batch). I need to focus the analysis on a subset of one table, and, for both performance and simplicity, have a function which loads that subset into another table (DELETE FROM another_table; INSERT INT

Re: [GENERAL] Junk date getting uploaded into date field

bsreejithin, 05.11.2013 10:04: > We have a csv file which we upload into postgres DB. If there are some > errors, like a data mismatch with the database table columns, postgres > should raise and error and upload should fail. > > What is happening now is that, in case we get some junk date in the

Re: [GENERAL] Junk date getting uploaded into date field

bsreejithin, 05.11.2013 13:14: > Not able to post the attached details as a comment in the reply box, so > attaching it as an image file : > It would have much easier if you had simply used copy & paste to post a text version

Re: [GENERAL] Solution for Synonyms

mrprice22 wrote on 22.11.2013 19:25: We are in the process of moving from Oracle to PostgreSQL. We use a stored procedure to populate some reporting tables once an hour. There are two sets of these tables, set A and set B. We use synonyms to point to the “active” set of tables at any given tim

Re: [GENERAL] Inserting rows containing composite foreign keys

Nelson Green, 25.11.2013 23:01: > Hello, > When inserting a record into the jobs table that references projects by name, > do I have to query the projects table twice, > once to get the funding source number, and once to get the project sequence > number, even though both results will > return

Re: [GENERAL] Full Stored Procedure Support, any time soon ?

Alban Hertroys wrote on 30.11.2013 22:34: - Multiple result sets Since you’re talking about procedures, you can’t possibly mean that those return multiple result sets? Yes, basically something like this: create procedure foobar() begin select * from table_1; select * from table_2 end;

Re: [GENERAL] Full Stored Procedure Support, any time soon ?

John R Pierce wrote on 30.11.2013 23:47: On 11/30/2013 2:30 PM, Noel Diaz wrote: This might involve the control of transaction state and the return of multiple result sets * PL/pgSQL stored procedure returning multiple result sets (SELECTs)?

Re: [GENERAL] client that supports editing views

Willy-Bas Loos, 09.12.2013 14:36: > I have a database with views that have rules on them, so that users > can do insert/update/delete on their part of the data. The rules ore > "do instead" rules that redirect the edits to the table that actually > holds the data, on which the users have no rights.

Re: [GENERAL] client that supports editing views

Willy-Bas Loos wrote on 09.12.2013 21:44: But the option to save the record is disabled, the other 2 buttons do not enable me to save the record. Anyway, i don't care much for MS Access, any client will do (that is, if it costs money, it would be nice if most users own it already or it isn't ve

Re: [GENERAL] client that supports editing views

Willy-Bas Loos, 10.12.2013 13:30: > cool, SQL Workbench/J: does the job. > It's too bad that it doesn't list the lables/views, so that you can't just > open them with a click > (i use select * from table to get the data), but it works well. > Did you check the "Database Explorer"? http://www.s

Re: [GENERAL] replacing expresion in plpgsql

John R Pierce, 16.12.2013 01:23: >> select current_timestamp, current_timestamp + interval '2' day; > > that should be interval '2 day' (note the ' moved), and for a variable > number passed as a parameter, try... Both are valid. interval '2' day is the ANSI SQL format though. -- Se

Re: [GENERAL] Add PK constraint to a Unique Index via updating system catalogs?

CS DBA wrote on 06.01.2014 23:30: We have a few very large tables with unique indexes on a column but the column is not defined as the Primary Key. Can we add a PK constraint via updates to the system catalogs so we can avoid the lengthy checks that would take place if we ran "alter table add co

[GENERAL] to_date() and invalid dates

Hi, I asked this a while back already: select to_date('2013-02-31', '-mm-dd'); will not generate an error (unlike e.g. Oracle) However in the release notes of 9.2.3[1] it is mentioned that - Reject out-of-range dates in to_date() (Hitoshi Harada) I tried the above statement using 9

Re: [GENERAL] to_date() and invalid dates

Albe Laurenz, 20.01.2014 15:29: >> I asked this a while back already: >> >>select to_date('2013-02-31', '-mm-dd'); >> >> will not generate an error (unlike e.g. Oracle) > > This is by design. When I previously asked this question the answer as "this is based on Oracle's to_date()": http:

Re: [GENERAL] to_date() and invalid dates

Michael Nolan, 20.01.2014 16:17: > Thomas, try this: > > '2013-02-31'::date Thanks, I know this "works", but this can't be used if you have a non-ISO date string -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgre

Re: [GENERAL] composite foreign key performance

Leonard Boyce, 22.01.2014 16:49: > We have an existing schema as follows; > lookup (id bytea PK, status text, .) > data A bytea as the PK? That sounds horrible. > Hoping I can get some advice here. What exactly is your question? -- Sent via pgsql-general mailing list (pgsql-general@p

Re: [GENERAL] Fully-automatic streaming replication failover when master dies?

Dmitry Koterov, 22.01.2014 22:35: > I googled 1 hour approximately, but have not found a ready solution > for this. So maybe this feature is in PostgreSQL todo-list, or > something similar exists somewhere... > > Before the actual question, I'd like to give a small analogy. What I > mostly love in

Re: [GENERAL] concurrent SELECT blocking ALTER?

Neil Harkins wrote on 29.01.2014 23:37: I totally understand DDL taking exclusive locks, the problem here seems to be that the *SELECTs* are taking out exclusive locks, locking out the ALTER, which feels like a bug. The SELECT is not holding an exclusive lock, it's holing a *shared* lock, but

Re: [GENERAL] Grep'ing for a string in all functions in a schema?

Wells Oliver wrote on 30.01.2014 21:45: Since Postgres does not consider a table as a dependency of a function if that table is referenced in the function (probably a good reason), I often find myself in a position of asking "is this table/sequence/index referenced in any of these N number of fun

Re: [GENERAL] client that supports editing views

Stefan Keller wrote on 08.02.2014 12:31: If pgAdmin is acceptable, you might want to try SQL Workbench/J: http://www.sql-workbench.net I'd like to revive that discussion and like to know: Q1 => Can anybody recommend a >> desktop GUI tool (preferrably open source) << for PG with customizable for

[GENERAL] Re: Is PostgreSQL 9.3 using indexes for pipelined top-N window function queries?

Behrang Saeedzadeh, 15.02.2014 02:35: > Hi, > > I just stumbled upon this article from 2012 [1], according to which > (emphasis mine): > > Window functions offer yet another way to implement pagination in > SQL. This is a flexible, and above all, standards-compliant method. > However, only SQL Se

Re: [GENERAL] configuration error

saravanan Chow, 20.02.2014 06:41: > There is nothing in the pg_log. > Log files contains : "2014-02-20 10:58:55 IST LOG: received fast shutdown > request > 2014-02-20 10:58:55 IST LOG: aborting any > active transactions > 2014

Re: [GENERAL] Moving data from M$ JetDB file to Postgres on Linux

Rich Shepard wrote on 01.03.2014 00:21: I just downloaded two scientific data files from a federal agency's Web site. Both are in M$ JetDB format. I run only linux and keep all my scientific dat in postgres. My Web search did not turn up anything useful; the closest was a thread from this

Re: [GENERAL] Moving data from M$ JetDB file to Postgres on Linux

Rich Shepard wrote on 01.03.2014 01:33: On Sat, 1 Mar 2014, Thomas Kellerer wrote: It's not clear to me what exactly you are trying to do. Or are you just trying to copy the data into the Postgres database? If the latter you could try one of the JDBC based query tools to export or cop

Re: [GENERAL] multiple results from a function

James Harper wrote on 03.03.2014 21:10: I'm working on an application to accept TDS (Microsoft SQL Server) connections and proxy them to postgres. MSSQL does things a little differently, for instance in addition to a functions it has stored procedures that can contain multiple select statement th

[GENERAL] Re: Is PostgreSQL 9.3 using indexes for pipelined top-N window function queries?

rows. > > The execution plan from Thomas Kellerer sees to fulfill requirement > (1) but definitively not (2). > > Even with 9.3.2, I were not able to reproduce the result of Thomas > (not showing any sort operation in the execution plan) with the test > data I also publis

Re: [GENERAL] COPY v. java performance comparison

Rob Sargent, 02.04.2014 21:37: > I loaded 37M+ records using jOOQ (batching every 1000 lines) in 12+ > hours (800+ records/sec). Then I tried COPY and killed that after > 11.25 hours when I realised that I had added on non-unque index on > the name fields after the first load. By that point is was

[GENERAL] public schema owner for newly created database

Hello all, I usually create new databases with an explicit owner which is the "application user" that is used by the application to connect to the database. I recently noticed when I do the following: postgres=# create user arthur identified by 'secret'; postgres=# create database guide owner =

Re: [GENERAL] public schema owner for newly created database

Alberto Cabello Sánchez, 08.04.2014 13:05: >> But I would have expected the owner of the database to be the owner of any >> "object" that is created during database initialization. >> >> Do I have a wrong assumption here? > > It seems you do. See: > > Why Postgresql Public Schema Is Not Owned B

Re: [GENERAL] Disable an index temporarily

Torsten Förtsch wrote on 20.04.2014 10:09: The problem is I have a number of indexes in a large system that are very similar. And I suspect some of them are superfluous. Example: btree (fmb_id, action_type) btree (fmb_id) Action_type in this case is one out of a handful of values (sh

Re: [GENERAL] Oracle to PostgreSQL replication

Rajni Baliyan, 09.05.2014 09:46: > I downloaded symmetricDS but I did not found any installer > (symmetric-pro-x.x.x.jar) > Can any one please help me in this This might help: http://www.symmetricds.org/doc/3.5/html/tutorial.html#tutorial-install -- Sent via pgsql-general mailing list (pg

[GENERAL] 9.4 beta - pg_get_viewdef() and WITH CHECK OPTION

Hello, when playing with 9.4 beta I noticed that the result of pg_get_viewdef() will not include the new WITH CHECK OPTION clause when the view was created using it. Is that intended (if so: why?) or is this an oversight/bug? Regards Thomas -- Sent via pgsql-general mailing list (pgsql-g

Re: [GENERAL] 9.4 beta - pg_get_viewdef() and WITH CHECK OPTION

Dean Rasheed wrote on 19.05.2014 01:10: when playing with 9.4 beta I noticed that the result of pg_get_viewdef() will not include the new WITH CHECK OPTION clause when the view was created using it. Is that intended (if so: why?) or is this an oversight/bug? Yes, that's correct. pg_get_viewde

[GENERAL] Re: Create Database automatacally after silent installation of postgresql. ?

hirenlad, 27.04.2011 09:47: Hiii Hey i m using postgresql 8.4. now i m install postgresql8.4 silently and it work properly, no issue during this process. Now problem is i want to create one database automatically after install postgresql 8.4. Can u plz inform me is it possible ? and if

[GENERAL] Error in the 9.1 documentation?

Hi, while going through the 9.1 new features, I think I have discovered an error in the manual regarding the CREATE TABLE command. It says: CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [ { column_name data_type [ COLLATE collation ] [ co

Re: [GENERAL] Error in the 9.1 documentation?

Thom Brown wrote on 07.05.2011 16:28: while going through the 9.1 new features, I think I have discovered an error in the manual regarding the CREATE TABLE command. The DEFAULT declaration was moved into the column_constraint section. Ah thanks, didn't see that (and I wouldn't expect the DEFA

[GENERAL] Documentation suggestion

Hi, I'd like to suggest a little enhancement to the documentation chapter about file-system backup http://www.postgresql.org/docs/current/static/backup-file.html As I regularly see people copying files between different operating systems, I think it would be a good idea to add a third restrict

Re: [GENERAL] database field list

Seb wrote on 29.05.2011 23:04: Hi, I've been scouring the system tables for a way to return a list of fields across all tables of a database. I see that pg_attribute is the one to query here, but I'm not sure how to rule out system fields. Thanks in advance for any pointers. information_sche

Re: [GENERAL] Problems with to_number

Chrishelring wrote on 10.06.2011 22:45: HI all, below is the view i´ve tried to create on a table. The purpose was to do some math on one of the columns (retning). The column is a double precision number. The result is that the function is not recognized ("ERROR: function to_number(double preci

Re: [GENERAL] An example for WITH QUERY

Durumdara, 22.06.2011 12:35: Hi! I have 3 tables. I want to run a query that collect some data from them, and join into one result table. I show a little example, how to do this in another DB with script: with tmp_a as ( select id, name, sum(cost) cost from items ... ), temp_b as ( s

Re: [GENERAL] variant column type

salah jubeh, 26.07.2011 19:02: Hello, suppose the following scenario the car speed is 240 the car has an airbag Here the first value is integer and the second value is boolean. Consider that I have this table structure feature (feature id feature name) car (car id, ) car_feature (car i

Re: [GENERAL] ERD Tool

Adarsh Sharma, 31.08.2011 13:54: Dear all, Is there any open source ERD Tool for Postgresql Database. I find some paid tools but looking for free tools. Have a look at Power*Architect: http://www.sqlpower.ca/page/architect It's not perfect but it's quite OK. As it is a multi-DBMS tool it doe

Re: [GENERAL] conditional insert

Pau Marc Muñoz Torres, 05.09.2011 11:38: Hi follk i trying to performe a conditional insert into a table, indeed, what i'm trying to do is not insert a record into the table if that record exist googleling i found something like insert into XX values (1,2,3) where not exist (select

[GENERAL] Problem with the 9.1 one-click installer Windows7 64bit

Hi, I tried to install 9.1 on a Windows7 64bit machine but the installation hangs during the initdb process. Looking at the taskmanager I could see that the installer script was waiting for icacls.exe to complete setting the approriate permissions on the data directory. As I know that proble

Re: [GENERAL] Problem with the 9.1 one-click installer Windows7 64bit

Merlin Moncure, 12.09.2011 21:28: With the second attempt, the installer again hang during initdb. Checking the state using ProcessExplorer I could see that the installer script was waiting for icacls.exe to set permissions for the user currently running the installer. It was running [icacls.exe

Re: [GENERAL] Problem with the 9.1 one-click installer Windows7 64bit

Craig Ringer, 14.09.2011 06:20: I forwarded your message to Dave Page in case the EDB folks needed to look into this. He commented that: "Aside from the fact that icacls is hanging for reasons unknown, it appears to be doing what it is designed to do - it traverses up the path from the data dire

Re: [GENERAL] Problem with the 9.1 one-click installer Windows7 64bit

Brar Piening, 14.09.2011 21:49: Thomas Kellerer wrote: So I killed the iacls.exe and the script proceeded, just to hang at the next call to icacls.exe when it tried to set the privileges on the directory for the postgres user despite the fact that that user already was the owner and had full

Re: [GENERAL] Problem with the 9.1 one-click installer Windows7 64bit

Craig Ringer, 14.09.2011 06:20: As Dave noted, it's a non-recursive grant. It isn't changing the permissions for C:\ and everything under it. It's only changing the permissions for C:\ its self. It's then stepping down the path of parents down to the datadir and doing the same thing to make sure

Re: [GENERAL] Problem with the 9.1 one-click installer Windows7 64bit

Craig Ringer, 16.09.2011 05:02: On 15/09/2011 4:18 PM, Thomas Kellerer wrote: I ran another install and monitored what the process was doing and it *is* recursively touching all files on my harddisk when icacls C:\ /grant "tkellerer":RX is called. Even without the /t switch. That

[GENERAL] Problem with pg_upgrade 9.0 -> 9.1

Hi, I was trying to upgrade my Postgres 9.0 installation using pg_upgrade. Running it first with --check revealed no problems. The when I did the actual migration, the following happened: === start console output Performing Consistency Checks

Re: [GENERAL] Indexes not allowed on (read-only) views: Why?

Craig Ringer, 17.09.2011 02:28: On 09/17/2011 05:47 AM, Stefan Keller wrote: A (read-only) view should behave like a table, right? CREATE INDEX t1_idx ON t1 (rem); ERROR: »v1« not a table SQL state: 42809 => Why should'nt it be possible to create indexes on views in PG? It's not so much th

Re: [GENERAL] SQL function and input variables

Martín Marqués, 21.09.2011 14:56: I was makeing an SQL function and got an error which on a sintax that I thouhgt would work: CREATE OR REPLACE FUNCTION dicInsertarPalabra(p TEXT) RETURNS INT AS $body$ INSERT INTO public.diccionario (palabra) VALUES (quote_literal(p)); SELECT COA

Re: [GENERAL] Materialized views in Oracle

Craig Ringer, 22.09.2011 08:34: - You don't see materialized views without selective updating ("fast refresh") as useful. [I disagree, though I can see how it wouldn't be very useful for the particular use case you're facing.] One thing that is often overlooked and that I find most useful is th

Re: [GENERAL] Problem with pg_upgrade 9.0 -> 9.1

Thomas Kellerer, 17.09.2011 12:32: I was trying to upgrade my Postgres 9.0 installation using pg_upgrade. Running it first with --check revealed no problems. The when I did the actual migration, the following happened: Mismatch of relation id: database "dellstore", old relid 83613,

Re: [GENERAL] SQL Help - Finding Next Lowest Value of Current Row Value

Jeff Adams wrote on 01.10.2011 23:30: Greetings, I have a large table (~19 million records). Records contains a field identifying a vessel and a field containing an time (epoch). Using the current rows vessel and time values, I need to be able to find the next lowest time value for the vessel an

Re: [GENERAL] One-click installer, Windows 7 32-bit, and icacls.exe

Dave Page wrote on 03.10.2011 10:11: Karl; can you please provide precise details of your Windows version, and anything unusual about your disk configuration? I know this doesn't happen on any of the installations of Windows 7 that we use for testing (which tend to be the MSDN builds, running on

Re: [GENERAL] Problem with pg_upgrade 9.0 -> 9.1

Bruce Momjian, 06.10.2011 02:15: I now got the same error (alas with a different relation id) while migrating a completely different data directory. Anything I can do to help find the reason for this problem (or bug?) Unfortuantely the data contains some confidential information so I cannot mak

Re: [GENERAL] Selecting All Columns Associated With Maximum Value of One Column

Rich Shepard, 06.10.2011 15:13: I was unaware of the windows functions. I see the document page for 9.0.5 so I'll carefully read that and upgrade from 9.0.4 (which may also have this function; I'll check). Windowing functions are available since 8.4 -- Sent via pgsql-general mailing list (pg

[GENERAL] 9.1 got really fast ;)

I have to share this, a statement that is finished before I even run it ;) Limit (cost=0.00..527.34 rows=20 width=4) (actual time=0.046..-2.436 rows=20 loops=1) Output: id, (count(*)) Buffers: shared hit=191 -> GroupAggregate (cost=0.00..12403455.78 rows=470416 width=4) (actual time=0

Re: [GENERAL] Question: How do you manage version control?

Bryan Montgomery wrote on 01.06.2012 17:28: So we've been reviewing our processes and working on improving them. One area we've been lacking is a procedure to version control our database functions, table changes, static data etc. I'm curious how others do it. Ideally, we want it to be part of

Re: [GENERAL] TYPE TABLE OF NUMBER

utsav, 04.06.2012 07:00: No.. in this we have to define domain and than used it . Our requirenment is like application pass the array of integer and that we have to use in PostgreSQL functions. You can pass an array directly. There is no need to define an "array type" in PostgreSQL -- Sen

Re: [GENERAL] Postgres Installation Password

Magruder, Ryan S. wrote on 11.06.2012 16:55: When attempting to download and install Postgres on my computer for work, it prompted me to create a password. Upon trying many different combinations I could not get one to work. An error message stated that the password I entered did not meet the con

Re: [GENERAL] UPDATE Syntax Check

Rich Shepard wrote on 13.06.2012 00:17: One table, waterchem, with primary key 'site' has columns easting and northing with no values in them. A second table, sites, with primary key 'name' has values for easting and northing for each row. I want to update waterchem with the easting and northi

Re: [GENERAL] Create view is not accepting the parameter in postgres functions

Chris Travers, 13.06.2012 09:16: If this ever changes, I would certainly hope that the SQL language functions would first be given named argument support. This is coming in 9.2 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] Backslashitis

haman...@t-online.de, 14.06.2012 10:17: Hi, I have a column declared as array of text. I can get a single backslash into one of the array elements by update ... set mycol[1] = E'blah \\here' If I try to update the whole array update ... set mycol = E'{"blah \\here"}' the backslash is missing. I

Re: [GENERAL] efficiency of wildcards at both ends

Sam Z J wrote on 20.06.2012 19:10: Hi all I'm curious how is wildcards at both ends implemented, e.g. LIKE '%str%' How efficient is it if that's the only search criteria against a large table? how much does indexing the column help and roughly how much more space is needed for the index? if t

Re: [GENERAL] efficiency of wildcards at both ends

Alan Hodgson wrote on 20.06.2012 19:39: I'm curious how is wildcards at both ends implemented, e.g. LIKE '%str%' How efficient is it if that's the only search criteria against a large table? how much does indexing the column help and roughly how much more space is needed for the index? Indexin

Re: [GENERAL] Select Rows With Only One of Two Values [RESOLVED]

Chris Angelico wrote on 20.07.2012 18:25: I don't know how hard it'd be to make it work on Postgres, but here's an epic piece of SQL awesomeness: http://thedailywtf.com/Articles/Stupid-Coding-Tricks-The-TSQL-Madlebrot.aspx That has already been done - and much cleaner I think ;) https://wiki.

Re: [GENERAL] insert binary data into a table column with psql

jtkells, 25.07.2012 03:43: Thanks much for your reply, that does the trick quite nicely. But, I just came to the realization that this only works if your are running the client and the file both resides on the database server. I thought that I would be able to do this from a remote server where

Re: [GENERAL] File system level backup

Manoj Agarwal wrote on 26.07.2012 12:39: Hi, I have two virtual machines with two different versions of Postgresql. One machine contains Postgres 7.4.19 and another has Postgres 8.4.3. I also have other instances of these two virtual machines. I need to transfer the database from one machine

Re: [GENERAL] Installer problem report with interesting solution

Craig Ringer wrote on 04.08.2012 03:39: Hi all On Stack Overflow I was following the efforts of a user who was unable to install Pg. http://stackoverflow.com/questions/11656410/postgresql-installation-issue#comment15679519_11656410 They've just come back to let me know they found a solution t

[GENERAL] Re: Some thoughts on table inheritance (which is uniquely awesome on PostgreSQL)

Chris Travers, 23.08.2012 05:22: The fact that this allows you to create essentially derived values from groups of re-used columns is itself remarkable and can be used to implement path traversal etc. which is not directly supported in PostgreSQL in the sense that it is in Oracle or DB2. What e

Re: [GENERAL] What text format is this and can I import it into Postgres?

Mike Christensen, 23.08.2012 02:41: Oh, also if anyone knows of a way to export an Access database to Postgres, that might be helpful. I don't have a copy of Access. If you have a Windows box, you can try SQL Workbench/J. Even though it is a Java application it can connect to an Access databas

[GENERAL] 9.2 and index only scans

Hi, I'm playing around with 9.2 beta4 and was looking into the new Index Only Scan feature. I was a bit surprised that a "count(*)" query does not use an index. Not even a count(col) where col is the PK of the table. Is that intended? If so, why is that the case? I would have thought that this

Re: [GENERAL] 9.2 and index only scans

Tom Lane wrote on 26.08.2012 16:31: Thomas Kellerer writes: I'm playing around with 9.2 beta4 and was looking into the new Index Only Scan feature. I was a bit surprised that a "count(*)" query does not use an index. Works for me. However, the cost estimate for that is heavi

Re: [GENERAL] 9.2 and index only scans

Jeff Janes wrote on 26.08.2012 20:45: The seq scan is estimated to use sequential reads, while the index-only scan is estimated to use random reads (because the index is scanned in logical order, not physical order). If you set random_page_cost equal to seq_page_cost, that would artificially fav

Re: [GENERAL] 9.2 and index only scans

Jeff Janes wrote on 26.08.2012 22:26: The seq scan is estimated to use sequential reads, while the index-only scan is estimated to use random reads (because the index is scanned in logical order, not physical order). Sounds like scanning the index in physical order would be an enhancement. That

Re: [GENERAL] 9.2 and index only scans

Martijn van Oosterhout, 28.08.2012 10:02: I'm not sure how oracle avoids the same issues: - The index has no visibility information, so you can't tell if an index entry refers to a row you can actually see in your session. The visibility map might help here in the future. In Oracle an ind

Re: [GENERAL] 9.2 and index only scans

Craig Ringer, 28.08.2012 15:04: In Oracle an index (entry) has the information about transactional visibility. Wow. Doesn't that mean that indexes are insanely expensive to update, since each index (and possibly also the table its self) needs updating? No, I don't think so. It's the same me

Re: [GENERAL] 9.2 and index only scans

Tom Lane, 28.08.2012 16:30: In Oracle an index (entry) has the information about transactional visibility. You sure about that? Yes, although technically it's not the index *entry*, but the index *block*. But the result is the same thing. The visibility information is stored on data block le

Re: [GENERAL] Add a check an a array column

Bret Stern wrote on 08.09.2012 22:18: A better place for validation is in the front-end, before adding/attempting to add data to the db (my opinion). Nice to see there are always other ways though. I beg to differ: every validation that can be enforced by declarative constraints *should* be c

Re: [GENERAL] Can a view use a schema search_path?

Adam Mackler, 17.09.2012 11:06: I have the feeling the answer is no, but I would like an authoritative answer before I give up. My plan was to have two schemas: one for the live data, and one for staging, training, and testing. Both schemas would have identically-named tables. I wanted to crea

Re: [GENERAL] idle in transaction query makes server unresponsive

Scot Kreienkamp wrote on 25.09.2012 22:35: The application is using a pooler and generally runs around 100 connections, but I've seen it as high as 200 during the day for normal use. It's on a large server; 64 cores total and about 500 gigs of memory. That's one of the reasons I left it at 512

Re: [GENERAL] Can not start postgresSQL 8.4

Boriss Redkins, 02.10.2012 10:42: I've got postgreSQL 8.4 and 91. installed on my Windows 7 machine. 9.1 version starts just fine. But when trying to start 8.4 with: Services Microsoft Corporation Version: 6.1.7600.16385 it does not start and no logs are produced. When starting in console: C

Re: [GENERAL] Moving from Java 1.5 to Java 1.6

Swayam Prakash Vemuri, 04.10.2012 08:52: Hi We have an application which uses postgresql 7.4.5. Now when we moved to Java 1.6, we are seeing lots of jdbc driver related compilation issues like shown at end of this email. Not only are you using an outdated (and unsupported) PostgreSQL versio

Re: [GENERAL] Moving from Java 1.5 to Java 1.6

John R Pierce, 05.10.2012 08:34: You are also moving to a Java version that is soon to be de-supported. Java is supported? only if you mean the non-stop stream of updates brought on by web exploit exposures. Yes it is. In a similar way as PostgreSQL is "supported". if you're using Java as

<    1   2   3   4   5   6   7   8   9   >