Zeraph Posts - Answer Overflow

Zeraph

•Created by Zeraph on 3/7/2024 in #help

Create index in DESC order

Consider this schema:

export const contactFormSubmission = pgTable(
  'contact_form_submission',
  {
    id: serial('id').primaryKey(),
    name: varchar('name').notNull(),
    email: varchar('email'),
    subject: varchar('subject'),
    message: varchar('message').notNull(),
    showInGuestbook: boolean('show_in_guestbook').default(false),
    submittedAt: timestamp('submitted_at').defaultNow(),
  },
  ({ showInGuestbook, submittedAt }) => ({
    guestbookIdx: index().on(showInGuestbook, submittedAt),
  }),
);

export const contactFormSubmission = pgTable(
  'contact_form_submission',
  {
    id: serial('id').primaryKey(),
    name: varchar('name').notNull(),
    email: varchar('email'),
    subject: varchar('subject'),
    message: varchar('message').notNull(),
    showInGuestbook: boolean('show_in_guestbook').default(false),
    submittedAt: timestamp('submitted_at').defaultNow(),
  },
  ({ showInGuestbook, submittedAt }) => ({
    guestbookIdx: index().on(showInGuestbook, submittedAt),
  }),
);

The index of the query is expected to serve this query only, so I wanna have it in DESC order.

db
    .select()
    .from(contactFormSubmission)
    .where(eq(contactFormSubmission.showInGuestbook, true))
    .orderBy(desc(contactFormSubmission.submittedAt));

db
    .select()
    .from(contactFormSubmission)
    .where(eq(contactFormSubmission.showInGuestbook, true))
    .orderBy(desc(contactFormSubmission.submittedAt));

I.e. the goal:

CREATE INDEX contact_form_submission_show_in_guestbook_submitted_at_index 
ON public.contact_form_submission 
USING btree 
(show_in_guestbook DESC, submitted_at DESC);

CREATE INDEX contact_form_submission_show_in_guestbook_submitted_at_index 
ON public.contact_form_submission 
USING btree 
(show_in_guestbook DESC, submitted_at DESC);

The issue is desc() doesn't seem to do any when declaring the index. How am I supposed to specify DESC in this case?

4 replies

DTDrizzle Team

•Created by Zeraph on 3/7/2024 in #help

Why fields with default value is nullable

Consider this schema:

export const blogMetadata = pgTable('blog_metadata', {
  id: varchar('id').primaryKey(),
  view: integer('view').default(0),
});

export const blogMetadata = pgTable('blog_metadata', {
  id: varchar('id').primaryKey(),
  view: integer('view').default(0),
});

When I do a query:

db.select().from(blogMetadata).where(eq(blogMetadata.id, id))

db.select().from(blogMetadata).where(eq(blogMetadata.id, id))

The resultant type is:

{
  id: string;
  view: number | null
}

{
  id: string;
  view: number | null
}

Why is view nullable in this case? It should always be an integer due to the default value of 0, isn't it? Or am I missing something?

3 replies

Gaming

Programming