Postgresql – Alter the values in a column

postgresql

I have at hand the following table (for data migration):

recvtimets     |         recvtime         | fiwareservicepath | entityid |   entitytype    |     attrname     |    attrtype     |               attrvalue               | attrmd
---------------+--------------------------+-------------------+----------+-----------------+------------------+-----------------+---------------------------------------+--------
 1559573267088 | 2019-06-03T14:47:47.88Z  | /basic            | 001      | WeatherObserved | location         | StructuredValue | {"coordinates":[-8.613502,41.143994]} | []
 1559573267088 | 2019-06-03T14:47:47.88Z  | /basic            | 001      | WeatherObserved | relativeHumidity | Number          | 59.1                                  | []
 1559573267088 | 2019-06-03T14:47:47.88Z  | /basic            | 001      | WeatherObserved | dateObserved     | Text            | 2015-09-01T10:15:57+01:00             | []
 1559573267088 | 2019-06-03T14:47:47.88Z  | /basic            | 001      | WeatherObserved | illuminance      | None            | null                                  | []
 1559573267088 | 2019-06-03T14:47:47.88Z  | /basic            | 001      | WeatherObserved | temperature      | Number          | 24.9                                  | []
 1559573267088 | 2019-06-03T14:47:47.88Z  | /basic            | 001      | WeatherObserved | name             | Text            | Rua das Flores                        | []
 1559573267782 | 2019-06-03T14:47:47.782Z | /basic            | 001      | WeatherObserved | name             | Text            | Praca do Marques - pole 1             | []
 1559573267782 | 2019-06-03T14:47:47.782Z | /basic            | 001      | WeatherObserved | illuminance      | None            | null                                  | []
 1559573267782 | 2019-06-03T14:47:47.782Z | /basic            | 001      | WeatherObserved | temperature      | Number          | 21.9                                  | []
 1559573267782 | 2019-06-03T14:47:47.782Z | /basic            | 001      | WeatherObserved | dateObserved     | Text            | 2015-09-01T10:16:24+01:00             | []
 1559573267782 | 2019-06-03T14:47:47.782Z | /basic            | 001      | WeatherObserved | location         | StructuredValue | {"coordinates":[-8.603802,41.161671]} | []
 1559573267782 | 2019-06-03T14:47:47.782Z | /basic            | 001      | WeatherObserved | relativeHumidity | Number          | 80.8                                  | []
 1559573267696 | 2019-06-03T14:47:47.696Z | /basic            | 001      | WeatherObserved | name             | Text            | Fundacao de Serralves                 | []
 1559573267696 | 2019-06-03T14:47:47.696Z | /basic            | 001      | WeatherObserved | illuminance      | None            | null                                  | []
 1559573267696 | 2019-06-03T14:47:47.696Z | /basic            | 001      | WeatherObserved | location         | StructuredValue | {"coordinates":[-8.65915,41.1591]}    | []
 1559573267696 | 2019-06-03T14:47:47.696Z | /basic            | 001      | WeatherObserved | relativeHumidity | Number          | 1                                     | []
 1559573267696 | 2019-06-03T14:47:47.696Z | /basic            | 001      | WeatherObserved | dateObserved     | Text            | 2015-09-01T10:16:22+01:00             | []

The first two columns depict the timestamp the application writes to the database (reading from some db). The time for which data was actually recorded however, is the dateObserved attribute in the attrname column and its corresponding value is that shown in attrvalue column.

This means the first 6 rows (having 2019-06-03T14:47:47.88Z in the recvtime column were recorded on 2015-09-01T10:15:57+01:00 (shown on attrvalue column), i.e. dateObserved = 2015-09-01T10:15:57+01:00

The next 6 rows having 2019-06-03T14:47:47.782Z in the recvtime column were recorded on 2015-09-01T10:16:24+01:00, i.e. dateObserved =2015-09-01T10:16:24+01:00

Likewise, the last 6 rows having recvtime as 2019-06-03T14:47:47.696Z were recorded on 2015-09-01T10:16:22+01:00 i.e. dateObserved = 2015-09-01T10:16:22+01:00

The goal is to alter the contents of recvtime column such that these values are replaced by the date recorded as contain in the attrvalue for the respective rows.

Accordingly, the end result becomes as in following table(i.e. the final table to produce this):

recvtimets     |           recvtime         | fiwareservicepath | entityid |   entitytype    |     attrname     |    attrtype     |               attrvalue               | attrmd
---------------+----------------------------+-------------------+----------+-----------------+------------------+-----------------+---------------------------------------+--------
 1559573267088 | 2015-09-01T10:15:57+01:00  | /basic            | 001      | WeatherObserved | location         | StructuredValue | {"coordinates":[-8.613502,41.143994]} | []
 1559573267088 | 2015-09-01T10:15:57+01:00  | /basic            | 001      | WeatherObserved | relativeHumidity | Number          | 59.1                                  | []
 1559573267088 | 2015-09-01T10:15:57+01:00  | /basic            | 001      | WeatherObserved | dateObserved     | Text            | 2015-09-01T10:15:57+01:00             | []
 1559573267088 | 2015-09-01T10:15:57+01:00  | /basic            | 001      | WeatherObserved | illuminance      | None            | null                                  | []
 1559573267088 | 2015-09-01T10:15:57+01:00  | /basic            | 001      | WeatherObserved | temperature      | Number          | 24.9                                  | []
 1559573267088 | 2015-09-01T10:15:57+01:00  | /basic            | 001      | WeatherObserved | name             | Text            | Rua das Flores                        | []
 1559573267782 | 2015-09-01T10:16:24+01:00  | /basic            | 001      | WeatherObserved | name             | Text            | Praca do Marques - pole 1             | []
 1559573267782 | 2015-09-01T10:16:24+01:00  | /basic            | 001      | WeatherObserved | illuminance      | None            | null                                  | []
 1559573267782 | 2015-09-01T10:16:24+01:00  | /basic            | 001      | WeatherObserved | temperature      | Number          | 21.9                                  | []
 1559573267782 | 2015-09-01T10:16:24+01:00  | /basic            | 001      | WeatherObserved | dateObserved     | Text            | 2015-09-01T10:16:24+01:00             | []
 1559573267782 | 2015-09-01T10:16:24+01:00  | /basic            | 001      | WeatherObserved | location         | StructuredValue | {"coordinates":[-8.603802,41.161671]} | []
 1559573267782 | 2015-09-01T10:16:24+01:00  | /basic            | 001      | WeatherObserved | relativeHumidity | Number          | 80.8                                  | []
 1559573267696 | 2015-09-01T10:16:22+01:00  | /basic            | 001      | WeatherObserved | name             | Text            | Fundacao de Serralves                 | []
 1559573267696 | 2015-09-01T10:16:22+01:00  | /basic            | 001      | WeatherObserved | illuminance      | None            | null                                  | []
 1559573267696 | 2015-09-01T10:16:22+01:00  | /basic            | 001      | WeatherObserved | location         | StructuredValue | {"coordinates":[-8.65915,41.1591]}    | []
 1559573267696 | 2015-09-01T10:16:22+01:00  | /basic            | 001      | WeatherObserved | relativeHumidity | Number          | 1                                     | []
 1559573267696 | 2015-09-01T10:16:22+01:00  | /basic            | 001      | WeatherObserved | dateObserved     | Text            | 2015-09-01T10:16:22+01:00             | []

How do I achieve this in SQL?

Best Answer

SELECT t1.recvtimets, 
       t2.attrvalue recvtime,
       t1.fiwareservicepath,
       t1.entityid,
       t1.entitytype,
       t1.attrname,
       t1.attrtype,
       t1.attrvalue,
       t1.attrmd
FROM tablename t1
JOIN tablename t2 ON t1.recvtimets = t2.recvtimets
                 AND t2.attrname = 'dateObserved'
WHERE t1.attrname != 'dateObserved'

Related Solutions

PostgreSQL – Order of Columns in Compound Index and Query Order

As a part of my work I maintain a fairly large PostgreSQL database (around 120gb on disk, several multi-million-row tables) and have collected a few tricks on how to speed up the queries. First some comments on your assumptions:

Yes, order is important, but it's only the first one that is really different, the rest are second class indexes.
I'm not sure it will always use both, my guess is that the query planner will use #1, then do something clever with the rest.
I have no experience with GIST.
Yes, add all indexes first, see what is used the most and what gives the best performance.
I would sugest that you try both and measure what works best. Try rewriting the sql with different subqueries, maybe country and time in one, then join with the intersect-query. I have not noticed any performance problem with IN-clauses, as long as the IN-list is not thousands of elements long. My guess is that a few different queries specifically tuned depending on the input criteria available will give the best results.
I would suggest against making a 4-way index. Try creating one and then check the size, they can get really huge. In my experience, four 1-key indexes have been almost as fast as a single 4-way index. A trick that does work nicely for some specific queries are partial indexes, ie something like this:

CREATE INDEX ON table_x (key1, key2, key3) WHERE some_x_column = 'XXXX';

I have created aliases in my .psqlrc-file with queries to help find what indexes to add or remove. Feel free to have a look at them over at GitHub: .psql

I use the :seq_scans and :bigtables a lot, and then \d table_name to get details about the table. Don't forget to reset the statistics after you've done some changes, select pg_stat_reset();

Postgresql – List distinct column values where those rows share other column values

This query will do. The trick is to use COUNT(DISTINCT city_id):

SQL Fiddle

PostgreSQL 8.3.20 Schema Setup:

CREATE TABLE Table1
    ("id" int, "name" varchar(6), "language" varchar(1), "dialect" varchar(2), "city_id" int)
;

INSERT INTO Table1
    ("id", "name", "language", "dialect", "city_id")
VALUES
    (01, 'London', 'A', 'A1', 1),
    (02, 'London', 'A', 'A2', 1),
    (03, 'London', 'B', 'B1', 2),
    (04, 'London', 'B', 'B2', 3)
;

Query 1:

select t.*, d.dups
FROM table1 t INNER JOIN
(
  select name, language, count(distinct city_id) as dups
  from table1
  group by name, language
  having count(distinct city_id) > 1
) d
ON t.name = d.name and t.language = d.language

Results:

| ID |   NAME | LANGUAGE | DIALECT | CITY_ID | DUPS |
|----|--------|----------|---------|---------|------|
|  3 | London |        B |      B1 |       2 |    2 |
|  4 | London |        B |      B2 |       3 |    2 |

Best Answer

Related Solutions

PostgreSQL – Order of Columns in Compound Index and Query Order

Postgresql – List distinct column values where those rows share other column values

Related Question