Linked Questions

48 votes
2 answers

Replace multiple columns with single JSON column

I am running PostgreSQL 9.3.4. I have a table with 3 columns: id name addr 1 n1 ad1 2 n2 ad2 I need to move the data to a new table with a JSON column like: id data 1 {"name": "n1&...
AliBZ's user avatar
  • 1,759
6 votes
4 answers

Return total number of rows and selected (aggregated) data

I have a function which is selecting some data from a table. I would like to return the selected data and the total number of rows in that table. How can I do that or how can I get the same result in ...
RockNinja's user avatar
  • 673
10 votes
1 answer

Syntax shorthand for updating only changed rows in UPSERT

I have a query which upserts into Postgres when a key conflicts only if there are changes to one of the other columns (to avoid unnecessary inserts and returned rows where nothing was actually changed)...
Caustic's user avatar
  • 203
7 votes
2 answers

Create smaller row from alias and preserve column names

Using Postgres: SELECT users."name" AS "name" , array_to_json(array_agg(sites)) as sites FROM remodel.users AS users JOIN remodel.user_sites AS user_sites ON JOIN ...
Gauss's user avatar
  • 73
2 votes
1 answer

Efficiently return two aggregated arrays from a m:n table

I have a table for a many-to-many relationship over a users table to represent a follow relationship between users: CREATE TABLE users ( id text PRIMARY KEY, username text NOT NULL ); CREATE ...
Russell Ormes's user avatar
2 votes
1 answer

Why does json_agg fail with error "function json_agg(record) does not exist"?

With postgresql version 9.2, I'm trying to run a query that selects certain columns to build a json object. Here's the query, which I derived from this question: select , json_agg((SELECT x ...
fraxture's user avatar
  • 127
0 votes
3 answers

How can I return multiple columns from an array of ids?

I have a two tables like the following: workshops table +----+--------------+------------+ | id | name | option_ids | +----+--------------+------------+ | 1 | Conversation | {2,3} | +----...
Gonzalo Rocha's user avatar
3 votes
2 answers

Filter and append data to row

My query and my data set (copied from sqlfiddle): CREATE TABLE one ( id int4 primary key, p_id int4, k_id int4 ); CREATE TABLE two( id int4 primary key, p_id int4, k_id int4, ...
RockNinja's user avatar
  • 673
0 votes
1 answer

Convert right side of join of many to many into array of objects

I have 2 tables: Table subjects: id, class_id, title, created_at, updated_at Table marks: id, student_id, class_id, mark, created_at, updated_at, subject_id I want to query subjects table and have it ...
Mihael's user avatar
  • 3
0 votes
0 answers

to_json vs ::json in postgres

I was wondering how to address the tsrange problem in postgres, as tsrange('2010-01-01 14:30', '2010-01-01 15:30')::json returns annoying escapes: "[\"2010-01-01 14:30:00\",\"2010-...
arthur's user avatar
  • 868
0 votes
1 answer

Combining table under different alias

I have two tables trips and users. Whenever an user posts a trip, it stores id of the user as foreign key. And while fetching, I am joining those two tables the following way. Query: SELECT title, ...
Zero Live's user avatar
  • 103