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

1691 questions
370
votes
24 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
  • 6,286
  • 6
  • 29
  • 43
280
votes
14 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
  • 11,230
  • 13
  • 64
  • 94
272
votes
15 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
  • 5,779
  • 5
  • 24
  • 27
252
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
  • 36,755
  • 28
  • 133
  • 162
249
votes
9 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
  • 11,102
  • 3
  • 34
  • 51
175
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
  • 7,444
  • 3
  • 32
  • 40
114
votes
7 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,598
  • 2
  • 12
  • 14
107
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
  • 3,349
  • 5
  • 31
  • 40
94
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
  • 4,222
  • 3
  • 33
  • 52
79
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,581
  • 5
  • 30
  • 31
75
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
  • 1,261
  • 1
  • 12
  • 15
68
votes
6 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,676
  • 1
  • 17
  • 22
61
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
  • 6,824
  • 25
  • 74
  • 131
61
votes
22 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
  • 989
  • 1
  • 11
  • 17
60
votes
3 answers

LocalDateTime to ZonedDateTime

I have Java 8 Spring web app that will support multiple regions. I need to make calendar events for a customer location. So let's say my web and Postgres server is hosted in MST timezone (but I guess it could be anywhere if we go cloud). But the…
sonoerin
  • 5,015
  • 23
  • 75
  • 132
1
2 3
…
99 100