Create a PostgreSQL table from a SAS table

I am creating a PostgreSQL table from a sas table and the sas table is showing below.type of column woe is numeric and other column are type of char.+----------+--------------+------+-------+-----+-----+| variable | new_variable | type | start | end | woe |+----------+--------------+------+-------+-----+-----+| A | mi_A | char | 1 | | 1.3 || A | mi_A | char | 0 | | 0.6 || B | mi_B | char | 1 | | 5.4 || B | mi_B | char | 0 | | 0.1 || gnd_cd | gnd_cd ...Read more

Create a PostgreSQL table from a SAS table

I am creating a PostgreSQL table from a sas table and the sas table is showing below.type of column woe is numeric and other column are type of char.+----------+--------------+------+-------+-----+-----+| variable | new_variable | type | start | end | woe |+----------+--------------+------+-------+-----+-----+| A | mi_A | char | 1 | | 1.3 || A | mi_A | char | 0 | | 0.6 || B | mi_B | char | 1 | | 5.4 || B | mi_B | char | 0 | | 0.1 || gnd_cd | gnd_cd ...Read more

postgresql - casting integer field to a timestamp ... and then to a date

BackgroundI need to write a SQL query that will sort records by date and then export the records to csv files, by month. So for example, consider the following query and results: select id, addy, abstime(start_ts) from wwdr where id > 3000;output:1361571 | :10011@mydomain| 2016-08-12 17:59:36+001361578 | :19448@mydomain| 2016-08-12 17:59:16+001361579 | :10307@mydomain| 2016-08-12 18:00:07+001361581 | :10702@mydomain| 2016-08-12 17:54:41+001361582 | :12264@mydomain| 2016-08-12 18:00:13+001361588 | :10561@mydomain| 2016-09-12 18:00:20+00136158...Read more

postgresql - Creating columns from results in Hive

In hive I'm trying to take the data in a particular column and then split that up into new columns. Then I want to move a column that contains some values and move those into those new columns. If this isn't clear here's what I meanTable Name: atableBefore:Name Date Label Valuename1 date1 label1 1name2 date2 label2 2name3 date3 label3 3name4 date4 label4 4name5 date5 label5 5Af...Read more

postgresql - Postgres crosstab (text, text) cascading within group

Table schemaDROP TABLE bla;CREATE TABLE bla (id INTEGER, city INTEGER, year_ INTEGER, month_ INTEGER, val INTEGER);DataINSERT INTO bla VALUES(1, 1, 2017, 1, 10);INSERT INTO bla VALUES(2, 1, 2017, 2, 20);INSERT INTO bla VALUES(3, 1, 2017, 1, 15);INSERT INTO bla VALUES(4, 1, 2017, 2, 5);INSERT INTO bla VALUES(5, 2, 2017, 1, 10);INSERT INTO bla VALUES(6, 2, 2017, 2, 15);INSERT INTO bla VALUES(7, 1, 2018, 1, 10);INSERT INTO bla VALUES(8, 1, 2018, 1, 10);I am trying to aggregate them and put into a pivot table format, so that for each (city, year_) ...Read more

pivot - Postgresql Crosstab Not Working As Expected

I have a temp table with event counts organized by id and type. Their are 6 possible events. Here are results from id 234.SELECT * FROM _tmp_grouped WHERE id = 234;I want to crosstab this so that the type becomes the column headers.SELECT * FROM crosstab('SELECT * FROM _tmp_grouped WHERE id = 234') AS (id INT, prompt_shown_last BIGINT, prompt_shown BIGINT, prompt_dismissed_last BIGINT, prompt_dismissed BIGINT, prompt_allowed_last BIGINT, prompt_allowed BIGINT);However, once I add additional ids, the crosstab results get funky.If I do no...Read more

postgresql - Where the NOTICE or error messages?

No NOTICE neither error messages atCREATE or replace FUNCTION copy_to_csv( fname text, query text, header boolean DEFAULT true, quotedfields text[] DEFAULT NULL, usedate boolean DEFAULT true) RETURNS text AS $f$DECLARE aux text :='';BEGIN RAISE NOTICE 'HELLO!!!!!'; IF p_quotedfields IS NOT NULL THEN aux := ', FORCE_QUOTE('|| array_to_string(quote_ident(quotedfields),',') ||')'; END IF; aux := format( 'COPY (%L) TO (%L) WITH (FORMAT CSV, HEADER %L%s)', query, CASE WHEN usedate THEN fname|| now()::date::text ELSE fname END |...Read more

postgresql - Group by trunced date in JPA

I need help!I need to build specification for SELECT date_trunc('day', start_time)FROM ExampleGROUP BY date_trunc('day', start_time)(PostgreSQL)I have code:CriteriaBuilder cb = entityManager.getCriteriaBuilder();CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);Root<Example> root = query.from(Example.class);Expression<Date> exp = cb.function("date_trunc", Date.class , cb.literal("day"), root.get("startTime"));List<Object[]> result = entityManager.createQuery(query.select(exp).groupBy(exp)).getResultList();Bu...Read more

Postgresql group by recurring items

I'm using postgresql to store historical data coming from an RTLS platform.Position data is not collected continuosly.The historical_movements is implemented as a single table as follow (it is a simplified table but enough to present the use case):User Area EnterTime ExitTimeJohn room1 2018-01-01 10:00:00 2018-01-01 10:00:05Doe room1 2018-01-01 10:00:00 2018-01-01 10:10:00John room1 2018-01-01 10:05:00 2018-01-01 10:10:00Doe room1 2018-01-01 10:20:00 2018-01-01 10:30:00John room2 2018-01-01 11:...Read more

group by - Feedback with a query I wrote [PostgreSQL]

So, I have written a query to extract the average unemployment rate in a given period.I would appreciate a feedback on the query and how far can I go to optimize something like this (or if it is worth optimizing at all)PS: The result of the query is correct. I am more interested in the perspective of the community at large when it comes to doing something as basic as this. What pitfalls do you see and recommendations on how one can avoid them?select extract(year from period) as year, avg(data) as yearly_unemp_rate from fred.unemployment_rate_st...Read more