Linked Questions

7
votes
2answers
15k views

Unnest multiple arrays into rows

I have been shown a great way to bulkinsert with this example: WITH p AS ( INSERT INTO parent_table (column_1) SELECT $1 RETURNING id) INSERT INTO child_table (parent_table_id, ...
6
votes
1answer
13k views

Is it possible to select distinct values in a json document using PostgreSQL?

I have a column that is using the JSON type. I would like to perform a query to select all distinct records for a particular field in the JSON string: I.e. given these three documents { id: 1, s: ...
8
votes
1answer
13k views

How to remove known elements from a JSON[] array in PostgreSQL?

I'm facing an issue regarding using the JSON data type in PostgreSQL. I try to achieve storing a Java model denormalized in the DB. The model features lists of complex objects. Thus, I decided to ...
5
votes
1answer
8k views

Delete array element by index

Is it possible to delete a Postgres array element by index? (Using Postgres 9.3.) I don't see anything for this in the docs (http://www.postgresql.org/docs/9.3/static/functions-array.html) but ...
3
votes
2answers
6k views

Order of returned record with IN statement postgres

I know that the order of returned records is not guaranteed with the IN statement for Pstgres. For example if I do this: SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588) ...
3
votes
4answers
2k views

Compare words in a string without considering their positions?

In Postgres 9.6 I want to test whether two strings like these are considered the same: 'this is a test number 01', 'number this is 01 a test' So I have created this function: CREATE OR REPLACE ...
3
votes
4answers
384 views

How to remove an element from a col with a CSV of ints in PostgreSQL 9.2?

I have PostgreSQL 9.2 database and a table: id integer, allowed_types character varying(255)` Sample data as below: id allowed_types 1 3,4,5,13,14 How I can remove 4 and 5 from allowed_types ...
3
votes
2answers
4k views

Casting an array of texts to an array of UUIDs

How can I cast an array of texts into an array of UUIDs? I need to do a join between two tables: users and projects. The users table has an array field named project_ids containing the project IDs ...
3
votes
2answers
414 views

Select orphaned rows (in a generic way)

I have a table my_tables which gets referenced as foreign key in several tables. I would like to select all rows of my_table which are not referenced in an other table. AFAIK, it should be possible ...
1
vote
3answers
668 views

How to store short stories for access to individual sentences?

I am building a database for the first time ever (using PostgreSQL), and am very conflicted over the most efficient/logical way of storing a body of text (aka, a story). The conflict stems from the ...
0
votes
1answer
42 views

How to eliminate for loop in a nested aggregate problem

I have a parsing problem that I am solving with a FOR LOOP in plpgsql, basically because I can't figure out how to solve it with subqueries and nested aggregates. My question is can the for loop be ...
0
votes
1answer
26 views

Reusing literal string in different parts of query

I created this PostgreSQL query while troubleshooting a problem that turned out to be due to non-printing characters in my strings: with cte as ( select 'The character between the xes is evil: xx' as ...