Hot answers tagged

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


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

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

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

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;"


2

You could implement an ON INSERT trigger and delete, for the same user, any rows with a timestamp older than the oldest (MIN) of a subselect ordered by timestamp DESC limit 10. Edit, using an array in users: An alternative is using an array straight in the users table: Add a type and a new array in users: CREATE TYPE past_logon AS (login_at timestamptz, ...


2

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


2

However, after searching for similar issues, all I can see from other people is this error for 'of relation base', not 'in file'. How is 'in file' different, and what does this mean? Is it more likely that I have a hardware issue? No, it's the same issue. The error message used to refer to the relation in older versions of PostgreSQL, but it was ...


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

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'


2

Join to a subquery that computes numero with the window function row_number(): UPDATE movimientos m SET numero = sub.rn FROM (SELECT id, row_number() OVER (ORDER BY orden, id) AS rn FROM movimientos) sub WHERE m.id = sub.id; Details for UPDATE syntax in the manual. If you have concurrent write access you need to lock the table to avoid race ...


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

No, it is not. Data that is gone is gone. This type of auditing is normally implemented by custom code (triggers writing to an audit table or something like this).


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

Seems like a decent approach. Of course, one should apply some human verification to this before automatically dropping everything that seems unused. For example, it's conceivable that the statistics were recently reset and/or an index is only used for some occasional batch tasks.


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

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

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

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



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