Stack Overflow
Stack Overflow

Questions tagged [postgresql-9.3]

for PostgreSQL questions specific to version 9.3.

The version 9.3 of was released Sept. 9, 2013.

Major improvements on the previous versions include:

  • writable external data with foreign data wrappers
  • data page checksums
  • streaming-only remastering
  • fast failover
  • event triggers
  • materialized views
  • improved JSON support
  • LATERAL JOIN
  • parallel pg_dump

The official documentation for this version is available at: http://www.postgresql.org/docs/9.3/static/index.html

1627 questions
274
votes
20 answers

How do I modify fields inside the new PostgreSQL JSON datatype?

With postgresql 9.3 I can SELECT specific fields of a JSON data type, but how do you modify them using UPDATE? I can't find any examples of this in the postgresql documentation, or anywhere online. I have tried the obvious: postgres=# create table…
user9645
  • 4,526
  • 4
  • 21
  • 33
220
votes
3 answers

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: "Zaphod", occupation: "Galactic President"} ] How…
Toby Hede
  • 35,582
  • 27
  • 127
  • 161
178
votes
11 answers

Allow docker container to connect to a local/host postgres database

I've recently been playing around with Docker and QGIS and have installed a container following the instructions in this tutorial. Everything works great, although I am unable to connect to a localhost postgres database that contains all my GIS…
marty_c
  • 4,171
  • 5
  • 21
  • 25
151
votes
4 answers

Check if a Postgres JSON array contains a string

I have a table to store information about my rabbits. It looks like this: create table rabbits (rabbit_id bigserial primary key, info json not null); insert into rabbits (info) values ('{"name":"Henry", "food":["lettuce","carrots"]}'), …
Snowball
  • 8,614
  • 3
  • 30
  • 47
141
votes
9 answers

postgresql list and order tables by size

How can I list all the tables of a PostgreSQL database and order them by size?
nothing-special-here
  • 8,238
  • 10
  • 56
  • 90
120
votes
1 answer

How to create index on JSON field in Postgres?

In PostgreSQL 9.3 Beta 2 (?), how do I create an index on a JSON field? I tried it using the -> operator used for hstore but got the following error: CREATE TABLE publishers(id INT, info JSON); CREATE INDEX ON publishers((info->'name')); ERROR: …
rlib
  • 5,716
  • 3
  • 25
  • 36
80
votes
17 answers

Hidden Features of PostgreSQL

I'm surprised this hasn't been posted yet. Any interesting tricks that you know about in Postgres? Obscure config options and scaling/perf tricks are particularly welcome. I'm sure we can beat the 9 comments on the corresponding MySQL thread :)
ramanujan
  • 5,235
  • 5
  • 28
  • 31
75
votes
2 answers

Refresh a materialized view automatically using a rule or notify

I have a materialized view on a PostgreSQL 9.3 database which seldom changes (about twice a day). But when it does, I'd like to update its data promptly. Here is what I was thinking about so far: There is a materialized view mat_view which gets its…
mawimawi
  • 3,814
  • 3
  • 29
  • 48
71
votes
1 answer

PostgreSQL Nested JSON Querying

On PostgreSQL 9.3.4, I have a JSON type column called "person" and the data stored in it is in the format {dogs: [{breed: <>, name: <>}, {breed: <>, name: <>}]}. I want to retrieve the breed of dog at index 0. Here are the two queries I…
ravishi
  • 2,849
  • 4
  • 28
  • 39
67
votes
8 answers

Postgresql LEFT JOIN json_agg() ignore/remove NULL

SELECT C.id, C.name, json_agg(E) AS emails FROM contacts C LEFT JOIN emails E ON C.id = E.user_id GROUP BY C.id; Postgres 9.3 creates output for example id | name | emails ----------------------------------------------------------- 1 | …
user3081211
  • 771
  • 1
  • 6
  • 5
65
votes
4 answers

keystroke to clear screen in psql?

I want a shortcut key to clear the screen in my (Windows 7) psql console just like CTRL-l clears the screen in my R console. I am tired of typing '! cls'. Do I need to write a macro for this? I am running Postgres 9.35. '\r' resets the query buffer…
rferrisx
  • 1,038
  • 1
  • 9
  • 14
57
votes
20 answers

Import psycopg2 Library not loaded: libssl.1.0.0.dylib

When I try to run the command: import psycopg2 I get the error: ImportError: dlopen(/Users/gwulfs/anaconda/lib/python2.7/site-packages/psycopg2/_psycopg.so, 2): Library not loaded: libssl.1.0.0.dylib Referenced from:…
Gideon
  • 949
  • 1
  • 11
  • 17
50
votes
2 answers

postgresql sequence nextval in schema

I have a sequence on postgresql 9.3 inside a schema. I can do this: SELECT last_value, increment_by from foo."SQ_ID";` last_value | increment_by ------------+-------------- 1 | 1 (1 fila) But this doesn't work: SELECT…
carlos
  • 973
  • 1
  • 8
  • 14
49
votes
3 answers

Cast syntax to convert a sum to float

Using PostgreSQL 9.3, I want to convert the calculated values to data type float. My first attempt: SELECT float(SUM(Seconds))/-1323 AS Averag; Gives me this error: syntax error at or near "SUM" My second attempt: SELECT…
MAK
  • 5,596
  • 16
  • 61
  • 105
47
votes
3 answers

"extra data after last expected column" while trying to import a csv file into postgresql

I try to copy the content of a CSV file into my postgresql db and I get this error "extra data after last expected column". The content of my CSV is agency_id,agency_name,agency_url,agency_timezone,agency_lang,agency_phone 100,RATP…
Frederic Le Feurmou
  • 1,456
  • 1
  • 14
  • 22
1
2 3
…
99 100