Tag Info

Hot answers tagged

3

You cannot use a parameter name (_t) as return type. Use polymorphic types instead: CREATE OR REPLACE FUNCTION vsr_versioning_at_time(_t anyelement, _d timestamp) RETURNS SETOF anyelement AS $func$ BEGIN RETURN QUERY EXECUTE format(' SELECT DISTINCT ON (gid) * FROM %s WHERE vrs_start_time <= $1 ORDER BY gid, ...


3

There are really several different things in play here: Recovery to the current state if the DB server is lost. Synchronous replication is your only option here, but it comes with some risks - if the replica goes down or fails the master will stall, and if there's much latency then performance will suffer due to the need for the replica to confirm before ...


2

Use COALESCE to catch and replace NULL values: SELECT f.name AS foo , 'Bazzes: ' || COALESCE(string_agg(b.baz, ', '), '') AS bazzes FROM foo f LEFT JOIN bar b ON b.fooid = f.id GROUP BY 1; concat() is another convenient option as you found yourself, in particular to concatenate multiple values. I suggest the variant concat_ws() ("with ...


2

The point of reference comes from the cafe in the center, so you can use a subquery to retrieve it from the addresses table instead of the manual input: SELECT c.*, a.*, ST_Distance(t.lonlat, a.lonlat) AS distance -- pick columns you need FROM addresses a JOIN cafes c ON c.id = a.cafe_id , (SELECT a0.lonlat FROM addresses a0 WHERE a0.cafe_id = ...


2

There is no difference. Three quotes from the documentation: 1) These SQL-standard functions all return values based on the start time of the current transaction: ... CURRENT_TIMESTAMP ... 2) transaction_timestamp() is equivalent to CURRENT_TIMESTAMP, but is named to clearly reflect what it returns. 3) now() is a traditional ...


2

In think your problem is that gourmet_id is declared as CHARACTER(30). If you change it to CHARACTER VARYING(30), it should work much faster. This is what happens: -> Seq Scan on counters c (cost=0.00..250342.85 rows=2725633 width=16) (actual time=0.009..1610.743 rows=2751732 loops=1) Filter: ((counter_name)::text = 'FnfHit'::text) Rows Removed by ...


1

How do I avoid sort in the explain result? Actuall I didn't ask any sort in the SQL Statement. You've asked for rows to be aggregated. One way to do this is to sort the data set and then scan it to collapse out duplicates. This can be faster than hash aggregation, which is the other way PostgreSQL knows how to do grouping. So while you didn't ...


1

There is no syntax variant that lets you update the whole row at once. However, there is a shorter form than what you have so far. Also, you do not actually want to update all columns. The WHERE condition on id pins down at least one column (id) to remain unchanged. But that's just nitpicking. UPDATE table_a a SET ( c1, c2, ...) = (b.c1, b.c2, ...


1

I would write the raw data to a very basic table without indexes or constraints, not even a primary key. If you can, insert many rows at once, that's faster than single-row inserts. If you can afford loosing some data in a catastrophic event, make that an unlogged table, that's faster. If you can afford loosing some data in a catastrophic event and all ...


1

The simplest method I have found to accomplish this is to swap out the string concatenation operator for the string concatenation function concat(). For some reason the former apparently coerces the entire result to NULL if one operand is null, as opposed to the latter which effectively casts any NULL arguments to ''. So this query performs as desired: ...


1

It's the file size that's the problem. Essentially there's a timeout expiring somewhere, the server logs will tell you where. When the timeout expires, no more data is produced, and after a while with no data the connection gets closed as per normal. The last time I had to tackle something like this (Which is a long time ago now), I wrote a small program ...



Only top voted, non community-wiki answers of a minimum length are eligible