for PostgreSQL questions specific to version 9.2.
7
votes
2answers
4k views
Postgres could not connect to server
After I did brew update and brew upgrade, my postgres got some problem. I tried to uninstall postgres and install again, but it didn't work as well.
This is the error message.(I also got this error ...
39
votes
1answer
9k views
How do I query using fields inside the new PostgreSQL JSON datatype?
I am looking for some docs and/or examples for the new JSON functions in PostgreSQL 9.2.
Specifically, given a series of JSON records:
[
{name: "Toby", occupation: "Software Engineer"},
{name: ...
2
votes
3answers
826 views
What is the maximum number of columns in a PostgreSQL select query
Do you know what the maximum number of columns that can be queried in Postgresql? I need to know this before I start my project.
2
votes
1answer
197 views
how to return only work time from reservations in PostgreSql
Select from great answer in How to find first free time in reservations table in PostgreSql
create table reservation (during tsrange,
EXCLUDE USING gist (during WITH &&)
);
is used to ...
3
votes
1answer
2k views
Entity Framework 5.0 PostgreSQL (Npgsql) default connection factory
I'm trying to get EF 5.0 code first working with PostgreSQL (Npgsql provider). I have Npgsql 2.0.12.1 installed via NuGet (referenced assembly is 2.0.12.0 though).
I have Npgsql declared in app.config ...
1
vote
1answer
165 views
Comparing arrays in PostgreSQL
I am attempting to determine if any item from a list of values is present in an array column in PostgreSQL.
SELECT * FROM data WHERE array IN (array)
I have this working using the && ...
11
votes
4answers
5k views
Postgresql 9.2 pg_dump version mismatch
I am trying to dump a Postgresql database using the pg_dump tool.
$ pg_dump books > books.out
How ever i am getting this error.
pg_dump: server version: 9.2.1; pg_dump version: 9.1.6
pg_dump: ...
3
votes
2answers
97 views
Insert using a function that returns two values per row
This function:
CREATE OR REPLACE FUNCTION fn_test1()
RETURNS SETOF date AS
$BODY$
declare
i int;
begin
i:=0;
while i<5 loop
return next '2001-01-02'::date;
i:=i+1;
end loop;
end
$BODY$
...
2
votes
2answers
103 views
Postgres.app upgrade, now Rails app won't start
I just upgraded my Postgres.app to latest version (9.2.4.1) am now unable to start my Rails app using Foreman or Rails server.
/Users/memoht/Sites/myapp/.gem/ruby/1.9.3/gems/pg-0.15.1/lib/pg.rb:4:in ...
heroku
2
votes
2answers
470 views
Remove duplicates from table based on multiple criteria and persist to other table
I have a taccounts table with columns like account_id(PK), login_name, password, last_login. Now I have to remove some duplicate entries according to a new business logic.
So, duplicate accounts will ...
2
votes
1answer
122 views
Querying row counts segregated by date ranges
I've got a PostgreSQL 9.2.1 database, where I'm attempting and failing to compose a SQL query which will show me the count of distinct tests (testname) which failed (current_status='FAILED' and ...
2
votes
2answers
208 views
How to find first free time in reservations table in PostgreSql
Reservation table contains reservations start dates, start hours and durations.
Start hour is by half hour increments in working hours 8:00 .. 18:00 in work days.
Duration is also by half hour ...
1
vote
1answer
25 views
Test script for transaction concurrency for postgresql
I would like to test some variants of transaction concurrency in PostgreSQL and for that I need a script which would force two transaction to start at exactly the same time. Something that does not ...
1
vote
1answer
109 views
ERROR: function expression in form cannot refer to other relations of same query level : How to work around LATERAL
DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;
CREATE TABLE primes
( pos SERIAL NOT NULL PRIMARY KEY
, val INTEGER NOT NULL
, CONSTRAINT primes_alt UNIQUE ...
0
votes
2answers
68 views
PostgreSQL: How to list all available datatypes?
Question:
In PostgreSQL (using SQL, not the console), how can I list all available datataypes ?
Ideally like this:
...
