[GENERAL] PGFoundry Sample databases (particularly world)?

PGFoundry's own link to their Sample databases gives me a 404 error. http://pgfoundry.org/projects/dbsamples/ Does anyone know of an alternative source for the sample databases? And what is going on at pgfoundry? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make ch

Re: [GENERAL] PGFoundry Sample databases (particularly world)?

Wow. I love this community. Thanks a bunch. > On May 5, 2015, at 10:41 , Marc Fournier wrote: > > > Fixed .. .let me know if there are any other issues … -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mail

[GENERAL] First-class Polymorphic joins?

It surprises me that no SQL database to my knowledge has polymorphic joins as a first-class feature. A polymorphic join is where a fk contains not just an id but an indicator of which table it refers to. So you could have a "tags" table, that can attach tags to any of a variety of other tables.

[GENERAL] Uber migrated from Postgres to MySQL

Honestly, I've never heard of anyone doing that. But it sounds like they had good reasons. https://eng.uber.com/mysql-migration/ Thoughts? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-genera

Re: [GENERAL] Filtering by UUID

On Sep 29, 2016, at 16:03 , Colin Morelli wrote: > > Hey list, > > I'm using UUID primary keys in my application. I need to add pagination, and > am trying to avoid OFFSET/LIMIT. I do have a created_at timestamp that I > could use, but it's possible for multiple records to be created at the sa

Re: [GENERAL] Filtering by UUID

way, if you have occasion to often filter the list on some other field. But you don't need the UUID field because even if there is collision, it will be a small number of records. > On Thu, Sep 29, 2016 at 7:09 PM Guyren Howe <mailto:guy...@gmail.com>> wrote: > On Sep 29,

[GENERAL] Immutable datastore library?

I would like to use Postgres as an immutable data store. A subject table would have a timestamp column, and that would be added to what would otherwise be the primary key. Trap updates and turn them into inserts. Have an associated _deleted table. Trap deletes and turn them into inserts of the

Re: [GENERAL] Immutable datastore library?

On Oct 17, 2016, at 13:54 , Thomas Munro wrote: > > I've done some projects using temporal (and bitemporal) models based > on Richard Snodgrass's excellent book (which I hear is widely read at > utility companies among others), without any special library support: > > http://www.cs.arizona.edu/~

[GENERAL] SELECT DISTINCT ON removes results

Using 9.5, this query: SELECT o.id, a.number AS awb FROM pt.orders o LEFT JOIN ( SELECT DISTINCT ON ((string_agg(air_way_bills.number::text, ','::text))) string_agg(air_way_bills.number::text, ','::text) AS number, air_way_bills.order_id

Re: [GENERAL] SELECT DISTINCT ON removes results

On Oct 28, 2016, at 13:50 , David G. Johnston wrote: > > ​On its face the statement "DISTINCT ON removes results" is not at all > surprising given its definition. What is surprising is that it removes *all* results…

Re: [GENERAL] SELECT DISTINCT ON removes results

On Oct 28, 2016, at 14:15 , Guyren Howe wrote: > > On Oct 28, 2016, at 13:50 , David G. Johnston <mailto:david.g.johns...@gmail.com>> wrote: >> >> ​On its face the statement "DISTINCT ON removes results" is not at all >> surprising given its def

Re: [GENERAL] SELECT DISTINCT ON removes results

> On Oct 28, 2016, at 13:50 , David G. Johnston > wrote: > > On Fri, Oct 28, 2016 at 1:39 PM, Guyren Howe <mailto:guy...@gmail.com>> wrote: > Using 9.5, this query: Unless I'm missing something, this ought to be impossible. Two queries differing only in having

[GENERAL] Love Your Database project — Thoughts on effectively handling constraints?

I believe a lot of application programmers, particularly but by no means limited to web application developers, have a tragic prejudice against treating their database as anything but a dumb data bucket. They also often lack awareness of even simple-to-use SQL/Postgres features that would make

[GENERAL] Re: [GENERAL] Love Your Database project — Thoughts on effectively handling constraints?

On Dec 16, 2016, at 16:52 , Tom Lane wrote: > >> So I’ve started a project to fix this. I’m initially going to write a series >> of blog posts demonstrating in principle how a developer can put much/all of >> their model logic in their database. > > Cool. This sounds well worth while. Thanks

[GENERAL] Re: [GENERAL] Love Your Database project — Thoughts on effectively handling constraints?

On Dec 16, 2016, at 16:52 , Tom Lane wrote: > > The server already does deliver more-structured error data, although I confess > that I have no idea how to get at it in Ruby on Rails. In psql the case > looks about like this: Thanks for the advice. I’ve worked out how to get at the same informa

[GENERAL] LYDB: What advice about stored procedures and other server side code?

I am putting together some advice for developers about getting the most out of SQL servers in general and Postgres in particular. I have in mind the likes of most web developers, who through ignorance or a strange cultural preference that has emerged, tend to treat their database server as a dum

[GENERAL] Book or other resource on Postgres-local code?

As an aside from my last question about my LYDB effort: https://medium.com/@gisborne/love-your-database-lydb-23c69f480a1d#.4jngp2rcb I would like to find a book or other resource about SQL server-side programming (st

[GENERAL] LYDB: Feasible to use PG roles instead of application-level security?

Further to my attempts to enlighten application developers about what they might better do in the database: https://medium.com/@gisborne/love-your-database-lydb-23c69f480a1d#.4jngp2rcb it occurs to me to wonder wheth

Re: [GENERAL] Book or other resource on Postgres-local code?

> On Dec 29, 2016, at 23:01 , Regina Obe wrote: > > >> As an aside from my last question about my LYDB effort: > >> https://medium.com/@gisborne/love-your-database-lydb-23c69f480a1d#.4jngp2rcb > >> I would like to find a book or other resource about SQL server-side >> programming (stored pro

[GENERAL] Not clear how to switch role without permitting switch back

For my Love Your Database Project: https://medium.com/@gisborne/love-your-database-lydb-23c69f480a1d#.8g1ezwx6r I’m trying to see how a typical web developer might use Postgres’ roles and row-level security to implem

[GENERAL] Are new connection/security features in order, given connection pooling?

Further to my recent inquiries about leveraging Postgres’ security features from client apps, it appears to me that some likely reasonably simple changes to those features would enable client apps to better leverage what are otherwise great features. *IF* we give our end users roles in Postgres

Re: [GENERAL] Are new connection/security features in order, given connection pooling?

On Jan 11, 2017, at 0:07 , John R Pierce wrote: > > On 1/10/2017 11:32 PM, Guyren Howe wrote: >> Further to my recent inquiries about leveraging Postgres’ security features >> from client apps, it appears to me that some likely reasonably simple >> changes to those feat

[GENERAL] Avoiding repeating simple field definitions

I saw a thing somewhere about avoiding repeating the same field definitions. So an app I’m working on uses an exactly 6-character sting as an identifier, which appears in many places. IIRC, the thing I read proposed defining a type AS IMPLICIT, but I’m not sure. Mainly because the docs urge cau

[GENERAL] Import Schema converting tinyint to Boolean?

Converting databases is a nightmare. IMPORT SCHEMA almost makes it easy. Almost. I'm converting a MySQL database to a Postgres database by doing a bunch of CREATE TABLE… AS SELECT * FROM, but the tinyints are coming across as tinyints. Seems like there ought to be an option somewhere to have th

[GENERAL] Explanation of tree-generating query

I feel like I'm pretty decent with Postgres. But I saw the following query on the excellent Periscope blog. I've no idea how it works, and the various symbols involved are difficult to look up either with google or in the documentation. I believe the @ sign is probably ABS, but the <= clause in

[GENERAL] Proper relational database?

Anyone familiar with the issue would have to say that the tech world would be a significantly better place if IBM had developed a real relational database with an elegant query language rather than the awful camel of a thing that is SQL. If I had a few $million to spend in a philanthropical mann

Re: [GENERAL] Proper relational database?

On Apr 21, 2016, at 13:56 , John McKown wrote: > > Just as a curiosity, what do you think of ANDL? > > http://www.andl.org/welcome-to-andl/ > > The developer has been posting some questions here about interfacing it to > PostgreSQL. But he doesn't just wa

Re: [GENERAL] Proper relational database?

On Apr 22, 2016, at 10:45 , Raymond Brinzer wrote: > > Are there relational algebra expressions, or other operations > necessary to a truly relational database, which cannot be translated > into SQL? I'm not aware that there are, but I'd be interested to hear > of it. If there were, there's a g

Re: [GENERAL] Proper relational database?

The SQL language is terrible but we can live with it. But the answer to "Are there any relational data stores that offer eventual consistency, easy distribution, schema-on-demand or any such things a large modern application can use?" appears to be no. And that's just awful. > On Apr 22, 2016,

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

On Apr 22, 2016, at 15:03 , Merlin Moncure wrote: > > On Thu, Apr 21, 2016 at 5:08 AM, Bráulio Bhavamitra > wrote: >> I'm finally having performance issues with PostgreSQL when doing big >> analytics queries over almost the entire database of more than 100gb of >> data. >> >> And what I keep r

Re: [GENERAL] Proper relational database?

On Apr 22, 2016, at 18:56 , wrote: > > Why schema-on-demand? Can you explain what you mean by that? Something that is attractive, for beginners or perhaps when prototyping is that you don't have to declare a table. You can just insert tuples into a predicate whose name you provide and they go

Re: [GENERAL] Proper relational database?

On Apr 23, 2016, at 19:43 , David Bennett wrote: > ow...@postgresql.org] On Behalf Of Eric Schwarzenbach > If I had a few $million to spend in a philanthropical manner, I would hire some of the best PG devs to develop a proper relational database >> server. Probably a query languag

[GENERAL] Thoughts on "Love Your Database"

I've long been frustrated with how most web developers I meet have no idea how to use an SQL database properly. I think I'm going to write a book called Love Your Database, aimed at web developers, that explains how to make their apps better by leveraging the power of SQL in general, and Postgre

[GENERAL] Debugging code on server?

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 code? I can imagine things you might set up: u

Re: [GENERAL] Thoughts on "Love Your Database"

On May 4, 2016, at 11:59 , Paul Jungwirth wrote: > > I think teaching this is a different thing than just teaching SQL syntax. I > haven't seen it written about a lot. It must be somewhere, but any book > encouraging "outsiders" to use more SQL could benefit from giving them > direction like t

[GENERAL] FIRST_VALUE argument must appear in group by?

I have this SELECT clause as part of a larger query: FIRST_VALUE(drs.id) OVER (PARTITION BY drs.order_ID ORDER BY drs.position ASC) AS current_drs_id Seems reasonable to me: group and sort the fields in this table and give me the first value. But I get "column "drs.id" must appear in the GROUP B

Re: [GENERAL] FIRST_VALUE argument must appear in group by?

On May 16, 2016, at 20:48 , David G. Johnston wrote: > > On Monday, May 16, 2016, Guyren Howe <mailto:guy...@gmail.com>> wrote: > I have this SELECT clause as part of a larger query: > FIRST_VALUE(drs.id) OVER (PARTITION BY drs.order_ID ORDER BY drs.position > ASC) A

Re: [GENERAL] FIRST_VALUE argument must appear in group by?

I am trying to use a window function, but it's not working. The subquery is effectively aggregating. > On May 17, 2016, at 6:18 , David G. Johnston > wrote: > > On Tue, May 17, 2016 at 12:04 AM, Guyren Howe <mailto:guy...@gmail.com>> wrote: > On May 16, 2016,

Re: [GENERAL] Thoughts on "Love Your Database"

On May 17, 2016, at 2:22 , Achilleas Mantzios wrote: > > Sorry if I missed something but what's wrong with pgadmin3 ? Apart from it's awful, clunky, bug-ridden and crash prone, nothing. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscriptio

Re: [GENERAL] Thoughts on "Love Your Database"

On May 20, 2016, at 13:38 , Pierre Chevalier Géologue wrote: > > Le 04/05/2016 18:29, Szymon Lipiński a écrit : >> On the other hand, when I was trying to store all my logic in a >> database, there was just one thing that made me hate it. Testing. >> Testing the procedures inside the database wa

[GENERAL] FIRST_VALUE: need to group by argument?

I am missing something here. I have two tables: orders id delivery_route_segments id, order_id, position, completed I want to find the first uncompleted deliver_route_segment for each order, by position. Seems to me I ought to be able to do this:

[GENERAL] hstore, but with fast range comparisons?

I want to do something that is perfectly satisfied by an hstore column. *Except* that I want to be able to do fast (ie indexed) <, > etc comparisons, not just equality. >From what I can tell, there isn’t really any way to get hstore to do this, so >I’ll have to go to a key-value table. But I th

[GENERAL] I did some testing of GIST/GIN vs BTree indexing…

Obviously, database benchmarking is basically a silly idea, because every workload, every hardware configuration, every schema are different, with very different consequences. Still, I was left with wondering when one should choose a BTree vs GIST or GIN (I didn’t even try to look at SP-GIST: a

Re: service allowing arbitrary relations was Re: [GENERAL] hstore, but with fast range comparisons?

>> I want to do something that is perfectly satisfied by an hstore column. >> *Except* that I want to be able to do fast (ie indexed) <, > etc >> comparisons, not just equality. >> >> From what I can tell, there isn’t really any way to get hstore to do this, >> so I’ll have to go to a key-val

[GENERAL] Re: [GENERAL] I did some testing of GIST/GIN vs BTree indexing…

On Dec 6, 2014, at 12:38 , Bruce Momjian wrote: > > On Wed, Dec 3, 2014 at 01:15:50AM -0800, Guyren Howe wrote: >> GIN is certainly not the “three times” size suggested in the docs, but >> perhaps >> that just hasn’t been updated for the 9.4 improvements. Ce

[GENERAL] Re: [GENERAL] I did some testing of GIST/GIN vs BTree indexing…

On Dec 10, 2014, at 19:38 , Bruce Momjian wrote: > > Are you saying when you use a GIN index on a,b,c fields, you can do > lookups on them independently, like 'c'? I was not aware that works, > but it might. I know it doesn't work for traditional btree as the index > is hierarchical. You can l

[GENERAL] Write from Postgres to SQL Server

I find myself in an environment wanting to move to Postgres, having a variety of smaller Postgres databases, but the business currently revolves around a “God” SQL Server database. I’m looking for general advice about making the two work together. More specifically, interoperating between the t

[GENERAL] Advice about software engineering inside Postgres?

I’m working on a project to get the likes of web developers to make more effective use of Postgres. This amounts to saying that much of the M in MVC should be implemented as code and relations in Postgres. The more I think about this, the more I see that a model written in Node, say, that just

[GENERAL] Are multiple array_aggs going to be in the same order?

If I do a SELECT with a GROUP_BY and multiple ARRAY_AGGs, will the ARRAY_AGGs be guaranteed to have entries in the same (ie corresponding) order? eg SELECT u.name, ARRAY_AGG(o.order_date) AS order_dates, ARRAY_AGG(o.order_total) AS order_totals FROM user u JOIN

[GENERAL] How to add columns to view with dependencies

Seems like a simple question, but I’ve never found a good answer to this and similar issues. I would think it was safe to let me add columns to a view on which other views depend, but Postgres won’t let me. I can imagine ways of sort-of dealing with this. I might maintain a SQL file with views

[GENERAL] Does a view not define a composite type?

I have a view: “reporting_helpers.customers_by_state_ranked" I try to define a function with this signature: CREATE OR REPLACE FUNCTION reporting.formatted_cust_by_state_bold(c "reporting_helpers.customers_by_state_ranked") RETURNS "reporting_helpers.customers_by_state_ranked" I am told “ER

Re: [GENERAL] Does a view not define a composite type?

> On Apr 23, 2017, at 10:12 , Guyren Howe wrote: > > I have a view: “reporting_helpers.customers_by_state_ranked" > > I try to define a function with this signature: > > CREATE OR REPLACE FUNCTION reporting.formatted_cust_by_state_bold(c > "reporting

[GENERAL] Use function to manipulate rows — how to get separate columns, rather than single row value

I’m trying to write a function that manipulates whole rows. It returns the same type as the table it is being applied to, but when I select the function on the rows, I get a single column of row type, rather than separate columns. My function looks like: CREATE OR REPLACE FUNCTION reporting.for

[GENERAL] Re: Use function to manipulate rows — how to get separate columns, rather than single row value

> On Apr 23, 2017, at 11:34 , Guyren Howe wrote: > > I’m trying to write a function that manipulates whole rows. It returns the > same type as the table it is being applied to, but when I select the function > on the rows, I get a single column of row type, rather than s

Re: [GENERAL] Python versus Other Languages using PostgreSQL

On May 9, 2017, at 2:07 , Paul Hughes wrote: > > Postgres might be a popular choice among Rails devs, but Ruby is not as > popular among the big web platforms that choose Postgres. Ahem. AirBnB, Bloomberg, Crunchbase, Github, Groupon, Heroku, Hulu, Kickstarter, Scribd, Shopify, Slideshare, S

[GENERAL] Text value within composite function result not quoted

Define a couple of types: CREATE TYPE request_in AS ( path text[], args jsonb, server text, port smallint, headers jsonb, body bytea, type_requested text[] ); CREATE TYPE request_out AS ( status smallint, headers jsonb,

Re: [GENERAL] Text value within composite function result not quoted

> On May 15, 2017, at 21:36 , Tom Lane wrote: > >> ... get this result: >> (200,{},Works!) >> This is the textual representation of the result I get in psql and Ruby. >> Note that the textual final value is not quoted. >> I imagine I can work out a way to deal with this, but this is not the mos

Re: [GENERAL] jsonb case insensitive search

On Jun 1, 2017, at 13:44 , Karl Czajkowski wrote: > If I remember correctly, isn't a compound index always just using > btree? In general, I have found better luck using several smaller > btree indices than one large compound one. Unless your entire query > can be answered from an index-only loo

[GENERAL] How to drop column from interrelated views

I’ve a set of interrelated views. I want to drop a column from a table and from all the views that cascade from it. I’ve gone to the leaf dependencies and removed the field from them. But I can’t remove the field from the intermediate views because Postgres doesn’t appear to be clever enough to

[GENERAL] How to stop array_to_json from interpolating column names that weren't there

9.6 I’ve a view that shows information about schemas: SELECT schemata.catalog_name, schemata.schema_name, ('/'::text || (schemata.schema_name)::text) AS schema_name_address FROM information_schema.schemata ORDER BY schemata.catalog_name, schemata.schema_name Fine. I now want to tur

Re: [GENERAL] How to stop array_to_json from interpolating column names that weren't there

On Jul 19, 2017, at 20:47 , Paul Jungwirth wrote: > >> which is great. I have an array of perfect JSON objects. Now I just need >> to turn that into a single JSON object. > > I think you're saying you want it as a single JSON *array*, right? An object > of objects doesn't make sense. Assuming

[GENERAL] Setting search_path ignored

CREATE ROLE thing_accessor; CREATE ROLE CREATE SCHEMA thing_accessor; CREATE SCHEMA covermything=> ALTER ROLE thing_accessor SET search_path=thing_accessor; ALTER ROLE covermything=# SET ROLE thing_accessor; SET covermything=> SHOW search_path; search_path - "$user",

Re: [GENERAL] Setting search_path ignored

I logged out and back and did SET ROLE and got the same resullt. On Oct 2, 2017, 10:06 -0400, David G. Johnston , wrote: > On Mon, Oct 2, 2017 at 7:00 AM, Guyren Howe wrote: > > > CREATE ROLE thing_accessor; > > > CREATE ROLE > > > CREATE SCHEMA thing

[GENERAL] Combing row returning functions using "ROWS FROM"?

I just noticed the reference to combining set returning functions using “ROWS FROM” at https://www.postgresql.org/docs/10/static/queries-table-expressions.html#queries-tablefunctions I have not pr