Linked Questions
19 questions linked to/from How to preserve the original order of elements in an unnested array?
125
votes
7
answers
403k
views
How to turn JSON array into Postgres array?
I have a column data of type json that holds JSON documents like this:
{
"name": "foo",
"tags": ["foo", "bar"]
}
I would like to turn the nested tags array into a concatenated string ('foo, ...
- 1,443
14
votes
3
answers
42k
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 ...
- 331
10
votes
1
answer
37k
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: ...
10
votes
2
answers
26k
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, ...
user32234
4
votes
3
answers
7k
views
How can you replace the last occurrence of a character in a string (text column)?
Let's say I have a text field with this value in Postgres:
'bar$foo$john$doe$xxx'
I'd like to replace just the last occurrence of the dollar ($) character with another character, for example '-'. ...
- 59
13
votes
2
answers
12k
views
Order of returned rows with IN statement
I know that the order of returned rows is not guaranteed with the IN statement in Postgres. For example if I do this:
SELECT users.id FROM users WHERE users.id IN (13589, 16674, 13588)
I may get this ...
9
votes
1
answer
17k
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 ...
- 193
8
votes
1
answer
22k
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 ...
- 193
4
votes
4
answers
1k
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 ...
- 43
3
votes
2
answers
2k
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,371
3
votes
4
answers
4k
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 ...
- 1,153
2
votes
3
answers
1k
views
PostgreSQL how to add ordinal numbers to rows created by regexp_split_to_table()?
I have two tables. Table logfile contains whole text file in one attribute; table rawtext should contain one line of text from the text file in each row:
create table LOGFILE ( LOGF_PK serial primary ...
4
votes
1
answer
3k
views
How to select sub-object with given keys from JSONB?
How to extract / get / select a "sub object" from a json(b) object in Postgres?
There seems to be a lot of info that get me almost all the way there but not quite. Lots of stuff about ...
- 143
3
votes
1
answer
3k
views
How can I match the items in an array column, with a different table in Postgres?
I have a table in a Postgres 12 database with that has an array column with a bunch of dog breed guids. I want to lookup these values and basically return the lookup values instead of the guids.
...
- 231
2
votes
1
answer
2k
views
Dynamically update JSONB rows - POSTGRESQL
I am currently trying to update some JSONB fields that we have on our database, we are moving nodes from the JSON a level up for an specific field.
The node we are trying to move, however, is not ...
