Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [postgresql-9.3]

PostgreSQL version 9.3

Specifically for PostgreSQL version 9.3 (Released Sep 09, 2013)

580 questions
146
votes
8 answers

How to turn JSON array into Postgres array?

I have a column data of type json that holds JSON documents like this: { "name": "foo", "tags": ["foo", "bar"] } I would like to turn the nested tags array into a concatenated string ('foo, bar'). That would be easily possible with the…
Christoph
  • 1,653
  • 2
  • 12
  • 8
127
votes
1 answer

Postgres Count with different condition on the same query

EDIT Postgres 9.3 I'm working on a report which has this following schema: http://sqlfiddle.com/#!15/fd104/2 The current query is working fine which looks like this: Basically it is a 3 table inner join. I did not make this query but the developer…
jackhammer013
  • 1,469
  • 2
  • 12
  • 11
72
votes
9 answers

Export Postgres table as json

Is there a way to export postgres table data as json to a file? I need the output to be line by line, like: {'id':1,'name':'David'} {'id':2,'name':'James'} ... EDIT: postgres version: 9.3.4
AliBZ
  • 1,827
  • 5
  • 17
  • 27
68
votes
3 answers

How to install the additional module pg_trgm

I just want to know how to install the module pg_tgrm as used in the trigram indexing scheme that allows you to do un-anchored search patterns on an index. WHERE foo LIKE '%bar%';
Lizardie
  • 813
  • 1
  • 6
  • 8
55
votes
7 answers

How to get all roles that a user is a member of (including inherited roles)?

Let's say I have two Postgresql database groups, "authors" and "editors", and two users, "maxwell" and "ernest". create role authors; create role editors; create user maxwell; create user ernest; grant authors to editors; --editors can do what…
Neil McGuigan
  • 8,653
  • 5
  • 42
  • 57
52
votes
2 answers

Refresh materalized view incrementally in PostgreSQL

Is it possible to refresh a materialized view incrementally in PostgreSQL i.e. only for the data that is new or has changed? Consider this table & materialized view: CREATE TABLE graph ( xaxis integer NOT NULL, value integer NOT…
user4150760
  • 1,129
  • 3
  • 14
  • 20
38
votes
4 answers

why pg_restore ignores --create ? Error: failed: FATAL: database "new_db" does not exist

I am trying to run following command: sshpass -p "pass" ssh x@1.2.3.4 "pg_dump -Fc -U foo some_db" | pg_restore --create --dbname=new_db I get: failed: FATAL: database "new_db" does not exist
andilabs
  • 697
  • 2
  • 6
  • 11
38
votes
4 answers

postgres - pg_dump and pg_restore without roles

I'm trying to restore a dump without having the appropriate roles on the receiving database. As mentioned here but also here, you need to have the --no-owner as an option, either in pg_dump or pg_restore or both. I've used the following command line…
Andy K
  • 707
  • 1
  • 10
  • 19
37
votes
2 answers

DELETE rows which are not referenced in other table

I have two tables in a PostgreSQL 9.3 database: Table link_reply has a foreign key named which_group pointing to table link_group. I want to delete all rows from link_group where no related row in link_reply exists. Sounds basic enough but I've been…
Hassan Baig
  • 2,079
  • 8
  • 31
  • 44
36
votes
3 answers

How to speed up select distinct?

I have a simple select distinct on some time series data: SELECT DISTINCT user_id FROM events WHERE project_id = 6 AND time > '2015-01-11 8:00:00' AND time < '2015-02-10 8:00:00'; And it takes 112 seconds. Here's the query…
Sam
  • 637
  • 2
  • 7
  • 12
34
votes
1 answer

Why does PostgreSQL allow querying for array[0] even though it uses 1-based arrays?

I have been playing with arrays in one of my PostgreSQL databases. I have created a table with a geometry array with at least one element: CREATE TABLE test_arrays ( polygons geometry(Polygon,4326)[], CONSTRAINT non_empty_polygons_chk …
Adam Matan
  • 12,079
  • 30
  • 82
  • 96
32
votes
4 answers

GRANT USAGE on all schemas in a database?

I want to GRANT USAGE to a user/role for a given database. The database has many schemas. I know there's an ON ALL TABLES IN SCHEMA, but I want "all schemas". I tried GRANT USAGE .. ON DATABASE, but that's obviously wrong (it doesn't actually…
300D7309EF17
  • 431
  • 1
  • 4
  • 6
29
votes
2 answers

How to handle bad query plan caused by exact equality on range type?

I'm performing an update where I require an exact equality on a tstzrange variable. ~1M rows are modified, and the query takes ~13 minutes. The result of EXPLAIN ANALYZE can be seen here, and the actual results are extremely different from those…
abeboparebop
  • 481
  • 4
  • 11
25
votes
4 answers

Transactions within a Transaction

What behaviour would PostgreSQL display if for example the script below were called BEGIN; SELECT * FROM foo; INSERT INTO foo(name) VALUES ('bar'); BEGIN; <- The point of interest END; Would PostgreSQL discard the second BEGIN or would a commit be…
Alex
  • 365
  • 1
  • 3
  • 5
25
votes
3 answers

Change existing column in PG to auto-incremental primary key

I have a database in Postgresql, which was migrated from SQL Server (only data). On SQL Server, a table from this database has these columns: measure_id datum measure where measure_id is auto-incremental primary key, datum is datetime and measure…
zetah
  • 355
  • 1
  • 3
  • 5
1
2 3
…
38 39