Linked Questions

0 votes
1 answer
156 views

Does a long string with "unique" constaint take a lot of space?

I've created a table "global_settings" for storing different settings of a web applications -- the ones, that I want to be able to edit on html page: global_settings: id primary key, key ...
Camila326's user avatar
2 votes
2 answers
283 views

Any pitfalls/benefits of creating PK with include(..) in PostgreSQL?

Application is aggressively caching data in memory and in order to support consistency (preventing persisting stale data) it is doing something like: -- typical table structure: create table t1 ( ...
Andrey B. Panfilov's user avatar
8 votes
1 answer
2k views

Do datatypes of columns affect query performance?

Let's suppose I have this table: Table "public.orders" Column | Type | Collation | Nullable | Default -----------------+---------------+-----------+----------+----...
tail's user avatar
  • 235
2 votes
1 answer
2k views

Sensible max_length for Postgres db charfields

I am using Django with a Postgres backend. In the Django models, I have to set the max_length attribute on the Charfields, then the Django ORM deals with the database in the backend. In a lot of cases,...
logicOnAbstractions's user avatar
1 vote
2 answers
1k views

Does it matter to convert integer primary key into equivalent string?

In a PostgreSQL DB, I have a sortable bigint primary key, which is a bit verbose in terms of readability. I want to encode it as base36 or base64, like using char(n). In theory the number of bytes ...
jack2684's user avatar
  • 203
1 vote
2 answers
82 views

Find the greatest difference between each unique record with different timestamps

I have a PostgreSQL table with the following schema and data: CREATE TABLE IF NOT EXISTS T( id uuid PRIMARY KEY, username varchar(15), person varchar(10), tweets int, ...
basebandit's user avatar
1 vote
2 answers
119 views

3 additional varchar(32) keys and also updating them -- too bad for performance?

I have a table in mind which basicaly would look like this: table1 -------- id bigserial autoincrement primary key, token1 varchar(32) unique not null, token2 varchar(32) unique not null, ...
goja3318's user avatar
16 votes
4 answers
54k views

Casting an array of texts to an array of UUIDs

How can I cast an array of texts into an array of UUIDs? I need to do a join between two tables: users and projects. The users table has an array field named project_ids containing the project IDs ...
Sig's user avatar
  • 383
1 vote
1 answer
3k views

Why does ENCODE on BYTEA not seem to have any performance impact?

I am working on a scenario to store strings in hex format of fixed length 64 to database. Obviously the choices are BYTEA and CHAR(64). Initial thought was to enforce a valid hex string storing it ...
Nazar Hussain's user avatar
13 votes
2 answers
105k views

How to use default value of data type as column default?

How can I specify the default value for a column in the DDL? I was pretty sure that the following method worked a while ago: CREATE TABLE test ( col_1 CHAR(12) NOT NULL, col_2 INTEGER NOT NULL ...
user3193317's user avatar
1 vote
3 answers
403 views

Not able to search on zipcode column a table in postgresql

Hello I have a postgresql question about searching on zip codes. Basicly, if I do a: select * from my_table where zip_ = '90210'; I am returned 0 rows. I know for a fact that there is thousands ...
Jeff WR's user avatar
  • 11
6 votes
1 answer
5k views

Conditional INSERT with a nested CTE?

I am trying to figure out if there is a way I can make nested CTEs work for this particular case. Consider the following (highly contrived) scenario which is based on the actual application: there's ...
ahron's user avatar
  • 803
1 vote
1 answer
359 views

Is normalizing further to index on INT FK rather than VARCHAR worth it?

I asked a question about whether or not I should normalize some static data and the general consensus was that it is not necessary since the data is unchanging, thus it is not redundant and will not ...
Adam Thompson's user avatar
1 vote
1 answer
2k views

Index by subkey in jsonb

I have a table payment with a jsonb column application. It contains payments and the total amount for one or more tours. For example, a parent can pay 1000 $ for all his family, e.g. 4 children and ...
stackdave's user avatar
  • 201
4 votes
1 answer
3k views

Nested Loop with Materialize resulting in very slow query on postgresql 9.5

I have an analytics database on Postgres 9.5 I'm taking advantage of constraint partitioning with table inheritance to split up events into monthly tables. The base (parent) table contains no rows. I ...
maxTrialfire's user avatar
  • 1,094

15 30 50 per page