The standard difference operator (-) returns day-based results for both date, timestamp and timestamp with time zone (the former returns days as int, the latter two return day-based intervals): From the day-based intervals you can extract days with the extract() function: select current_date - '2017-01-01', extract(day from now()::timestamp - '2017-01-01 00:00:00'), extract(day from now() - '2017-01-01 00:00:00Z');

select count(*) filter (where year <= 1945) as pre1945, count(*) filter (where year between 1946 and 1964) as period2, count(*) filter (where year between 1965 and 1974) as period3, ... from ... where ...;

where("period @> date ? ", shares_at)

split_part(email, '@', 2) AS domain

Writing to the database may fail (e.g. it will not respond). When that happens, the process handling outbox pattern will try to resend the event after some time and try to do it until the message is correctly marked as sent in the database.

But there is no absolute reason to use JOIN LATERAL (...) ON TRUE because you could just write it as a CROSS JOIN LATERAL instead

If you want to return a NULL-extended row in the case where the lateral join returns no rows, then you would use LEFT JOIN LATERAL (...) ON TRUE. Any condition other than TRUE is not necessary, as you could have just wrote it into the subquery itself

rank() OVER (ORDER BY

typical use would be to reference a json or jsonb column laterally from another table in the query's FROM clause.

If you're already using PostgreSQL anyway, just use citext as the type of the email_address column.

Most applications do not need to process massive amounts of data. This has led to a resurgence in data management systems with traditional architectures; SQLite, Postgres, MySQL are all growing strongly, while “NoSQL” and even “NewSQL” systems are stagnating.

The array subscript numbers are written within square brackets. By default PostgreSQL uses a one-based numbering convention for arrays, that is, an array of n elements starts with array[1] and ends with array[n].

create_table "project" do |t| t.bigint "employee_ids", array: true t.string "title" end

class Project< ApplicationRecord belongs_to_many :employees end

interval values can be written using the following verbose syntax: [@] quantity unit [quantity unit...] [direction] where quantity is a number (possibly signed); unit is microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium, or abbreviations or plurals of these units

daterange — Range of date, datemultirange — corresponding Multirange

add_column :videos, :tag_ids, :bigint, array: true Tag.has_many :videos, array: true

Post.in_order_of(:type, %w[Draft Published Archived]).order(:created_at).pluck(:name) which generates SELECT posts.name FROM posts ORDER BY CASE posts.type WHEN 'Draft' THEN 1 WHEN 'Published' THEN 2 WHEN 'Archived' THEN 3 ELSE 4 END ASC, posts.created_at ASC

scale: 3 means 3 of those digits come after the decimal point (surely it should be the other way round, scale for how big the number is and precision for its decimal places?):

Python is known for using more memory than more optimized languages and, in this case, it uses 7 times more than PostgresML.

PostgresML outperforms traditional Python microservices by a factor of 8 in local tests and by a factor of 40 on AWS EC2.

You can unknowingly be sending your critical database traffic in the clear because your client uses a default of allow or disable while the server you’re connecting to does, in fact, support SSL.

You can unknowingly be sending your critical database traffic in the clear because your client uses a default of prefer, allow or disable and the server you’re connecting to does not support SSL.

What Should I Do?

Many popular SQL clients do not use SSL by default. If you aren’t deliberate about choosing encryption, the connection will be unencrypted.

SSL is disabled by default in jdbc, npgsql, node-postgres, and pgx.

There are a lot of PostgreSQL servers connected to the Internet: we searched shodan.io and obtained a sample of more than 820,000 PostgreSQL servers connected to the Internet between September 1 and September 29. Only 36% of the servers examined had SSL certificates. More than 523,000 PostgreSQL servers listening on the Internet did not use SSL (64%)

At most 15% of the approximately 820,000 PostgreSQL servers listening on the Internet require encryption. In fact, only 36% even support encryption. This puts PostgreSQL servers well behind the rest of the Internet in terms of security. In comparison, according to Google, over 96% of page loads in Chrome on a Mac are encrypted. The top 100 websites support encryption, and 97 of those default to encryption.

Otherwise behaves according to the value of null_value_treatment which must be one of 'raise_exception', 'use_json_null', 'delete_key', or 'return_target'. The default is 'use_json_null'.

How does PostgreSQL access a column? It will fetch the row and then dissect this tuple to calculate the position of the desired column inside the row. So if we want to access column #1000 it means that we have to figure out how long those first 999 columns before our chosen one really are. This can be quite complex. For integer we simply have to add 4, but in case of varchar, the operation turns into something really expensive.

alter database mydb set search_path = "$user", public, postgis

Check both execution plans (using explain (analyze, verbose) and you'll see

Your app is specific to Postgres and proud of it. You use the mature declarative data validation that only a real database can provide.

When we insert data into the comments table, we need it to route to be inserted into a partition instead of the actual table. For that, we can create a trigger: add_partition_trigger :comments, :comments_by_year, [ { if: 'NEW.year = 2016', insert: :comments_2016 }, { elsif: 'NEW.year = 2017', insert: :comments_2017 }, { else: "RAISE EXECEPTION 'comments_by_year recieived an unexpected value: %', NEW.year;" } ]

ARRAY( SELECT DISTINCT ... FROM unnest(arr) )

And that can all can be written with CROSS JOIN LATERAL which is much cleaner, SELECT ARRAY( SELECT DISTINCT e FROM ( VALUES ('foo', 'bar', 'foo', 'baz' ) ) AS t(a,b,c,d) CROSS JOIN LATERAL unnest(ARRAY[a,b,c,d]) AS a(e) -- ORDER BY e; -- if you want it sorted );

You can use to_char to convert the timestamp using appropriate format mask: COALESCE(to_char(timestamp_type, 'YYYY-MM-DD'), varchar_col)

.pgpass does not define a default database. It only provides the passwords for a combination of hostname, database and username.

The most important part of this query is the with block. It's a powerful resource, but for this example, you can think of it as a "way to store a variable" that is the path of the contact you need to update, which will be dynamic depending on the record.

It just builds the path as '{1, value}', but we need to convert to text[] because that’s the type expected on the jsonb_path function.

For user-contributed data that's freeform and unstructured, use jsonb. It should perform as well as hstore, but it's more flexible and easier to work with.

SET object = object - 'b' || '{"a":1,"d":4}';

(The null result should not be confused with a SQL NULL; see the examples.)

A LATERAL item can appear at top level in the FROM list, or within a JOIN tree. In the latter case it can also refer to any items that are on the left-hand side of a JOIN that it is on the right-hand side of.

SELECT base.nr, multiples.multiple FROM (SELECT generate_series(1,10) AS nr) base, LATERAL ( SELECT multiples.multiple FROM ( SELECT generate_series(1,10) AS b_nr, base.nr * 2 AS multiple ) multiples WHERE multiples.b_nr = base.nr ) multiples;

SELECT s.id, s1.percent_water , s1.percent_water * 100 AS percent_water_100 FROM samples s , LATERAL (SELECT s.wet_weight / NULLIF(s.dry_weight - 1, 0) AS percent_water) s1;

You can use jsonb_agg and jsonb_array_elements_text to flatten or unnest an array of arrays in PostgreSQL: SELECT jsonb_array_elements_text(jsonb_agg(array_of_arrays)) FROM x;

jsonb_pretty

That's going to be extremely ugly. Nothing about this makes sense. Your JSON schema should just have one object that has {"is_enabled":true}, or something like this {"name":"change","is_enable":true}.

unnest ( anyarray ) → setof anyelement Expands an array into a set of rows. The array's elements are read out in storage order.

And this has some immediate benefits: more efficiency, significantly faster to process, supports indexing (which can be a significant advantage, as we'll see later), simpler schema designs (replacing entity-attribute-value (EAV) tables with jsonb columns, which can be queried, indexed and joined, allowing for performance improvements up until 1000X!)

Besides efficiency, there are extra ways in which you can benefit from storing JSON in binary form. One such enhancement is the GIN (Generalized Inverted Index) indexes and a new brand of operators that come with them.

SELECT jsonb_array_elements_text(data->'genres') AS genre FROM books WHERE book_id = 1; That will expand the JSON array into a column:

This is an important one, as it will enable us to use the aggregate functions that we are familiar when dealing with relational databases, but in the otherwise counter-intuitive environment of JSON data.

As stated in the title, I am in a situation where I need to return a count of occurrences within an array, that is within a jsonb column.

SELECT DISTINCT ON (1) t.id, t.name, d.last FROM tbl t LEFT JOIN LATERAL json_array_elements_text(data) WITH ORDINALITY d(last, rn) ON d.last <> t.name ORDER BY d.rn DESC;

Unnest in a LEFT JOIN LATERAL (clean and standard-conforming)

jsonb_each_text

To find the problematic rows you can use a query like SELECT id FROM t WHERE jsonb_typeof(data_col) <> 'object';

select t.* from my_table t, jsonb_each(my_col) as value1(key1, value1), jsonb_each(value1) as value2(key2, value2) where jsonb_typeof(my_col) = 'object' and jsonb_typeof(value1) = 'object' and value2->>'Param3' = '6';

This means that you have a json structure different from that described in your question. You cannot use generic jsonb functions when the json structures differ in each row.

json_array_elements_text ( json ) → setof text jsonb_array_elements_text ( jsonb ) → setof text Expands the top-level JSON array into a set of text values. select * from json_array_elements_text('["foo", "bar"]') → value ----------- foo bar

Use the -> operator instead of ->> in the reference to children. The way you have it, you'd first cast json / jsonb to text and then back to json.

The clean way to call a set-returning function is LEFT [OUTER] JOIN LATERAL. This includes rows without children. To exclude those, change to a [INNER] JOIN LATERAL

FROM test x1 LEFT JOIN test x2 ON x1.id = (x2.data->>'parent')::INT;

JOIN LATERAL

The age_in_years is calculated for every record of the blog table. So, it works like a correlated subquery, but the subquery records are joined with the primary table and, for this reason, we can reference the columns produced by the subquery.

jsonb_array_elements('{"test": ["a1", ["b1", "b2"]]}'::jsonb->'test') as json

jsonb_typeof(json)

INSERT INTO accounts (id, token, affiliate_code) VALUES (value1, value2, value3) ON CONFLICT (id) DO UPDATE SET token = value2,

You can use the pg_typeof() function, which also works well for arbitrary values. SELECT pg_typeof("stu_id"), pg_typeof(100)

SELECT * FROM departments AS d, LATERAL (SELECT * FROM employees AS e WHERE e.department_ID = d.department_ID)

In a FROM clause, the LATERAL keyword allows an inline view to reference columns from a table expression that precedes that inline view.

Subqueries appearing in FROM can be preceded by the key word LATERAL. This allows them to reference columns provided by preceding FROM items. (Without LATERAL, each subquery is evaluated independently and so cannot cross-reference any other FROM item.) TL;DR - LATERAL allows subqueries to reference earlier tables.

SELECT * FROM ( -- build virtual table of all hours between -- a date range SELECT start_ts, start_ts + interval '1 hour' AS end_ts FROM generate_series( '2017-03-01'::date, '2017-03-03'::timestamp - interval '1 hour', interval '1 hour' ) AS t(start_ts) ) AS cal LEFT JOIN ( -- build virtual table of uptimes SELECT * FROM ( VALUES ('2017-03-01 01:15:00-06'::timestamp, '2017-03-01 02:15:00-06'::timestamp), ('2017-03-01 08:00:00-06', '2017-03-01 20:00:00-06'), ('2017-03-02 19:00:00-06', null) ) AS t(start_ts, end_ts) ) AS uptime ON cal.end_ts > uptime.start_ts AND cal.start_ts <= coalesce(uptime.end_ts, current_timestamp)

FROM generate_series('2017-03-01'::date, '2017-03-03'::timestamp - interval '1 hour', interval '1 hour' ) AS t(start_ts)

In this case it's probably better to have the function return a tabletype, RETURNS table, or define the output with OUT variables. Therecord defining syntax at time of query: SELECT foo() AS (a int, b text);

dlbink is a good example, since thestructure of the input query directly controls the stucture of thereturn type. The sever has no way to deal with that when the query isparsed and planned, so you have to help it out.

>> We have that already, it's named 'json_each_text' > Apparently you haven't looked at json parse/deparse costs ;P Well, a PL function is gonna be none too cheap either. Using something like JSON definitely has lots to recommend it --- eg, it probably won't break when you find out your initial spec for the transport format was too simplistic.

Also, In PostgreSQL, all tables already have a type created by the same name.

You can return the record directly from the UPDATE, which is much faster than calling an additional SELECT statement. Use RETURN QUERY and UPDATE with a RETURNING clause.

I personaly prefer the types approach. it assures that if the function is edited, all the queries will return correct results.

scope :with_spec_options, ->(spec_options) { where("specs->'spec_option' @> ?", spec_options.to_json) }

where("specs->'spec_option' ?| array[:options]", options: spec_options)

-- The array on the right side is not considered contained within the -- array on the left, even though a similar array is nested within it: SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb; -- yields false -- But with a layer of nesting, it is contained: SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;

PostgreSQL Window Functions

it supports dynamic loading and catalog-driven operations to let users customize its data types, functions, and more.

If anyone else wonders How to just get data type of a varible (not column) you can use the pg_typeof(any) function.

In PostrgreSQL 8.4 and up you can use: select array_agg(x) from (select unnest(ARRAY[1,5,3,7,2]) AS x order by x) as _; But it will not be very fast.

The best way to sort an array of integers is without a doubt to use the intarray extension, which will do it much, much, much faster than any SQL formulation: CREATE EXTENSION intarray; SELECT sort( ARRAY[4,3,2,1] );

It is also possible to construct an array from the results of a subquery. In this form, the array constructor is written with the key word ARRAY followed by a parenthesized (not bracketed) subquery.

An array constructor is an expression that builds an array value using values for its member elements. A simple array constructor consists of the key word ARRAY, a left square bracket [, a list of expressions (separated by commas) for the array element values, and finally a right square bracket ].

Sure, with json_object_keys(). This returns a set - unlike the JavaScript function Object.keys(obj) you are referring to, which returns an array. Feed the set to an ARRAY constructor to transform it: SELECT id, ARRAY(SELECT json_object_keys(obj)) AS keys FROM tbl_items;

A more verbose form would be to spell out a LATERAL join instead of the correlated subquery: SELECT t.id, k.keys FROM tbl_items t LEFT JOIN LATERAL (SELECT ARRAY(SELECT * FROM json_object_keys(t.obj)) AS keys) k ON true;

If you have variable number of ids (or you have a lot of them), you can use json[b]_array_elements() to extract each element of the array, build up an id list and then query it with the any-containment operator ?|: select * from jsonbtest where to_json(array(select jsonb_array_elements(data) ->> 'id'))::jsonb ?| array['1884595530', '791712670'];

Which database technology to choose

In most cases JSONB is likely what you want when looking for a NoSQL, schema-less, datatype

At this point it’s probably worth replacing hstore use with jsonb in all new applications.

the request to find people with matching phone numbers could be turned into a query like

If you're choosing between json/jsonb/hstore, generally choose jsonb unless you have a reason not to

Relational databases are designed around joins, and optimized to do them well. Unless you have a good reason not to use a normalized design, use a normalised design. jsonb and things like hstore are good for when you can't use a normalized data model, such as when the data model changes rapidly and is user defined. If you can model it relationally, model it relationally. If you can't, consider json etc.

Joins are not expensive. Who said it to you? As basically the whole concept of relational databases revolve around joins (from a practical point of view), these product are very good at joining. The normal way of thinking is starting with properly normalized structures and going into fancy denormalizations and similar stuff when the performance really needs it on the reading side. JSON(B) and hstore (and EAV) are good for data with unknown structure.

I faced similar issues a while ago and created this timestamp9 extension for Postgres.

Event.joins(:packages).having('array_agg(packages.type) @> array[?]', packages).group(:id)

Which to use? ANY is a later, more versatile addition, it can be combined with any binary operator returning a boolean value. IN burns down to a special case of ANY. In fact, its second form is rewritten internally: IN is rewritten with = ANY NOT IN is rewritten with <> ALL