Hot answers tagged

8

1. Assuming that sku and item_number will always hold unique values I consider that you have come up to the answer by yourself, since an item_number is an optional attribute (or column at the implementation phase), i.e., it does not apply to all the product occurrences, therefore, from a logical point of view, it decidedly cannot (should not) be declared as ...


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, ...


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 ...


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

If it's one time setup, I think dump would be easiest. But if You need your update be available on customer, then I think streaming replication would be easier, set up once and no need to update in the future as the replication will handle the data updates. http://www.postgresql.org/docs/current/static/high-availability.html


2

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 =, ...


2

Here's a shell script that can do what you want: SCHEMA="myschema" DB="mydb" psql -Atc "select tablename from pg_tables where schemaname='$SCHEMA'" $DB |\ while read TBL; do psql -c "COPY $SCHEMA.$TBL TO STDOUT WITH CSV" $DB > $TBL.csv done Make sure you set the DB and SCHEMA variables to your particular database and schema. The wrapping psql ...


2

PostgreSQL can only make use of a function index when the comparison is against the results of the function, e.g.: AND (s.full_path)::text ~ '/userfiles/account/[0-9]+/[a-z]+/[0-9]+' Alternatively, create the index without typecasting: CREATE INDEX CONCURRENTLY ix_full_path ON gorfs.inode_segments USING btree (full_path); Note also that the character / ...


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, ', '));


2

There are many ways for doing this. When measurements can be trusted to be sequential (time), then one can use a simple self left join, which should perform very fast: SELECT * FROM data d LEFT JOIN data prev ON (prev.time = d.time - 1) WHERE (d.foo, d.bar) IS DISTINCT FROM (prev.foo, prev.bar) Another one is using window functions (PG version >= 8.4): ...


2

If using psql via bash command line, you can simply do: psql -c "COPY (select query) To '/tmp/test_`date +"%Y-%m-%d_%H%M%S"`.csv' DELIMITER ',' CSV HEADER;"


1

Your strategy for getting information from full_path can be useful for a one-off, but for ongoing queries to it, especially over millions of records and expecting quick results, it is far from optimal. Considering the sheer size of your tables, you'll probably benefit from datawarehousing. If your tables are constantly updated, you'll need a trigger to keep ...


1

The id field in your ny_stations table does not seem to be defined as a serial, so it is expected that pg_get_serial_sequence will return nothing. The duplicate you get relates to one of the records in your SELECT DISTINCT ... FROM ny_raw_trips ... is returning two rows with the same id: SELECT start_station_id, COUNT(*) FROM ( SELECT DISTINCT ...


1

In Oracle a schema and a user is the same thing. In Postgres it isn't. So there is no direct "mapping" on what you did in Oracle in "Postgres land". A table is always owned by the user who created it initially there is no way to change that. If you do not want to give the user ink the privilege to create tables, the "Postgres" way would be to give that ...


1

pg_indexes.tablename only contains the table name, not the schema name. The schema name is available in the column schemaname. So you need to use select * from pg_indexes where tablename ='asignacion' and schemaname = 'distribucion';


1

PostgreSQL can be built to either use the operating system's time zone database or its own. Check with the command pg_config --configure whether the option --with-system-tzdata was used. If not, it is using its own time zone data, and that hasn't been updated yet. If so, to fix, either wait for the next releases, or install the packages the way you have ...


1

Perhaps this will help. If you'll rely on the account_id from full_path often, then you'll benefit from a function and a functional index for it: CREATE OR REPLACE FUNCTION gorfs.f_get_account_from_full_path(p_full_path text) RETURNS int AS $body$ SELECT (regexp_matches($1, '^/userfiles/account/([0-9]+)/[a-z]+/[0-9]+'))[1]::int $body$ LANGUAGE SQL ...


1

You have to define the aliases for the columns outside the subqueries: with d as ( select distinct emp.ahrims_id, rank.name as rank from hr_employee as emp, ana_rank as rank, unit_identification_code as uic, record_record as rec where emp.unit_identificatin_code_id = uic.id and rec.hr_employee_id = emp.id and rank.id = ...


1

The results from these views do not overlap and together cover 100% of the table. What keeps you from just querying the underlying table? Should be fastest: SELECT x.* FROM cases x JOIN case_clients cacl ON cacl.case_id = x.main_id WHERE cacl.client_id = 12046 ORDER BY x.sort_nr, x.id;


1

Just add an ON DELETE CASCADE option to your foreign key: ALTER TABLE links_link DROP CONSTRAINT constraint_name, ADD CONSTRAINT constraint_name FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON DELETE CASCADE; Apparently in django the above is translated as: on_delete=models.CASCADE


1

If a milestone can have multiple tasks (which is highly likely), then your statement will only update the taskcompleted for the "last" task processed in the query (the "last" is undefined here though). If you want to check all tasks for a milestone to be completed you need consider all taskcompleted combined with an AND condition. This can be done using ...


1

You can also set it as a default for any user or role (takes effect after a new connection is established): ALTER ROLE <rolename> SET client_encoding = 'UTF8';


1

Streaming replication in PostgreSQL uses the same security mechanisms as a normal frontend-backend connection. So in order to encrypt the traffic, SSL should be used, and access control should be set up. Alternatively, or additionally, consider a VPN of some kind. PostgreSQL instances aren't really meant to be run facing the open internet.


1

To get all members of all roles: SELECT r.rolname as username,r1.rolname as "role" FROM pg_catalog.pg_roles r JOIN pg_catalog.pg_auth_members m ON (m.member = r.oid) JOIN pg_roles r1 ON (m.roleid=r1.oid) WHERE r.rolcanlogin ORDER BY 1; Add r1.rolname='council_stuff' to filter on only that one. Be aware that users ...


1

Yes, you can refer to rows of higher levels in a subquery. But that would not solve your problem since you need to consider the whole table, not just the current row. It's not completely clear what the query is trying to achieve and data types (table definition!) are also missing. Be aware that your invocation of generate_series() returns timestamptz, which ...


1

...or you can simply use parenthesis to isolate the components and cast as usual: select ((regexp_matches(full_path, '\d+'))[1])::int from gorfs.seg where account_id = 4865 The question is: why do you need to extract the account_id from the full_path if you already have it in the where clause?



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