    SELECT jsonb_array_length(&#39;[&quot;question&quot;,&quot;solved&quot;]&#39;) AS length;

or

    SELECT json_array_length(&#39;[&quot;question&quot;,&quot;solved&quot;]&#39;) AS length;




As seen in the documentation for [TimeComplexity], Python&#39;s `list` type is implemented is using an array.

So if an array is being used and we do a few appends, eventually you will have to reallocate space and copy all the information to the new space.  
After all that, how can it be O(1) worst case ?

[TimeComplexity]:https://wiki.python.org/moin/TimeComplexity

Why is the time complexity of python&#39;s list.append() method O(1)?

I installed postgres using homebrew.
I would like to locate he files pg_hba.conf and postgresql.conf but I cannot find them.

I followed this :

https://askubuntu.com/questions/256534/how-do-i-find-the-path-to-pg-hba-conf-from-the-shell

and this 

http://www.kelvinwong.ca/tag/pg_hba-conf/

but I still cannot find it.

Cannot find pg_hba.conf and postgreql.conf file on OS X?

I&#39;m running the latest version of PostgreSQL `9.4.5-1.pgdg14.04+1`, and am attempting to calculate the length of a JSONB array using the `JSON_ARRAY_LENGTH` function as described in the [PostgreSQL 9.4 Documentation][1]

Here is the exact query I&#39;m attempting to run:

    SELECT JSON_ARRAY_LENGTH(&#39;[&quot;hi&quot;,&quot;ho&quot;]&#39;::jsonb) AS length

When I run that query, I would expect to be returned a value of `2`, but instead am encountering the error: `ERROR: function json_array_length(jsonb) does not exist`

Am I missing something very obvious in the documentation? It specifically states you may call `JSON_ARRAY_LENGTH` passing either a `json` or `jsonb` data-type. I&#39;m explicitly casting to `jsonb` so I&#39;m at a bit of a loss.
 
Has anyone else encountered this problem, or would someone point out what I&#39;m doing wrong here?


**UPDATE: I Mis-Read The Documentation**

I should have been calling `JSONB_ARRAY_LENGTH`, not `JSON_ARRAY_LENGTH`. Notice the &quot;B&quot; after &quot;JSON&quot;. Thanks guys.

  [1]: http://www.postgresql.org/docs/9.4/static/functions-json.html

Calculate JSONB Array Length Using PostgreSQL 9.4

I'm running the latest version of PostgreSQL <code>9.4.5-1.pgdg14.04+1</code>, and am attempting to calculate the length of a JSONB array using the <code>JSON_ARRAY_LENGTH</code> function as described in the <a href="http://www.postgresql.org/docs/9.4/static/functions-json.html" target="_blank" rel="noopener noreferrer">PostgreSQL 9.4 Documentation</a>
Here is the exact query I'm attempting to run:
<pre><code class="hljs language-css">SELECT JSON_ARRAY_LENGTH('["hi","ho"]'::jsonb) AS length
</code></pre>
When I run that query, I would expect to be returned a value of <code>2</code>, but instead am encountering the error: <code>ERROR: function json_array_length(jsonb) does not exist</code>
Am I missing something very obvious in the documentation? It specifically states you may call <code>JSON_ARRAY_LENGTH</code> passing either a <code>json</code> or <code>jsonb</code> data-type. I'm explicitly casting to <code>jsonb</code> so I'm at a bit of a loss.
Has anyone else encountered this problem, or would someone point out what I'm doing wrong here?
UPDATE: I Mis-Read The Documentation
I should have been calling <code>JSONB_ARRAY_LENGTH</code>, not <code>JSON_ARRAY_LENGTH</code>. Notice the "B" after "JSON". Thanks guys.

I would like to store an array of floating point values with Ecto using Postgres.
I&#39;m using Ecto with the Phoenix Framework and Elixir.

How would I define my model and migration for this?

I haven&#39;t tried much, except searching the web, which didn&#39;t find anything useful :-(

I did try defining a model with a schema like this:

      schema &quot;my_model&quot; do
        field :my_array, :array
    
        timestamps
      end

which gave an error &quot;invalid or unknown type :array for field :my_array&quot;


How to store array with Ecto using Postgres

I am coming from MySQL, and in MySQL you can use AUTOINCREMENT for a row&#39;s unique id as the primary key.

I find that there is no AUTOINCREMENT in Postgresql, only SEQUENCE or UUID.I have read somewhere that we can use UUID as the primary key of a table. This has the added advantage of masking other user&#39;s id (as I want to build APIs that take the ID in as a parameter). Which should I use for Postgresql?

UUID or SEQUENCE for primary key?

I am using docker-compose to deploy a multicontainer python Flask web application. I&#39;m having difficulty understanding how to create tables in the postgresql database during the build so I don&#39;t have to add them manually with psql. 

My docker-compose.yml file is:

    web:
      restart: always
      build: ./web
      expose:
        - &quot;8000&quot;
      links:
        - postgres:postgres
      volumes:
        - /usr/src/flask-app/static
      env_file: .env
      command: /usr/local/bin/gunicorn -w 2 -b :8000 app:app
    
    nginx:
      restart: always
      build: ./nginx/
      ports:
        - &quot;80:80&quot;
      volumes:
        - /www/static
      volumes_from:
        - web
      links:
        - web:web
    
    data:
      restart: always
      image: postgres:latest
      volumes:
        - /var/lib/postgresql
      command: &quot;true&quot;
    
    postgres:
      restart: always
      image: postgres:latest
      volumes_from:
        - data
      ports:
        - &quot;5432:5432&quot;

I dont want to have to enter psql in order to type in:

    CREATE DATABASE my_database;
    CREATE USER this_user WITH PASSWORD &#39;password&#39;;
    GRANT ALL PRIVILEGES ON DATABASE &quot;my_database&quot; to this_user;
    \i create_tables.sql

I would appreciate guidance on how to create the tables.

Using docker-compose to create tables in postgresql database

I&#39;m building an app running on NodeJS using postgresql.
I&#39;m using SequelizeJS as ORM.
To avoid using real postgres daemon and having nodejs on my own device, i&#39;m using containers with docker-compose.

when I run ```docker-compose up```
it starts the pg database

    database system is ready to accept connections

and the nodejs server.
but the server can&#39;t connect to database.

    Error: connect ECONNREFUSED 127.0.01:5432

If I try to run the server without using containers (with real nodejs and postgresd on my machine) it works.

But I want it to work correctly with containers. I don&#39;t understand what i&#39;m doing wrong.

here is the `docker-compose.yml` file

    web:
      image: node
      command: npm start
      ports:
        - &quot;8000:4242&quot;
      links:
        - db
      working_dir: /src
      environment:
        SEQ_DB: mydatabase
        SEQ_USER: username
        SEQ_PW: pgpassword
        PORT: 4242
        DATABASE_URL: postgres://username:pgpassword@127.0.0.1:5432/mydatabase
      volumes:
        - ./:/src
    db:
      image: postgres
      ports:
      - &quot;5432:5432&quot;
      environment:
        POSTGRES_USER: username
        POSTGRES_PASSWORD: pgpassword

Could someone help me please?

(someone who likes docker :) )


ECONNREFUSED for Postgres on nodeJS with dockers

Could anybody tell me more about difference between physical replication and logical replication in PostgreSQL?

Difference between Stream Replication and logical replication

I have DB table with jsonb column.

    number  | data
        1   | {&quot;name&quot;: &quot;firstName&quot;, &quot;city&quot;: &quot;toronto&quot;, &quot;province&quot;: &quot;ON&quot;}

I need a way to update data column.
So my output should look like:

    {&quot;name&quot;: &quot;firstName&quot;, &quot;city&quot;: &quot;ottawa&quot;, &quot;province&quot;: &quot;ON&quot;, &quot;phone&quot;: &quot;phonenum&quot;, &quot;prefix&quot;: &quot;prefixedName&quot;}

Is it possible with json_set?
I have added query like:

    update table_name set data = jsonb_set(data, &#39;{city}&#39;, &#39;&quot;ottawa&quot;&#39;) where number = 1;

However, I need a way to add new key-value if it does not exists and update key value if it exists. Is it possible to achieve this in single query?

postgres jsonb_set multiple keys update

What&#39;s difference between JSON and JSONB data type in PosgresSQL?

1. When should be used specific one? 
2. What&#39;s benefits or disadvantages with respect to other?

Difference between JSON and JSONB in Postgres

[*For versions less than 9.5 see this question*](https://stackoverflow.com/q/30707482/124486)
----

I have created a table in PostgreSQL using this:

    CREATE TEMP TABLE jsontesting
    AS
      SELECT id, jsondata::jsonb FROM ( VALUES
        (1, &#39;[&quot;abra&quot;,&quot;value&quot;,&quot;mango&quot;, &quot;apple&quot;, &quot;sample&quot;]&#39;),
        (2, &#39;[&quot;japan&quot;,&quot;china&quot;,&quot;india&quot;, &quot;russia&quot;, &quot;australia&quot;]&#39;),
        (3, &#39;[&quot;must&quot;, &quot;match&quot;]&#39;),
        (4, &#39;[&quot;abra&quot;,&quot;value&quot;,&quot;true&quot;, &quot;apple&quot;, &quot;sample&quot;]&#39;),
        (5, &#39;[&quot;abra&quot;,&quot;false&quot;,&quot;mango&quot;, &quot;apple&quot;, &quot;sample&quot;]&#39;),
        (6, &#39;[&quot;string&quot;,&quot;value&quot;,&quot;mango&quot;, &quot;apple&quot;, &quot;sample&quot;]&#39;),
        (7, &#39;[&quot;must&quot;, &quot;watch&quot;]&#39;)
      ) AS t(id,jsondata);

Now what I wanted was to 

  * **add** Something like *append_to_json_array* takes in the actual jsondata which is a json-array and the newString which I have to add to that jsondata array and this function should return the updated json-array. 

        UPDATE jsontesting
        SET jsondata=append_to_json_array(jsondata, &#39;newString&#39;)
        WHERE id = 7;

  * **remove** a value from the json data array, one function for removing the value.

I tried to search documentation of PostgreSQL but found nothing there.

Appending (pushing) and removing from a JSON array in PostgreSQL 9.5+

I have the following table called module_data.  Currently it has three rows of entries:

					id                               data
	0ab5203b-9157-4934-8aba-1512afb0abd0 {&quot;title&quot;:&quot;Board of Supervisors Meeting&quot;,&quot;id&quot;:&quot;1i3Ytw1mw98&quot;}
	7ee33a18-63da-4432-8967-bde5a44347a0 {&quot;title&quot;:&quot;Board of Supervisors Meeting&quot;,&quot;id&quot;:&quot;4-dNAg2mn6o&quot;}
	8d71ca35-74eb-4751-b635-114bf04843f1 {&quot;title&quot;:&quot;COPD 101&quot;, &quot;id&quot;:&quot;l9O0jCR-sxg&quot;}

Column data&#39;s datatype is `jsonb`.  I&#39;m trying to query it using `like` operator. Something like the following:

    SELECT * FROM module_data WHERE title LIKE &#39;%Board%&#39;;

I&#39;ve been looking at the `jsonb` support and there doesn&#39;t seem to be a `like` operator. If anyone has any advice.

Content Type	Original Author	Original Content on Stackoverflow
Question	Joshua Burns	View Question on Stackoverflow
Solution 1 - Postgresql	Jorge André Pereira	View Answer on Stackoverflow

Calculate JSONB Array Length Using PostgreSQL 9.4

Postgresql Problem Overview

Postgresql Solutions

Solution 1 - Postgresql

Cannot find pg_hba.conf and postgreql.conf file on OS X?

Why is the time complexity of python's list.append() method O(1)?

Attributions