Hot answers tagged

8

1. Provided that sku and item_number will always hold unique values I consider that you have come to the answer yourself by means of the identification of item_number as an optional attribute (or column at the implementation phase), i.e., determining that it does not apply to all the product occurrences (rows). Therefore, from a logical point of view, it ...


7

to_timestamp() expects the parameter to be given in seconds. Your value is in miliseconds. Just divide it by 1000: SELECT to_timestamp(1462975819.250); gives 2016-05-11 16:10:19.25+02


5

If the account id is always at the third position use split_part() select * from gorfs.seg WHERE split_part(full_path, '/', 4) = '4865' Note that due to the leading / the actual index is 4, not 3. The following query: with seg (id, full_path) as ( values (1, '/userfiles/account/4556/attachment/1234'), (2, ...


5

Seems like you are storing milliseconds since 1970-01-01 00:00:00 so Adam's answer is right. Another way would be to use interval addition to the 'epoch' timestamp (which is 1970-01-01 00:00:00): select timestamptz 'epoch' + 1462975819250 * interval '1 millisecond' as my_timestamp ; Tested: test=# select timestamptz 'epoch' + 1462975819250 * ...


5

It's way easier in plpgsql: CREATE FUNCTION kraj_pilkarze(p_kraj varchar(30)) RETURNS VOID AS $$ DECLARE _r RECORD; _id_kraj int; _nazwa_zespolu text; BEGIN _id_kraj := id_kraj FROM kraj WHERE nazwa = $1; FOR _r IN SELECT imie, nazwisko, id_zespol, id_kraj FROM pilkarz LOOP IF r.id_kraj = _id_kraj THEN _nazwa_zespolu := nazwa FROM ...


4

This turned out to be a tricky puzzle which took up far more time than I thought it would - I guess for you too? :-). I (finally) managed to solve it thus: First I created (using DDL and DML for those who wish to try this at home) a table called stringtest: CREATE TABLE stringtest (my_string VARCHAR(255)); and populated it as below INSERT INTO ...


4

I don't think that - a default index generation for foreign key columns - would lead to serious problems. It was just a decision taken from the PostgreSQL developers, to leave this choice to each database designer / administrator. We have the choice to either add an index when creating a foreign key or not. If they had taken the opposite decision, then ...


4

as well as inserting into the login_coordinates table I don't see the benefit of redundant storage. Just write to the table login_coordinates. Easy enough to access with only 10 rows per user. Don't update the user row as well. Basically, what @Ziggy already suggested, with more flesh. Based on this table: CREATE TABLE login_coordinates ( ...


4

You want to filter out groups of rows rather than individual rows. That is, you want to keep only the groups that have ue.user_id = 1. Therefore use HAVING, rather than WHERE or ON, to add that condition, because HAVING is used for group filtering: SELECT SUM(owe) FROM ( SELECT (expenses.amount/count(*)) AS owe FROM expenses ...


4

Better data types text is a sub-optimal data type for key columns. It would be more efficient to use integer. Related: Indexes: integer vs string performance if the number of nodes in the index is the same '26c72242-7e3b-4982-92c5-021b622d7ecd' in your example looks like a UUID. If you need to use UUIDs, still don't store them as text. The appropriate ...


4

In case you need assistance in merging all data, you could do it this way: DO $DO$ DECLARE _tab text; _r record; BEGIN FOR _r IN -- refine the following query according to your particular needs, if the following doesn't work well SELECT a.attrelid::regclass::text AS table_name FROM pg_attribute a JOIN pg_class c ON (c.oid = a.attrelid) ...


4

If id is defined as the primary key, you can omit grouping by all the foo columns you want for the output as long as you are grouping by the id. This special case of grouping is in accordance with the current SQL standard and has also been covered in the PostgreSQL manual, starting from version 9.1: When GROUP BY is present, or any aggregate functions ...


4

you can download the PostgreSql backup here.. http://postgresql-backup.com/ this stores the backups on a network folder, FTP server or in the cloud (Google Drive).


4

Your query is quite close. In addition to the max rating100 per group, the derived table should also return the individual rating100 values, so that you can check if the row's rating matches its group's maximum. SELECT COUNT(*) FROM ( SELECT horse_name, race_id, race_result, rating100, max(rating100) OVER (partition by ...


4

Since you need a subquery in either case, I would use a plain aggregate in the subquery (may be cheaper): SELECT count(*) FROM (SELECT race_id, max(rating100) AS rating100 FROM horse_main GROUP BY 1) x JOIN horse_main h USING (race_id, rating100) WHERE h.race_result = 1; If there are many rows per race_id, it will be faster to get ...


4

Basically it's a crosstab query: PostgreSQL Crosstab Query The dynamic result type is a problem, though. ... is it possible to create a dynamic query which will output the right thing regardless of the number of runs? No. Currently (including Postgres 9.6) not possible with a single SELECT statement. Not unless you know the return type at call time ...


4

A couple of things that have caught you out here. In the SQL Server version using the Point, the order of the coordinate is Lat Lon, eg Y X. The OGC Point construct is X Y or Lon Lat. The next is that the PostGIS query you've posted actually returns 8.20039023523232e-005 which while appearing similar to the SQL Server result, is quite a lot smaller. The ...


3

As @NeilMcGuigan suggested, an Exclusion Constraint is the key here. The part I was unfamiliar with was the ability to use the <> operator. As available post Postgres 9.0: CREATE TABLE accrual ( add_date date NOT NULL, user_id integer NOT NULL, rate integer NOT NULL, amount numeric(7,3), EXCLUDE USING gist (date WITH =, ...


3

Try this: -- This will concatenate the values: \set myvariable '/var/lib/pgsql95/' :myvariable '/pg_tblspc' -- This will expand the variable single-quoted: CREATE TABLESPACE aspire_data OWNER aspireapp LOCATION :'myvariable'


3

If you want percentiles per state, then use PARTITION BY state_id in the OVER clause. And that GROUP BY looks spurious at least. I think it needs to be removed if you want percentiles. And group by the PK is a no-operation anyway. SELECT id, population, state_id, ntile(100) OVER (PARTITION BY state_id ORDER BY car20) AS percentile ...


3

Short answer: integer is faster than varchar or text in every aspect. Won't matter much for small tables and / or short keys. The difference grows with the length of the keys and the number of rows. string ... 20 characters long, which in memory is roughly 5x that of the integer (if an integer is 4 bytes, and the strings are pure ASCII at 1 byte per ...


3

Add the second predicate of your query to the partial index as well: WHERE "Post"."createdAt" > '2015-08-19 14:55:50.398' Your timestamp is probably a moving target, but I am going to assume you have lots of old rows that are excluded in most of your queries and only few "younger" rows are of interest. A typical use case. You can cut off old rows in ...


3

That's like asking Microsoft or GNU.org to remove PATH from the command line. Ain't gonna happen. Use fully qualified names or always run ALTER ROLE <your_login_role> SET search_path TO a,b,c; at the beginning of your session. I'm not sure if you can run this with psqlrc but you could try. You can view your search_path with show search_path You ...


3

An index is added in PostgreSQL, too, when a PRIMARY KEY or a UNIQUE constraint is created, as it is clearly stated in the docs. See CREATE TABLE: PostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce uniqueness. Thus, it is not necessary to create an index explicitly for primary key columns. ... ...


3

There are many ways to do this. Many will work both in SQL Server and Postgres, like using the ROW_NUMBER() function: WITH cte AS ( SELECT e.enumber, e.bday, c.empid, c.pay, ROW_NUMBER() OVER (PARTITION BY c.empid ORDER BY c.id DESC) AS rn FROM employee AS e INNER JOIN humanr AS c ON e.empid = ...


2

csd's answer really saved me. One minor issue is that the last "getSubjectX500Principal()" should have been a "getIssuerX500Principal()". I didn't have enough rep to comment, so I ended up writing a complete answer with a more detailed walkthrough for those of us who don't know or have forgotten java. Make sure your server can compile java. Try the command ...


2

What you're asking the DB to do in Query one is: Give me ALL from table A FILTERED Give me ALL from table B FILTERED Give me ALL from table C FILTERED Give me ALL from table D FILTERED And then Union. In the second query you first get all the data, and only after that you do the join and the filter. JOIN and WHERE on a UNION query, which doesn't really ...


2

If your attribute item_number is unique, you can leave it in your original table even in case it can have null values. In fact the PostgreSQL manual says: For the purpose of a unique constraint, null values are not considered equal. So this could be right solution: CREATE TABLE product ( sku text PRIMARY KEY, name text ...


2

Stop postgresql service cp -a source_data_directory destination_data_directory chown -R postgres_user /destination_data_directory export PGDATA=destination_data_directory Changing data directory to destination_data_directory within postgresql.conf pg_ctl start


2

You could just alter the table and convert the column in one step: alter table ogrtable alter column arrcolumn type varchar using (array_to_string(arrcolumn, ', '));



Only top voted, non community-wiki answers of a minimum length are eligible