Thanks to function type resolution we can also pass date values to generate_series() because there is an implicit cast from date to timestamp as well as from date to timestamptz. Would be ambiguous, but timestamptz is “preferred” among “Date/time types”. Detailed explanation: Generating time series between two dates in PostgreSQL For a bare date the … Read more
In Postgres 9.3 or later use a LATERAL join: SELECT v.col_a, v.col_b, f.* — no parentheses, f is a table alias FROM v_citizenversions v LEFT JOIN LATERAL f_citizen_rec_modified(v.col1, v.col2) f ON true WHERE f.col_c = _col_c; Why LEFT JOIN LATERAL … ON true? Record returned from function has columns concatenated For older versions, there is … Read more
I think you’re looking for SELECT select_expressions INTO: select test_table.name into name from test_table where id = x; That will pull the name from test_table where id is your function’s argument and leave it in the name variable. Don’t leave out the table name prefix on test_table.name or you’ll get complaints about an ambiguous reference.
There is more to this question than it may seem. Simple version This is much faster and simpler: SELECT property_name ,(count(value_a = value_b OR NULL) * 100) / count(*) AS pct FROM my_obj GROUP BY 1; Result: property_name | pct ————–+—- prop_1 | 17 prop_2 | 43 How? You don’t need a function for this … Read more
Simpler, shorter, faster: EXISTS. IF EXISTS (SELECT FROM people p WHERE p.person_id = my_person_id) THEN — do something END IF; The query planner can stop at the first row found – as opposed to count(), which scans all (qualifying) rows regardless. Makes a big difference with big tables. The difference is small for a condition … Read more
find the file pg_hba.conf – it may be located, for example in /etc/postgresql-9.1/pg_hba.conf. cd /etc/postgresql-9.1/ Back it up cp pg_hba.conf pg_hba.conf-backup place the following line (as either the first un-commented line, or as the only one): For all occurrence of below (local and host) , except replication section if you don’t have any it has … Read more
I remember having raised an almost identical point when PG9 was in alpha state. Here was the answer from Tom Lane (high-profile PG core developer): http://archives.postgresql.org/pgsql-general/2010-01/msg00221.php In short: won’t fix. Not to say that I agree with your suggestion that the current behavior is a bug. Look at it from the opposite angle: it’s the … Read more
if you are running on Linux you may be interested in what worked for me: sudo apt-get install postgresql sudo apt-get install libpq-dev Then gem install pg then bundle install src: http://wikimatze.de/installing-postgresql-gem-under-ubuntu-and-mac
[*] Yes, this is a common aggregation problem. Before SQL3 (1999), the selected fields must appear in the GROUP BY clause[*]. To workaround this issue, you must calculate the aggregate in a sub-query and then join it with itself to get the additional columns you’d need to show: SELECT m.cname, m.wmname, t.mx FROM ( SELECT … Read more
Can be done without conversion to/from int (but to/from timestamp instead) SELECT date_trunc(‘day’, dd):: date FROM generate_series ( ‘2007-02-01’::timestamp , ‘2008-04-01’::timestamp , ‘1 day’::interval) dd ;